11g 一个sql案例 hash group by
来源：互联网 发布：舞蹈mmd软件 编辑：程序博客网 时间：2024/05/22 12:20
处理了一个sql导致的告警问题，这个sql本身的写法是有问题的，这个sql有2个执行计划一个好的，一个不好的，好的执行计划是谓词反馈后，确定了正常的结果集，抛出语句的本身问题，看下oracle是怎么搞错的
错误的执行计划SQL_ID 9z2dtfxqun0xp, child number 5-------------------------------------select * from ( select row_limit.*, rownum rownum_ from (select distinct w1.product_name as productName, w5.year_return_rate as yearReturnRate, NVL(w2.new_count, 0) as newUserCount, NVL(trunc(w2.sum_amount, 2), 0.00) as newTotalSaleAmount, NVL(w3.old_count, 0) as oldUserCount, NVL(trunc(w3.sum_amount, 2), 0.00) as oldTotalSaleAmount, NVL(w1.id_count, 0) as totalOrderCount, NVL(trunc(w1.sum_save, 2), 0.00) as totalCouponAmount, NVL(trunc(w4.sum_plus, 2), 0.00) as totalPlusCouponAmout, NVL(trunc(w6.Income, 2), 0.00) as incomeAmount from ( ( select distinct j.name || ' ' || p.year_return_rate as product_name, count(distinct o.id) as id_count, sum(o.save_amount) as sum_save from tb_finance_order o, tb_finance_product p, tb_finance_project j where p.project_id=j.id and p.id=o.product_id and o.product_type = 0 and o.order_status in (2,3) and o.transfer_product_id is null and o.order_time >= :1 and o.order_time <= :2Plan hash value: 1795073995-----------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 3596K(100)| | | ||* 1 | VIEW | | 20 | 5040 | 3596K (1)| 11:59:17 | | ||* 2 | COUNT STOPKEY | | | | | | | || 3 | VIEW | | 48 | 11472 | 3596K (1)| 11:59:17 | | ||* 4 | SORT UNIQUE STOPKEY | | 48 | 40320 | 3595K (1)| 11:59:10 | | ||* 5 | HASH JOIN RIGHT OUTER | | 19M| 15G| 170K (1)| 00:34:06 | | || 6 | VIEW | | 2907 | 383K| 3427 (1)| 00:00:42 | | || 7 | HASH UNIQUE | | 2907 | 454K| 3427 (1)| 00:00:42 | | || 8 | HASH GROUP BY | | 2907 | 454K| 3427 (1)| 00:00:42 | | ||* 9 | FILTER | | | | | | | ||* 10 | HASH JOIN | | 2907 | 454K| 3426 (1)| 00:00:42 | | || 11 | TABLE ACCESS FULL | TB_FINANCE_PROJECT | 46 | 3266 | 5 (0)| 00:00:01 | | ||* 12 | HASH JOIN | | 3223 | 280K| 3421 (1)| 00:00:42 | | ||* 13 | TABLE ACCESS FULL | TB_FINANCE_PRODUCT | 7841 | 321K| 1230 (1)| 00:00:15 | | || 14 | PARTITION RANGE ITERATOR | | 12241 | 561K| 2191 (1)| 00:00:27 | KEY | KEY ||* 15 | TABLE ACCESS FULL | TB_FINANCE_ORDER | 12241 | 561K| 2191 (1)| 00:00:27 | KEY | KEY ||* 16 | HASH JOIN RIGHT OUTER | | 668K| 449M| 166K (1)| 00:33:24 | | || 17 | VIEW | | 2805 | 405K| 57668 (1)| 00:11:33 | | || 18 | HASH GROUP BY | | 2805 | 1060K| 57668 (1)| 00:11:33 | | || 19 | VIEW | VW_DAG_0 | 2805 | 1060K| 57667 (1)| 00:11:33 | | || 20 | HASH GROUP BY | | 2805 | 635K| 57667 (1)| 00:11:33 | | ||* 21 | FILTER | | | | | | | ||* 22 | HASH JOIN RIGHT ANTI | | 2805 | 635K| 57666 (1)| 00:11:32 | | || 23 | VIEW | VW_NSO_1 | 430 | 11180 | 54240 (1)| 00:10:51 | | || 24 | NESTED LOOPS | | 430 | 46440 | 54240 (1)| 00:10:51 | | || 25 | NESTED LOOPS | | 430 | 46440 | 54240 (1)| 00:10:51 | | || 26 | VIEW | | 430 | 16340 | 53380 (1)| 00:10:41 | | ||* 27 | FILTER | | | | | | | || 28 | HASH GROUP BY | | 430 | 18920 | 53380 (1)| 00:10:41 | | || 29 | PARTITION RANGE ALL | | 629K| 26M| 53364 (1)| 00:10:41 | 1 |1048575||* 30 | TABLE ACCESS FULL | TB_FINANCE_ORDER | 629K| 26M| 53364 (1)| 00:10:41 | 1 |1048575|| 31 | PARTITION RANGE ITERATOR | | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 32 | INDEX RANGE SCAN | IDX_ORDER_OTIMENEW | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 33 | TABLE ACCESS BY LOCAL INDEX ROWID | TB_FINANCE_ORDER | 1 | 70 | 2 (0)| 00:00:01 | 1 | 1 ||* 34 | HASH JOIN | | 2907 | 584K| 3426 (1)| 00:00:42 | | || 35 | TABLE ACCESS FULL | TB_FINANCE_PROJECT | 46 | 3082 | 5 (0)| 00:00:01 | | ||* 36 | HASH JOIN | | 3223 | 437K| 3421 (1)| 00:00:42 | | ||* 37 | TABLE ACCESS FULL | TB_FINANCE_PRODUCT | 7841 | 290K| 1230 (1)| 00:00:15 | | || 38 | PARTITION RANGE ITERATOR | | 12241 | 1207K| 2191 (1)| 00:00:27 | KEY | KEY ||* 39 | TABLE ACCESS FULL | TB_FINANCE_ORDER | 12241 | 1207K| 2191 (1)| 00:00:27 | KEY | KEY ||* 40 | HASH JOIN RIGHT OUTER | | 23815 | 12M| 109K (1)| 00:21:52 | | || 41 | VIEW | | 1528 | 188K| 1236 (1)| 00:00:15 | | || 42 | HASH UNIQUE | | 1528 | 122K| 1236 (1)| 00:00:15 | | ||* 43 | HASH JOIN | | 7072 | 566K| 1235 (1)| 00:00:15 | | || 44 | TABLE ACCESS FULL | TB_FINANCE_PROJECT | 46 | 3082 | 5 (0)| 00:00:01 | | ||* 45 | TABLE ACCESS FULL | TB_FINANCE_PRODUCT | 7841 | 114K| 1230 (1)| 00:00:15 | | ||* 46 | HASH JOIN RIGHT OUTER | | 1559 | 656K| 108K (1)| 00:21:37 | | || 47 | VIEW | | 102 | 15096 | 54677 (1)| 00:10:57 | | || 48 | HASH GROUP BY | | 102 | 39474 | 54677 (1)| 00:10:57 | | || 49 | VIEW | VW_DAG_1 | 102 | 39474 | 54676 (1)| 00:10:57 | | || 50 | HASH GROUP BY | | 102 | 21828 | 54676 (1)| 00:10:57 | | ||* 51 | HASH JOIN | | 102 | 21828 | 54675 (1)| 00:10:57 | | || 52 | TABLE ACCESS FULL | TB_FINANCE_PROJECT | 46 | 3082 | 5 (0)| 00:00:01 | | || 53 | NESTED LOOPS | | 113 | 16611 | 54670 (1)| 00:10:57 | | || 54 | NESTED LOOPS | | 430 | 16611 | 54670 (1)| 00:10:57 | | || 55 | NESTED LOOPS | | 430 | 46870 | 54240 (1)| 00:10:51 | | || 56 | VIEW | | 430 | 16340 | 53380 (1)| 00:10:41 | | ||* 57 | FILTER | | | | | | | || 58 | HASH GROUP BY | | 430 | 18920 | 53380 (1)| 00:10:41 | | || 59 | PARTITION RANGE ALL | | 629K| 26M| 53364 (1)| 00:10:41 | 1 |1048575||* 60 | TABLE ACCESS FULL | TB_FINANCE_ORDER | 629K| 26M| 53364 (1)| 00:10:41 | 1 |1048575|| 61 | PARTITION RANGE ITERATOR | | 1 | 71 | 2 (0)| 00:00:01 | KEY | KEY ||* 62 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_FINANCE_ORDER | 1 | 71 | 2 (0)| 00:00:01 | KEY | KEY ||* 63 | INDEX RANGE SCAN | IDX_ORDER_OTIMENEW | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 64 | INDEX UNIQUE SCAN | TB_FINANCE_PRODUCT_PK | 1 | | 0 (0)| | | ||* 65 | TABLE ACCESS BY INDEX ROWID | TB_FINANCE_PRODUCT | 1 | 38 | 1 (0)| 00:00:01 | | ||* 66 | HASH JOIN RIGHT OUTER | | 1528 | 422K| 53376 (1)| 00:10:41 | | || 67 | VIEW | | 1 | 135 | 49949 (1)| 00:10:00 | | || 68 | HASH GROUP BY | | 1 | 205 | 49949 (1)| 00:10:00 | | ||* 69 | FILTER | | | | | | | || 70 | NESTED LOOPS | | 1 | 205 | 49948 (1)| 00:10:00 | | || 71 | NESTED LOOPS | | 1 | 205 | 49948 (1)| 00:10:00 | | || 72 | NESTED LOOPS | | 1 | 190 | 49947 (1)| 00:10:00 | | ||* 73 | HASH JOIN | | 2907 | 505K| 3426 (1)| 00:00:42 | | || 74 | TABLE ACCESS FULL | TB_FINANCE_PROJECT | 46 | 3082 | 5 (0)| 00:00:01 | | ||* 75 | HASH JOIN | | 3223 | 349K| 3421 (1)| 00:00:42 | | ||* 76 | TABLE ACCESS FULL | TB_FINANCE_PRODUCT | 7841 | 290K| 1230 (1)| 00:00:15 | | || 77 | PARTITION RANGE ITERATOR | | 12241 | 872K| 2191 (1)| 00:00:27 | KEY | KEY ||* 78 | TABLE ACCESS FULL | TB_FINANCE_ORDER | 12241 | 872K| 2191 (1)| 00:00:27 | KEY | KEY || 79 | PARTITION RANGE ALL | | 1 | 12 | 16 (0)| 00:00:01 | 1 |1048575||* 80 | TABLE ACCESS BY LOCAL INDEX ROWID | TB_PLUS_RECEIVE_CODE | 1 | 12 | 16 (0)| 00:00:01 | 1 |1048575||* 81 | INDEX RANGE SCAN | IDX_PLUS_RECEIVE_ORDERID | 1 | | 16 (0)| 00:00:01 | 1 |1048575||* 82 | INDEX UNIQUE SCAN | IDX_PLUS_COUPON_ID | 1 | | 0 (0)| | | || 83 | TABLE ACCESS BY INDEX ROWID | TB_PLUS_COUPON | 1 | 15 | 1 (0)| 00:00:01 | | || 84 | VIEW | | 1528 | 220K| 3427 (1)| 00:00:42 | | || 85 | HASH UNIQUE | | 1528 | 110K| 3427 (1)| 00:00:42 | | || 86 | HASH GROUP BY | | 1528 | 110K| 3427 (1)| 00:00:42 | | || 87 | VIEW | VW_DAG_2 | 2907 | 210K| 3427 (1)| 00:00:42 | | || 88 | HASH GROUP BY | | 2907 | 502K| 3427 (1)| 00:00:42 | | ||* 89 | FILTER | | | | | | | ||* 90 | HASH JOIN | | 2907 | 502K| 3426 (1)| 00:00:42 | | || 91 | TABLE ACCESS FULL | TB_FINANCE_PROJECT | 46 | 3082 | 5 (0)| 00:00:01 | | ||* 92 | HASH JOIN | | 3223 | 346K| 3421 (1)| 00:00:42 | | ||* 93 | TABLE ACCESS FULL | TB_FINANCE_PRODUCT | 7841 | 290K| 1230 (1)| 00:00:15 | | || 94 | PARTITION RANGE ITERATOR | | 12241 | 860K| 2191 (1)| 00:00:27 | KEY | KEY ||* 95 | TABLE ACCESS FULL | TB_FINANCE_ORDER | 12241 | 860K| 2191 (1)| 00:00:27 | KEY | KEY |-----------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("ROWNUM_">0) 2 - filter(ROWNUM<=20) 4 - filter(ROWNUM<=20) 5 - access("W1"."PRODUCT_NAME"="W6"."PRODUCT_NAME") 9 - filter(:12>=:11) 10 - access("P"."PROJECT_ID"="J"."ID") 12 - access("P"."ID"="O"."PRODUCT_ID") 13 - filter("P"."PROJECT_ID" IS NOT NULL) 15 - filter(("O"."ORDER_TIME">=:11 AND "O"."TRANSFER_PRODUCT_ID" IS NULL AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."PRODUCT_TYPE"=0 AND "O"."ORDER_TIME"<=:12)) 16 - access("W1"."PRODUCT_NAME"="W3"."PRODUCT_NAME") 21 - filter(:8>=:7) 22 - access("O"."ID"="ID") 27 - filter((MIN("ORDER_TIME")>=:5 AND MIN("ORDER_TIME")<=:6)) 30 - filter(("PRODUCT_TYPE"=0 AND INTERNAL_FUNCTION("ORDER_STATUS") AND "TRANSFER_PRODUCT_ID" IS NULL)) 32 - access("O"."ORDER_TIME"="M"."MIN_TIME") 33 - filter(("O"."PRODUCT_TYPE"=0 AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."TRANSFER_PRODUCT_ID" IS NULL AND "O"."ACCOUNT_ID"="M"."ACCOUNT_ID")) 34 - access("P"."PROJECT_ID"="J"."ID") 36 - access("P"."ID"="O"."PRODUCT_ID") 37 - filter("P"."PROJECT_ID" IS NOT NULL) 39 - filter(("O"."ORDER_TIME">=:7 AND "O"."TRANSFER_PRODUCT_ID" IS NULL AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."PRODUCT_TYPE"=0 AND "O"."ORDER_TIME"<=:8)) 40 - access("W1"."PRODUCT_NAME"="W5"."PRODUCT_NAME") 43 - access("P"."PROJECT_ID"="J"."ID") 45 - filter("P"."PROJECT_ID" IS NOT NULL) 46 - access("W1"."PRODUCT_NAME"="W2"."PRODUCT_NAME") 51 - access("P"."PROJECT_ID"="J"."ID") 57 - filter((MIN("ORDER_TIME")>=:3 AND MIN("ORDER_TIME")<=:4)) 60 - filter(("PRODUCT_TYPE"=0 AND INTERNAL_FUNCTION("ORDER_STATUS") AND "TRANSFER_PRODUCT_ID" IS NULL)) 62 - filter(("O"."PRODUCT_TYPE"=0 AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."TRANSFER_PRODUCT_ID" IS NULL AND "O"."ACCOUNT_ID"="M"."ACCOUNT_ID")) 63 - access("O"."ORDER_TIME"="M"."MIN_TIME") 64 - access("P"."ID"="O"."PRODUCT_ID") 65 - filter("P"."PROJECT_ID" IS NOT NULL) 66 - access("W1"."PRODUCT_NAME"="W4"."PRODUCT_NAME") 69 - filter(:10>=:9) 73 - access("P"."PROJECT_ID"="J"."ID") 75 - access("P"."ID"="O"."PRODUCT_ID") 76 - filter("P"."PROJECT_ID" IS NOT NULL) 78 - filter(("O"."ORDER_TIME">=:9 AND "O"."TRANSFER_PRODUCT_ID" IS NULL AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."PRODUCT_TYPE"=0 AND "O"."ORDER_TIME"<=:10)) 80 - filter("R"."STATUS"=3) 81 - access("O"."ID"="R"."ORDER_ID") filter("R"."ORDER_ID" IS NOT NULL) 82 - access("C"."ID"="R"."PLUS_COUPON_ID") 89 - filter(:2>=:1) 90 - access("P"."PROJECT_ID"="J"."ID") 92 - access("P"."ID"="O"."PRODUCT_ID") 93 - filter("P"."PROJECT_ID" IS NOT NULL) 95 - filter(("O"."ORDER_TIME">=:1 AND "O"."TRANSFER_PRODUCT_ID" IS NULL AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."PRODUCT_TYPE"=0 AND "O"."ORDER_TIME"<=:2))Note----- - dynamic sampling used for this statement (level=4)正确的执行计划
SQL_ID 9z2dtfxqun0xp, child number 4-------------------------------------select * from ( select row_limit.*, rownum rownum_ from (select distinct w1.product_name as productName, w5.year_return_rate as yearReturnRate, NVL(w2.new_count, 0) as newUserCount, NVL(trunc(w2.sum_amount, 2), 0.00) as newTotalSaleAmount, NVL(w3.old_count, 0) as oldUserCount, NVL(trunc(w3.sum_amount, 2), 0.00) as oldTotalSaleAmount, NVL(w1.id_count, 0) as totalOrderCount, NVL(trunc(w1.sum_save, 2), 0.00) as totalCouponAmount, NVL(trunc(w4.sum_plus, 2), 0.00) as totalPlusCouponAmout, NVL(trunc(w6.Income, 2), 0.00) as incomeAmount from ( ( select distinct j.name || ' ' || p.year_return_rate as product_name, count(distinct o.id) as id_count, sum(o.save_amount) as sum_save from tb_finance_order o, tb_finance_product p, tb_finance_project j where p.project_id=j.id and p.id=o.product_id and o.product_type = 0 and o.order_status in (2,3) and o.transfer_product_id is null and o.order_time >= :1 and o.order_time <= :2Plan hash value: 3096314289-------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 109K(100)| | | ||* 1 | VIEW | | 1 | 252 | 109K (1)| 00:21:53 | | ||* 2 | COUNT STOPKEY | | | | | | | || 3 | VIEW | | 1 | 239 | 109K (1)| 00:21:53 | | ||* 4 | SORT UNIQUE STOPKEY | | 1 | 840 | 109K (1)| 00:21:53 | | ||* 5 | HASH JOIN OUTER | | 1 | 840 | 109K (1)| 00:21:53 | | ||* 6 | HASH JOIN OUTER | | 1 | 714 | 108K (1)| 00:21:38 | | ||* 7 | HASH JOIN OUTER | | 1 | 566 | 53478 (1)| 00:10:42 | | ||* 8 | HASH JOIN OUTER | | 1 | 431 | 53472 (1)| 00:10:42 | | ||* 9 | HASH JOIN OUTER | | 1 | 296 | 53449 (1)| 00:10:42 | | || 10 | VIEW | | 1 | 148 | 6 (17)| 00:00:01 | | || 11 | HASH UNIQUE | | 1 | 74 | 6 (17)| 00:00:01 | | || 12 | HASH GROUP BY | | 1 | 74 | 6 (17)| 00:00:01 | | || 13 | VIEW | VW_DAG_2 | 1 | 74 | 6 (17)| 00:00:01 | | || 14 | HASH GROUP BY | | 1 | 177 | 6 (17)| 00:00:01 | | ||* 15 | FILTER | | | | | | | || 16 | NESTED LOOPS | | 1 | 177 | 5 (0)| 00:00:01 | | || 17 | NESTED LOOPS | | 1 | 177 | 5 (0)| 00:00:01 | | || 18 | NESTED LOOPS | | 1 | 110 | 4 (0)| 00:00:01 | | || 19 | PARTITION RANGE ITERATOR | | 1 | 72 | 3 (0)| 00:00:01 | KEY | KEY ||* 20 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_FINANCE_ORDER | 1 | 72 | 3 (0)| 00:00:01 | KEY | KEY ||* 21 | INDEX RANGE SCAN | IDX_ORDER_OTIMENEW | 1 | | 2 (0)| 00:00:01 | KEY | KEY ||* 22 | TABLE ACCESS BY INDEX ROWID | TB_FINANCE_PRODUCT | 1 | 38 | 1 (0)| 00:00:01 | | ||* 23 | INDEX UNIQUE SCAN | TB_FINANCE_PRODUCT_PK | 1 | | 0 (0)| | | ||* 24 | INDEX UNIQUE SCAN | SYS_C0020304 | 1 | | 0 (0)| | | || 25 | TABLE ACCESS BY INDEX ROWID | TB_FINANCE_PROJECT | 1 | 67 | 1 (0)| 00:00:01 | | || 26 | VIEW | | 1 | 148 | 53443 (1)| 00:10:42 | | || 27 | HASH GROUP BY | | 1 | 387 | 53443 (1)| 00:10:42 | | || 28 | VIEW | VW_DAG_0 | 1 | 387 | 53442 (1)| 00:10:42 | | || 29 | HASH GROUP BY | | 1 | 208 | 53442 (1)| 00:10:42 | | ||* 30 | FILTER | | | | | | | || 31 | NESTED LOOPS ANTI | | 1 | 208 | 53441 (1)| 00:10:42 | | || 32 | NESTED LOOPS | | 1 | 206 | 5 (0)| 00:00:01 | | || 33 | NESTED LOOPS | | 1 | 139 | 4 (0)| 00:00:01 | | || 34 | PARTITION RANGE ITERATOR | | 1 | 101 | 3 (0)| 00:00:01 | KEY | KEY ||* 35 | TABLE ACCESS BY LOCAL INDEX ROWID | TB_FINANCE_ORDER | 1 | 101 | 3 (0)| 00:00:01 | KEY | KEY ||* 36 | INDEX RANGE SCAN | IDX_ORDER_OTIMENEW | 1 | | 2 (0)| 00:00:01 | KEY | KEY ||* 37 | TABLE ACCESS BY INDEX ROWID | TB_FINANCE_PRODUCT | 1 | 38 | 1 (0)| 00:00:01 | | ||* 38 | INDEX UNIQUE SCAN | TB_FINANCE_PRODUCT_PK | 1 | | 0 (0)| | | || 39 | TABLE ACCESS BY INDEX ROWID | TB_FINANCE_PROJECT | 1 | 67 | 1 (0)| 00:00:01 | | ||* 40 | INDEX UNIQUE SCAN | SYS_C0020304 | 1 | | 0 (0)| | | || 41 | VIEW PUSHED PREDICATE | VW_NSO_1 | 1 | 2 | 53436 (1)| 00:10:42 | | ||* 42 | HASH JOIN | | 1 | 108 | 53436 (1)| 00:10:42 | | || 43 | PARTITION RANGE ALL | | 1 | 70 | 56 (0)| 00:00:01 | 1 |1048575||* 44 | TABLE ACCESS BY LOCAL INDEX ROWID | TB_FINANCE_ORDER | 1 | 70 | 56 (0)| 00:00:01 | 1 |1048575||* 45 | INDEX RANGE SCAN | TB_FINANCE_ORDER_IDX | 1 | | 55 (0)| 00:00:01 | 1 |1048575|| 46 | VIEW | | 430 | 16340 | 53380 (1)| 00:10:41 | | ||* 47 | FILTER | | | | | | | || 48 | SORT GROUP BY | | 430 | 18920 | 53380 (1)| 00:10:41 | | || 49 | PARTITION RANGE ALL | | 629K| 26M| 53364 (1)| 00:10:41 | 1 |1048575||* 50 | TABLE ACCESS FULL | TB_FINANCE_ORDER | 629K| 26M| 53364 (1)| 00:10:41 | 1 |1048575|| 51 | VIEW | | 1 | 135 | 23 (5)| 00:00:01 | | || 52 | HASH GROUP BY | | 1 | 205 | 23 (5)| 00:00:01 | | ||* 53 | FILTER | | | | | | | || 54 | NESTED LOOPS | | 1 | 205 | 22 (0)| 00:00:01 | | || 55 | NESTED LOOPS | | 1 | 205 | 22 (0)| 00:00:01 | | || 56 | NESTED LOOPS | | 1 | 138 | 21 (0)| 00:00:01 | | || 57 | NESTED LOOPS | | 1 | 100 | 20 (0)| 00:00:01 | | || 58 | NESTED LOOPS | | 1 | 85 | 19 (0)| 00:00:01 | | || 59 | PARTITION RANGE ITERATOR | | 1 | 73 | 3 (0)| 00:00:01 | KEY | KEY ||* 60 | TABLE ACCESS BY LOCAL INDEX ROWID | TB_FINANCE_ORDER | 1 | 73 | 3 (0)| 00:00:01 | KEY | KEY ||* 61 | INDEX RANGE SCAN | IDX_ORDER_OTIMENEW | 1 | | 2 (0)| 00:00:01 | KEY | KEY || 62 | PARTITION RANGE ALL | | 1 | 12 | 16 (0)| 00:00:01 | 1 |1048575||* 63 | TABLE ACCESS BY LOCAL INDEX ROWID | TB_PLUS_RECEIVE_CODE | 1 | 12 | 16 (0)| 00:00:01 | 1 |1048575||* 64 | INDEX RANGE SCAN | IDX_PLUS_RECEIVE_ORDERID | 1 | | 16 (0)| 00:00:01 | 1 |1048575|| 65 | TABLE ACCESS BY INDEX ROWID | TB_PLUS_COUPON | 1 | 15 | 1 (0)| 00:00:01 | | ||* 66 | INDEX UNIQUE SCAN | IDX_PLUS_COUPON_ID | 1 | | 0 (0)| | | ||* 67 | TABLE ACCESS BY INDEX ROWID | TB_FINANCE_PRODUCT | 1 | 38 | 1 (0)| 00:00:01 | | ||* 68 | INDEX UNIQUE SCAN | TB_FINANCE_PRODUCT_PK | 1 | | 0 (0)| | | ||* 69 | INDEX UNIQUE SCAN | SYS_C0020304 | 1 | | 0 (0)| | | || 70 | TABLE ACCESS BY INDEX ROWID | TB_FINANCE_PROJECT | 1 | 67 | 1 (0)| 00:00:01 | | || 71 | VIEW | | 1 | 135 | 6 (17)| 00:00:01 | | || 72 | HASH UNIQUE | | 1 | 160 | 6 (17)| 00:00:01 | | || 73 | HASH GROUP BY | | 1 | 160 | 6 (17)| 00:00:01 | | ||* 74 | FILTER | | | | | | | || 75 | NESTED LOOPS | | 1 | 160 | 5 (0)| 00:00:01 | | || 76 | NESTED LOOPS | | 1 | 160 | 5 (0)| 00:00:01 | | || 77 | NESTED LOOPS | | 1 | 89 | 4 (0)| 00:00:01 | | || 78 | PARTITION RANGE ITERATOR | | 1 | 47 | 3 (0)| 00:00:01 | KEY | KEY ||* 79 | TABLE ACCESS BY LOCAL INDEX ROWID | TB_FINANCE_ORDER | 1 | 47 | 3 (0)| 00:00:01 | KEY | KEY ||* 80 | INDEX RANGE SCAN | IDX_ORDER_OTIMENEW | 1 | | 2 (0)| 00:00:01 | KEY | KEY ||* 81 | TABLE ACCESS BY INDEX ROWID | TB_FINANCE_PRODUCT | 1 | 42 | 1 (0)| 00:00:01 | | ||* 82 | INDEX UNIQUE SCAN | TB_FINANCE_PRODUCT_PK | 1 | | 0 (0)| | | ||* 83 | INDEX UNIQUE SCAN | SYS_C0020304 | 1 | | 0 (0)| | | || 84 | TABLE ACCESS BY INDEX ROWID | TB_FINANCE_PROJECT | 1 | 71 | 1 (0)| 00:00:01 | | || 85 | VIEW | | 102 | 15096 | 54677 (1)| 00:10:57 | | || 86 | HASH GROUP BY | | 102 | 39474 | 54677 (1)| 00:10:57 | | || 87 | VIEW | VW_DAG_1 | 102 | 39474 | 54676 (1)| 00:10:57 | | || 88 | HASH GROUP BY | | 102 | 21828 | 54676 (1)| 00:10:57 | | ||* 89 | HASH JOIN | | 102 | 21828 | 54675 (1)| 00:10:57 | | || 90 | TABLE ACCESS FULL | TB_FINANCE_PROJECT | 46 | 3082 | 5 (0)| 00:00:01 | | || 91 | NESTED LOOPS | | 113 | 16611 | 54670 (1)| 00:10:57 | | || 92 | NESTED LOOPS | | 430 | 16611 | 54670 (1)| 00:10:57 | | || 93 | NESTED LOOPS | | 430 | 46870 | 54240 (1)| 00:10:51 | | || 94 | VIEW | | 430 | 16340 | 53380 (1)| 00:10:41 | | ||* 95 | FILTER | | | | | | | || 96 | HASH GROUP BY | | 430 | 18920 | 53380 (1)| 00:10:41 | | || 97 | PARTITION RANGE ALL | | 629K| 26M| 53364 (1)| 00:10:41 | 1 |1048575||* 98 | TABLE ACCESS FULL | TB_FINANCE_ORDER | 629K| 26M| 53364 (1)| 00:10:41 | 1 |1048575|| 99 | PARTITION RANGE ITERATOR | | 1 | 71 | 2 (0)| 00:00:01 | KEY | KEY ||*100 | TABLE ACCESS BY LOCAL INDEX ROWID | TB_FINANCE_ORDER | 1 | 71 | 2 (0)| 00:00:01 | KEY | KEY ||*101 | INDEX RANGE SCAN | IDX_ORDER_OTIMENEW | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||*102 | INDEX UNIQUE SCAN | TB_FINANCE_PRODUCT_PK | 1 | | 0 (0)| | | ||*103 | TABLE ACCESS BY INDEX ROWID | TB_FINANCE_PRODUCT | 1 | 38 | 1 (0)| 00:00:01 | | || 104 | VIEW | | 105 | 13230 | 1236 (1)| 00:00:15 | | || 105 | HASH UNIQUE | | 105 | 8610 | 1236 (1)| 00:00:15 | | ||*106 | HASH JOIN | | 7072 | 566K| 1235 (1)| 00:00:15 | | || 107 | TABLE ACCESS FULL | TB_FINANCE_PROJECT | 46 | 3082 | 5 (0)| 00:00:01 | | ||*108 | TABLE ACCESS FULL | TB_FINANCE_PRODUCT | 7841 | 114K| 1230 (1)| 00:00:15 | | |-------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("ROWNUM_">0) 2 - filter(ROWNUM<=20) 4 - filter(ROWNUM<=20) 5 - access("W1"."PRODUCT_NAME"="W5"."PRODUCT_NAME") 6 - access("W1"."PRODUCT_NAME"="W2"."PRODUCT_NAME") 7 - access("W1"."PRODUCT_NAME"="W6"."PRODUCT_NAME") 8 - access("W1"."PRODUCT_NAME"="W4"."PRODUCT_NAME") 9 - access("W1"."PRODUCT_NAME"="W3"."PRODUCT_NAME") 15 - filter(:2>=:1) 20 - filter(("O"."TRANSFER_PRODUCT_ID" IS NULL AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."PRODUCT_TYPE"=0)) 21 - access("O"."ORDER_TIME">=:1 AND "O"."ORDER_TIME"<=:2) 22 - filter("P"."PROJECT_ID" IS NOT NULL) 23 - access("P"."ID"="O"."PRODUCT_ID") 24 - access("P"."PROJECT_ID"="J"."ID") 30 - filter(:8>=:7) 35 - filter(("O"."TRANSFER_PRODUCT_ID" IS NULL AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."PRODUCT_TYPE"=0)) 36 - access("O"."ORDER_TIME">=:7 AND "O"."ORDER_TIME"<=:8) 37 - filter("P"."PROJECT_ID" IS NOT NULL) 38 - access("P"."ID"="O"."PRODUCT_ID") 40 - access("P"."PROJECT_ID"="J"."ID") 42 - access("O"."ACCOUNT_ID"="M"."ACCOUNT_ID" AND "O"."ORDER_TIME"="M"."MIN_TIME") 44 - filter(("O"."PRODUCT_TYPE"=0 AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."TRANSFER_PRODUCT_ID" IS NULL)) 45 - access("O"."ID"="O"."ID") 47 - filter((MIN("ORDER_TIME")>=:5 AND MIN("ORDER_TIME")<=:6)) 50 - filter(("PRODUCT_TYPE"=0 AND INTERNAL_FUNCTION("ORDER_STATUS") AND "TRANSFER_PRODUCT_ID" IS NULL)) 53 - filter(:10>=:9) 60 - filter(("O"."TRANSFER_PRODUCT_ID" IS NULL AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."PRODUCT_TYPE"=0)) 61 - access("O"."ORDER_TIME">=:9 AND "O"."ORDER_TIME"<=:10) 63 - filter("R"."STATUS"=3) 64 - access("O"."ID"="R"."ORDER_ID") filter("R"."ORDER_ID" IS NOT NULL) 66 - access("C"."ID"="R"."PLUS_COUPON_ID") 67 - filter("P"."PROJECT_ID" IS NOT NULL) 68 - access("P"."ID"="O"."PRODUCT_ID") 69 - access("P"."PROJECT_ID"="J"."ID") 74 - filter(:12>=:11) 79 - filter(("O"."TRANSFER_PRODUCT_ID" IS NULL AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."PRODUCT_TYPE"=0)) 80 - access("O"."ORDER_TIME">=:11 AND "O"."ORDER_TIME"<=:12) 81 - filter("P"."PROJECT_ID" IS NOT NULL) 82 - access("P"."ID"="O"."PRODUCT_ID") 83 - access("P"."PROJECT_ID"="J"."ID") 89 - access("P"."PROJECT_ID"="J"."ID") 95 - filter((MIN("ORDER_TIME")>=:3 AND MIN("ORDER_TIME")<=:4)) 98 - filter(("PRODUCT_TYPE"=0 AND INTERNAL_FUNCTION("ORDER_STATUS") AND "TRANSFER_PRODUCT_ID" IS NULL)) 100 - filter(("O"."PRODUCT_TYPE"=0 AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."TRANSFER_PRODUCT_ID" IS NULL AND "O"."ACCOUNT_ID"="M"."ACCOUNT_ID")) 101 - access("O"."ORDER_TIME"="M"."MIN_TIME") 102 - access("P"."ID"="O"."PRODUCT_ID") 103 - filter("P"."PROJECT_ID" IS NOT NULL) 106 - access("P"."PROJECT_ID"="J"."ID") 108 - filter("P"."PROJECT_ID" IS NOT NULL)Note----- - dynamic sampling used for this statement (level=4) - cardinality feedback used for this statement看到错误的执行计划的顺序是
w1-w4 w1-w2 w1-w5 w1-w3 w1-w6
正确的执行计划顺序是
w1-w3 w1-w4 w1-w6 w1-w2 w1-w5
错误的执行计划评估的每个内部视图的行数
w1:1 w1:1w4:1528 w4:1 实际值11w2:102 w2:102w5:1528 w5 105 实际值105w6:2907 w6:1 实际值11第一列是错误的执行计划评估，第二列是正确的执行计划评估
通过查询实际数据确认，错误的执行计划的评估数据与实际值相差较大，可以看到基数反馈是拿到了正确的执行计划，但是由于错误的执行计划执行时间很长，并且执行次数很多，导致了热点争用，还是导致了cpu的上升，需要手工处理。
下面再次看下评估错误的部分的sql，将其单独执行查看执行计划
select /* test */ distinct j.name || ' ' || p.year_return_rate as product_name, count(distinct o.id) as id_count, sum(o.save_amount) as sum_save from finance.tb_finance_order o, finance.tb_finance_product p, finance.tb_finance_project j where p.project_id=j.id and p.id=o.product_id and o.product_type = 0 and o.order_status in (2,3) and o.transfer_product_id is null and o.order_time >= to_date('2017-11-17','yyyy-mm-dd') and o.order_time <= to_date('2017-11-24','yyyy-mm-dd') group by j.name,p.year_return_rate;SQL_ID 6a35tu3sgfp4g, child number 0-------------------------------------select /* test */ distinct j.name || ' ' || p.year_return_rate as product_name, count(distinct o.id) as id_count, sum(o.save_amount) as sum_save from finance.tb_finance_order o, finance.tb_finance_product p, finance.tb_finance_project j where p.project_id=j.id and p.id=o.product_id and o.product_type = 0 and o.order_status in (2,3) and o.transfer_product_id is null and o.order_time >= to_date('2017-11-17','yyyy-mm-dd') and o.order_time <= to_date('2017-11-24','yyyy-mm-dd') group by j.name,p.year_return_ratePlan hash value: 453986200-------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 3704 (100)| | | || 1 | HASH UNIQUE | | 1528 | 110K| 3704 (1)| 00:00:45 | | || 2 | HASH GROUP BY | | 1528 | 110K| 3704 (1)| 00:00:45 | | || 3 | VIEW | VW_DAG_0 | 1830 | 132K| 3704 (1)| 00:00:45 | | || 4 | HASH GROUP BY | | 1830 | 316K| 3704 (1)| 00:00:45 | | ||* 5 | HASH JOIN | | 1830 | 316K| 3703 (1)| 00:00:45 | | || 6 | TABLE ACCESS FULL | TB_FINANCE_PROJECT | 46 | 3082 | 5 (0)| 00:00:01 | | ||* 7 | HASH JOIN | | 2029 | 217K| 3698 (1)| 00:00:45 | | ||* 8 | TABLE ACCESS FULL | TB_FINANCE_PRODUCT | 7841 | 290K| 1230 (1)| 00:00:15 | | || 9 | PARTITION RANGE SINGLE| | 7706 | 541K| 2468 (1)| 00:00:30 | 27 | 27 ||* 10 | TABLE ACCESS FULL | TB_FINANCE_ORDER | 7706 | 541K| 2468 (1)| 00:00:30 | 27 | 27 |-------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 5 - access("P"."PROJECT_ID"="J"."ID") 7 - access("P"."ID"="O"."PRODUCT_ID") 8 - filter("P"."PROJECT_ID" IS NOT NULL) 10 - filter(("O"."ORDER_TIME">=TIMESTAMP' 2017-11-17 00:00:00' AND "O"."TRANSFER_PRODUCT_ID" IS NULL AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."ORDER_TIME"<=TIMESTAMP' 2017-11-24 00:00:00' AND "O"."PRODUCT_TYPE"=0))我们看到也是评估出错误了，再次查看该sql的执行计划，发现谓词反馈给了一个正确的执行计划，如下：
SQL_ID 6a35tu3sgfp4g, child number 1-------------------------------------select /* test */ distinct j.name || ' ' || p.year_return_rate as product_name, count(distinct o.id) as id_count, sum(o.save_amount) as sum_save from finance.tb_finance_order o, finance.tb_finance_product p, finance.tb_finance_project j where p.project_id=j.id and p.id=o.product_id and o.product_type = 0 and o.order_status in (2,3) and o.transfer_product_id is null and o.order_time >= to_date('2017-11-17','yyyy-mm-dd') and o.order_time <= to_date('2017-11-24','yyyy-mm-dd') group by j.name,p.year_return_ratePlan hash value: 453986200-------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 3704 (100)| | | || 1 | HASH UNIQUE | | 11 | 814 | 3704 (1)| 00:00:45 | | || 2 | HASH GROUP BY | | 11 | 814 | 3704 (1)| 00:00:45 | | || 3 | VIEW | VW_DAG_0 | 1830 | 132K| 3704 (1)| 00:00:45 | | || 4 | HASH GROUP BY | | 1830 | 316K| 3704 (1)| 00:00:45 | | ||* 5 | HASH JOIN | | 1830 | 316K| 3703 (1)| 00:00:45 | | || 6 | TABLE ACCESS FULL | TB_FINANCE_PROJECT | 46 | 3082 | 5 (0)| 00:00:01 | | ||* 7 | HASH JOIN | | 2029 | 217K| 3698 (1)| 00:00:45 | | ||* 8 | TABLE ACCESS FULL | TB_FINANCE_PRODUCT | 7841 | 290K| 1230 (1)| 00:00:15 | | || 9 | PARTITION RANGE SINGLE| | 7706 | 541K| 2468 (1)| 00:00:30 | 27 | 27 ||* 10 | TABLE ACCESS FULL | TB_FINANCE_ORDER | 7706 | 541K| 2468 (1)| 00:00:30 | 27 | 27 |-------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 5 - access("P"."PROJECT_ID"="J"."ID") 7 - access("P"."ID"="O"."PRODUCT_ID") 8 - filter("P"."PROJECT_ID" IS NOT NULL) 10 - filter(("O"."ORDER_TIME">=TIMESTAMP' 2017-11-17 00:00:00' AND "O"."TRANSFER_PRODUCT_ID" IS NULL AND INTERNAL_FUNCTION("O"."ORDER_STATUS") AND "O"."ORDER_TIME"<=TIMESTAMP' 2017-11-24 00:00:00' AND "O"."PRODUCT_TYPE"=0))Note----- - cardinality feedback used for this statement对比2个执行计划，发现HASH GROUP BY给出的反馈是错误的，oracle在hash group by上之前版本问题就很多，我的环境是11.2.0.4不知道是否是一个bug，待确认，此处测试，讲hash group by关闭，使用sort group by，执行计划评估返回的值是一样的。在出问题的时候，讲中间件重启过，重启后，数据库中的游标中依然存在坏的执行计划，oracle没有将坏的执行计划在共享池中去除掉。
阅读全文
0 0
- 11g 一个sql案例 hash group by
- group by hash join优化案例
- 关于group by 的一个SQL题目
- SQL循序渐进(11)GROUP BY子句
- sql-2017-11-09 group by cnt
- sql group by order by
- sql group by，order by
- SQL GROUP BY
- sql group by :ZT
- SQL Group by 学习
- SQL Group By
- Sql group by 用法
- sql 语句group by
- SQL: GROUP BY Clause
- SQL Group by
- SQL Group by 学习
- sql "group by"整理
- sql 语句group by
- 如何优雅的添加MGR节点？
- java获取系统属性
- 【Java】错误解决笔记
- STL之算术与集合
- 文章标题
- 11g 一个sql案例 hash group by
- 待完成列表技能树
- iOS之UITableView如何优雅的插入数据: 旋转180°下拉加载cell顶置
- Java 运算符
- android studio miui install failed.小米手机USB调试应用失败。
- 趣图：Java 开发
- 模版——函数模版、类模版
- 【C++专题】static_cast, dynamic_cast, const_cast探讨
- 8.1.5 字符串与字符、字节数组
