[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很少使用罢了。

ITPub博客

[20231204]建立监测索引ind_m.sql脚本.txt

最新文章