熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle9i新特性-索引監視及注意事項[修正版]

2022-06-13   來源: Oracle 

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

  SQL> connect scott/tiger@conner
Connected to Oraclei Enterprise Edition Release
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=;

  DEPTNO DNAME          LOC

    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  // :: // ::

  SQL>
                     
 

  Oraclei的Bug

  在之前如果你不慎監控了SYSI_OBJAUTH索引並且不幸在重起數據庫之前沒有停止它那麼你的數據庫將會無法啟動並且
不會給出任何錯誤信息

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

  ALTER INDEX SYSI_OBJAUTH MONITORING USAGE

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

  [oracle@jumper oradata]$ sqlplus / as sysdba
SQL*Plus: Release Production on Sat Dec ::

  Copyright (c) Oracle Corporation All rights reserved

  Connected to:
Oraclei Enterprise Edition Release Production
With the Partitioning option
JServer Release Production

  SQL> alter index SYSI_OBJAUTH monitoring usage ;

  Index altered

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

  Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
Database mounted

  此時數據庫掛起而且不會有任何提示在alert<sid>log文件中你可以看到:

  [oracle@jumper bdump]$ tail f alert_connerlog Completed: ALTER DATABASE   MOUNTSat Dec  :: ALTER DATABASE OPENSat Dec  :: LGWR: Primary database is in CLUSTER CONSISTENT modeThread opened at log sequence   Current log# seq# mem# : /opt/oracle/oradata/conner/redologSuccessful open of redo thread Sat Dec  :: SMON: enabling cache recoverySat Dec  :: Restarting dead background process QMNQMN started with pid=

  然後數據庫將會停在此處

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

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

  [oracle@jumper oradata]$ rm rf conner[oracle@jumper oradata]$ cp R connerbak/ conner[oracle@jumper oradata]$ sqlplus / as sysdba
SQL*Plus: Release Production on Sat Dec ::

  Copyright (c) Oracle Corporation All rights reserved

  Connected to an idle instance

  SQL> startup
ORACLE instance started

  Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
Database mounted
Database opened
SQL>
 

   在特殊的情況下你可能需要清除這個v$object_usage視圖中的信息

  Oracle的說法是在下一次收集該對象的索引使用情況時會自動覆蓋上一次的信息不提供清除手段

  稍微研究了一下

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

  create or replace view v$object_usage(index_name table_name monitoring used start_monitoring end_monitoring)asselect ioname tname       decode(bitand(iflags ) NO YES)       decode(bitand(ouflags ) NO YES)       oustart_monitoring       ouend_monitoringfrom sysobj$ io sysobj$ t sysind$ i sysobject_usage ouwhere ioowner# = userenv(SCHEMAID)  and iobj# = ouobj#  and ioobj# = ouobj#  and tobj# = ibo#/
                     
 

  注意到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 // :: // ::

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

  SQL> connect /as sysdba
Connected
SQL> /

  OBJ#      FLAGS START_MONITORING    END_MONITORING

                // :: // ::    
                
 

  實際上我們清除了object_usage表的記錄實際上也就清空了v$object_usage的信息

  SQL> delete from object_usage;

   row deleted

  SQL> commit;

  Commit complete

  SQL> select * from v$object_usage;

  no rows selected
      
 

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


From:http://tw.wingwit.com/Article/program/Oracle/201311/17979.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.