Wednesday, April 30, 2008

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.

No comments:

Post a Comment

Google