监控索引使用情况 V$OBJECT_USAGE DBA_INDEX_USAGE 只能看当前用户下的-优快云博客

本文链接：https://blog.youkuaiyun.com/jnrjian/article/details/149977018

Symptoms

Turning on the index monitoring shows the indexes as USED, when the statistics are calculated using DBMS_STATS with CASCADE=TRUE.

Changes

Cause

Sometimes an index needs to be monitored and see if it is used or not.
This information is very helpful in deciding if the indexes, which exist in the database, can be dropped or not.
The index monitoring can be done using
ALTER INDEX index_name MONITORING USAGE for starting to monitor the index and ALTER INDEX index_name NOMONITORING USAGE to stop monitoring the index.
The V$OBJECT_USAGE view provide the result if that specific index is used or not by the CBO (Cost Based Optimizer).

The "USED" column in V$OBJECT_USAGE is changed to YES by the optimizer if the index is being used in an access path.
But there is a situation when the index was never actually used by any user's/application statement, but it appears as used in the V$OBJECT_USAGE view.

The problem occurs when the statistics are calculated using DBMS_STATS (GATHER_SCHEMA_STATS, GATHER_TABLE_STATS or GATHER_DATABASE_STATS) with CASCADE=TRUE

Ex:
SQL> create index indx_ename on emp(ename);

Index created.
SQL> alter index indx_ename monitoring usage;

Index altered.

SQL> select index_name, table_name, monitoring, used from v$object_usage;

INDEX_NAME TABLE_NAME MON USED
------------------------------ ---------------------
INDX_ENAME EMP YES NO <--- Index is monitored but was never used
SQL> begin
2 dbms_stats.gather_schema_stats(ownname => '<Owner>',CASCADE => TRUE);
3 end;
4 /

PL/SQL procedure successfully completed.
After checking again the V$OBJECT_USAGE we can see the index appears as used, when it was not included in any query executed from the user perspective.
SQL> select index_name, table_name, monitoring, used from v$object_usage;

INDEX_NAME TABLE_NAME MON USE
------------------------------ --------------------
INDX_ENAME EMP YES YES <--- Monitored and used
The above result is correct as the index was used in an access path, when the statistics were computed, but also can lead to wrong conclusions, regarding the real usage of the index.

Solution

As any of DBMS_STATS.GATHER_SCHEMA_STATS, GATHER_TABLE_STATS or GATHER_DATABASE_STATS with CASCADE option will changed the indexes to USED in V$OBJECT_USAGE;

To get accurate result regarding the index monitoring, the CASCADE option has not to be used or if this is not possible, at least this situation has to be taken into account.

Lately the problem was solved by Bug 6798910 fixed in 10.2.0.5 and 11.2 patch set. Please check if there is any one offs patch for your OS and install it.
--- 19C已经fix
INDX_ENAME EMP YES YES（NO） <--- Monitored and used

---只能看当前用户下的

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

Even though configured monitoring usage, V$OBJECT_USAGE does not show any entries.

Example)

SQL> connect /as sysdba

SQL> alter index <USER>.<INDEX NAME> monitoring usage;

SQL> select * from v$object_usage;

no rows selected <<<<<<<<< any entries are not displayed by sys user.

Changes

Cause

This is an expected behavior.

As Oracle Database Reference for each versions mentioned, V$OBJECT_USAGE only shows the information which is owned by the current user.

V$OBJECT_USAGE
V$OBJECT_USAGE displays statistics about index usage gathered from the database for the indexes owned by the current user. <<<<<<<<<<<<<<< here
You can use this view to monitor index usage. All indexes that have been used at least once can be monitored and displayed in this view.

Solution

Please connect to a schema user that you intend to check the usage.

SQL> connect <USER>/<PASSWORD>

SQL> select * from v$object_usage;

INDEX_NAME
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
MON USE START_MONITORING END_MONITORING
--- --- ------------------- -------------------
<INDEX NAME>
<TABLE NAME>
YES NO 10/02/2020 09:47:04

Symptoms

You have enabled usage monitoring on some objects using the 'monitoring usage' on some objects, but even after that, a query on the "V$OBJECT_USAGE" view returns no rows or it brings information for other objects and not the one you are looking for.

Changes

Cause

The "V$OBJECT_USAGE" view only retrieves information about object usage of the current connected user/schema.

This is the actual definition extracted from catalog.sql
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') <==== filter to retrieve current user info. only
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
/

Solution

To view the information in the "V$OBJECT_USAGE" view you have to be connected as the user owner of the objects you want to monitor, otherwise you might not find the monitoring information you are looking for.

See the below notes linked in the References section below

Note 203645.1 - How to Monitor Index Usage?
Note 144070.1 - Identifying Unused Indexes
Note 136642.1 - Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command
Note 180902.1 - VIEW: "V$OBJECT_USAGE" Reference Note
Note 1015945.102 - How to Monitor Most Recently Accessed Indexes

Oracle9i Database Administrator's Guide

----12.2 改进

This document explains new features of Index Monitoring in Oracle 12.2.

Solution

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

There is a big improvement in Index Monitoring in Oracle 12.2.

In previous releases, we could monitor index usage as follows.

1. Enable MONITORING for the index.

SQL> alter index <Index_Name> monitoring usage;

2. See if the index has been used by querying v$object_usage.

Reference :

Document 136642.1 Identifying Unused Indexes with the ALTER INDEX MONITORING USAGE Command

This old feature has some limitations such as not knowing out how many times the index has been used.

In 12.2, index monitoring is enabled by default and can track index usage more accurately.

Relevant parameters
~~~~~~~~~~~~~~~~~~~
The underscore parameter "_iut_stat_collection_type" controls index usage monitoring type.
It has two values SAMPLED and ALL. It is SAMPLED by default.
It should be ALL to get the most accurate result. But, it can cause some overhead.
So, it is recommended to set ALL only during the monitoring period.

ALTER SYSTEM SET "_iut_stat_collection_type" = ALL;

Relevant views
~~~~~~~~~~~~~~
V$INDEX_USAGE_INFO keeps track of index usage since the last flush. A flush occurs every 15 minutes. After each flush, ACTIVE_ELEM_COUNT is reset to 0 and LAST_FLUSH_TIME is updated to the current time.

DBA_INDEX_USAGE displays cumulative statistics for each index.

INDEX_STATS_COLLECTION_TYPE=1 if _iut_stat_collection_type = SAMPLED.
INDEX_STATS_COLLECTION_TYPE=0 if _iut_stat_collection_type = ALL.

SQL> DESC v$index_usage_info
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_STATS_ENABLED NUMBER
INDEX_STATS_COLLECTION_TYPE NUMBER
ACTIVE_ELEM_COUNT NUMBER
ALLOC_ELEM_COUNT NUMBER
MAX_ELEM_COUNT NUMBER
FLUSH_COUNT NUMBER
TOTAL_FLUSH_DURATION NUMBER
LAST_FLUSH_TIME TIMESTAMP(3)
STATUS_MSG VARCHAR2(256)
CON_ID NUMBER

DBA_INDEX_USAGE displays object-level index usage once it has been flushed to disk.

SQL> DESC dba_index_usage
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
OWNER NOT NULL VARCHAR2(128)
TOTAL_ACCESS_COUNT NUMBER
TOTAL_EXEC_COUNT NUMBER
TOTAL_ROWS_RETURNED NUMBER
BUCKET_0_ACCESS_COUNT NUMBER
BUCKET_1_ACCESS_COUNT NUMBER
BUCKET_2_10_ACCESS_COUNT NUMBER
BUCKET_2_10_ROWS_RETURNED NUMBER
BUCKET_11_100_ACCESS_COUNT NUMBER
BUCKET_11_100_ROWS_RETURNED NUMBER
BUCKET_101_1000_ACCESS_COUNT NUMBER
BUCKET_101_1000_ROWS_RETURNED NUMBER
BUCKET_1000_PLUS_ACCESS_COUNT NUMBER
BUCKET_1000_PLUS_ROWS_RETURNED NUMBER
LAST_USED DATE

Demo in HR demo schema
~~~~~~~~~~~~~~~~~~~~~~

SQL> conn hr/hr

SQL> alter session set nls_date_format = 'DD-MON-RR hh24:mi:ss';

INDEX_STATS_ENABLED INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT LAST_FLUSH_TIME
------------------- --------------------------- ----------------- -----------------------------------
1 1 0 29-AUG-17 12.16.32.572 PM

SQL> alter session set "_iut_stat_collection_type" = all;