在生产库上经常发现执行计划中索引选择不合适导致查询效率低下的情况，针对这种情况，我们可以采用重新收集统计信息（或设定统计信息）、绑定执行计划、增加hint写法（修改代码或后台增加hint）等技术手段来优化查询，但这些方法往往有一些前提条件，比如说统计信息过大无法及时收集需要配置定时任务，绑定的执行计划也不是很理想，绑定变量的值不同不能使用一种hint写法等，这样的结果倒推必须进行索引整改，以提高更好的查询效率，但如果涉及的是一张很大的分区表，索引整改必须很慎重，不然调整不理想可能会引起严重的性能问题，因此，本文想根据这个问题提供一种分析思路和操作步骤，使分区大表的索引调整的操作可以考虑得更全面些，更有效达到理想的查询效果。
02
思考：把问题想清楚
我们在接到分区大表索引整改任务需求后，需要考虑到索引调整涉及以下一些操作：
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
fromv$sql
A表索引涉及的SQL是否绑定使用情况：
B表索引涉及的SQL是否绑定使用情况：
可以看到涉及的SQL没有做执行计划绑定，如果有绑定的sql，需要进一步分析调整的索引对绑定有没有直接影响，可能需要在调整索引后重新进行绑定。
3.1.3查询这些SQL执行计划明细
进一步查询这些sql的执行计划，确定执行效率慢是因为使用了问题索引而导致的性能问题：
注：可以保留这些执行计划，方便索引调整后进行执行计划的比较。
3.1.4查询对应SQL的运行情况
select*
a.instance_number,
module,
plan_hash_value,
EXECUTIONS_DELTAexec,
decode(EXECUTIONS_DELTA,
0,
buffer_gets_deltA,
round(BUFFER_GETS_DELTA/EXECUTIONS_DELTA))per_get,
ROWS_PROCESSED_DELTA,
round(ROWS_PROCESSED_DELTA/EXECUTIONS_DELTA,3))per_rows,
ELAPSED_TIME_DELTA,
round(ELAPSED_TIME_DELTA/EXECUTIONS_DELTA/1000,
2))time_ms,
DISK_READS_DELTA,
round(DISK_READS_DELTA/EXECUTIONS_DELTA,2))per_read
fromdba_hist_sqlstata,DBA_HIST_SNAPSHOTb
wherea.snap_id=b.snap_id
anda.instance_number=b.instance_number
orderby1desc)
whererownum<100;
3.2.分析处理不被使用的索引
3.2.1监控索引的使用情况
从awr中获取肯定被使用的索引，可排除后再进行索引监控，本次主要考虑监控A表和B表上索引是否都被使用。
selectb.object_owner,b.object_name
fromdba_hist_snapshota,dba_hist_sql_planb,dba_hist_sqlstatc
wherea.snap_id=c.snap_id
andb.sql_id=c.sql_id
--替换索引名称
anda.startup_time>(selectstartup_timefromv$instance)
通过把表上的索引和上述语句查询出的索引进行比较，把没有对应的索引进行监控操作，详见3.2.2章节，考虑删除无用索引。
3.2.2验证索引是否使用
开启索引监控
alterindex
select*fromv$object_usage
关闭
alterindex
3.2.3将不再被使用的索引备份后删除
备份要删除的不被使用的索引脚本后，删除索引。
dropindexINDEX_NAME;
3.3分析处理存在碎片的索引
从业务上分析某些经常进行DML操作的表，对其索引进行以下分析操作：
3.3.1分析索引
查看索引碎片是否严重
analyzeindexIDX_NMAEvalidatestructure;
索引已分析
selectt.name,--索引名
t.lf_rows,--numberofleafrows(valuesintheindex)
t.lf_blks,
t.del_lf_rows,--numberofdeletedleafrowsintheindex
(t.del_lf_rows/t.lf_rows)*100ratio--删除比例
fromindex_statst
NAMELF_ROWSLF_BLKSDEL_LF_ROWSRATIO
-----------------------------------------------------------------------
IDX_NAME360000130014364339.9008333
如果RATIO值大于30％以上说明索引碎片比较严重，就需要考虑索引重建了，以下是重建索引的方法：
定期重建索引：alterindexindex_namerebuildonline;
非组合分区索引
SETECHOOFF
settermoutoff
setfeedbackoff
setheadingoff
setlinesize200
setpagesize10000
spoolc:/partition.sql
spooloff
对于组合分区索引
spoolc:/subpartition.sql
失效分区索引重建
FROMDBA_IND_SUBPARTITIONS
UNIONALL
FROMDBA_IND_PARTITIONS
FROMDBA_INDEXESA
3.3.3查询TIMEKEY字段作为索引第一栏位的索引
fromdba_ind_columnst2,dba_segmentst3,dba_indexest4
--输入具体用户名
andt2.column_position=1
--TIMEKEY字段在索引第一栏位上
andt3.bytes>50*1024*1024*1024
--大于等于1G的索引，根据实际修改
--排除不进行调整的索引
andt2.index_name=t4.index_name
--过滤出分区索引
orderbyt3.bytesdesc
或从ASH中查询可疑索引：
selectt2.index_name
andt2.index_namein
(selecta.object_name
fromgv$active_session_historyt1,
wheret1.current_obj#=a.object_id
本次索引调整确定主要修改A表和B表上以TIMEKEY字段为第一栏位的索引。
A表索引信息如下：
B表索引信息如下：
检查发现A表TIMEKEY开头的索引是一个主键，并不需要优化，如果有涉及的SQL低下再考虑直接创建索引。B表TIMEKEY开头的索引涉及IDX01、IDX04是本次需要调整的索引。
3.3.4单独创建TIMEKEY字段的分区索引
CreateindexIDX_TIMEKEYonB表(TIMEKEY)nologginglocalonlineparallel8;
AlterindexIDX_TIMEKEYnoparallel;
3.3.5创建排除TIMEKEY字段的组合索引
将TIMEKEY字段放置在索引最后栏位
1.CreateindexIDX_IDX011onB表(其他字段顺序,TIMEKEY)nologginglocalonlineparallel8;
AlterindexIDX_IDX011noparallel;
2.CreateindexIDX_IDX041onB表(其他字段顺序,TIMEKEY)nologginglocalonlineparallel8;
AlterindexIDX_IDX041noparallel;
3.3.6查询之前涉及的SQL执行计划是否有变化
注：需要具体分析判断以确定这些SQL的执行性能是否有更好。
3.3.7修改索引为不可见或不可用
将索引修改为不可见状态，使索引不再被使用
AlterindexIDX_IDX01invisible;--建议使用，visible恢复为可见
AlterindexIDX_IDX04invisible;--建议使用，visible恢复为可见