[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/給出一些討論，比如分析索引，可能導致統計增加以及主外來鍵因
--//素導致因素給特別注意，自己應該引起注意。

[20211104]12cR2 new index usage tracking.txt

相關文章