空华三界。如风卷烟。幻影六塵。犹汤沃雪。廓然無际。唯一真心。

发现数据库中的热表

上一篇 / 下一篇 2008-04-04 12:16:45

发现数据库中的热表

1. 如果你使用的是10g，并且statistics_level=typical or all,那么所有的表实际上是monitoring的状态。此时可以通过dba_tab_modifications来查看每个表自上次gather statistics后发生过的动作，从而找出热表. 为了保证内存中的最新统计数据被刷新到dba_tab_modifications中，在查询之前要使用dbms_stats.flush_database_monitoring_info刷新统计数据

SQL> select table_name, monitoring from dba_tables where wner='PCAI';

TABLE_NAME MONITORIN
-------------------- ---------
PLAN_TABLE YES
TEST YES
TEST1 YES

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

1 select LAST_ANALYZED, a.table_name, inserts, updates, deletes from dba_tables a, all_tab_modifications b
2 where a.table_name=b.table_name and a.owner='PCAI'
3* order by inserts, updates, deletes
SQL> /

no rows selected

SQL> insert into pcai.test values('aa',11);

1 row created.

SQL> insert into pcai.test values('aa',11);

1 row created.

1 select LAST_ANALYZED, a.table_name, inserts, updates, deletes from dba_tables a, all_tab_modifications b
2 where a.table_name=b.table_name and a.owner='PCAI'
3* order by inserts, updates, deletes
SQL> /

no rows selected

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> /

LAST_ANALYZED TABLE_NAME INSERTS UPDATES DELETES
-------------------------- -------------------- ---------- ---------- ----------
29-JAN-2007 22:00:08 TEST 2 0 0

表明从上次29-JAN-2007 的分析之后，TEST被插入两次

2. 下面的语句可以用来找当前的被查询和修改最多的表

select * from
(select
o.owner, o.object_name, sum(tch) TouchTime
from x$bh b , dba_objects o
where b.obj = o.data_object_id
group by o.owner, o.object_name
order by sum(tch) desc)
where rownum <=10

输出结果类似于:
SQL> /

OWNER OBJECT_NAME TOUCHTIME
------------------------------ ------------------------------ ----------
PCAI BIG 655
SYS SYS_IOT_TOP_8802 579
SYSMAN MGMT_JOB_EXEC_IDX04 381
SYSMAN MGMT_FAILOVER_TABLE 347
SYSMAN MGMT_METRIC_COLLECTIONS 277
SYSMAN MGMT_SYSTEM_PERFORMANCE_LOG 267
SYSMAN MGMT_EMD_PING 239
SYSMAN MGMT_JOB_EMD_STATUS_QUEUE 222
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY 171
SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 141

10 rows selected.