【隐式转换】
SQL_ID PLAN_HASH_VALUE Date time No.execs LIO/exec CPUTIM/exec ETIME/exec PIO/exec ROWs/exec------------- --------------- -------------------- ---------- ---------------- ----------- ----------- -------------- -------------3gh2kvcqpxfqm 3702824465 06/25/19_0940_1000 3595 754.22 .02 .02 .00 35.903gh2kvcqpxfqm 3702824465 06/25/19_1000_1020 3153 754.16 .02 .02 .00 41.943gh2kvcqpxfqm 3702824465 06/25/19_1020_1040 3077 754.17 .02 .02 .00 43.993gh2kvcqpxfqm 3702824465 06/25/19_1040_1100 2779 754.15 .02 .02 .00 49.723gh2kvcqpxfqm 3702824465 06/25/19_1100_1120 2917 754.17 .02 .02 .00 48.383gh2kvcqpxfqm 3702824465 06/25/19_1120_1140 2384 754.17 .02 .02 .00 60.203gh2kvcqpxfqm 3702824465 06/25/19_1140_1200 1867 754.19 .02 .02 .00 77.883gh2kvcqpxfqm 3702824465 06/25/19_1200_1220 1089 754.19 .02 .02 .00 134.543gh2kvcqpxfqm 3702824465 06/25/19_1220_1240 892 754.14 .01 .02 .00 165.263gh2kvcqpxfqm 3702824465 06/25/19_1240_1300 820 754.16 .01 .02 .00 180.783gh2kvcqpxfqm 3702824465 06/25/19_1320_1340 1393 753.61 .02 .02 .00 108.363gh2kvcqpxfqm 3702824465 06/25/19_1420_1440 2559 753.85 .02 .02 .00 61.713gh2kvcqpxfqm 3702824465 06/25/19_1440_1500 2575 754.13 .02 .02 .00 62.333gh2kvcqpxfqm 3702824465 06/25/19_1540_1600 2616 754.16 .02 .02 .00 64.29SQL_ID 3gh2kvcqpxfqm--------------------update tablename set usecount=usecount+1,usedate=:1 where userid=:2 and appid =:3Plan hash value: 3702824465---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | | | 6 (100)| || 1 | UPDATE | APPUSEINFO | | | | || 2 | TABLE ACCESS FULL| APPUSEINFO | 1 | 25 | 6 (0)| 00:00:01 |---------------------------------------------------------------------------------SQL_ID NAME DATATYPE_STRING BIND_VALUE LAST_CAPTURED------------- ---------- --------------------- -------------------------------- -------------------3gh2kvcqpxfqm :1 VARCHAR2(128)--3gh2kvcqpxfqm :2 NUMBER 1209 2019-06-25 16:44:453gh2kvcqpxfqm :3 NUMBER 10086 2019-06-25 16:44:45SQL> desc tablename;Name Null? Type----------------------- -------- ----------------xx NUMBER(38)xx NUMBER(38)--USERID VARCHAR2(200)xx VARCHAR2(200)xx NUMBER(38)
【没有绑定变量】
异常等待事件（read by other session）告警。
经整改修复后，其优化效果如下：
--优化前，其逻辑读为 431617.88 ，执行时间为 135.66 s；
--优化后，其逻辑读为 1909.23 ，执行时间为 0.03 s；
--详情如下：
--优化前，其逻辑读为 431617.88 ，执行时间为 135.66 s；
SQL_ID 2u21mdmdcqtb9, child number 2-------------------------------------select sum(tasknum)from (select count(*) as tasknumfrom tablenamewhere (task_status = '08' or task_status = '13')and busi_code in ('CAR_WHOLE', 'CAR_PRICE', 'CAR_TEAM')and (oper_type = 'CarSingle_5' or oper_type = '10')and task_time >= to_date('2019-03-01', 'YYYY-MM-DD')and task_time <=to_date('2019-03-31 23:59', 'YYYY-MM-DD hh24:mi')and worker in ('804995')union allselect count(*) as tasknumfrom tablename1where (task_status = '08' or task_status = '13')and busi_code in ('CAR_WHOLE', 'CAR_PRICE', 'CAR_TEAM')and (oper_type = 'CarSingle_5' or oper_type = '10')and task_time >= to_date('2019-03-01', 'YYYY-MM-DD')and task_time <=to_date('2019-03-31 23:59', 'YYYY-MM-DD hh24:mi')and worker in ('804995'))
--优化后，其逻辑读为 1909.23 ，执行时间为 0.03 s；
SQL_ID 4p2gur94kdn7n--------------------select sum(tasknum)from (select count(*) as tasknumfrom tablenamewhere (task_status = '08' or task_status = '13')and busi_code in ('CAR_WHOLE', 'CAR_PRICE', 'CAR_TEAM')and (oper_type = 'CarSingle_5' or oper_type = '10')and task_time >= to_date(:1, 'YYYY-MM-DD')and task_time <= to_date(:2, 'YYYY-MM-DD hh24:mi')and worker in (:3)union allselect count(*) as tasknumfrom tablename1where (task_status = '08' or task_status = '13')and busi_code in ('CAR_WHOLE', 'CAR_PRICE', 'CAR_TEAM')and (oper_type = 'CarSingle_5' or oper_type = '10')and task_time >= to_date(:4, 'YYYY-MM-DD')and task_time <= to_date(:5, 'YYYY-MM-DD hh24:mi')and worker in (:6))Plan hash value: 2349846031--------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 19 (100)| || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | VIEW | | 2 | 26 | 19 (0)| 00:00:01 || 3 | UNION-ALL | | | | | || 4 | SORT AGGREGATE | | 1 | 40 | | || 5 | FILTER | | | | | || 6 | TABLE ACCESS BY INDEX ROWID| tablename | 1 | 40 | 7 (0)| 00:00:01 || 7 | INDEX RANGE SCAN | IDX_EPS_TRACE_INFO_02 | 3 | | 4 (0)| 00:00:01 || 8 | SORT AGGREGATE | | 1 | 43 | | || 9 | FILTER | | | | | || 10 | TABLE ACCESS BY INDEX ROWID| tablename1 | 1 | 43 | 12 (0)| 00:00:01 || 11 | INDEX RANGE SCAN | IDX_EPS_TRACE_INFO_01_OLD | 11 | | 4 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------------------
【索引设计不合理】
1、索引跳跃扫描
根据业务反馈，该select查询语句反映慢，该语句为单表查询语句。
其执行计划走索引跳跃扫描（INDEX SKIP SCAN），逻辑读为42213
SQL语句：
--优化前，执行时间为00:00:04.10，逻辑读为42262；
--优化后，执行时间为00:00:00.17，逻辑读为472；
详情如下：
Plan hash value: 1441683948------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 24432 (100)| 950 |00:00:00.38 | 42213 | 3 ||* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| tablename | 1 | 68 | 24432 (1)| 950 |00:00:00.38 | 42213 | 3 ||* 2 | INDEX SKIP SCAN | IDX_TBL_TSK_INFO_07 | 1 | 27435 | 423 (1)| 30328 |00:00:00.18 | 12683 | 3 |------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter((((TO_NUMBER("T"."APPDT")<=20180907 AND TO_NUMBER("T"."APPDT")>=20180907 AND ("T"."TASKSTA"='10' OR "T"."TASKSTA"IS NULL)) OR (TO_NUMBER("T"."PREFNSHTM")<=20180907 AND TO_NUMBER("T"."PREFNSHTM")>=20180907 ANDINTERNAL_FUNCTION("T"."TASKSTA"))) AND "T"."TASKSTA"<>'95' AND "T"."BQTYPE"<>'TLTB' AND "T"."BQTYPE"<>'TLYT' AND"T"."BQTYPE"<>'TLSH' AND INTERNAL_FUNCTION("T"."BQTYPE")))2 - access("T"."BRANCH"='00000000000023')filter("T"."BRANCH"='00000000000023')
--表的统计信息失效情况
WNER TABLE_NAME PARTITION_NA OBJECT_TYPE STALE_STATS LAST_ANALYZED------------ ------------------------- ------------ ------------------------ ----------- -------------------OWN TABLE_NAME TABLE YES 2018-09-08 20:12:06OWN TABLE_NAME P10 PARTITION YES 2018-09-08 20:47:18OWN TABLE_NAME P11 PARTITION YES 2018-09-08 20:47:29OWN TABLE_NAME P12 PARTITION YES 2018-09-08 20:47:40OWN TABLE_NAME P13 PARTITION YES 2018-09-08 20:47:50OWN TABLE_NAME P14 PARTITION YES 2018-09-08 20:48:01OWN TABLE_NAME P15 PARTITION YES 2018-09-08 20:48:11OWN TABLE_NAME P16 PARTITION YES 2018-09-08 20:48:21OWN TABLE_NAME P17 PARTITION YES 2018-09-08 20:48:31OWN TABLE_NAME P18 PARTITION YES 2018-09-08 20:48:41OWN TABLE_NAME P19 PARTITION YES 2018-09-08 20:48:52OWN TABLE_NAME P20 PARTITION YES 2018-09-08 20:49:02OWN TABLE_NAME P21 PARTITION YES 2018-09-08 20:49:12OWN TABLE_NAME P22 PARTITION YES 2018-09-08 20:49:22OWN TABLE_NAME P23 PARTITION YES 2018-09-08 20:49:33OWN TABLE_NAME P24 PARTITION YES 2018-09-08 20:49:43OWN TABLE_NAME P25 PARTITION YES 2018-09-08 20:49:53OWN TABLE_NAME P26 PARTITION YES 2018-09-08 20:50:04OWN TABLE_NAME P27 PARTITION YES 2018-09-08 20:50:14OWN TABLE_NAME P28 PARTITION YES 2018-09-08 20:50:24OWN TABLE_NAME P29 PARTITION YES 2018-09-08 20:50:34OWN TABLE_NAME P30 PARTITION YES 2018-09-08 20:50:44OWN TABLE_NAME P31 PARTITION YES 2018-09-08 20:50:54OWN TABLE_NAME P32 PARTITION YES 2018-09-08 20:51:03OWN TABLE_NAME P01 PARTITION YES 2018-09-08 20:45:29OWN TABLE_NAME P02 PARTITION YES 2018-09-08 20:45:45OWN TABLE_NAME P03 PARTITION YES 2018-09-08 20:45:58OWN TABLE_NAME P04 PARTITION YES 2018-09-08 20:46:11OWN TABLE_NAME P05 PARTITION YES 2018-09-08 20:46:23OWN TABLE_NAME P06 PARTITION YES 2018-09-08 20:46:34OWN TABLE_NAME P07 PARTITION YES 2018-09-08 20:46:45OWN TABLE_NAME P08 PARTITION YES 2018-09-08 20:46:57OWN TABLE_NAME P09 PARTITION YES 2018-09-08 20:47:07
--表的索引情况
TABLE_OWNERER TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION--------------- ------------------------- ------------------------------ ------------------------- ---------------OWNER TABLE_NAME IDX_TABLE_NAME_01 OPRNOW 1OWNER TABLE_NAME IDX_TABLE_NAME_01 TASKSTA 2OWNER TABLE_NAME IDX_TABLE_NAME_01 TASKID 3OWNER TABLE_NAME IDX_TABLE_NAME_01 PRIORITY 4OWNER TABLE_NAME IDX_TABLE_NAME_01 MODNO 5OWNER TABLE_NAME IDX_TABLE_NAME_02 PREFNSHTM 1OWNER TABLE_NAME IDX_TABLE_NAME_02 CITY 2OWNER TABLE_NAME IDX_TABLE_NAME_02 BRANCH 3OWNER TABLE_NAME IDX_TABLE_NAME_02 COUNTY 4OWNER TABLE_NAME IDX_TABLE_NAME_03 OPRNO 1OWNER TABLE_NAME IDX_TABLE_NAME_03 SCANSTAT 2OWNER TABLE_NAME IDX_TABLE_NAME_03 TASKSTA 3OWNER TABLE_NAME IDX_TABLE_NAME_04 CHATFLAG 1OWNER TABLE_NAME IDX_TABLE_NAME_05 TASKID 1OWNER TABLE_NAME IDX_TABLE_NAME_05 MODNO 2OWNER TABLE_NAME IDX_TABLE_NAME_06 SYS_NC00096$ 1OWNER TABLE_NAME IDX_TABLE_NAME_06 SYS_NC00097$ 2OWNER TABLE_NAME IDX_TABLE_NAME_06 APPDT 3--OWNER TABLE_NAME IDX_TABLE_NAME_07 CITY 1 -->执行计划中使用的索引--OWNER TABLE_NAME IDX_TABLE_NAME_07 APPDT 2--OWNER TABLE_NAME IDX_TABLE_NAME_07 BRANCH 3--OWNER TABLE_NAME IDX_TABLE_NAME_07 COUNTY 4OWNER TABLE_NAME IDX_TABLE_NAME_08 APP_SENDMSG 1OWNER TABLE_NAME IDX_TABLE_NAME_08 BQTYPE 2OWNER TABLE_NAME IDX_TABLE_NAME_09 REQ_SYS_NO 1OWNER TABLE_NAME IDX_TABLE_NAME_10 TSKOPRNO 1OWNER TABLE_NAME IDX_TABLE_NAME_10 APPDT 2OWNER TABLE_NAME IDX_TABLE_NAME_11 VERDICTDT 1OWNER TABLE_NAME IDX_TABLE_NAME_12 FFOID 1OWNER TABLE_NAME IDX_TABLE_NAME_13 APPDT 1OWNER TABLE_NAME IDX_TABLE_NAME_13 APPTM 2OWNER TABLE_NAME IDX_TABLE_NAME_14 SERVNO 1OWNER TABLE_NAME IDX_TABLE_NAME_14 COMMNAME 2OWNER TABLE_NAME IDX_TABLE_NAME_15 BQTYPE 1OWNER TABLE_NAME IDX_TABLE_NAME_15 CUSTIDNUM 2OWNER TABLE_NAME IDX_TABLE_NAME_20 OPRNOW 1OWNER TABLE_NAME IDX_TABLE_NAME_20 TASKSTA 2OWNER TABLE_NAME IDX_TABLE_NAME_20 TASKID 3OWNER TABLE_NAME IDX_TABLE_NAME_21 YD_CITY 1OWNER TABLE_NAME IDX_TABLE_NAME_21 APPDT 2OWNER TABLE_NAME IDX_TABLE_NAME_21 YD_BRANCH 3OWNER TABLE_NAME IDX_TABLE_NAME_21 COUNTY 4OWNER TABLE_NAME IDX_TABLE_NAME_22 TOPOPRNO 1OWNER TABLE_NAME IDX_TABLE_NAME_22 APPDT 2OWNER TABLE_NAME IDX_TABLE_NAME_23 BRHOPRNO 1OWNER TABLE_NAME IDX_TABLE_NAME_23 APPDT 2OWNER TABLE_NAME IDX_TABLE_NAME_24 DSTOPRNO 1OWNER TABLE_NAME IDX_TABLE_NAME_24 APPDT 2OWNER TABLE_NAME PK_TABLE_NAME MODNO 1
OWNERER TABLE_NAME COLUMN_NAME NUM_ROWS NUM_DISTINCT NUM_NULLS LAST_ANALYZED HISTOGRAM------------ ------------------------------ ------------------------- ---------- ------------ ---------- ------------------- -----------------OWNER TABLE_NAME PREFNSHTM 3132683 1290960 32 2018-09-08 20:12:06 NONE--OWNER TABLE_NAME APPDT 3132683 36 0 2018-09-08 20:12:06 FREQUENCY--OWNER TABLE_NAME TASKSTA 3132683 9 0 2018-09-08 20:12:06 FREQUENCY--OWNER TABLE_NAME BRANCH 3132683 45 500530 2018-09-08 20:12:06 FREQUENCY--OWNER TABLE_NAME BQTYPE 3132683 35 0 2018-09-08 20:12:06 FREQUENCY
--预生产库原执行计划 (没有返回数据)
Plan hash value: 1441683948------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 24658 (100)| 0 |00:00:04.10 | 42262 | 12723 ||* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| tablename | 1 | 68 | 24658 (1)| 0 |00:00:04.10 | 42262 | 12723 ||* 2 | INDEX SKIP SCAN | IDX_TBL_TSK_INFO_07 | 1 | 28197 | 417 (1)| 30725 |00:00:00.87 | 12432 | 12432 |------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter((((TO_NUMBER("T"."APPDT")<=20180907 AND TO_NUMBER("T"."APPDT")>=20180907 AND ("T"."TASKSTA"='10' OR "T"."TASKSTA"IS NULL)) OR (TO_NUMBER("T"."PREFNSHTM")<=20180907 AND TO_NUMBER("T"."PREFNSHTM")>=20180907 ANDINTERNAL_FUNCTION("T"."TASKSTA"))) AND "T"."TASKSTA"<>'95' AND "T"."BQTYPE"<>'TLTB' AND "T"."BQTYPE"<>'TLYT' AND"T"."BQTYPE"<>'TLSH' AND INTERNAL_FUNCTION("T"."BQTYPE")))2 - access("T"."BRANCH"='00000000000023')filter("T"."BRANCH"='00000000000023')
--预生产库优化后的执行计划 (没有返回数据)
Plan hash value: 2801306107---------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |---------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 541 (100)| 0 |00:00:00.17 | 472 || 1 | CONCATENATION | | 1 | | | 0 |00:00:00.17 | 472 ||* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| tablename | 1 | 11 | 271 (1)| 0 |00:00:00.06 | 236 ||* 3 | INDEX RANGE SCAN | IND_X01 | 1 | 69 | 202 (1)| 0 |00:00:00.06 | 236 ||* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| tablename | 1 | 57 | 271 (1)| 0 |00:00:00.11 | 236 ||* 5 | INDEX RANGE SCAN | IND_X01 | 1 | 69 | 202 (1)| 0 |00:00:00.11 | 236 |---------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - filter((INTERNAL_FUNCTION("T"."TASKSTA") AND "T"."TASKSTA"<>'95' AND "T"."BQTYPE"<>'TLTB' AND"T"."BQTYPE"<>'TLYT' AND "T"."BQTYPE"<>'TLSH' AND INTERNAL_FUNCTION("T"."BQTYPE")))3 - access("T"."BRANCH"='00000000000023')filter((TO_NUMBER("T"."PREFNSHTM")<=20180907 AND TO_NUMBER("T"."PREFNSHTM")>=20180907))4 - filter((("T"."TASKSTA"='10' OR "T"."TASKSTA" IS NULL) AND "T"."TASKSTA"<>'95' AND "T"."BQTYPE"<>'TLSH' AND"T"."BQTYPE"<>'TLYT' AND "T"."BQTYPE"<>'TLTB' AND INTERNAL_FUNCTION("T"."BQTYPE") AND(LNNVL(TO_NUMBER("T"."PREFNSHTM")<=20180907) OR LNNVL(TO_NUMBER("T"."PREFNSHTM")>=20180907) OR(LNNVL("T"."TASKSTA"='12') AND LNNVL("T"."TASKSTA"='99') AND LNNVL("T"."TASKSTA"='B')))))5 - access("T"."BRANCH"='00000000000023')filter((TO_NUMBER("T"."APPDT")<=20180907 AND TO_NUMBER("T"."APPDT")>=20180907))
2、全表扫描
CPU告警使用率100%优化分析。
主要是SQL语句 07namsa75x3dk，执行计划走全表扫描，逻辑读非常高；
执行计划走全表扫描：
其每小时执行次数为 4 ，执行时间为3.26 s，逻辑读为433930.25 ；
执行计划走索引范围扫描：
其每小时执行次数为 4148，执行时间为0.01 s，逻辑读为962.19 ；
SQL_ID PLAN_HASH_VALUE Date time No.execs LIO/exec CPUTIM/exec ETIME/exec PIO/exec ROWs/exec------------- --------------- -------------------- ---------- ---------------- ----------- ----------- -------------- -------------07namsa75x3dk 2039854505 01/20/19_0700_0800 4 433930.25 3.26 3.26 .00 8.0007namsa75x3dk 1442656572 01/20/19_0700_0800 0 .00 .00 .00 .00 20.0007namsa75x3dk 2039854505 01/20/19_0800_0900 3 433930.33 3.24 3.24 .00 16.0007namsa75x3dk 1442656572 01/20/19_0800_0900 0 .00 .00 .00 .00 20.0007namsa75x3dk 2039854505 01/20/19_0900_1000 3 433931.67 3.20 3.20 .00 21.3307namsa75x3dk 1442656572 01/20/19_0900_1000 0 .00 .00 .00 .00 20.0007namsa75x3dk 1442656572 01/21/19_1000_1100 4148 962.19 .01 .01 .00 7.5707namsa75x3dk 1442656572 01/21/19_1100_1200 3467 1100.39 .01 .01 .00 14.7107namsa75x3dk 1442656572 01/21/19_1200_1300 2116 1212.77 .01 .01 .00 29.8607namsa75x3dk 1442656572 01/21/19_1300_1400 2817 3108.53 .03 .03 .00 28.7407namsa75x3dk 1442656572 01/21/19_1400_1500 3084 1448.92 .01 .01 .00 31.8507namsa75x3dk 1442656572 01/21/19_1500_1600 3453 1327.12 .01 .01 .00 34.1307namsa75x3dk 1442656572 01/21/19_1600_1700 3391 882.29 .01 .01 .00 40.3007namsa75x3dk 1442656572 01/21/19_1700_1800 2457 1107.54 .01 .01 .00 61.2407namsa75x3dk 1442656572 01/21/19_1800_1900 1542 950.02 .01 .01 .00 103.3707namsa75x3dk 1442656572 01/21/19_2100_2200 936 439.12 .00 .00 .00 187.18SQL_ID 07namsa75x3dk--------------------Plan hash value: 1442656572------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 7 (100)| || 1 | COUNT STOPKEY | | | | | || 2 | VIEW | | 6 | 19464 | 7 (15)| 00:00:01 || 3 | SORT ORDER BY STOPKEY | | 6 | 1650 | 7 (15)| 00:00:01 || 4 | TABLE ACCESS BY INDEX ROWID| BIG_POLICY | 6 | 1650 | 6 (0)| 00:00:01 || 5 | INDEX RANGE SCAN | IDX_BIGPOLICY_APPTYPEFLAG | 7 | | 3 (0)| 00:00:01 |------------------------------------------------------------------------------------------------------------Plan hash value: 2039854505------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 7 (100)| || 1 | COUNT STOPKEY | | | | | || 2 | VIEW | | 6 | 19464 | 7 (15)| 00:00:01 || 3 | SORT ORDER BY STOPKEY | | 6 | 1650 | 7 (15)| 00:00:01 || 4 | TABLE ACCESS BY INDEX ROWID| BIG_POLICY | 6 | 1650 | 6 (0)| 00:00:01 || 5 | INDEX RANGE SCAN | IDX_BIGPOLICY_APPTYPEFLAG | 7 | | 3 (0)| 00:00:01 |------------------------------------------------------------------------------------------------------------
--表的索引情况
TABLE_OWNER TABLE_NAME INDEX_NAME INDEX_TY STATUS NUM_ROWS COLUMN_NAME COLUMN_POSITION------------ ------------------------- ------------------------------ -------- ------ ---------- ------------------ ---------------OWNER TABLENAME IDX_BIGPOLICY_APPTYPEFLAG NORMAL VALID 7673564 CUSTOM_FLAG 1OWNER TABLENAME IDX_BIGPOLICY_APPTYPEFLAG NORMAL VALID 7673564 APP_TYPE 2OWNER TABLENAME IDX_BIGPOLICY_BZAPPLYNO NORMAL VALID 4172080 BUSINESS_APPLY_NO 1OWNER TABLENAME IDX_BIGPOLICY_BZID NORMAL VALID 7977816 BUSINESS_POLICY_ID 1OWNER TABLENAME IDX_BIGPOLICY_LIFEID NORMAL VALID 3093786 LIFE_AGENT_ID 1OWNER TABLENAME IDX_BIGPOLICY_PERSONID NORMAL VALID 8514860 PERSON_ID 1OWNER TABLENAME IDX_BIGPOLICY_RENEWID NORMAL VALID 4521052 RENEWAL_TASK_ID 1OWNER TABLENAME IDX_BIGPOLICY_TCAPPLYNO NORMAL VALID 4782459 TRAFFIC_APPLY_NO 1OWNER TABLENAME IDX_BIGPOLICY_TCID NORMAL VALID 8185871 TRAFFIC_POLICY_ID 1OWNER TABLENAME IDX_BIGPOLICY_VEHNUMBER NORMAL VALID 7427941 REGISTRATION_NUMBE 1R
3、位图转化
CPU使用率为99.47%优化分析。
主要是07jmp50h6pssa语句，
优化前：
其逻辑读为591816，其执行时间为767s，进行SQL改写（改为绑定变量形式，避免进行多次硬解析，创建组合索引。
优化后，其逻辑读为6，执行时间为0.01s（预生产库测试）；
SQL语句07jmp50h6pssa 执行计划走错原因分析：
1.ID=4、ID=6关键字为位图转换
（BITMAP CONVERSION FROM ROWIDS）
出现位图转换，主要是表中存在一些不适当的索引，这些索引的列选择性不高。oracle就有可能选择两个这样的索引转为bitmap来执行，然后根据这两个索引的值再确认共同有的ROWID，最后再通过ROWID回表提取符合条件的数据；
2.ID=7 关键字为排序（SORT ORDER BY），
字段a.message_Id > '20180801084645'为范围过滤，选择性不高；
3.建议创建更加合适的索引，避免位图转换，考虑创建组合索引。
--优化前
Plan hash value: 4014160676-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|* 1 | TABLE ACCESS BY INDEX ROWID | T_SHOWDELMSG_POLICYNUM | 1 | 1 | 9 (12)| 0 |00:00:06.51 | 55774 | 15487 | 15718 | | | | || 2 | BITMAP CONVERSION TO ROWIDS | | 1 | | | 3 |00:00:06.51 | 55772 | 15487 | 15718 | | | | || 3 | BITMAP AND | | 1 | | | 1 |00:00:06.51 | 55772 | 15487 | 15718 | | | | || 4 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | 1 |00:00:00.01 | 4 | 0 | 0 | | | | ||* 5 | INDEX RANGE SCAN | IDX_T_SHOWDELMSG_POLICYNUM_N02 | 1 | 15 | 4 (0)| 5 |00:00:00.01 | 4 | 0 | 0 | | | | || 6 | BITMAP CONVERSION FROM ROWIDS| | 1 | | | 3 |00:00:06.51 | 55768 | 15487 | 15718 | | | | || 7 | SORT ORDER BY | | 1 | | | 3870K|00:00:04.52 | 55768 | 15487 | 15718 | 69M| 2877K| 100M (1)| 64512 ||* 8 | INDEX RANGE SCAN | IDX_T_SHOWDELMSG_POLICYNUM_N03 | 1 | 15 | 4 (0)| 3992K|00:00:00.01 | 55763 | 0 | 0 | | | | |-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--优化后
Plan hash value: 2229986459--------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |--------------------------------------------------------------------------------------------------------------------------------------|* 1 | TABLE ACCESS BY INDEX ROWID| T_SHOWDELMSG_POLICYNUM | 1 | 1 | 5 (0)| 0 |00:00:00.01 | 6 | 2 || * 2| INDEX RANGE SCAN | IND_T01 | 1 | 1 | 4 (0)| 3 |00:00:00.01 | 4 | 2 |--------------------------------------------------------------------------------------------------------------------------------------
--表索引情况
TABLE_OWNER TABLE_NAME INDEX_NAME INDEX_TY STATUS NUM_ROWS COLUMN_NAME COLUMN_POSITION------------ ----------------------- ------------------------------ -------- -------- ---------- --------------- ---------------OWNER TABLENAME IDX_TABLENAME_N01 NORMAL VALID 393582000 POLICYNUM 1OWNER TABLENAME IDX_TABLENAME_N02 NORMAL VALID 367349000 ZHENGJIANHAO 1OWNER TABLENAME IDX_TABLENAME_N03 NORMAL VALID 384279000 MESSAGE_ID 1OWNER TABLENAME IDX_TABLENAME_N04 NORMAL VALID 95019556 CREATEDATE 1OWNER TABLENAME PK_TABLENAME_N NORMAL VALID 398149000 ID 1
--表的统计信息情况
OWNER TABLE_NAME COLUMN_NAME NUM_ROWS NUM_DISTINCT NUM_NULLS LAST_ANALYZED HISTOGRAM------------ ------------------------------ ------------------------- ---------- ------------ ---------- ------------------- -----------------TYDY T_SHOWDELMSG_POLICYNUM MESSAGE_ID 387641000 304418316 0 2018-10-30 10:39:32 NONE--TYDY T_SHOWDELMSG_POLICYNUM LETTERNO 387641000 21 0 2018-10-30 10:39:32 NONE--TYDY T_SHOWDELMSG_POLICYNUM ZHENGJIANHAO 387641000 9214124 34058000 2018-10-30 10:39:32 NONE
推荐文章 · 点击阅读
文章转载自数据与人，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
