一次分区大表索引整改的案例分析(上)
01
问题描述
在生产库上经常发现执行计划中索引选择不合适导致查询效率低下的情况，针对这种情况，我们可以采用重新收集统计信息（或设定统计信息）、绑定执行计划、增加hint写法（修改代码或后台增加hint）等技术手段来优化查询，但这些方法往往有一些前提条件，比如说统计信息过大无法及时收集需要配置定时任务，绑定的执行计划也不是很理想，绑定变量的值不同不能使用一种hint写法等，这样的结果倒推必须进行索引整改，以提高更好的查询效率，但如果涉及的是一张很大的分区表，索引整改必须很慎重，不然调整不理想可能会引起严重的性能问题，因此，本文想根据这个问题提供一种分析思路和操作步骤，使分区大表的索引调整的操作可以考虑得更全面些，更有效达到理想的查询效果。
02
思考：把问题想清楚
因生产库调整的时间窗口有限，这里就需要考虑全面，尽量让本次索引调整达到更好的效果，本文以A表和B表以TIMEKEY时间字段为索引第一栏位的分区索引调整为主要目标，主要是因为在高并发DML时，这种以时间字段为索引前缀的单边增长索引很容易造成大量索引分裂冲突的enq: TX - index contention等待事件，降低了数据库性能。另一个原因是索引不合适，TIMEKEY字段的条件是按区间值查询，一些查询的选择度不高，扫描成本过高，效率比较低，所以在这样的业务场景下，不能把TIMEKEY字段作为前缀，而需要采用其他字段，另采用单独以TIMEKEY时间字段创建索引的方式，以满足时间区间值的查询，本次调整在生产库停机窗口里进行。
我们在接到分区大表索引整改任务需求后，需要考虑到索引调整涉及以下一些操作：
1.删除没有使用的索引
2.删除重复索引
3.索引存在大量碎片需要重建
4.旧索引是否被做了绑定
5.如何删除旧索引
6.调整原先不合适的索引（新索引字段顺序重建索引）
7.调整异常需要回退
8.统计信息不准确影响执行计划索引选择
9.需要创建测试环境验证调整方案（本次在停机窗口中处理没有涉及）
因此，我们需要列出一些详细步骤以验证这些问题。
03
执行：操作步骤
3.1 查询索引被sql使用情况
3.1.1 查询可疑索引被哪些SQL使用
A表：
B表：
这里需要将这些查询出的sql_id记录下来，以便在索引调整后分析这些sql执行效率情况，作为索引调整是否有改善的评定标准。
3.1.2 查询这些SQL是否做了绑定
selectsql_id,sql_profile, sql_patch,sql_plan_baseline
from v$sql
where sql_id in('27u1xuzp85wf2',
'202mhmdbxmwa2',
'476dghw847hhm',
'9pv2k73abv8kk',
'5cm2whgqbpspj',
'f6f6pdqgjn9up',
'b6wwaatpvsgfu',
'4p2jw9971dzgh',
'494w6gzrqdn6w');
A表索引涉及的SQL是否绑定使用情况：
B表索引涉及的SQL是否绑定使用情况：
可以看到涉及的SQL没有做执行计划绑定，如果有绑定的sql，需要进一步分析调整的索引对绑定有没有直接影响，可能需要在调整索引后重新进行绑定。
3.1.3 查询这些SQL执行计划明细
进一步查询这些sql的执行计划，确定执行效率慢是因为使用了问题索引而导致的性能问题：
select* from table(dbms_xplan.display_cursor('27u1xuzp85wf2',null,'ADVANCED ALLSTATS LASTPEEKED_BINDS'));
注：可以保留这些执行计划，方便索引调整后进行执行计划的比较。
3.1.4 查询对应SQL的运行情况
针对已经产生性能影响的sql可通过以下查询显示每个快照时间段的sql执行情况，为进一步优化提供参考标准。
select*
from(selectto_char(begin_interval_time,'yyyy-mm-ddhh24:mi:ss') begin_time,
a.instance_number,
module,
plan_hash_value,
EXECUTIONS_DELTA exec,
decode(EXECUTIONS_DELTA,
0,
buffer_gets_deltA,
round(BUFFER_GETS_DELTA/EXECUTIONS_DELTA)) per_get,
decode(EXECUTIONS_DELTA,
0,
ROWS_PROCESSED_DELTA,
round(ROWS_PROCESSED_DELTA/EXECUTIONS_DELTA,3)) per_rows,
decode(EXECUTIONS_DELTA,
0,
ELAPSED_TIME_DELTA,
round(ELAPSED_TIME_DELTA /EXECUTIONS_DELTA /1000,
2))time_ms,
decode(EXECUTIONS_DELTA,
0,
DISK_READS_DELTA,
round(DISK_READS_DELTA/EXECUTIONS_DELTA,2)) per_read
fromdba_hist_sqlstat a, DBA_HIST_SNAPSHOT b
wherea.snap_id = b.snap_id
and a.instance_number =b.instance_number
and a.sql_id ='823m4p0358qv7'
orderby1desc)
whererownum<100;
3.2. 分析处理不被使用的索引
3.2.1 监控索引的使用情况
从awr中获取肯定被使用的索引，可排除后再进行索引监控，本次主要考虑监控A表和B表上索引是否都被使用。
selectb.object_owner, b.object_name
from dba_hist_snapshot a, dba_hist_sql_planb,dba_hist_sqlstat c
where a.snap_id =c.snap_id
and b.sql_id = c.sql_id
and b.object_type ='INDEX'
and b.object_name=' &INDEX_NAME'
--替换索引名称
and a.startup_time >(select startup_time from v$instance)
通过把表上的索引和上述语句查询出的索引进行比较，把没有对应的索引进行监控操作，详见3.2.2章节，考虑删除无用索引。
3.2.2 验证索引是否使用
开启索引监控
alter index <INDEX_NAME> monitoringusage;
过一段时间查看，一般是1周时间，如果是涉及月帐的，需要一个月
select * from v$object_usage
关闭
alter index <INDEX_NAME> nomonitoringusage;
3.2.3 将不再被使用的索引备份后删除
备份要删除的不被使用的索引脚本后，删除索引。
dropindex INDEX_NAME;
3.3 分析处理存在碎片的索引
从业务上分析某些经常进行DML操作的表，对其索引进行以下分析操作：
3.3.1 分析索引
查看索引碎片是否严重
analyze index IDX_NMAE validate structure;
索引已分析
select t.name, --索引名
t.lf_rows, --number of leafrows (values in the index)
t.lf_blks,
t.del_lf_rows, --number of deletedleaf rows in the index
(t.del_lf_rows / t.lf_rows)*100 ratio --删除比例
from index_stats t
where t.name='IDX_NAME';
NAME LF_ROWS LF_BLKS DEL_LF_ROWS RATIO
---------------------------------------- ---------- ----------- ----------
IDX_NAME 360000 1300 143643 39.9008333
已用时间: 00: 00: 00.01
如果RATIO值大于30％以上说明索引碎片比较严重，就需要考虑索引重建了，以下是重建索引的方法：
定期重建索引：alter indexindex_name rebuild online;
非组合分区索引
SET ECHO OFF
set termout off
set feedback off
set heading off
set linesize 200
set pagesize 10000
spool c:/partition.sql
select 'alter index ' || index_owner || '.'||index_name ||' rebuild partition ' || PARTITION_NAME || ' nologging parallel;'
from dba_ind_partitions where index_owner='&index_owner'and index_name=&index_name;
spool off
对于组合分区索引
SET ECHO OFF
set termout off
set feedback off
set heading off
set linesize 200
set pagesize 10000
spool c:/subpartition.sql
select 'alter index ' || index_owner || '.'||index_name ||' rebuild subpartition ' || subpartition_name || ' parallel ;'
from dba_ind_subpartitions where index_owner='&index_owner'and index_name='&index_name';
spool off
失效分区索引重建
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.'|| INDEX_NAME ||
'REBUILD SUBPARTITION ' || SUBPARTITION_NAME || ' NOLOGGING online;'
FROMDBA_IND_SUBPARTITIONS
WHEREINDEX_OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
ANDSTATUS = 'UNUSABLE'
UNION ALL
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.'|| INDEX_NAME ||
'REBUILD PARTITION ' || PARTITION_NAME || ' NOLOGGING online;'
FROMDBA_IND_PARTITIONS
WHEREINDEX_OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
ANDSTATUS = 'UNUSABLE'
UNION ALL
SELECT 'alter index ' || OWNER || '.' ||A.INDEX_NAME ||
' REBUILD online nologging;'
FROMDBA_INDEXES A
WHEREOWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
ANDSTATUS = 'UNUSABLE';
3.3.2调整以TIMEKEY时间字段作为第一栏位的分区索引
本次分区索引调整主要目标是调整以TIMEKEY时间字段为索引第一栏位的分区索引，主要是因为在高并发DML时，这种索引容易引起大量索引热块冲突的enq:TX - index contention等待事件，降低了数据库性能。另一个原因是执行计划选择这种索引不合适，TIMEKEY字段的条件是按区间值查询，扫描成本过高，不能把TIMEKEY字段置为索引第一栏位，而其他字段索引对于业务更有选择度。
考虑把TIMEKEY时间字段单独创建索引，以满足时间区间值的查询。
3.3.3查询TIMEKEY字段作为索引第一栏位的索引
select*
from dba_ind_columns t2, dba_segments t3, dba_indexes t4
where t3.segment_type ='INDEX'
and t3.owner ='&OWNER'
--输入具体用户名
and t2.column_position =1
and t2.column_name ='TIMEKEY'
--TIMEKEY字段在索引第一栏位上
and t3.bytes >50*1024*1024*1024
--大于等于1G的索引，根据实际修改
and t2.index_name notlike'%_PK%'
--排除不进行调整的索引
and t2.index_name notlike'%_pk%'
--排除不进行调整的索引
and t2.index_name = t4.index_name
and t4.partitioned ='YES'
--过滤出分区索引
orderby t3.bytes desc
或从ASH中查询可疑索引：
selectt2.index_name
from dba_ind_columns t2,dba_segments t3, dba_indexes t4
where t3.segment_type ='INDEX'
and t3.owner ='&OWNER'
and t2.column_position =1
and t2.column_name ='TIMEKEY'
and t3.bytes >50*1024*1024*1024
and t2.index_name notlike'%_PK%'
and t2.index_name notlike'%_pk%'
and t2.index_name =t4.index_name
and t4.partitioned ='YES'
and t2.index_name in
(selecta.object_name
fromgv$active_session_history t1,
(select*fromdba_objects t where t.object_type ='INDEX') a
wheret1.current_obj# = a.object_id
and t1.EVENT ='enq:TX - index contention');
本次索引调整确定主要修改A表和B表上以TIMEKEY字段为第一栏位的索引。
A表索引信息如下：
B表索引信息如下：
检查发现A表TIMEKEY开头的索引是一个主键，并不需要优化，如果有涉及的SQL低下再考虑直接创建索引。B表TIMEKEY开头的索引涉及IDX01、IDX04是本次需要调整的索引。
3.3.4 单独创建TIMEKEY字段的分区索引
Create index IDX_TIMEKEY on B表(TIMEKEY)nologging local online parallel 8;
Alterindex IDX_TIMEKEY noparallel;
3.3.5 创建排除TIMEKEY字段的组合索引
将TIMEKEY字段放置在索引最后栏位
1. Create index IDX_IDX011 on B表(其他字段顺序, TIMEKEY)nologging local online parallel 8;
Alterindex IDX_IDX011 noparallel;
2. Createindex IDX_IDX041 on B表(其他字段顺序, TIMEKEY) nologging local online parallel 8;
Alterindex IDX_IDX041 noparallel;
3.3.6 查询之前涉及的SQL执行计划是否有变化
select* from table(dbms_xplan.display_cursor('27u1xuzp85wf2',null,'ADVANCED ALLSTATS LASTPEEKED_BINDS'));
注：需要具体分析判断以确定这些SQL的执行性能是否有更好。
3.3.7 修改索引为不可见或不可用
将索引修改为不可见状态，使索引不再被使用
Alter index IDX_IDX01 invisible; --建议使用，visible恢复为可见
Alter index IDX_IDX04 invisible; --建议使用，visible恢复为可见
或将索引修改为不可用状态，使索引不被使用和维护
Alter index IDX_IDX01 unusable; --恢复需要重建
3.3.8 回退本次操作方法_需要具体分析判断
Alter index IDX_IDX01 visible;
Alter index IDX_IDX04 visible;
Drop index IDX_IDX011;
Drop index IDX_IDX041;
3.3.9 删除原TIMEKEY字段为第一栏位的索引
Dropindex idx_indexname;
注：由于索引已经修改为invisible状态，可不急于删除，如果有发现一些SQL执行效率不好还可考虑回退，过一段时间验证无误后再考虑删除。