Oracle10g is more dependent on collected statistics than in prior versions of Oracle. For example, the default optimizer mode is now FIRST ROWS instead of RULE (which will invoke the cost-based optimizer, CBO). Oracle wants to increase SQL performance by using more accurate statistics when some objects in the SQL have statistics and other objects do not. Prior versions of Oracle uses assumptions (guidelines like 100 blocks in the table, 100 rows in each block, even data distribution and on indexes: 1 level, 25 leaf blocks, 100 distinct key values, and a clustering factor of 800) on objects in a query on objects with no statistics; where others in the query had statistics or simply revert to the Rule-based optimizer (where various index types are weighted against each other) when none of the objects in the query have statistics.

Oracle9 introduced dynamic sampling. This is a method of collecting statistics on either a portion of objects or the entire object (based on the “level”, or amount of sampling to perform) for objects named in a SQL statement that either have no statistics collected at all or have obsolete statistics.

Dynamic sampling is used to improve performance by using a more accurate method of predicating both cardinality and selectivity for tables without statistics or stale statistics. Stale statistics are when the object has had more than 10 percent of change since the last time statistics were collected. Statistics are monitored using Table Monitoring introduced in Oracle9 release 2. If a table is being monitored, its changes are noted and the STALE parameter is invoked when using DMBS_STATS to collect statistics or for dynamic sampling to kick in.

Dynamic sampling issues a SQL statement that randomly samples the table’s blocks (and depending on the level and the SQL,) uses a single table “where clause predicate” to estimate selectivity. Dynamic sampling is not for the quick-running query, but instead for the longer queries where the time to sample is not important to the total query time. Oracle makes the determination to do dynamic sampling based on the number of blocks in the table without statistics. The minimum number of blocks in a table being considered for dynamic sampling is 32. Dynamic sampling will not occur on tables with less than 32 blocks. Oracle, of course, uses these statistics to determine a better explain plan for the SQL. If the SQL is repeating, Oracle continues to benefit from an explain plan that utilized dynamic sample-collected statistics as the explain plan stored in the library cache and is reused as in past versions of Oracle for similar SQL statements.

The OPTIMIZER_DYNAMIC_SAMPLING init.ora parameter was introduced in Oracle9. It has valid settings from 0 to 10, where 0 says ‘No dynamic sampling’ and each level increases the amount of statistic collection that is done.

There is also a new hint, DYNAMIC_SAMPLING(table level), where table is the name of the table to be dynamically sampled and level is the same settings from 0 to 10 (described below).

The default number of blocks that are dynamically sampled is 32. This is the default for both Oracle9 and Oracle10. Perhaps Oracle will allow for this default to be adjusted in future releases but, for now, it is internally set at 32 blocks. Dynamic sampling does not occur on table objects that contain less blocks than this default.

The following are the level descriptions:

• Level 0: Perform no dynamic sampling at all.
• Level 1: Sample tables that appear in join or sub-query conditions that have no indexes and have more blocks than 32 (the default for dynamic sampling)
• Level 2: Dynamically sample all unanalyzed tables that have more than 32 blocks.
• Level 3: Dynamic samples tables using a single column that applies selectivity to the table being sampled in addition to the level 2 rules.
• Level 4: Dynamic samples tables using 2 or more columns that applies selectivity to the table being sampled in addition to the level 3 rules.
• Level 5: Dynamic samples up to 64 blocks on tables using level 4 rules. The table of course has to be larger than 64 blocks in size (2 times the default sampling size).
• Level 6: Dynamic samples up to 128 blocks on tables using level 4 rules. The table, of course, has to be larger than 128 blocks in size (4 times the default sampling size).
• Level 7: Dynamic samples up to 256 blocks on tables using level 4 rules. The table, of course, has to be larger than 256 blocks in size (8 times the default sampling size).
• Level 8: Dynamic samples up to 1024 blocks on tables using level 4 rules. The table, of course, has to be larger than 1024 blocks in size (32 times the default sampling size).
• Level 9: Dynamic samples up to 4096 blocks on tables using level 4 rules. The table has to be larger than 4096 blocks in size (128 times the default sampling size).
• Level 10: Dynamic sampling of all blocks on tables using level 4 rules.

Oracle9 has this init.ora parameter set to Level 0 for no dynamic sampling. Oracle10 has this init.ora parameter set to Level 2 by default.

Oracle is continuing to make improvements in both how it collects statistics and how it uses these statistics. Dynamic sampling should prove to be a useful tool to improve performance of longer-running SQL where statistics either not been collected or have become obsolete (stale).

--

Dan Hotka is a Training Specialist who has over 27 years in the computer industry and over 22 years of experience with Oracle products. He is an internationally recognized Oracle expert with Oracle experience dating back to the Oracle V4.0 days. Dan's latest book is the Oracle10g on Linux by Oracle Press. He is also the author of Oracle9i Development By Example and Oracle8i from Scratch by Que and has co-authored 6 other popular books. He is frequently published in Oracle trade journals, and regularly speaks at Oracle conferences and user groups around the world. Visit his website at www.DanHotka.com. Dan can be reached at dhotka@earthlink.net.