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