索引不佳的表现
对于联机交易系统,主要交易查询都必须走索引。索引不佳或未创建索引时,通常会出现SQL执行计划有全表扫描, INDEX SKIP SCAN,执行计划改变,SQL逻辑读高,SQL物理读等相关表现,造成Oracle内部资源争用,产生Oracle等待事件,引发CPU,内存等系统资源增高,最终会导致业务响应缓慢。
案例大放送
结合上期分享索引使用的建议,下面与大家分享一下日常遇到一些索引使用不佳的案例。
(1) 索引缺失造成全表扫描,物理读高,响应时间慢
sql_id 27qn1ubzucqvp 物理读高,执行时间长。
BP_AND_PR_REL_HIST_JRNL表大小17G,表在列JRNL_CRT_TM 没有索引,全表扫描,造成物理读高,SQL性能差。经查看,过滤条件JRNL_CRT_TM的distinct值较大,如下图,该列应创建索引。
说明:这种情况在执行次数少或表中数据少时不会表现出异常,上线后当业务量增加,表中数据增加后就会造成业务影响。对大表查询的SQL语句执行计划走全表扫描最容易导致业务缓慢,同时也和并发量相关。所以,需要我们开发人员未雨绸缪,在开发过程中要有创建索引的意识。
(2) 复合索引组合字段不合理,选择性不高,逻辑读高
sql_id g95h505hhxb89语句逻辑读很高,查看索引IDX_M_SBSTRCVPY_BTCH_INF_HST_3如下:
该索引对应单列ETRUNT_ID,语句中过滤条件字段为etrunt_jrnl_no和ETRUNT_ID,查看列信息如下:
可以发现建立etrunt_jrnl_no和ETRUNT_ID的复合索引其唯一值更高,选择性会更好,能有效降低逻辑读。
(3) 复合索引组合字段顺序不合理,执行计划有INDEX SKIP SCAN,逻辑读高
如上图,某系统一条主要SQL,逻辑读高,执行计划中有INDEX SKIP SCAN,索引如下:
查看列上统计信息,发现列MRCH_ID选择性明显好于BACK_TRAD_DATE,BACK_TRAD_DATE distinct值太低,不适合做前导列。
这种情况下,调整复合索引为(MRCH_ID,BACK_TRAD_DATE)。
(4) 存在冗余索引
IND_AUTO_TSK_SPINF_TBL_N1索引字段(CCSTTRID),IND AUTO_TSK_SPINF_TBL_N2索引字段(CCSTTR_ID, PD_CMPT_NO,APL _BTCH_NO),IND_AUTO_TSK_SPINF_TBL_N1为冗余索引,针对上述insert语句,需要维护索引,冗余索引影响SQL性能。
(5) 繁忙表上索引未分区或分区不合理
繁忙表索引应进行HASH分区,从而避免热点,减少索引分裂的大量出现。 应合理选择分区字段,避免HASH不均。
例如某系统数据库突然有几分钟响应时间长,经查出数据库的主要等待事件:
分析可以发现:索引分裂引发数据库性能问题,该系统时常出现该现象,进一步分析定位到索引THDPTY_DBDF_LIST_PartC。这个索引是为HASH分区索引,,以("TRAD_DATE")为分区建,导致数据分布不均匀,查看每个分区记录数如下。因为并发是在同一时间发生的,针对每天操作不可能均匀散到8个分区,HASH失去意义,需要调整分区建。
(6) 外键字段未建索引
某系统时常报dead lock,影响业务。通过alert log和trace文件,获得如下信息:
判定跟表INFO_APP_COMP有关。获得该表建表语句,发现表INFO_APP_COM的INFO_PLATFORM_ID列上有外键,其对应父表为INFO_PLATFORM。
查看INFO_APP_COM外键列上没有索引。
所以,对于父表INFO_PLATFORM的删除或更新操作,会锁定INFO_APP_COMP上所有记录,当INFO_APP_COMP表上发生修改或更新操作时就容易发生死锁。这种情况必须在外键列上创建索引。
总结语
希望通过上述具体案例,让大家更好理解上一期索引使用的一些建议要求,对Oracle开发工作起到帮助