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