专栏首页IT大咖说一次分区大表索引整改的案例分析(上)

一次分区大表索引整改的案例分析(上)

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执行效率不好还可考虑回退,过一段时间验证无误后再考虑删除。

本文分享自微信公众号 - IT大咖说(itdakashuo),作者:罗贵林

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 一次分区大表索引整改的案例分析(下)

    确定成功收集统计信息后,发现还是没有效果,在当时操作过程中认为收集统计信息后,oracle没有走上正确的索引就是成本优化器判断错误,于是决定手工绑定走错索引的s...

    IT大咖说
  • NEO区块链开发入门:有实操!

    NEO区块链公开课是NGD(NEO Global Development )、NEL(NewEconoLab)、InnoSpace创新院共同发起的区块链技术学习...

    IT大咖说
  • 前端老牌框架衰退,IMVC(同构 MVC)成未来趋势?

    IT大咖说
  • mysql索引的类型和优缺点

    现在来介绍了数据库索引,及其优、缺点。针对MySQL索引的特点、应用进行了详细的描述。分析了如何避免MySQL无法使用,如何使用EXPLAIN分析查询语句,如何...

    wangxl
  • MySQL索引原理以及查询优化

    一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,...

    yaphetsfang
  • MySQL——索引基础

    本篇文章,我们将从索引基础开始,介绍什么是索引以及索引的几种类型,然后学习如何创建索引以及索引设计的基本原则。

    撸码那些事
  • MySQL优化以及索引的使用

    注意:索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时...

    关忆北.
  • 稀疏索引和稠密索引你了解吗?

    最近参加了一个面试,面试官先问了mysql的数据库的索引的底层数据接口,我回答了:平时都用的是innodb引擎,所以其底层的索引数据类型是B+树。面试官问我用没...

    居士
  • Mysql索引

    Mysql官方对索引的定义为:索引(index)是帮助Mysql高效获取数据的数据结构。

    彼岸舞
  • 索引入门:顺序索引

    之前我对索引的了解基本就是主索引和二级索引,此外还经常见到一些其他概念,如聚集索引和非聚集索引,稀疏索引和密集索引等,今天系统整理一下。

    Apache IoTDB

扫码关注云+社区

领取腾讯云代金券