在Oracle9i中,如何監視索引並清除監視資訊

eygle發表於2019-05-21
Oracle索引

對於DML操作來說，索引對於資料庫是一個效能負擔.如果索引沒有被有效的使用,那麼其存在性就值得從新考慮.
從Oracle9i開始,Oracle允許你監視索引的使用


對於DML操作來說，索引對於資料庫是一個效能負擔.如果索引沒有被有效的使用,那麼其存在性就值得從新考慮.
1. 從Oracle9i開始,Oracle允許你監視索引的使用:

SQL> connect scott/tiger@conner
Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0 
Connected as scott

SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
PK_DEPT
PK_EMP

開始監視pk_dept索引:

SQL> alter index pk_dept monitoring usage;

Index altered

在此過程中,如果查詢使用索引,將會記錄下來:

SQL> select * from dept where deptno=10;

DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK

停止監視:

SQL> alter index pk_dept nomonitoring usage;

Index altered

查詢索引使用情況,YES表示在監視過程中索引被使用到:

SQL> select * from v$object_usage;

INDEX_NAME        TABLE_NAME         MONITORING USED START_MONITORING    END_MONITORING
----------------- ------------------ ---------- ---- ------------------- -------------------
PK_DEPT           DEPT               NO         YES  10/28/2004 10:55:19 10/28/2004 10:55:47

SQL>

2.Oracle9i的Bug

在9205之前，如果你不慎監控了SYS.I_OBJAUTH1索引，並且不幸在重起資料庫之前沒有停止它，那麼你的資料庫將會無法啟動，並且
不會給出任何錯誤資訊。

以下這條簡單的語句可以輕易再現這個問題:

'ALTER INDEX SYS.I_OBJAUTH1 MONITORING USAGE'

如果你有了足夠好的備份(嚴重警告，請不要拿你的生產資料庫進行測試)，你可以嘗試一下:


[oracle@jumper oradata]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Dec 4 10:09:30 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> alter index SYS.I_OBJAUTH1 monitoring usage ;

Index altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 80811208 bytes
Fixed Size 451784 bytes
Variable Size 37748736 bytes
Database Buffers 41943040 bytes
Redo Buffers 667648 bytes
Database mounted.

 

此時，資料庫掛起，而且不會有任何提示，在alert.log檔案中，你可以看到:


[oracle@jumper bdump]$ tail -f alert_conner.log 
Completed: ALTER DATABASE   MOUNT
Sat Dec  4 10:09:49 2004
ALTER DATABASE OPEN
Sat Dec  4 10:09:49 2004
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 54
  Current log# 2 seq# 54 mem# 0: /opt/oracle/oradata/conner/redo02.log
Successful open of redo thread 1.
Sat Dec  4 10:09:49 2004
SMON: enabling cache recovery
Sat Dec  4 10:10:33 2004
Restarting dead background process QMN0
QMN0 started with pid=9

然後資料庫將會停在此處。

如果不知道此bug存在，你可能會一籌莫展的。

現在你能做的就是從備份中恢復，或者升級。


[oracle@jumper oradata]$ rm -rf conner
[oracle@jumper oradata]$ cp -R connerbak/ conner
[oracle@jumper oradata]$ sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Dec 4 10:19:07 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 80811208 bytes
Fixed Size 451784 bytes
Variable Size 37748736 bytes
Database Buffers 41943040 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>

 

3. 在特殊的情況下,你可能需要清除這個v$object_usage檢視中的資訊.


Oracle的說法是,在下一次收集該物件的索引使用情況時會自動覆蓋上一次的資訊,不提供清除手段.

稍微研究了一下.

v$object_usage是基於以下基表建立起來的:


create or replace view v$object_usage
(index_name, table_name, monitoring, used, start_monitoring, end_monitoring)
as
select io.name, t.name,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
       ou.start_monitoring,
       ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#
/

 

注意到v$object_usage關鍵資訊來源於OBJECT_USAGE表.
另外我們可以注意一下,此處v$object_usage的查詢基於userenv('SCHEMAID')建立.
所以以不同使用者登入,你是無法看到其他使用者的索引監視資訊的,即使是dba,但是可以從object_usage表中得到.

 

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
PK_DEPT                        DEPT                           NO  YES 10/28/2004 10:55:19 10/28/2004 10:55:47

SQL> select * from object_usage;
select * from object_usage
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> connect /as sysdba
Connected.
SQL> /

      OBJ#      FLAGS START_MONITORING    END_MONITORING
---------- ---------- ------------------- -------------------
      6288          1 10/28/2004 10:55:19 10/28/2004 10:55:47

實際上我們清除了object_usage表的記錄,實際上也就清空了v$object_usage的資訊.

 

 

SQL> delete from object_usage;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from v$object_usage;

no rows selected

 

此操作對資料庫沒有潛在的影響,但是請謹慎使用.作為實驗目的提供.

 

 

本文作者:
eygle,Oracle技術關注者,來自中國最大的Oracle技術論壇itpub.
www.eygle.com是作者的個人站點.你可通過Guoqiang.Gai@gmail.com來聯絡作者.歡迎技術探討交流以及連結交換.

原文出處:

http://www.eygle.com/internal/How.to.Monitor.Index.and.How.to.Clean.out.v$object_usage.htm

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/764/viewspace-120512/，如需轉載，請註明出處，否則將追究法律責任。

相關文章