Monday, April 22, 2013

OPTIMIZER_USE_PENDING_STATISTICS -- 11g Feature


In the previous releases, gathered statistics are immediately placed into dictionary tables, and started to be used by the optimizer.
Pending_statistics option can be used to place those statistics into pending tables. Thus new statistics can be tested by the sessions which has optimizer_use_pending_statistics set to TRUE.
If the new statistics are found useful, they can be placed on the dictionary tables.
By this method, there is an opportunity to prevent plan regressions that result from incomplete or incorrect statistics.

Pending statistics can be viewed from the USER_%_PENDING_STATS dictionary views.

Pending statistics can be collected by the dbms_stats.set_table_prefs as follows;

--First, set publish option for the table to FALSE;
DBMS_STATS.SET_TABLE_PREFS(‘SCHEMA NAME’,’TABLE_NAME’,‘PUBLISH’,’FALSE’);
--and then collect statistics;
DBMS_STATS.GATHER_TABLE_STATS(‘SCHEMA_NAME’,’TABLE_NAME’);

By this way, collected statistics for the TABLE_NAME will be not published.

Pending statistics can be published by the dbms_stats.publish_pending_stats  as follows;

DBMS_STATS.PUBLISH_PENDING_STATS(‘SCHEMA NAME’,’TABLE_NAME’);

I found this feature very useful, because sometimes you can't anticipate the results of  gathering statistics..

Described above, also shows the new capabilities of DBMS_STATS package on 11g.
DBMS_STATS package has been the most efficient way for gathering statistics since Oracle 8i . Oracle 11g introduces DBMS_STATS.SET_*_PREFS procedure which offers much granularity of control. (DBMS_STATS.SET_PARAM has deprecated..)



No comments :

Post a Comment

If you will ask a question, please don't comment here..

For your questions, please create an issue into my forum.

Forum Link: http://ermanarslan.blogspot.com.tr/p/forum.html

Register and create an issue in the related category.
I will support you from there.