Sunday, July 29, 2007

Monitoring DML operations

A very useful feature introduced in Oracle DB 9i called MONITORING.

This feature monitors DML operations - insert/update/delete (and truncate) on monitored tables.
Table monitoring can be implemented on a table by executing the following statement:
"alter table TABLE_NAME monitoring".

Once monitor option is enable for a table, all DML operations on it are register in a view - sys.DBA_TAB_MODIFICATIONS (based on sys.mon_mods$ table).
The DML counters from this view represent the number of DML operations since the last gather statistics.
The overhead on performance is not to be considerable since this mechanism work on memory and flush changes to DBA_TAB_MODOFICATIONS view periodically.

In 9i, tables should be implemented with monitoring by executing "alter table ... monitoring", but in 10g monitor is enabled by default.

The advantage of this feature is when gathering statistics.
Based on the dba_tab_modifications view we can gather statistics for only tables that have more than X% changes (compared to num_rows in user_tables) since last gather statistics.
Actually, there is no reason for us to re-analyze tables that has no enough changes.

Here is a simple example:

SQL>
SQL> create table test1
2 (A number
3 ,B varchar2(100));

Table created

SQL>
SQL> select monitoring from user_tables where table_name = 'TEST1';

MONITORING
----------
YES

-- In 10g table automatically has monitor option enabled.
-- For 9i you should execute the following:


SQL>
SQL> alter table test1 monitoring;

Table altered

SQL>
SQL> select * from user_tab_modifications;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- -------------

SQL>
SQL> insert into test1 values (1,'a');

1 row inserted

SQL> insert into test1 values (2,'b');

1 row inserted

SQL> insert into test1 values (3,'c');

1 row inserted

SQL> commit;

Commit complete

SQL>
SQL> select * from user_tab_modifications;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- -------------

SQL>
SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

SQL> select * from user_tab_modifications;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --------- -------------
TEST1 3 0 0 7/29/2007 1 NO 0

SQL>



In the next post I will explain how to integrate monitoring feature with Gather Schema Statistics concurrent in Oracle Apps 11i.
The monitoring discovered as very efficient when gathering statistics in Oracle Apps database or any quite large database.

For questions, remarks, clarifications or any issue please leave a comment.

Regards
:-)

10 comments:

Babu B said...

Nice Work !!!

Babu

Aviad said...

Thanks Babu

Aviad

mishka said...

Hi, Aviad.

Actually there is a small mistake:

Monitoring feature was inroduced by Oracle with 8i, however due to implementation bug it was not usefull untill 9i.

Michael Bialik

Aviad said...

Hi Michael,

You are right, thanks for the correction.
I hope you are doing well 

Regards,
Aviad

Anonymous said...

Hi,

What is the monitoring clause in create table used for ?

Raindeer

Aviad said...

Hi Raindeer,

This is the same “monitoring” clause as in the “alter table ... monitoring” statement.
Monitoring can be set at table creation time or alternatively after the table has been created.

Aviad

theni_ganesh said...

Can you tell why after gather_table_stats and flusing still the user tab_modifications not populated?

Here is my test:

SQL> select inserts,updates,deletes from user_tab_modifications
2 where table_name = 'MY_TAB2';

INSERTS UPDATES DELETES
---------- ---------- ----------
12173 0 11999

SQL> insert into my_tab2 select * from all_objects;

12173 rows created.

SQL> commit;

Commit complete.

SQL> select inserts,updates,deletes from user_tab_modifications
2 where table_name = 'MY_TAB2';

INSERTS UPDATES DELETES
---------- ---------- ----------
12173 0 11999

SQL> exec dbms_stats.gather_table_stats(user,'MY_TAB2');

PL/SQL procedure successfully completed.

SQL> select inserts,updates,deletes from user_tab_modifications
2 where table_name = 'MY_TAB2';

no rows selected

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select inserts,updates,deletes from user_tab_modifications
2 where table_name = 'MY_TAB2';

no rows selected

IlayaDhasan said...

Please post comments to this post,so that I get email whenever you post comments on this.

SQL> select inserts,updates,deletes from user_tab_modifications
2 where table_name = 'MY_TAB2';

INSERTS UPDATES DELETES
---------- ---------- ----------
12173 0 11999

SQL> insert into my_tab2 select * from all_objects;

12173 rows created.

SQL> commit;

Commit complete.

SQL> select inserts,updates,deletes from user_tab_modifications
2 where table_name = 'MY_TAB2';

INSERTS UPDATES DELETES
---------- ---------- ----------
12173 0 11999

SQL> exec dbms_stats.gather_table_stats(user,'MY_TAB2');

PL/SQL procedure successfully completed.

SQL> select inserts,updates,deletes from user_tab_modifications
2 where table_name = 'MY_TAB2';

no rows selected

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select inserts,updates,deletes from user_tab_modifications
2 where table_name = 'MY_TAB2';

no rows selected


why user_tab_modifications not populated?

Aviad said...

Hi Ilaya Dhasan/ theni_ganesh,

In order to see the dml count in dba_tab_modification view you need to execute dbms_stats.flush_database_monitoring_info right after the dml.
When you gather table statistics, all dml counters for this table erased. This is the idea, count dml statements to decide whether gather table statistics or not (based on thresholds).

So try the following:

SQL> select inserts,updates,deletes from user_tab_modifications where table_name = 'MY_TAB2';

no rows selected

SQL> insert into my_tab2 select * from all_objects where rownum < 10;

9 rows created.

SQL> commit;

Commit complete.

SQL> select inserts,updates,deletes from user_tab_modifications where table_name = 'MY_TAB2';

no rows selected

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 = 'MY_TAB2';

INSERTS UPDATES DELETES
---------- ---------- ----------
9 0 0

SQL> exec dbms_stats.gather_table_stats('SYSTEM','MY_TAB2');

PL/SQL procedure successfully completed.

SQL> select inserts,updates,deletes from user_tab_modifications where table_name = 'MY_TAB2';

no rows selected

HTH,
Aviad

Anonymous said...

Good