[20231204]建立監測索引ind_m.sql指令碼.txt

--//前幾天在QQ上收到同事的請求,問一臺資料庫伺服器插入緩慢,問我最近動了什麼沒有,隨後還貼了一些對方工程師的建議,就是刪除一

--//些索引.該表一共12個索引,其中我加了4個索引(我建議索引的風格與對方原來的不同),其中1個在原來的基礎上增加了1個欄位(原來

--//的索引我刪除了),相當於我僅僅增加3個索引,我可以確定我增加的索引一定查詢用到的,該表很大,記憶裡目前已經到了18G.

--//說實在要刪除索引我比對方要積極,而且我一般觀察很久以後再刪除,先隱含一段時間後再刪除,而且有一些一看就知道根本不會用的,

--//我直接就刪除了,比如一些科室編碼索引,這些鍵值均勻分佈的,不作count統計根本不會用.我不知道對方如何得出這樣的結論,如何分

--//析的,實際上問題的本質是該伺服器執行在虛擬機器上,可能其他虛擬機器的執行可能影響該伺服器的IO,導致磁碟IO緩慢,這個從dg上

--//可以看出,dg也是執行在虛擬機器上,該機器的磁碟IO就更慢,而且有時候延遲很大的情況.

--//另外說一點,我個人非常不喜歡生產系統執行在虛擬機器上,出現問題可能根本不是該機器的問題,而是其他虛擬伺服器導致的問題,更

--//麻煩的是團隊配合上存在問題,實際上就是相互推諉,我不知道對方如何得出這樣的結論,明顯是一個小白,給這樣的人維護系統要死人

--//的.我記得12c引入改進了索引使用跟蹤,驗證看看是否這些索引現在全部使用.我看了以前的筆記,自己寫一個指令碼驗證看看.

--//Oracle12.2引入了索引使用跟蹤，以取代以前的索引監控。而不是隻判斷是否使用了索引(DBA_OBJECT_USAGE.used)，使用跟蹤提供

--//了一個量化的索引使用統計資料，如訪問次數、每次訪問返回的行數。

--//有兩個檢視V$INDEX_USAGE_INFO 和DBA_INDEX_USAGE 以及3個隱藏引數，用於報告和控制索引使用情況的跟蹤：

--//3個隱藏引數_iut_開頭.

> @ 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.

--//V$INDEX_USAGE_INFO

--//跟蹤自上次重新整理以來的索引使用情況。每15分鐘重新整理一次。每次重新整理後，ACTIVE_ELEM_COUNT被重置為0，LAST_FLUSH_TIME被更新為

--//當前時間。

--//DBA_INDEX_USAGE

--//將物件重新整理到磁碟後，顯示物件級索引使用情況。

$ cat ind_m.sql

-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.

--------------------------------------------------------------------------------

--

-- Name: ind_m.sql

-- Purpose: display index monitor of information

--

-- Author: lfree

-- Usage:

-- @ind_m owner.index_name

-- @ind_m owner.table_name

-- @ind_m index_name

-- @ind_m table_name

--

--------------------------------------------------------------------------------

column INDEX_NAME format a30

column owner format a20

set term off

column v_owner new_value v_owner

column v_table new_value v_table

select

upper(CASE

WHEN INSTR('&1','.') > 0 THEN

SUBSTR('&1',INSTR('&1','.')+1)

ELSE

'&1'

END

) v_table,

CASE WHEN INSTR('&1','.') > 0 THEN

UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))

ELSE

user

END v_owner

from dual;

set term on

SELECT u.owner

, i.table_name

, u.name index_name

, u.total_access_count tot_acc_cnt

, u.total_exec_count tot_exec_cnt

, u.total_rows_returned tot_rows_cnt

, u.bucket_0_access_count b0

, u.bucket_1_access_count b1

, u.bucket_2_10_access_count b2_10

, u.bucket_11_100_access_count b11_100

, u.bucket_101_1000_access_count b101_1k

, u.bucket_1000_plus_access_count b1k

, u.last_used

FROM DBA_INDEX_USAGE u

RIGHT JOIN DBA_INDEXES i

ON i.index_name = u.name and i.owner=u.owner

--// WHERE i.owner = '&&v_owner' and ( i.table_name like '%&&v_table%' or u.name like '%&&v_table%')

WHERE i.owner = '&&v_owner' and ( i.table_name = '&&v_table' or u.name = '&&v_table')

ORDER BY u.last_used;

--//注意如果索引的owner與表的owner不同,可能查詢不到結果!!

--//在生產系統測試看看。

1.環境：

SYS@192.168.100.235:1521/orcl> @ pr

==============================

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.3.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.

2.測試：

SYS@192.168.100.235:1521/orcl> @ ind_m lis.LIS_TEST

OWNER TABLE_NAME INDEX_NAME TOT_ACC_CNT TOT_EXEC_CNT TOT_ROWS_CNT B0 B1 B2_10 B11_100 B101_1K B1K LAST_USED

----- ---------- ------------------------------ ----------- ------------ ------------ ---------- ---------- ---------- ---------- ---------- ---------- -------------------

LIS LIS_TEST IX_LIS_TEST_PHONE_NO 17 17 0 17 0 0 0 0 0 2023-04-17 05:12:37

LIS LIS_TEST IX_LIS_TEST_ORDER_TIME 91 91 87204092 0 0 0 0 0 91 2023-08-18 17:21:45

LIS LIS_TEST IX_LIS_TEST_IDENTITY_ID 9 9 237 3 0 1 5 0 0 2023-11-08 16:04:11

LIS LIS_TEST I_LIS_TEST_AUDIT_TIME 19239 19239 36933968 0 0 0 8196 8233 2810 2023-11-12 17:53:38

LIS LIS_TEST I_LIS_TEST_ORIGINAL_BARCODE 238 238 0 238 0 0 0 0 0 2023-12-05 08:18:01

LIS LIS_TEST I_LIS_TEST_VISIT_NO 372 372 76 354 2 16 0 0 0 2023-12-06 08:49:06

LIS LIS_TEST I_LIS_TEST_TEST_DATE_INST_ID_X 64049733 64005504 5.9003E+10 20129476 7893309 2771919 6815824 19549949 6889360 2023-12-06 15:34:23

LIS LIS_TEST IX_LIS_TEST_PAT_NAME 17694409 17694437 951529674 23213 906092 4176725 10366595 2188793 32993 2023-12-06 15:34:23

LIS LIS_TEST PK_LIS_TEST 5074403702 552348703 1.3347E+11 4554195834 520187451 0 0 0 18625 2023-12-06 15:34:23

LIS LIS_TEST IX_LIS_TEST_PAT_ID 109504279 109495230 3250613103 3660234 9177375 34258917 55652565 6750663 4491 2023-12-06 15:34:23

LIS LIS_TEST IX_LIS_TEST_PAT_BARCODE 16268438 16268469 393980811 122425 529530 6645422 8341394 629049 619 2023-12-06 15:34:23

LIS LIS_TEST IX_LIS_TEST_BARCODE 128143541 134974976 575735257 41331179 86693684 45511 44128 25558 2433 2023-12-06 15:34:23

12 rows selected.

--//I_開頭的索引是我建立的。你可以發現全部索引都使用過，只不過有幾個像IX_LIS_TEST_PHONE_NO很少使用罷了。

[20231204]建立監測索引ind_m.sql指令碼.txt

相關文章