In this article, we'll explore some ways to optimize SQL queries that controls run due to how queries are run starting in Assure 9.0 & higher.

Changing the execution profile

There are also a couple of ways we can manipulate when Assure does control data statistics updates. The first way is by changing the various execution profile *_UPDATE_STATISTICS properties to false.

Assure supports execution profiles for capturing and scanning control points - "small", "medium", "large" and "extralarge". There is also a "default" setting, which is configurable (it is the backward compatible "extralarge" in the package provided). Basically, the out of box properties are for "small" to work well with tens-to-hundreds, "medium" to work well with hundreds-to-thousands, "large" to work with thousands-to-tens-of-thousands and "extralarge" to work with many-tens-of-thousands-or-more. The out of box defaults for Assure are to behave like it did before execution profiles (everything is "extralarge").

For medium through extralarge, the out of box configuration is for control data database statistics to be performed. This means you can set those to false. In override.properties you would add:

medium_UPDATE_STATISTICS=false
medium_SCAN_UPDATE_STATISTICS=false
large_UPDATE_STATISTICS=false
large_SCAN_UPDATE_STATISTICS=false
extralarge_UPDATE_STATISTICS=false
extralarge_SCAN_UPDATE_STATISTICS=false

Setting UPDATE_STATS_MIN_UNITS to a higher value

The other way is the UPDATE_STATS_MIN_UNITS property, whose value is a number. By default, this property is simply 1, so all multi-capture or scan control points will update stats, subject to what the above execution profile has set to true. If you choose to set this to a higher value in override.properties, then only control points that have processed (captured or scanned) that many records will do the stats.

Note: Assure will first check if the execution profile does stats, and if it does, then it will check if the value set to UPDATE_STATS_MIN_UNITS has been met.

Reverting to the 8.4 & older query

Beginning with release 9.0, the control data statistics update statement that control points might issue changed. In the older 8.4 release, the following was used:

ANALYZE TABLE {0} ESTIMATE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SAMPLE 20000 ROWS

In release 9.0 & newer the newer statement is:

'{'call DBMS_STATS.GATHER_TABLE_STATS(''@DATABASE_SCHEMA@'',''{0}'')'}'

We have found that, despite the newer query looking better than the old one to most DBAs, the newer one is more comprehensive and can run slower than the old query.

With that in mind, one of the things you could do is override the configuration and put it back the way it was in the older release. To do so, you would put the following in override.properties in the same config folder where IA.properties is for your installation configuration, and then run update-config.

ORACLE_UPDATE_TABLE_STATS_SQL=ANALYZE TABLE {0} ESTIMATE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SAMPLE 20000 ROWS

To recap: some DBA's prefer to say "don't let Assure do it; we do stats overnight, just leave it". So, they might set the UPDATE_STATS_MIN_UNITS to a large value (say a billion) so that Assure doesn't do it at all. We can also have a control point specific minimum units configuration override for situations where you have turned it off but then find that the performance of, say, a recon control point that runs after a multi-capture that captures a lot of new data, has gotten really bad unless the recon cp runs the next day (after the system-wide stats maintenance occurs). If all else fails, you can change the query back to the old 8.4 version.