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.

11g RAC Database hanged or appears to hanged

11g RAC Database hanged or appears to hanged

Around lunch time 1:00 PM database hanged. When we tried to login as sysdba
our sqlplus session also hanged. we not able to login on either of node of RAC.
Thought to kill all the remote connection , so we will able to login

ps -aef |grep LOCAL=NO |awk '{ print $2}' |xargs kill -9

When your database hanged and you not able to login, we have used following procedure
to create hang analysis dump

sqlplus /nolog SQL> set _prelim on SQL> connect / as sysdba SQL> oradebug setmypid Statement processed. SQL> oradebug unlimit Statement processed. SQL> oradebug -g all hanganalyze 3 SQL> oradebug -g all hanganalyze 3 Cycle 1: (1/1965)--(1/1839) Hang Analysis in /u04/oraout/abcap/11gdiag/diag/rdbms/abcap/abc2ap/trace/abc2ap_diag_9269.trc

We also generate ASH report for that(=hang) duration.
Here is what , hang analysis show

# # Session id is in RED colour for further analysis # Chains most likely to have caused the hang: [a] Chain 1 Signature: 'row cache lock'<='row cache lock' (cycle) Chain 1 Signature Hash: 0x75bdd0c =============================================================================== Cycles: ------------------------------------------------------------------------------- Chain 1: ------------------------------------------------------------------------------- Oracle session identified by: { instance: 1 (abcap.abc1ap) os id: 27244 process id: 343, oracle@abc232 session id: 1839 session serial #: 12 } is waiting for 'row cache lock' with wait info: { p1: 'cache id'=0x7 p2: 'mode'=0x0 p3: 'request'=0x5 time in wait: 0.526566 secs heur. time in wait: 18.534318 secs timeout after: 2.473434 secs wait id: 49 blocking: 2 sessions current sql: short stack: <-ksedsts()+285<-ksdxfstk()+22<-ksdxcb()+1599<-sspuser()+102<-semtimedop()+36<-sskgpwwait()+211<-skgpwwait()+104<-ksliwat()+1256<-kslwaitctx()+135<-kqrigt()+1592<-kqrLockAndPinPo()+420<-kqrpre1()+915<-kqrpre()+57<-kziasfc()+235<-kpolnb()+5279<-kpoauth()+582<-opiodr()+991<-ttcpip()+1066<-opitsk()+1275<-opiino()+882<-opiodr()+991<-opidrv()+462<-sou2o()+91<-opimai_real()+109<-ssthrdmain()+142<-main()+116<-__libc_start_main()+211<-_start()+33 wait history: 1. event: 'row cache lock' wait id: 48 p1: 'cache id'=0x7 time waited: 3.001908 secs p2: 'mode'=0x0 p3: 'request'=0x5 2. event: 'row cache lock' wait id: 47 p1: 'cache id'=0x7 time waited: 3.001615 secs p2: 'mode'=0x0 wait id: 47 p1: 'cache id'=0x7 time waited: 3.001615 secs p2: 'mode'=0x0 p3: 'request'=0x5 3. event: 'row cache lock' wait id: 46 p1: 'cache id'=0x7 time waited: 3.000437 secs p2: 'mode'=0x0 p3: 'request'=0x5 } and is blocked by => Oracle session identified by: { instance: 1 (abcap.abc1ap) os id: 27941 process id: 125, oracle@abc232 session id: 1965 session serial #: 476 } which is waiting for 'row cache lock' with wait info: { p1: 'cache id'=0x7 p2: 'mode'=0x0 p3: 'request'=0x3 time in wait: 0.462402 secs heur. time in wait: 18.536906 secs timeout after: 2.537598 secs wait id: 30 blocking: 2 sessions current sql: short stack: <-ksedsts()+285<-ksdxfstk()+22<-ksdxcb()+1599<-sspuser()+102<-semtimedop()+36<-sskgpwwait()+211<-skgpwwait()+104<-ksliwat()+1256<-kslwaitctx()+135<-kqrigt()+1592<-kqrLockAndPinPo()+420<-kqrpre1()+915<-kqrpre()+57<-kkdlgui()+186<-kziavdb.()+2023<-kziaia.()+220<-kpolnb()+580<-kpoauth()+582<-opiodr()+991<-ttcpip()+1066<-opitsk()+1275<-opiino()+882<-opiodr()+991<-opidrv()+462<-sou2o()+91<-opimai_real()+109<-ssthrdmain()+142<-main()+116<-__libc_start_main()+211<-_start()+33 wait history: 1. event: 'row cache lock' wait id: 29 p1: 'cache id'=0x7 time waited: 3.001776 secs p2: 'mode'=0x0 p3: 'request'=0x3 2. event: 'row cache lock' wait id: 28 p1: 'cache id'=0x7 time waited: 3.001578 secs p2: 'mode'=0x0 p3: 'request'=0x3 3. event: 'row cache lock' wait id: 27 p1: 'cache id'=0x7

When checked above session ( as marked in RED in above windows ) in ASH table.
We found both sessions are belong to “sys” user.

Lets have a look on ASH report, one which we created, when database was hanged

Top User Events Event Event Class % Event Avg Active Sessions resmgr:cpu quantum Scheduler 80.66 55.53 row cache lock Concurrency 7.37 5.08 db file sequential read User I/O 3.55 2.45 null event Other 2.66 1.83 CPU + Wait for CPU CPU 1.90 1.31 Top Event P1/P2/P3 Values Event % Event P1 Value, P2 Value, P3 Value % Activity Parameter 1 Parameter 2 Parameter 3 resmgr:cpu quantum 80.75 "1","0","0" 60.85 location "2","0","0" 17.81 "3","0","0" 2.09 row cache lock 7.38 "10","0","3" 7.31 cache id mode request db file sequential read 3.58 "59","23006","1" 0.20 file# block# blocks <

So ASH report is showing different story , that mean database was not hanged , it appeared to hanged due to event “resmgr:cpu quantum”

As per

Oracle® Database Reference
11g Release 1 (11.1)
Part Number B28320-01

resmgr: cpu quantum

The session is waiting to be allocated a quantum of cpu. This event occurs when the resource manager is enabled and is throttling CPU consumption. To reduce the occurrence of this wait event, increase the CPU allocation for the sessions's current consumer group.

Wait Time: The time the session waited to acquire a CPU quantum

We have not enabled any resource plan , how it come into picture,In Oracle
11g all predefine maintenance windows use DEFAULT_MAINTENANCE_PLAN resource plan and Automatedmaintenance tasks run under its subplan ORA$AUTOTASK_SUB_PLAN supportEmptyParas]-->which has 25% resource allocation.

Possible Work around

Disable resource plan