ITPub博客

首页 > 数据库 > Oracle > Oracle 12.2中的索引统计被调用情况

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

原创 Oracle 作者:巡完南山巡南山 时间:2019-06-06 08:29:28 0 删除 编辑

本文内容摘自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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2016-04-11

  • 博文量
    39
  • 访问量
    27984