Oracle9i的索引監視及注意事項_MySQL

來源：懂視網責編：小采時間：2020-11-09 17:35:30

Oracle9i的索引監視及注意事項_MySQL

Oracle9i的索引監視及注意事項_MySQL:ORACLE9 對于DML操作來說，索引對于數據庫是一個性能負擔.如果索引沒有被有效的使用,那么其存在性就值得從新考慮.1. 從Oracle9i開始,Oracle允許你監視索引的使用:SQL> connect scott/tiger@conner Connected to Oracle9i Ent

推薦度：

點擊下載本文 文檔為doc格式

導讀Oracle9i的索引監視及注意事項_MySQL:ORACLE9 對于DML操作來說，索引對于數據庫是一個性能負擔.如果索引沒有被有效的使用,那么其存在性就值得從新考慮.1. 從Oracle9i開始,Oracle允許你監視索引的使用:SQL> connect scott/tiger@conner Connected to Oracle9i Ent

ORACLE9

對于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

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

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

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

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com