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

[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 -- Copyright 2023 lfree. All rights reserved. -- 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很少使用罢了。