Friday, May 7, 2010

Relationship between Table monitoring and STATISTICS_LEVEL parameter in 10g

Before Oracle 10g , we have to enable DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement.In 10G, the MONITORING and NOMONITORING keywords have been deprecated. If you do specify these keywords, they are ignored.

In 10g, table-monitoring feature is controlled by the STATISTICS_LEVEL parameter. When STATISTICS_LEVEL is set to BASIC, monitoring is disabled on the table. When STATISTICS_LEVEL is set to TYPICAL, then monitoring is enabled. By default STATISTICS_LEVEL is set to Typical, that is monitoring of tables is enabled.

It is strongly recommended toset STATISTICS_LEVEL to Typical in 10g.
By setting this parameter to BASIC, you will be disabling most of the manageability features in 10g like below:
ASH (Active Session History);
AWR (Automatic Workload Repository);
ASSM(Automatic Shared Memory Management);
ADDM(Automatic Database Diagnostic Monitor).

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. information about how many rows are affected is maintained in the SGA, until periodically (about every 15 minutes) SMON flush the data into the data dictionary.

This data dictionary information is made visible through the tables DBA_TAB_MODIFICATIONS,ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views.

Oracle uses these views to identify tables with stale statistics. Whenever there is 10% change in data in a table, Oracle considers its statistics to be stale.

Since in 10g CBO is used, having up to date statistics is so important to generate good execution plans. Automatic statistics collection job using DBMS_STATS package depends on the monitoring data to determine when to collect statistics on objects with stale objects.

Stats are considered stale when #(INSERTS + UPDATES + DELETES) >= 10% of NUM_ROWS from dba_tables.

Example:

SQL> create table myt1 as select * from dba_objects;

Table created.

SQL> select table_name, last_analyzed from USER_TABLES where table_name = 'MYT1';

TABLE_NAME                     LAST_ANAL
------------------------------ ---------
MYT1

SQL> execute DBMS_STATS.GATHER_TABLE_STATS (ownname=>'OCM', tabname=>'MYT1');

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed from USER_TABLES where table_name = 'MYT1';

TABLE_NAME                     LAST_ANAL
------------------------------ ---------
MYT1                           01-DEC-09

SQL> select inserts, updates, deletes from USER_TAB_MODIFICATIONS where table_name = 'MYT1';

no rows selected

SQL> select count(*) from myt1;

COUNT(*)
----------
52671

So, we have table with 52671 records and statistics is up to date.

Now, lets add a new record and that means really really little change for Oracle in regards of stale statistics:
SQL> insert into myt1 select * from myt1 where rownum<2;    

1 row created.

SQL> commit;

Commit complete.

SQL> select inserts, updates, deletes from USER_TAB_MODIFICATIONS where table_name = 'MYT1';

no rows selected
As you see although monitoring is there, but no records indicating the changes. Because we have to wait 15 minutes or do it manually:
SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> select inserts, updates, deletes from USER_TAB_MODIFICATIONS where table_name = 'MYT1';

INSERTS    UPDATES    DELETES
---------- ---------- ----------
1          0          0
Ok, now lets try statics with option GATHER_STALE - it tells Oracle do not try to gather statistics if it's statistics is up to date:
SQL> exec  DBMS_STATS.GATHER_SCHEMA_STATS (ownname=>'OCM', OPTIONS=>'GATHER STALE');

PL/SQL procedure successfully completed.

SQL> select inserts, updates, deletes from USER_TAB_MODIFICATIONS where table_name = 'MYT1';

INSERTS    UPDATES    DELETES
---------- ---------- ----------
1          0          0
As you see, modification is there again, because according Oracle's calculation, adding one record to a table with 52671 records, can not be reason for stale statistics. So, it did not purged that records from modification history and kept them for future analyzes.

Now, lets do bigger changes:
SQL> insert into myt1 select * from myt1;

52672 rows created.

SQL> commit;

Commit complete.

SQL> select inserts, updates, deletes from USER_TAB_MODIFICATIONS where table_name = 'MYT1';

INSERTS    UPDATES    DELETES
---------- ---------- ----------
1          0          0

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> select inserts, updates, deletes from USER_TAB_MODIFICATIONS where table_name = 'MYT1';

INSERTS    UPDATES    DELETES
---------- ---------- ----------
52673          0          0

Now, it means We have done 50% change in a table. So, Oracle should understand it as really stale statistics:
SQL> exec  DBMS_STATS.GATHER_SCHEMA_STATS (ownname=>'OCM', OPTIONS=>'GATHER STALE');

PL/SQL procedure successfully completed.

SQL> select inserts, updates, deletes from USER_TAB_MODIFICATIONS where table_name = 'MYT1';

no rows selected
Yes, records disappeared from modification history and table's statistics is up to date.

3 comments:

Anonymous said...

very good info. easy to understand
thanks for sharing the info

Anonymous said...

Thanks Teymur. This article helped me to understand importance of statistics_level parameter.

BB said...

This is very informative indeed. But i have a client who wants to generate monthly report about how many INSERT/DELETE/UPDATE were made to which tables, doesn't matter, if a particular table was modified more than once in a month. He may add some tables going forward. So i did not suggest the idea of a trigger as he is not conversant with Oracle. Is there some thing where Oracle stores this kind of data.