SQL> SELECT COUNT (*)
2 FROM (SELECT DISTINCT prpcmain.*
3 FROM prpcmain
4 WHERE prpcmain.riskcode IN ('0506', '0590', '0507', '0508')
5 AND SUBSTR (othflag, 4, 1) != '1'
6 AND underwriteflag = '4'
7 AND NOT EXISTS (
8 SELECT 1
9 FROM prpjpayexchsub a, prpjpayexch b
10 WHERE a.certino = prpcmain.policyno
11 AND a.exchangeno = b.exchangeno
12 AND b.paystatus = '1')
13 AND ( prpcmain.comcode LIKE '2510183%'
14 OR prpcmain.comcode IN ('2510183601')
15 OR ( prpcmain.handlercode = '251000149'
16 OR prpcmain.operatorcode = '251000149'
17 OR prpcmain.handler1code = '251000149'
18 )
19 OR 1 = 0
20 )
21 AND ( prpcmain.startdate >
22 TO_DATE ('2017-08-31', 'YYYY-MM-DD')
23 OR ( prpcmain.startdate =
24 TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
25 'YYYY-MM-DD'
26 )
27 AND prpcmain.starthour > 0
28 )
29 )
30 AND (SYSDATE - prpcmain.inputdate < 10)
31 AND NOT EXISTS (
32 SELECT 1
33 FROM prpjpaywechatorder wchatorder,
34 prpjpaywechatdetail detail
35 WHERE wchatorder.exchangeno = detail.exchangeno
36 AND detail.certino = prpcmain.policyno
37 AND wchatorder.paystatus = '1')
...........................以下省略
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 1580 (1)| 00:00:19 |
| 1 | SORT AGGREGATE | | 1 | 107 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | PRPCMAIN | 1 | 107 | 1552 (1)| 00:00:19 |
|* 3 | INDEX RANGE SCAN | IDX_PRPCMAIN_151128 | 2 | | 1550 (1)| 00:00:19 |
| 4 | NESTED LOOPS | | 2 | 112 | 8 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 2 | 112 | 8 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | PRPJPAYEXCHSUB | 2 | 76 | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_PRPJPAYEXCHSUB_CERTINO | 2 | | 4 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_JPAYEXCH | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | PRPJPAYEXCH | 1 | 18 | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 2 | 112 | 4 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 2 | 112 | 4 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | PRPJPAYWECHATDETAIL | 2 | 76 | 2 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX_PRPJPAYDTL_CERTINO | 2 | | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_PRPJPAYWECHATORDER | 1 | | 0 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | PRPJPAYWECHATORDER | 1 | 18 | 1 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 2 | 110 | 4 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 2 | 110 | 4 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | PRPJPOSEXCHZBFDETAIL | 2 | 74 | 3 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | IDX_CERTINO | 2 | | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_PRPJPOSEXCHZBFORDER | 1 | | 0 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | PRPJPOSEXCHZBFORDER | 1 | 18 | 1 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 68 | 12 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | 2 | 68 | 12 (0)| 00:00:01 |
| 24 | NESTED LOOPS | | 2 | 112 | 9 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID| PRPJPAYEXCHSUB | 2 | 76 | 5 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | IDX_PRPJPAYEXCHSUB_CERTINO | 2 | | 4 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID| PRPJPAYEXCH | 1 | 18 | 2 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | PK_JPAYEXCH | 1 | | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | PK_JCHECK | 1 | | 1 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | PRPJCHECK | 1 | 12 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
以上是优化前执行计划
以上sql语句先完成子查询后,回主表prpcmain 通过低效索引 IDX_PRPCMAIN_151128 条件 underwriteflag = '4'先到表中取数,再通过两个连接字段进行连接过滤,
执行时间过长。由于多个session 并发执行,导致cpu资源耗尽
第一感觉是既然索引低效,不妨使用no_index 阻止使用这个索引试试,看看优化器会怎样选择执行计划
SQL>
SQL> SELECT COUNT (*)
2 FROM (SELECT/*+ no_index(prpcmain IDX_PRPCMAIN_151128)*/ DISTINCT prpcmain.*
3 FROM prpcmain
....
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 115 | | 2435 (1)| 00:00:30 |
| 1 | SORT AGGREGATE | | 1 | 115 | | | |
| 2 | NESTED LOOPS ANTI | | 1 | 115 | | 2435 (1)| 00:00:30 |
| 3 | NESTED LOOPS ANTI | | 1 | 113 | | 2423 (1)| 00:00:30 |
| 4 | NESTED LOOPS ANTI | | 1 | 111 | | 2415 (1)| 00:00:29 |
| 5 | NESTED LOOPS ANTI | | 1 | 109 | | 2411 (1)| 00:00:29 |
|* 6 | TABLE ACCESS BY INDEX ROWID | PRPCMAIN | 1 | 107 | | 2408 (1)| 00:00:29 |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 8 | BITMAP AND | | | | | | |
| 9 | BITMAP OR | | | | | | |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 11 | SORT ORDER BY | | | | | | |
|* 12 | INDEX RANGE SCAN | IDX_PRPCMAIN_1 | | | | 42 (0)| 00:00:01 |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 14 | SORT ORDER BY | | | | | | |
|* 15 | INDEX RANGE SCAN | IDX_PRPCMAIN_1 | | | | 42 (0)| 00:00:01 |
| 16 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 17 | INDEX RANGE SCAN | IDX_PRPCMAIN_OPERATORCODE | | | | 12 (0)| 00:00:01 |
| 18 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 19 | INDEX RANGE SCAN | IDX_PRPCMAIN_HANDLERCODE | | | | 6 (0)| 00:00:01 |
| 20 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 21 | INDEX RANGE SCAN | IDX_PRPCMAIN_HANDLER1CODE | | | | 6 (0)| 00:00:01 |
| 22 | BITMAP OR | | | | | | |
| 23 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 24 | SORT ORDER BY | | | | 3208K| | |
|* 25 | INDEX RANGE SCAN | IDX_PRPCMAIN_STARTDATE | | | | 633 (1)| 00:00:08 |
| 26 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 27 | INDEX RANGE SCAN | IDX_PRPCMAIN_STARTDATE | | | | 19 (0)| 00:00:01 |
| 28 | VIEW PUSHED PREDICATE | VW_SQ_3 | 1 | 2 | | 3 (0)| 00:00:01 |
通过使用no_index提示,sql执行由几乎无法完成,到30多秒,逻辑读由200多万 降到了 1500多
看来效果明显,通过观察执行计划,由原来的后回主表,改为先使用BITMAP CONVERSION
把多个b tree 索引转化为一个位图,到表内一次取出数据,然后根据连接字段上的数据传入谓词到各个
子查询 not exists
通过测试,也可以使用提示 /*+ index_combine(prpcmain)*/ 来主动要求优化器使用这个执行计划,
效果略有不同的是,把 in 子句也进行了 BITMAP CONVERSION ,其效果不如最后过滤 in 子句条件,逻辑读略高,在5万多。
最终使用提示 /*+ index_combine(prpcmain IDX_PRPCMAIN_1)*/
指定一个或者多个 or 条件内使用的索引,可以避免in 条件也进行转换
2 FROM (SELECT DISTINCT prpcmain.*
3 FROM prpcmain
4 WHERE prpcmain.riskcode IN ('0506', '0590', '0507', '0508')
5 AND SUBSTR (othflag, 4, 1) != '1'
6 AND underwriteflag = '4'
7 AND NOT EXISTS (
8 SELECT 1
9 FROM prpjpayexchsub a, prpjpayexch b
10 WHERE a.certino = prpcmain.policyno
11 AND a.exchangeno = b.exchangeno
12 AND b.paystatus = '1')
13 AND ( prpcmain.comcode LIKE '2510183%'
14 OR prpcmain.comcode IN ('2510183601')
15 OR ( prpcmain.handlercode = '251000149'
16 OR prpcmain.operatorcode = '251000149'
17 OR prpcmain.handler1code = '251000149'
18 )
19 OR 1 = 0
20 )
21 AND ( prpcmain.startdate >
22 TO_DATE ('2017-08-31', 'YYYY-MM-DD')
23 OR ( prpcmain.startdate =
24 TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
25 'YYYY-MM-DD'
26 )
27 AND prpcmain.starthour > 0
28 )
29 )
30 AND (SYSDATE - prpcmain.inputdate < 10)
31 AND NOT EXISTS (
32 SELECT 1
33 FROM prpjpaywechatorder wchatorder,
34 prpjpaywechatdetail detail
35 WHERE wchatorder.exchangeno = detail.exchangeno
36 AND detail.certino = prpcmain.policyno
37 AND wchatorder.paystatus = '1')
...........................以下省略
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 1580 (1)| 00:00:19 |
| 1 | SORT AGGREGATE | | 1 | 107 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | PRPCMAIN | 1 | 107 | 1552 (1)| 00:00:19 |
|* 3 | INDEX RANGE SCAN | IDX_PRPCMAIN_151128 | 2 | | 1550 (1)| 00:00:19 |
| 4 | NESTED LOOPS | | 2 | 112 | 8 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 2 | 112 | 8 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | PRPJPAYEXCHSUB | 2 | 76 | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_PRPJPAYEXCHSUB_CERTINO | 2 | | 4 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_JPAYEXCH | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | PRPJPAYEXCH | 1 | 18 | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 2 | 112 | 4 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 2 | 112 | 4 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | PRPJPAYWECHATDETAIL | 2 | 76 | 2 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX_PRPJPAYDTL_CERTINO | 2 | | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_PRPJPAYWECHATORDER | 1 | | 0 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | PRPJPAYWECHATORDER | 1 | 18 | 1 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 2 | 110 | 4 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 2 | 110 | 4 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | PRPJPOSEXCHZBFDETAIL | 2 | 74 | 3 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | IDX_CERTINO | 2 | | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_PRPJPOSEXCHZBFORDER | 1 | | 0 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | PRPJPOSEXCHZBFORDER | 1 | 18 | 1 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 68 | 12 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | 2 | 68 | 12 (0)| 00:00:01 |
| 24 | NESTED LOOPS | | 2 | 112 | 9 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID| PRPJPAYEXCHSUB | 2 | 76 | 5 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | IDX_PRPJPAYEXCHSUB_CERTINO | 2 | | 4 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID| PRPJPAYEXCH | 1 | 18 | 2 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | PK_JPAYEXCH | 1 | | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | PK_JCHECK | 1 | | 1 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | PRPJCHECK | 1 | 12 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
以上是优化前执行计划
以上sql语句先完成子查询后,回主表prpcmain 通过低效索引 IDX_PRPCMAIN_151128 条件 underwriteflag = '4'先到表中取数,再通过两个连接字段进行连接过滤,
执行时间过长。由于多个session 并发执行,导致cpu资源耗尽
第一感觉是既然索引低效,不妨使用no_index 阻止使用这个索引试试,看看优化器会怎样选择执行计划
SQL>
SQL> SELECT COUNT (*)
2 FROM (SELECT/*+ no_index(prpcmain IDX_PRPCMAIN_151128)*/ DISTINCT prpcmain.*
3 FROM prpcmain
....
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 115 | | 2435 (1)| 00:00:30 |
| 1 | SORT AGGREGATE | | 1 | 115 | | | |
| 2 | NESTED LOOPS ANTI | | 1 | 115 | | 2435 (1)| 00:00:30 |
| 3 | NESTED LOOPS ANTI | | 1 | 113 | | 2423 (1)| 00:00:30 |
| 4 | NESTED LOOPS ANTI | | 1 | 111 | | 2415 (1)| 00:00:29 |
| 5 | NESTED LOOPS ANTI | | 1 | 109 | | 2411 (1)| 00:00:29 |
|* 6 | TABLE ACCESS BY INDEX ROWID | PRPCMAIN | 1 | 107 | | 2408 (1)| 00:00:29 |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 8 | BITMAP AND | | | | | | |
| 9 | BITMAP OR | | | | | | |
| 10 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 11 | SORT ORDER BY | | | | | | |
|* 12 | INDEX RANGE SCAN | IDX_PRPCMAIN_1 | | | | 42 (0)| 00:00:01 |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 14 | SORT ORDER BY | | | | | | |
|* 15 | INDEX RANGE SCAN | IDX_PRPCMAIN_1 | | | | 42 (0)| 00:00:01 |
| 16 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 17 | INDEX RANGE SCAN | IDX_PRPCMAIN_OPERATORCODE | | | | 12 (0)| 00:00:01 |
| 18 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 19 | INDEX RANGE SCAN | IDX_PRPCMAIN_HANDLERCODE | | | | 6 (0)| 00:00:01 |
| 20 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 21 | INDEX RANGE SCAN | IDX_PRPCMAIN_HANDLER1CODE | | | | 6 (0)| 00:00:01 |
| 22 | BITMAP OR | | | | | | |
| 23 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 24 | SORT ORDER BY | | | | 3208K| | |
|* 25 | INDEX RANGE SCAN | IDX_PRPCMAIN_STARTDATE | | | | 633 (1)| 00:00:08 |
| 26 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 27 | INDEX RANGE SCAN | IDX_PRPCMAIN_STARTDATE | | | | 19 (0)| 00:00:01 |
| 28 | VIEW PUSHED PREDICATE | VW_SQ_3 | 1 | 2 | | 3 (0)| 00:00:01 |
通过使用no_index提示,sql执行由几乎无法完成,到30多秒,逻辑读由200多万 降到了 1500多
看来效果明显,通过观察执行计划,由原来的后回主表,改为先使用BITMAP CONVERSION
把多个b tree 索引转化为一个位图,到表内一次取出数据,然后根据连接字段上的数据传入谓词到各个
子查询 not exists
通过测试,也可以使用提示 /*+ index_combine(prpcmain)*/ 来主动要求优化器使用这个执行计划,
效果略有不同的是,把 in 子句也进行了 BITMAP CONVERSION ,其效果不如最后过滤 in 子句条件,逻辑读略高,在5万多。
最终使用提示 /*+ index_combine(prpcmain IDX_PRPCMAIN_1)*/
指定一个或者多个 or 条件内使用的索引,可以避免in 条件也进行转换
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23944170/viewspace-2144833/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23944170/viewspace-2144833/