一天查看数据库长会话，发现1个sql跑得非常慢，1个多小时不出结果，花了点时间把它给优化了。
优化前：
SELECT 20131023,
"A2"."ORG_ID",
COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
WHEN 'DP' THEN
"A2"."RES_CODE"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
WHEN 'BOX' THEN
"A2"."RES_CODE"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
WHEN 'ONU' THEN
"A2"."RES_CODE"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
WHEN 'OBD' THEN
"A2"."RES_CODE"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE
WHEN ("A1"."CON_TYPE" = '001' AND "A2"."RES_TYPE" = 'DP') THEN
"A1"."RES_ID"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE
WHEN ("A1"."CON_TYPE" = '002' AND "A2"."RES_TYPE" = 'BOX') THEN
"A1"."RES_ID"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE
WHEN ("A1"."CON_TYPE" = '0011' AND "A2"."RES_TYPE" = 'ONU') THEN
"A1"."RES_ID"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE
WHEN ("A1"."CON_TYPE" = '0022' AND "A2"."RES_TYPE" = 'OBD') THEN
"A1"."RES_ID"
END,
'nls_sort=''BINARY'''))
FROM "CRM_SZ"."AAA" "A2",
"CRM_SZ"."BBB" "A1"
WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE"
GROUP BY "A2"."ORG_ID"
运行计划：
Plan hash value: 2627707252
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1065 | 3 (34)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 1065 | 3 (34)| 00:00:01 |
| 2 | NESTED LOOPS OUTER| | 1 | 1065 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| AAA | 1 | 539 | 2 (0)| 00:00:01 |
|* 4 | INDEX FULL SCAN | IX_MO_CON_VALUE | 1 | 526 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A1"."RES_ID"(+)="A2"."RES_CODE")
filter("A1"."RES_ID"(+)="A2"."RES_CODE")
cbo估算错了，rows全是1，导致走nl
手工count了一把：
select count(*) from "CRM_SZ"."AAA" ;--1365564
select count(*) from "CRM_SZ"."BBB";--119949
走nl那岂不是sb啦。
第一次优化后：
SELECT/*+use_hash(A1,A2) swap_join_inputs(A1)*/20131023,
"A2"."ORG_ID",
COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
WHEN 'DP' THEN
"A2"."RES_CODE"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
WHEN 'BOX' THEN
"A2"."RES_CODE"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
WHEN 'ONU' THEN
"A2"."RES_CODE"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
WHEN 'OBD' THEN
"A2"."RES_CODE"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE
WHEN ("A1"."CON_TYPE" = '001' AND "A2"."RES_TYPE" = 'DP') THEN
"A1"."RES_ID"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE
WHEN ("A1"."CON_TYPE" = '002' AND "A2"."RES_TYPE" = 'BOX') THEN
"A1"."RES_ID"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE
WHEN ("A1"."CON_TYPE" = '0011' AND "A2"."RES_TYPE" = 'ONU') THEN
"A1"."RES_ID"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE
WHEN ("A1"."CON_TYPE" = '0022' AND "A2"."RES_TYPE" = 'OBD') THEN
"A1"."RES_ID"
END,
'nls_sort=''BINARY'''))
FROM "CRM_SZ"."AAA" "A2",
"CRM_SZ"."BBB" "A1"
WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE"
GROUP BY "A2"."ORG_ID"
63 rows selected.
Elapsed: 00:00:47.64
Execution Plan
----------------------------------------------------------
Plan hash value: 3074972763
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1065 | 4 (50)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 1065 | 4 (50)| 00:00:01 |
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 1065 | 3 (34)| 00:00:01 |
| 3 | INDEX FULL SCAN | IX_MO_CON_VALUE | 1 | 526 | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | AAA | 1 | 539 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A1"."RES_ID"(+)="A2"."RES_CODE")
Statistics
----------------------------------------------------------
1065 recursive calls
3 db block gets
13375 consistent gets
16369 physical reads
0 redo size
4862 bytes sent via SQL*Net to client
791 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
12 sorts (memory)
1 sorts (disk)
63 rows processed
第二次优化后：
SELECT/*+use_hash(A1,A2) full(A1) full(A2) parallel(A1,5) parallel(A2,5) swap_join_inputs(A1)*/20131023,
"A2"."ORG_ID",
COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
WHEN 'DP' THEN
"A2"."RES_CODE"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
WHEN 'BOX' THEN
"A2"."RES_CODE"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
WHEN 'ONU' THEN
"A2"."RES_CODE"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"
WHEN 'OBD' THEN
"A2"."RES_CODE"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE
WHEN ("A1"."CON_TYPE" = '001' AND "A2"."RES_TYPE" = 'DP') THEN
"A1"."RES_ID"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE
WHEN ("A1"."CON_TYPE" = '002' AND "A2"."RES_TYPE" = 'BOX') THEN
"A1"."RES_ID"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE
WHEN ("A1"."CON_TYPE" = '0011' AND "A2"."RES_TYPE" = 'ONU') THEN
"A1"."RES_ID"
END,
'nls_sort=''BINARY''')),
COUNT(DISTINCT NLSSORT(CASE
WHEN ("A1"."CON_TYPE" = '0022' AND "A2"."RES_TYPE" = 'OBD') THEN
"A1"."RES_ID"
END,
'nls_sort=''BINARY'''))
FROM "CRM_SZ"."AAA" "A2",
"CRM_SZ"."BBB" "A1"
WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE"
GROUP BY "A2"."ORG_ID";
63 rows selected.
Elapsed: 00:00:10.21
Execution Plan
----------------------------------------------------------
Plan hash value: 4044842257
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1065 | 6 (34)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 1 | 1065 | 6 (34)| 00:00:01 | Q1,04 | P->S | QC (RAND) |
| 3 | SORT GROUP BY | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,04 | PCWP | |
| 4 | PX RECEIVE | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,04 | PCWP | |
| 5 | PX SEND HASH | :TQ10003 | 1 | 1065 | 6 (34)| 00:00:01 | Q1,03 | P->P | HASH |
| 6 | SORT GROUP BY | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,03 | PCWP | |
| 7 | PX RECEIVE | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,03 | PCWP | |
| 8 | PX SEND HASH | :TQ10002 | 1 | 1065 | 6 (34)| 00:00:01 | Q1,02 | P->P | HASH |
| 9 | SORT GROUP BY | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,02 | PCWP | |
|* 10 | HASH JOIN RIGHT OUTER| | 1 | 1065 | 5 (20)| 00:00:01 | Q1,02 | PCWP | |
| 11 | PX RECEIVE | | 1 | 526 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10000 | 1 | 526 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 13 | PX BLOCK ITERATOR | | 1 | 526 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 14 | TABLE ACCESS FULL| BBB | 1 | 526 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 15 | PX RECEIVE | | 1 | 539 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 16 | PX SEND HASH | :TQ10001 | 1 | 539 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 17 | PX BLOCK ITERATOR | | 1 | 539 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 18 | TABLE ACCESS FULL| AAA | 1 | 539 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("A1"."RES_ID"(+)="A2"."RES_CODE")
Statistics
----------------------------------------------------------
585 recursive calls
4 db block gets
14267 consistent gets
13126 physical reads
808 redo size
4888 bytes sent via SQL*Net to client
840 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
23 sorts (memory)
0 sorts (disk)
63 rows processed优化前。运行计划走了nl。1个多小时查不出结果
第一次优化后，首次00:00:47.64能够出结果
第二次优化后，首次00:00:10.21出结果，反复运行（有缓存）的情况下3s出结果。