Oracle有使用某個索引嗎?（如何監控）

Does Oracle use my index or not?

?

One can use the index monitoring feature to check if indexes are used by an application or not. When the MONITORING USAGE property is set for an index, one can query the v$object_usage to see if the index is being used or not. Here is an example:

SQL> CREATE TABLE t1 (c1 NUMBER);

Table created.

?

SQL> CREATE INDEX t1_idx ON t1(c1);

Index created.

?

SQL> ALTER INDEX t1_idx MONITORING USAGE;

Index altered.

?

SQL>

SQL> Prompt this view should be consulted as the owner of the object of interest (e.g. system will mostly see an empty view).

SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

TABLE_NAME???????????????????? INDEX_NAME???????????????????? MON USE

------------------------------ ------------------------------ --- ---

T1???????????????????????????? T1_IDX???????????????????????? YES NO

?

SQL> SELECT * FROM t1 WHERE c1 = 1;

no rows selected

?

SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;

TABLE_NAME????????????? ???????INDEX_NAME???????????????????? MON USE

------------------------------ ------------------------------ --- ---

T1???????????????????????????? T1_IDX???????????????????????? YES YES

To reset the values in the v$object_usage view, disable index monitoring and re-enable it:

ALTER INDEX indexname NOMONITORING USAGE;

ALTER INDEX indexname MONITORING?? USAGE;

轉載于:https://www.cnblogs.com/preftest/archive/2011/11/23/2260019.html

總結

以上是生活随笔為你收集整理的Oracle有使用某个索引吗?（如何监控）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。