[20211104]12cR2 new index usage tracking.txt

--//Oracle12.2引入了索引使用跟踪，以取代以前的索引监控。而不是只判断是否使用了索引(DBA_OBJECT_USAGE.used)，使用跟踪提供
--//了一个量化的索引使用统计数据，如访问次数、每次访问返回的行数。
--//11g使用V$OBJECT_USAGE视图,我估计很少人使用。

--//有两个视图V$INDEX_USAGE_INFO 和DBA_INDEX_USAGE 以及3个隐藏参数，用于报告和控制索引使用情况的跟踪：

--//V$INDEX_USAGE_INFO
--//跟踪自上次刷新以来的索引使用情况。每15分钟冲洗一次。每次刷新后，ACTIVE_ELEM_COUNT被重置为0，LAST_FLUSH_TIME被更新为
--//当前时间。

--//DBA_INDEX_USAGE
--//将对象刷新到磁盘后，显示对象级索引使用情况。
--//在生产系统简单探究看看:

1.环境：
SYS@127.0.0.1:xxxxx/ddhhh> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@127.0.0.1:xxxxx/ddhhh> @ hide _iut_
NAME                      DESCRIPTION                              DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
------------------------- ---------------------------------------- ------------- ------------- ------------ ----- ---------
_iut_enable               Control Index usage tracking             TRUE          TRUE          TRUE         FALSE IMMEDIATE
_iut_max_entries          Maximum Index entries to be tracked      TRUE          30000         30000        FALSE IMMEDIATE
_iut_stat_collection_type Specify Index usage stat collection type TRUE          SAMPLED       SAMPLED      TRUE  IMMEDIATE
--//缺省_iut_enable=true,也就是缺省索引监控是打开的。
--//如果需要获得准确的统计可以设置_iut_stat_collection_type=ALL.

SYS@127.0.0.1:xxxxx/ddhhh> @ bg m00
ADDR                 PID SPID   PROGRAM
---------------- ------- ------ --------------------------
00000002A0E005A8       9 20287  oracle@oda1 (M001)
00000002C0D72868      62 17664  oracle@oda1 (M000)
00000002C0D7BE78      97 20291  oracle@oda1 (M002)
00000002D0EE4FD0     311 62640  oracle@oda1 (M003)
--//感觉oracle设计不好，后台进程没有简单的说明。

$ cat bgx.sql
select s.program, s.module, s.action, s.sid, p.pid, p.spid
from v$session s, v$process p
where s.paddr=p.addr and S.PROGRAM like upper('%&1%')
order by s.program;

SYS@127.0.0.1:xxxxx/ddhhh> @ bgx m00
PROGRAM                    MODULE       ACTION                          SID     PID SPID
-------------------------- ------------ ----------------------------- ----- ------- ------
oracle@oda1 (M000)         MMON_SLAVE   Intensive AutoTask Dispatcher   608      62 17664
oracle@oda1 (M001)         MMON_SLAVE   Automatic Report Flush         2719       9 20287
oracle@oda1 (M002)         MMON_SLAVE   KDILM background EXEcution     5139      97 20291
oracle@oda1 (M003)         MMON_SLAVE   KDILM background CLeaNup       3330     311 62640
--//猜测后台进程M000 完成这项工作。

SYS@127.0.0.1:xxxxx/ddhhh> select * from v$index_usage_info
  2  @ prxx
==============================
INDEX_STATS_ENABLED           : 1
INDEX_STATS_COLLECTION_TYPE   : 1
ACTIVE_ELEM_COUNT             : 121
ALLOC_ELEM_COUNT              : 210
MAX_ELEM_COUNT                : 30000
FLUSH_COUNT                   : 52
TOTAL_FLUSH_DURATION          : 850315
LAST_FLUSH_TIME               : 2021-11-04 11:08:06.424
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
STATUS_MSG                    :
CON_ID                        : 0
PL/SQL procedure successfully completed.
--//等15分钟看看。
SYS@127.0.0.1:xxxxx/ddhhh> select * from v$index_usage_info
  2  @ prxx
==============================
INDEX_STATS_ENABLED           : 1
INDEX_STATS_COLLECTION_TYPE   : 1
ACTIVE_ELEM_COUNT             : 92
ALLOC_ELEM_COUNT              : 210
MAX_ELEM_COUNT                : 30000
FLUSH_COUNT                   : 53
TOTAL_FLUSH_DURATION          : 881731
LAST_FLUSH_TIME               : 2021-11-04 11:23:09.270
STATUS_MSG                    :
CON_ID                        : 0
PL/SQL procedure successfully completed.
--//基本可以确定15分钟刷新1次。

select * from dba_index_usage;
--//结果不输出了，以后可以利用它确定索引是否有效,当然也存在一些问题。
SYS@127.0.0.1:xxxxx/ddhhh> @desc dba_index_usage
Name                            Null?    Type
------------------------------- -------- ----------------------------
OBJECT_ID                       NOT NULL NUMBER
NAME                            NOT NULL VARCHAR2(128)
OWNER                           NOT NULL VARCHAR2(128)
TOTAL_ACCESS_COUNT                       NUMBER
TOTAL_EXEC_COUNT                         NUMBER
TOTAL_ROWS_RETURNED                      NUMBER
BUCKET_0_ACCESS_COUNT                    NUMBER
BUCKET_1_ACCESS_COUNT                    NUMBER
BUCKET_2_10_ACCESS_COUNT                 NUMBER
BUCKET_2_10_ROWS_RETURNED                NUMBER
BUCKET_11_100_ACCESS_COUNT               NUMBER
BUCKET_11_100_ROWS_RETURNED              NUMBER
BUCKET_101_1000_ACCESS_COUNT             NUMBER
BUCKET_101_1000_ROWS_RETURNED            NUMBER
BUCKET_1000_PLUS_ACCESS_COUNT            NUMBER
BUCKET_1000_PLUS_ROWS_RETURNED           NUMBER
LAST_USED                                DATE
--//还建立几个bucket分别确定访问以及返回的次数，这样可以简单确定索引是否有效。
--//查看视图DBA_INDEX_USAGE定义，可以确定写入wri$_index_usage表。
CREATE OR REPLACE FORCE VIEW SYS.DBA_INDEX_USAGE
(OBJECT_ID, NAME, OWNER, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT,
 TOTAL_ROWS_RETURNED, BUCKET_0_ACCESS_COUNT, BUCKET_1_ACCESS_COUNT, BUCKET_2_10_ACCESS_COUNT, BUCKET_2_10_ROWS_RETURNED,
 BUCKET_11_100_ACCESS_COUNT, BUCKET_11_100_ROWS_RETURNED, BUCKET_101_1000_ACCESS_COUNT, BUCKET_101_1000_ROWS_RETURNED, BUCKET_1000_PLUS_ACCESS_COUNT,
 BUCKET_1000_PLUS_ROWS_RETURNED, LAST_USED)
BEQUEATH DEFINER
AS
(SELECT o.obj#
       ,o.name
       ,u.name
       ,iu.total_access_count
       ,iu.total_exec_count
       ,iu.total_rows_returned
       ,iu.bucket_0_access_count
       ,iu.bucket_1_access_count
       ,iu.bucket_2_10_access_count
       ,iu.bucket_2_10_rows_returned
       ,iu.bucket_11_100_access_count
       ,iu.bucket_11_100_rows_returned
       ,iu.bucket_101_1000_access_count
       ,iu.bucket_101_1000_rows_returned
       ,iu.bucket_1000_plus_access_count
       ,iu.bucket_1000_plus_rows_returned
       ,iu.last_used
   FROM sys.wri$_index_usage iu, sys.obj$ o, sys.user$ u
  WHERE (o.obj# = iu.obj# AND o.owner# = u.user#));
CREATE OR REPLACE PUBLIC SYNONYM DBA_INDEX_USAGE FOR SYS.DBA_INDEX_USAGE;
GRANT SELECT ON SYS.DBA_INDEX_USAGE TO SELECT_CATALOG_ROLE;

--//扫描共享池可以发现执行DML语句如下：
--//5cu0x10yu88sw
MERGE INTO sys.wri$_index_usage iu
     USING DUAL
        ON (iu.obj# = :objn)
WHEN MATCHED
THEN
   UPDATE SET
      iu.total_access_count = iu.total_access_count + :ns
     ,iu.total_rows_returned = iu.total_rows_returned + :rr
     ,iu.total_exec_count = iu.total_exec_count + :ne
     ,iu.bucket_0_access_count = iu.bucket_0_access_count + :nsh0
     ,iu.bucket_1_access_count = iu.bucket_1_access_count + :nsh1
     ,iu.bucket_2_10_access_count = iu.bucket_2_10_access_count + :nsh2_10
     ,iu.bucket_2_10_rows_returned = iu.bucket_2_10_rows_returned + :nrh2_10
     ,iu.bucket_11_100_access_count =
         iu.bucket_11_100_access_count + :nsh11_100
     ,iu.bucket_11_100_rows_returned =
         iu.bucket_11_100_rows_returned + :nrh11_100
     ,iu.bucket_101_1000_access_count =
         iu.bucket_101_1000_access_count + :nsh101_1000
     ,iu.bucket_101_1000_rows_returned =
         iu.bucket_101_1000_rows_returned + :nrh101_1000
     ,iu.bucket_1000_plus_access_count =
         iu.bucket_1000_plus_access_count + :nsh1000plus
     ,iu.bucket_1000_plus_rows_returned =
         iu.bucket_1000_plus_rows_returned + :nrh1000plus
     ,last_used = SYSDATE
WHEN NOT MATCHED
THEN
   INSERT
              (
                 iu.obj#
                ,iu.total_access_count
                ,iu.total_rows_returned
                ,iu.total_exec_count
                ,iu.bucket_0_access_count
                ,iu.bucket_1_access_count
                ,iu.bucket_2_10_access_count
                ,iu.bucket_2_10_rows_returned
                ,iu.bucket_11_100_access_count
                ,iu.bucket_11_100_rows_returned
                ,iu.bucket_101_1000_access_count
                ,iu.bucket_101_1000_rows_returned
                ,iu.bucket_1000_plus_access_count
                ,iu.bucket_1000_plus_rows_returned
                ,iu.last_used
              )
       VALUES
              (
                 :objn
                , :ns
                , :rr
                , :ne
                , :nsh0
                , :nsh1
                , :nsh2_10
                , :nrh2_10
                , :nsh11_100
                , :nrh11_100
                , :nsh101_1000
                , :nrh101_1000
                , :nsh1000plus
                , :nrh1000plus
                ,SYSDATE
              )

SYS@127.0.0.1:xxxxx/ddhhh> @ ashtop session_id,PROGRAM,module,action,event sql_id='5cu0x10yu88sw' trunc(sysdate)-100 sysdate
    Total
  Seconds     AAS %This   SESSION_ID PROGRAM                    MODULE       ACTION                                EVENT FIRST_SEEN          LAST_SEEN
--------- ------- ------- ---------- -------------------------- ------------ ------------------------------------- ----- ------------------- -------------------
        1      .0  100% |        913 oracle@oda2 (M002)         MMON_SLAVE   Index usage tracking statistics flush       2021-11-04 04:21:02 2021-11-04 04:21:02
--//在实例2抓取到1次等待时间，噢前面说明是M002执行的而且在实例2上执行的。
--//在实例2执行如下：
SYS@ddhhh> @ bgx m00
PROGRAM                    MODULE       ACTION                                 SID     PID SPID
-------------------------- ------------ ------------------------------------- ---- ------- ------
oracle@oda2 (M000)         MMON_SLAVE   Monitor FRA Space                      608      62 17135
oracle@oda2 (M001)         MMON_SLAVE   Index usage tracking statistics flush  612     122 20809
oracle@oda2 (M002)         MMON_SLAVE   Auto-Flush Slave Action                913     123 20815
oracle@oda2 (M003)         MMON_SLAVE   Automatic Report Flush                1216     124 20821
--//sid=913可以对上。
--//在实例1执行如下：
SYS@127.0.0.1:xxxxx/ddhhh> @ bgx m00
PROGRAM                    MODULE       ACTION                          SID     PID SPID
-------------------------- ------------ ----------------------------- ----- ------- ------
oracle@oda1 (M000)         MMON_SLAVE   Intensive AutoTask Dispatcher   608      62 17664
oracle@oda1 (M001)         MMON_SLAVE   Automatic Report Flush         2719       9 20287
oracle@oda1 (M002)         MMON_SLAVE   KDILM background EXEcution     5139      97 20291
oracle@oda1 (M003)         MMON_SLAVE   KDILM background CLeaNup       3330     311 62640
--//oracle rac 两台机器还分工，M00N后台进程两边的任务还不同。
--//链接：blog.dbi-services.com/12cr2-new-index-usage-tracking/给出一些讨论，比如分析索引，可能导致统计增加以及主外键因
--//素导致因素给特别注意，自己应该引起注意。