|
Oracle10g Cost Base Optimizer -Monitoring Attribute-
|
Last time we confirmed that, in Oracle 10g, when statistics_level=typical or all, Monitoring attribute was YES as default.
This time we would like to make sure whether Monitoring attribute does work or not. A table with Monitoring attribute records how many times insert/delete/update are executed in dba_tab_modifications dictionary view as monitoring information.
Environment
Miracle Linux Standard Edition V2.1
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Monitoring information of tables with Monitoring attribute
SQL> select TABLE_NAME,MONITORING from tabs
@ where table_name='EMP2';
TABLE_NAME MONITO
----------- ------
EMP2 YES
SQL> insert into emp2 select * from emp;
14 rows created.
SQL> select * from dba_tab_modifications where table_owner='EMP2';
no rows selected
|
None of the monitoring data is recorded. In fact, monitoring data is recorded in SGA, and SMON writes into dictionary every 15 minutes.
By the following command the monitoring data in SGA can be written into dictionary immediately.
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL> select * from dba_tab_modifications where table_owner='EMP2';
TABLE_NAME PARTITION_NAME
---------- -----------------
EMP2
SUBPARTITION_NAME INSERTS
---------------------- ----------
14
UPDATES DELETES TIMESTAMP TRUNCA DROP_SEGMENTS
---------- ---------- ----------- ------ -------------
0 0 09/22 19:20 NO 0
|
The monitoring data is recorded. The number 14 in INSERTS row shows that insert has been executed 14 times.
Since the insert transaction is not committed yet, what will happen if we execute rollback? Will the monitoring data return to the original status?
Execute rollback on transactions toward tables with Monitoring attribute
SQL> rollback;
Rollback complete.
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL> select * from dba_tab_modifications where table_owner='EMP2';
TABLE_NAME PARTITION_NAME
---------- -----------------
EMP2
SUBPARTITION_NAME INSERTS
---------------------- ----------
14
UPDATES DELETES TIMESTAMP TRUNCA DROP_SEGMENTS
---------- ---------- ----------- ------ -------------
0 0 09/22 19:20 NO 0
|
Since the real data is rollbacked, the monitoring data is supposed to have been inserted even though nothing is actually inserted. However, when will this monitoring data disappear?
The timing when the monitoring data of tables with Monitoring attribute disappears
<Executing shutdown>
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance is shutdown.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1297048 bytes
Variable Size 262419816 bytes
Database Buffers 20971520 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> select * from dba_tab_modifications where table_owner='EMP2';
TABLE_NAME PARTITION_NAME
---------- -----------------
EMP2
SUBPARTITION_NAME INSERTS
---------------------- ----------
14
UPDATES DELETES TIMESTAMP TRUNCA DROP_SEGMENTS
---------- ---------- ----------- ------ -------------
0 0 09/22 19:20 NO 0
|
The monitoring data does not disappear by shutdown.
<Obtaining the statistics>
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP2');
PL/SQL procedure successfully completed.
SQL> select * from dba_tab_modifications where table_owner='EMP2';
no rows selected
|
Now, the monitoring data has disappeared.
Conclusion
1. The monitoring data of tables with Monitoring attribute is recorded in SGA, and SMON writes it into dictionary every 15 minutes.
2. Immediate writing of the data into the dictionary by executing dbms_stats.flush_database_monitoring_info procedure.
3. The monitoring data of tables with Monitoring attribute disappears after the statistics is obtained.
Next time we would like to elucidate the relation between Monitoring attribute, which has become the default since Oracle 10g, and CBO(Cost Base Optimizer).
Takuya Kishimoto
|
|
