Oracle 12C 新特性-索引使用情况跟踪

Oracle 12.2中的索引监控有了很大改进。

在以前的版本中，我们可以如下监视索引使用情况。

1.为索引启用MONITORING。

SQL> alter index <Index_Name> monitoring usage;

2.通过查询v $ object_usage查看索引是否已被使用。

参考：

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

这个旧功能有一些限制，例如不知道索引的使用次数。

在12.2中，默认情况下启用索引监视，可以更准确地跟踪索引使用情况。

相关参数
~~~~~~~~~~~~~~~~~~~
隐含参数“_iut_stat_collection_type”控制索引使用情况监视类型。
它有两个值SAMPLED和ALL。默认为SAMPLED。
可以设置为ALL以获得最准确的结果。 但是它会导致一些开销。
因此，建议仅在特定监控期间设置ALL。

ALTER SYSTEM SET "_iut_stat_collection_type" = ALL;

相关视图
~~~~~~~~~~~~~~
V$INDEX_USAGE_INFO跟踪自上次刷新以来的索引使用情况。 每15分钟刷新一次。 每次刷新后，ACTIVE_ELEM_COUNT将重置为0，LAST_FLUSH_TIME将更新为当前时间。

INDEX_STATS_COLLECTION_TYPE=1 如果_iut_stat_collection_type = SAMPLED.
INDEX_STATS_COLLECTION_TYPE=0 如果 _iut_stat_collection_type = ALL.

 

 

 

实验：

当 INDEX_STATS_COLLECTION_TYPE=1 时，发现并没有捕获到 索引被使用

SQL> /

INDEX_STATS_ENABLED INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT
------------------- --------------------------- -----------------
ALLOC_ELEM_COUNT MAX_ELEM_COUNT FLUSH_COUNT TOTAL_FLUSH_DURATION
---------------- -------------- ----------- --------------------
LAST_FLUSH_TIME
---------------------------------------------------------------------------
STATUS_MSG
--------------------------------------------------------------------------------
    CON_ID
----------
                  1                           0                 1
               1          30000           2                    3

SQL> select * from test where id=1000;

        ID DESCRIPTION                                        CREATED_D
---------- -------------------------------------------------- ---------
      1000 Description for 1000                               01-JAN-18

SQL> explain plan for  select * from test where id=1000;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);                         

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1111023958

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

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time
    |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    30 |     1   (0)| 00:00
:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST  |     1 |    30 |     1   (0)| 00:00
:01 |

|*  2 |   INDEX UNIQUE SCAN         | T1_PK |     1 |       |     0   (0)| 00:00
:01 |

等 刷新后查看：

SQL>  select NAME from dba_index_usage;

NAME
--------------------------------------------------------------------------------
COORD_OP_PRIM

当 INDEX_STATS_COLLECTION_TYPE=0 时，成功捕获到 索引被使用

SQL> /

INDEX_STATS_ENABLED INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT
------------------- --------------------------- -----------------
ALLOC_ELEM_COUNT MAX_ELEM_COUNT FLUSH_COUNT TOTAL_FLUSH_DURATION
---------------- -------------- ----------- --------------------
LAST_FLUSH_TIME
---------------------------------------------------------------------------
STATUS_MSG
--------------------------------------------------------------------------------
    CON_ID
----------
                  1                           0                 1
               1          30000           3                13421
21-APR-21 12.08.49.652 AM

SQL> select * from test where id=1000;

        ID DESCRIPTION                                        CREATED_D
---------- -------------------------------------------------- ---------
      1000 Description for 1000                               01-JAN-18

SQL> select * from test where id=1000;

        ID DESCRIPTION                                        CREATED_D
---------- -------------------------------------------------- ---------
      1000 Description for 1000                               01-JAN-18

等 刷新后查看：

SQL> select name ,TOTAL_ACCESS_COUNT   from dba_index_usage;

NAME
--------------------------------------------------------------------------------
TOTAL_ACCESS_COUNT
------------------
COORD_OP_PRIM
              1004

T1_PK
                 2