New article on statistics and
the Oracle SQL optimizer
Glen Godrum, and independent Oracle consultant in Houston has
published an excellent DBAZine article on how to influence the
cost-based SQL optimizer:
http://www.dbazine.com/oracle/or-articles/goodrum3.
In this article Goodrum demonstrates how to alter the schema
statistics (with dbms_stats) to change the execution plans for
critical SQL and he also notes the importance of the
clustering_factor metric:
To make the
index statistics match reality for this query we need to change
three things:
·
The number of distinct keys needs to match the number of distinct
values that we used for the column statistics earlier. Again, this
would be the number of rows in the index (500,000) divided by the
expected average number of rows per cycle (25), or 20,000.
·
The number of leaf blocks per key should be the number of leaf
blocks in the index divided by the number of keys.
·
The number of data blocks per key should be the
index cluster factor divided by the number of distinct keys. This is probably the most
important, since it tells the optimizer how many table blocks must
be fetched for each key value in the index.
Godrum also concludes that the only safe way to test the actual
performance of any statistics change is to test it in a full
production environment, and not with a small artificial “proof”:
The only way to know for sure whether a given change
will help or not is to try it in a test database containing
production-sized
tables.
|
