Chinaunix首页 | 论坛 | 博客
  • 博客访问: 980963
  • 博文数量: 358
  • 博客积分: 8185
  • 博客等级: 中将
  • 技术积分: 3751
  • 用 户 组: 普通用户
  • 注册时间: 2008-10-15 16:27
个人简介

The views and opinions expressed all for my own,only for study and test, not reflect the views of Any Company and its affiliates.

文章分类

全部博文(358)

文章存档

2012年(8)

2011年(18)

2010年(50)

2009年(218)

2008年(64)

我的朋友

分类: Oracle

2009-06-05 14:41:24

 
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.
阅读(891) | 评论(0) | 转发(0) |
0

上一篇:ORA-12638

下一篇:Shell编程

给主人留下些什么吧!~~