Oracle 12.2中的索引统计被调用情况

本文内容摘自MOS文章，但MOS ID和标题一起写出来就说是敏感词汇，很无语，只能在敏感数字之间加了横杠，官网查看时去掉即可 24696-40.1和1366-42.1

12c之前的版本中，monitoring usage不能查看到index使用的次数，现在12c中得到了解决，如果能像logmnr一样，有更详细的信息就完美了。

相关参数

~~~~~~~~~~~~~~~~~~~

隐含参数“_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

HR 中的演示

~~~~~~~~~~~~~~~~~~~~~~

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;

SQL> select INDEX_STATS_ENABLED, INDEX_STATS_COLLECTION_TYPE, ACTIVE_ELEM_COUNT, LAST_FLUSH_TIME from v$index_usage_info;

INDEX_STATS_ENABLED INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT LAST_FLUSH_TIME

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

1 0 0 29-AUG-17 12.16.32.572 PM

SQL> select OBJECT_ID, NAME, OWNER, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED from dba_index_usage;

no rows selected

SQL> select first_name from employees where employee_id = 100;

FIRST_NAME

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

Steven

SQL> select INDEX_STATS_ENABLED, INDEX_STATS_COLLECTION_TYPE, ACTIVE_ELEM_COUNT, LAST_FLUSH_TIME from v$index_usage_info;

INDEX_STATS_ENABLED INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT LAST_FLUSH_TIME

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

1 0 1 29-AUG-17 12.31.35.020 PM

SQL> select OBJECT_ID, NAME, OWNER, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED from dba_index_usage;

no rows selected

-- 等待15分钟

SQL> select INDEX_STATS_ENABLED, INDEX_STATS_COLLECTION_TYPE, ACTIVE_ELEM_COUNT, LAST_FLUSH_TIME from v$index_usage_info;

INDEX_STATS_ENABLED INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT LAST_FLUSH_TIME

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

1 0 1 29-AUG-17 12.46.37.381 PM

SQL> select OBJECT_ID, NAME, OWNER, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED from dba_index_usage;

OBJECT_ID NAME OWNER TOTAL_ACCESS_COUNT TOTAL_EXEC_COUNT LAST_USED

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

73347 EMP_EMP_ID_PK HR 1 1 29-AUG-17 12:46:37

SQL> select first_name from employees where employee_id = 100;

FIRST_NAME

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

Steven

SQL> select INDEX_STATS_ENABLED, INDEX_STATS_COLLECTION_TYPE, ACTIVE_ELEM_COUNT, LAST_FLUSH_TIME from v$index_usage_info;

INDEX_STATS_ENABLED INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT LAST_FLUSH_TIME

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

1 0 1 29-AUG-17 12.46.37.381 PM

SQL> select OBJECT_ID, NAME, OWNER, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED from dba_index_usage;

OBJECT_ID NAME OWNER TOTAL_ACCESS_COUNT TOTAL_EXEC_COUNT LAST_USED

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

73347 EMP_EMP_ID_PK HR 1 1 29-AUG-17 12:46:37

-- 等待15分钟

SQL> select INDEX_STATS_ENABLED, INDEX_STATS_COLLECTION_TYPE, ACTIVE_ELEM_COUNT, LAST_FLUSH_TIME from v$index_usage_info;

INDEX_STATS_ENABLED INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT LAST_FLUSH_TIME

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

1 0 1 29-AUG-17 01.01.39.587 PM

SQL> select OBJECT_ID, NAME, OWNER, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED from dba_index_usage;

OBJECT_ID NAME OWNER TOTAL_ACCESS_COUNT TOTAL_EXEC_COUNT LAST_USED

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

73347 EMP_EMP_ID_PK HR 2 2 29-AUG-17 13:01:39

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26964624/viewspace-2646892/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26964624/viewspace-2646892/