DBMS_STATS Enhancements in Oracle 11g Database

DBMS_STATS Enhancements in Oracle 11g Database
Virag Sharma virag123@gmail.com

We know in 10g and 11g there is automatic job that collect stats of database based on certain preferences settings , lets have a look what are these preferences and what are there default values and how DBMS_STATS is different in 11g from 10g

Changing Preferences for Statistics

Preference name	Default Values
CASCADE	AUTO
DEGREE	AUTO
ESTIMATE_PERCENT	AUTO
METHOD_OPT	FOR ALL COLUMN SIZE AUTO
NO_INVALIDATE	AUTO
GRANULARITY	AUTO
PUBLISH ( New in 11g)	TRUE
INCREMENTAL new in 11g	FALSE
STALE_PERCENT (New in 11g)	10

# # In Oracle 10g # $ sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 6 19:04:57 2008 SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual; DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_SAMPLE_SIZE SQL> SQL> SELECT dbms_stats.get_param('method_opt') FROM dual; DBMS_STATS.GET_PARAM('METHOD_OPT') -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE AUTO SQL> SELECT dbms_stats.get_param('GRANULARITY') FROM dual; DBMS_STATS.GET_PARAM('GRANULARITY') -------------------------------------------------------------------------------- AUTO SQL> SELECT dbms_stats.get_param('NO_INVALIDATE') FROM dual; DBMS_STATS.GET_PARAM('NO_INVALIDATE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_INVALIDATE SQL> SELECT dbms_stats.get_param('DEGREE') FROM dual; DBMS_STATS.GET_PARAM('DEGREE') -------------------------------------------------------------------------------- NULL SQL> SELECT dbms_stats.get_param('CASCADE') FROM dual; DBMS_STATS.GET_PARAM('CASCADE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_CASCADE <> SQL> exec dbms_stats.set_param('ESTIMATE_PERCENT','100'); PL/SQL procedure successfully completed. SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual; DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') --------------------------------------------------------------------- 100 SQL> exec dbms_stats.RESET_PARAM_DEFAULTS(); PL/SQL procedure successfully completed. SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual; DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') --------------------------------------------------------------------- DBMS_STATS.AUTO_SAMPLE_SIZE ->

GET_PARAM , RESET_GLOBAL_PREFS_DEFAULTS and SET_PARAM are obsolete in Oracle 11g.

In place of above procedures need to use following procedures GET_PREFS , RESET_GLOBAL_PREF_DEFAULTS and SET_GLOBAL_PREFS

SQL> Select dbms_stats.GET_PREFS('CASCADE') from dual; DBMS_STATS.GET_PREFS('CASCADE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_CASCADE SQL> Select dbms_stats.GET_PREFS('DEGREE') from dual; DBMS_STATS.GET_PREFS('DEGREE') -------------------------------------------------------------------------------- NULL SQL> Select dbms_stats.GET_PREFS('ESTIMATE_PERCENT') from dual; DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_SAMPLE_SIZE SQL> Select dbms_stats.GET_PREFS('METHOD_OPT') from dual; DBMS_STATS.GET_PREFS('METHOD_OPT') -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE AUTO SQL> Select dbms_stats.GET_PREFS('NO_INVALIDATE') from dual; DBMS_STATS.GET_PREFS('NO_INVALIDATE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_INVALIDATE SQL> Select dbms_stats.GET_PREFS('GRANULARITY') from dual; DBMS_STATS.GET_PREFS('GRANULARITY') -------------------------------------------------------------------------------- AUTO SQL> Select dbms_stats.GET_PREFS('PUBLISH') from dual; DBMS_STATS.GET_PREFS('PUBLISH') -------------------------------------------------------------------------------- TRUE SQL> Select dbms_stats.GET_PREFS('INCREMENTAL') from dual; DBMS_STATS.GET_PREFS('INCREMENTAL') -------------------------------------------------------------------------------- FALSE SQL> Select dbms_stats.GET_PREFS('STALE_PERCENT') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT') -------------------------------------------------------------------------------- 10

In Oracle 11g you can set preference not only on database level but on global level , schema level , table level as well. It really give more control to DBA for example on one table you want histograms should collected always or want to set METHOD_OPT => ‘FOR ALL COLUMNS SIZE 254’ on some tables

Procedure for setting preference on global , database , schema and table level are given below

1. SET_GLOBAL_PREFS

2. SET_DATABASE_PREFS

3. SET_SCHEMA_PREFS

4. SET_TABLE_PREFS

• Preference set on global apply for new objects or object which no preference available

• Database level preference will be applied on all objects in the database excluding the tables owned by Oracle. These tables can included by passing TRUE for the add_sys parameter of procedure.

• If you set preference value to NULL , it will set to Oracle default value

>

# #Test case # SQL> create table test1(abc number); Table created. SQL> create table test2(abc number); Table created. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1') -------------------------------------------------------------------------------- 10 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 10 SQL> execute dbms_stats.set_table_prefs('SYS', 'TEST2', 'STALE_PERCENT', '35'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', '20'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1') -------------------------------------------------------------------------------- 20 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> exec dbms_stats.SET_DATABASE_PREFS('STALE_PERCENT', '15'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1') -------------------------------------------------------------------------------- 20 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> exec dbms_stats.set_database_prefs('STALE_PERCENT','30'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'BUG', 'BUG_USER') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','BUG','BUG_USER') -------------------------------------------------------------------------------- 30 SQL> exec dbms_stats.SET_DATABASE_PREFS('STALE_PERCENT', '15'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'BUG', 'BUG_USER') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','BUG','BUG_USER') -------------------------------------------------------------------------------- 15

Published and Pending Statistics

Statistics published by default when system/user/job collect stats. In Oracle 11g we have option to put newly collect stats in pending state and published latter once satisfied by performance testing based on new statistics.

In 10g we have face performance issue when collected stats on OLTP (24 X 7 ) database, probably because stats of one index/ table published before other. So collect stats in pending stat , test it and once satisfied with testing published it.

SQL> col name form a40 SQL> col VALUE form a6 SQL> col ISSES_MODIFIABL VALUE form a6 SQL> col ISSES_MODIFIABL form a6 SQL> col ISSYS_MODIFIABLE form a12 SQL> select name,value,isses_modifiable,issys_modifiable from v$parameter where name='optimizer_use_pending_statistics' ; NAME VALUE ISSES_MODIFIABL ISSYS_MODIFI ---------------------------------------- ------ --------------- ------------ optimizer_use_pending_statistics FALSE TRUE IMMEDIATE

# # Test Case # # By default Publish is set TRUE # SQL> select dbms_stats.get_prefs('PUBLISH') publish from dual PUBLISH --------- TRUE SQL> select dbms_stats.get_prefs('PUBLISH', 'VIRSHARM', 'TEST1') publish from dual; PUBLISH ------------ TRUE # # Setting Publish false for TEST1 table # SQL> exec dbms_stats.set_table_prefs('VIRSHARM', 'TEST1', 'PUBLISH', 'false'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('PUBLISH', 'VIRSHARM', 'TEST1') publish from dual; PUBLISH ------------- FALSE SQL> execute dbms_stats.gather_table_stats('VIRSHARM', 'TEST1'); PL/SQL procedure successfully completed. SQL> select table_name, last_analyzed from user_col_pending_stats where table_name ='TEST1' TABLE_NAME LAST_ANALYZED ---------- --------------- TEST1 30-APR-08 SQL> select table_name, last_analyzed from user_ind_pending_stats where table_name ='TEST1'; no rows selected SQL> select table_name, last_analyzed from user_col_pending_stats where table_name ='TEST1'; TABLE_NAME LAST_ANALYZED ---------- --------------- TEST1 30-APR-08 # # Following views will have no record for table TEST1 # because stats not published # SQL> select table_name, last_analyzed from user_tables where table_name = 'TEST1'; TABLE_NAME LAST_ANALYZED ------------------------------ ------------------- TEST1 SQL> select index_name, last_analyzed from user_indexes where table_name ='TEST1'; no rows selected SQL> select column_name, last_analyzed from user_tab_columns where table_name='TEST1' ; COLUMN_NAME LAST_ANALYZED ------------ --------------- ABC

>

# # Testing # SQL> ALTER SESSION SET optimizer_use_pending_statistics =TRUE; Session altered. # # OR export / import STATS # SQL> exec dbms_stats.create_stat_table('VIRSHARM','STATS'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.export_pending_stats(tabname=>'TEST1',stattab=>'STATS'); PL/SQL procedure successfully completed. # # Use expdp /impdp ot exp/imp to import stats in TEST/QA database # SQL> exec dbms_stats.import_table_stats(ownname=>'VIRSHARM',tabname=>'TEST1', stattab=>'STATS'); select column_name, last_analyzed from user_tab_columns wheretable_name='TEST1';

# # Published stats # Publish the Stats to Data Dictionary for Optimizer Usage # SQL> exec dbms_stats.publish_pending_stats(tabname=>'TEST1'); PL/SQL procedure successfully completed.

If Stats published and performance become worse , then you can restore old version of stats. Oracle will manage the historical statistics repository, purging the statistics on a regular basis, by default every 31 days

By default Oracle keep stats for 31 day after that it purge. Retention can be increased useing following

SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION ( 60 ); PL/SQL procedure successfully completed.