Oracle 9i monitoring table and Index
To implement monitoring of table and index
The action of monitoring table can help us to determine the tables that is uncommodity,while index monitoring can help us to remove the unused index so as to enhance the perofmance.
-------------------------
The following action is operation steps﹕
1.SQL>select * from v$version;
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
2.SQL> create table xiaolong as select * from user_tables;
Table created.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL>
3.Begin monitor the special schema table
SQL>begin
dbms_stats.alter_schema_tab_monitoring('SCHEMA_NAME',TRUE);
END;
/
PL/SQL procedure successfully completed.
4.
SQL>desc user_tab_modifications;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
TRUNCATED VARCHAR2(3)
DROP_SEGMENTS NUMBER
SQL>
SQL> col table_nama for a10
SQL> select table_name , updates from user_tab_modifications;
no rows selected
SQL>
SQL> update xiaolong set table_name = 'monitor_tab';
260 rows updated.
SQL> commit;
Commit complete.
SQL> select table_name , updates from user_tab_modifications;
no rows selected
SQL>
5.
SQL>exec dbms_stats.flush_database_monitoring_info();
PL/SQL procedure successfully completed.
SQL> select table_name , updates from user_tab_modifications;
TABLE_NAME UPDATES
------------------------------------------------------------ ----------
XIAOLONG 260
SQL> select count(1) from xiaolong;
COUNT(1)
----------
260
6.When you achieve the task,you should not forget to close the monitor action of table.
SQL>begin
dbms_stats.alter_schema_tab_monitoring('SCHEMA_NAME',FALSE);
END;
/
PL/SQL procedure successfully completed.
------------------------------------------
Tips and Notes:
1.Before execute the command 'exec dbms_stats.flush_database_monitoring_info()'﹐The related information of monitoring store in sga﹐means that after you execute the command ,you will get the information of monitoring.
2.If the time of createion table is posterior than the time of execution command 'dbms_stats.alter_schema_tab_monitoring('SCHEMA_NAME',TRUE),thus this table will no be in monitoring.
3.Snapshot logs and table can not be in monitoring.
阅读(853) | 评论(0) | 转发(0) |
