优化SQL从而降低CPU资源的使用，从而解决DBTime值异常增长。

2021-08-13 14:52:59

########通过组合索引降低逻辑读，从而降低CPU资源的使用，从而解决DBTime值异常增长。
观察DBTime告警曲线异常增长，从而生成了ASH报告，通过报告知道是CPU资源占用DBTime。从top SQL 看其中几条SQL都是全表扫描
下面是一个SQL的执行计划，我们分析表，表上的字段以及字段数据分布等信息，通过创建一个组合索引消除全表扫描，使得逻辑读降低到之前的大概1/40
从后续观察看DBTime基本保持在1000左右，而之前高冲到3700多。

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 31apzx76zt983, child number 0
-------------------------------------
select
PK,QBID,KHBZ,JC,SJ,SJM,SJBDH,ZMLM,TM,ZCC,LCCC,
SFCC,DZH,DZM,FZH,FZM,DDKYD,DDKYDMC,GD, to_char(DFRQ,'yyyy-mm-dd
hh24:mi:ss') DFRQ, JYZH,CS,ZZ,HC,YY,FYY,JZX,PBS,ZBR,JCR,ZTBZ,
CLBZ,JQBID,TRAINID,to_char(FSSJ,'yyyy-MM-dd hh24:mi:ss') FSSJ,
to_char(JSSJ,'yyyy-mm-dd hh24:mi:ss') JSSJ, to_char(QSSJ,'yyyy-mm-dd
hh24:mi:ss') QSSJ, KKH,CDZT,YWBZ,BWM,JYDDT,YSPK

from xxxx
where qssj between to_date(:1 ,'yyyy-mm-dd hh24:mi:ss') and to_date(:2
,'yyyy-mm-dd hh24:mi:ss') and jc = :3 and sjm = :4 order by qssj

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(TO_DATE(:2,'yyyy-mm-dd
hh24:mi:ss')>=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss'))
5 - filter(TO_DATE(:2,'yyyy-mm-dd
hh24:mi:ss')>=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss'))
6 - filter(("SJM"=:4 AND "JC"=:3 AND "ROUTENAME"<>'模拟发送' AND
"GD"<>'单机'))
7 - access("QSSJ">=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss') AND
"QSSJ"<=TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss'))
8 - filter(TO_DATE(:2,'yyyy-mm-dd
hh24:mi:ss')>=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss'))
9 - filter(("SJM"=:4 AND "JC"=:3 AND "GD"<>'单机' AND
"QSSJ">=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss') AND
"QSSJ"<=TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss') AND "ROUTENAME"<>'模拟发送'))
10 - filter(TO_DATE(:2,'yyyy-mm-dd
hh24:mi:ss')>=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss'))
11 - filter(("SJM"=:4 AND "JC"=:3 AND "GD"<>'单机' AND
"ROUTENAME"<>'模拟发送'))
12 - access("QSSJ">=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss') AND
"QSSJ"<=TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss'))
我们从执行计划分析可以知道对象xxxx肯定不是一张表，因为在执行计划中我们看到三张表，从而可以推断他应该是
用户定义的视图。而这个视图包含对三张表的查询并做union all操作。
而第九步骤执行了全表扫描，再看Predicate Information部分
9 - filter(("SJM"=:4 AND "JC"=:3 AND "GD"<>'单机' AND
"QSSJ">=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss') AND
"QSSJ"<=TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss') AND "ROUTENAME"<>'模拟发送'))
这是我们创建索引的依据，此时的全表扫描是需要从每行记录中过滤数据，设计三个字段sjm,jc,qssj
我们通过查询字段的数据分布知道sjm和qssj适合创建索引，从过滤组合看，我们建议创建sjm和qssj的组合索引
等值查询字段在先。
COLUMN_NAME NDV NUL NUM_NULLS DATA_TYPE LOW_VAL_25 HIGH_VAL_25 LAST_ANALYZED
QSSJ 1406080 Y 0 DATE 78790415161A18 78790807150120 2021-08-09 22:35:50 HYBRID
SJM 61116 Y 0 VARCHAR2 31313030303032 39393939393939 2021-08-09 22:35:50
JC 27362 Y 3 VARCHAR2 2020444634442F34303832 BFE27373342F30393438 2021-08-09 22:35:50 HYBRID

下面创建组合索引，注意此时等值查询在前。

create index ZHGL.idx_LS_xxxx_SJM_QSSJ on ZHGL.LS_xxxx(SJM,QSSJ);

通过统计信息我们看执行计划是否变化。
explain plan for
select
PK,QBID,KHBZ,JC,SJ,SJM,SJBDH,ZMLM,TM,ZCC,LCCC,
SFCC,DZH,DZM,FZH,FZM,DDKYD,DDKYDMC,GD, to_char(DFRQ,'yyyy-mm-dd
hh24:mi:ss') DFRQ, JYZH,CS,ZZ,HC,YY,FYY,JZX,PBS,ZBR,JCR,ZTBZ,
CLBZ,JQBID,TRAINID,to_char(FSSJ,'yyyy-MM-dd hh24:mi:ss') FSSJ,
to_char(JSSJ,'yyyy-mm-dd hh24:mi:ss') JSSJ, to_char(QSSJ,'yyyy-mm-dd
hh24:mi:ss') QSSJ, KKH,CDZT,YWBZ,BWM,JYDDT,YSPK

from ZHGL.xxxx

where qssj between to_date(:1 ,'yyyy-mm-dd hh24:mi:ss') and to_date(:2
,'yyyy-mm-dd hh24:mi:ss') and jc = :3 and sjm = :4 order by qssj;
select * from table (dbms_xplan.display());

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss')>=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss'))
5 - filter(TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss')>=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss'))
6 - filter("SJM"=:4 AND "JC"=:3 AND "ROUTENAME"<>'模拟发送' AND "GD"<>'单机')
7 - access("QSSJ">=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss') AND "QSSJ"<=TO_DATE(:2,'yyyy-mm-dd
hh24:mi:ss'))
8 - filter(TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss')>=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss'))
9 - filter("JC"=:3 AND "GD"<>'单机' AND "ROUTENAME"<>'模拟发送')
10 - access("SJM"=:4 AND "QSSJ">=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss') AND
"QSSJ"<=TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss'))
11 - filter(TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss')>=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss'))
12 - filter("SJM"=:4 AND "JC"=:3 AND "GD"<>'单机' AND "ROUTENAME"<>'模拟发送')
13 - access("QSSJ">=TO_DATE(:1,'yyyy-mm-dd hh24:mi:ss') AND "QSSJ"<=TO_DATE(:2,'yyyy-mm-dd
hh24:mi:ss'))

37 rows selected.
显然执行计划已经变化，第九步LS_xxxx表的数据通过索引找数据了，后续我们通过持续观察每个小时该SQL平均逻辑读
可以看到自从10点创建所有后，逻辑读明显下降，比例差不多是原先的1/40.
创建索引后的逻辑读统计
BTIME PLAN_HASH_VALUE EXECS AVG_ROWS AVG_ELAS AVG_CPUS AVG_LIOS AVG_PIOS
---------- --------------- ---------- ---------- ---------- ---------- ---------- ----------
0812 17:00 2641581618 250 .54 .962335636 .95882328 249025.044 0
0812 20:00 2641581618 356 .853932584 .956745787 .950190767 245281.051 .699438202
0813 07:00 2641581618 3641 .759956056 .955151072 .95122947 245954.167 .321889591
0813 08:00 2641581618 7161 .715402877 .970107745 .965671832 246854.346 .017595308 <<<之前逻辑读是246854.
0813 09:00 2641581618 12011 .710432104 1.0879792 1.07740201 0 .167013571
0813 10:00 3919762757 9776 .659472177 .016625501 .016200727 5433.31403 .154869067 <<<逻辑读降到5433
0813 11:00 3919762757 5816 .641678129 .017110643 .016668694 5775.44395 .116746905
0813 12:00 3919762757 2325 .660215054 .026489935 .025953871 9602.3428 .091612903
0813 13:00 3919762757 7032 .770193402 .020521865 .020023768 7513.56755 .108361775

结论： DBTime中CPU资源占比较高要看top SQL ,从而定位原因，一般是高逻辑读造成，优化的措施就是较少逻辑读，通过索引，缩表
或者业务执行层面减少逻辑读。