下面是一条在一体机上面运行的SQL语句，消耗了大量的CPU，SQL为精简一些列后来测试的，但是对整个SQL的性能无影响。
欢迎大家加入QQ群：
相互学习ORACLE技术
SELECT OFFER_SERV_SUM AS N37364,
LOCAL_CODE LOCAL_CODE,
AREA_ID AREA_ID,
DVLP_AREA_ID MG_AREA_ID,
DVLP_ORG_ID ORG_ID,
CASE
WHEN COLUMN_111111 IN
(SELECT LOCAL_ITEM_CODE
FROM XXXXXXXXXXX.XXXXXXXXX
WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID = 64)
THEN
1
ELSE
0
END
|| CASE
WHEN TO_CHAR (COLUMN_111111) IN
(SELECT LOCAL_ITEM_CODE
FROM XXXXXXXXXXX.XXXXXXXXX A
WHERE PROV_TYPE_ID = 49
AND PROV_ITEM_ID IN (85, 86, 68, 69, 70, 71))
THEN
1
ELSE
0
END
|| CASE
WHEN TO_CHAR (COLUMN_111111) IN
(SELECT COLUMN_111111
FROM XXXXXXXXXXX.BBBBBBBBBBBBB
WHERE BT_CODE = 1
AND ( UPPER (NAME) LIKE '{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}4S{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}'
OR NAME LIKE '{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}??{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}'))
THEN
1
ELSE
0
END
|| CASE
WHEN TO_CHAR (COLUMN_111111) IN
(SELECT LOCAL_ITEM_CODE
FROM XXXXXXXXXXX.XXXXXXXXX
WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID = 64)
THEN
1
ELSE
0
END
|| CASE
WHEN TO_CHAR (COLUMN_111111) IN
(SELECT COLUMN_111111 FROM XXXXXXXXXXX.CCCCCCCCCCC)
THEN
1
ELSE
0
END
|| CASE
WHEN TO_CHAR (COLUMN_111111) IN
(SELECT COLUMN_111111
FROM XXXXXXXXXXX.BBBBBBBBBBBBB
WHERE BT_CODE = 2
AND ( UPPER (NAME) LIKE '{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}4S{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}'
OR NAME LIKE '{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}??{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}'))
THEN
1
ELSE
0
END
C_ALL
FROM XXXXXXXXXXX.EEEEEEEEEEEEEEEEEEEE_D_201407 PARTITION (P20140727) A
WHERE LOCAL_CODE = 'XXX'
这条语句，运行了30分钟，都没有任何的发应，并且下面也可以看到处理的行数为190W。
ERROR:
ORA-01013: user requested cancel of current operation
1908315 rows selected.
Elapsed: 00:27:09.83
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
46 recursive calls
325790 db block gets
180241878 consistent gets
3294 physical reads
94580 redo size
42593717 bytes sent via SQL*Net to client
1399955 bytes received via SQL*Net from client
127222 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1908315 rows processed
下面是SQL运行的统计信息，是平均值
CPU ELA DISK GET ROWS ROWS
PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE FETCHES
---------------- ---------------- ---------------- ---------------- ---------------- ----------------
5,083,968,119 5,350,213,227 22,610 338,280,770 4,302,704 4,302,704
下面是改写SQL后的运行结果
SELECT OFFER_SERV_SUM AS N37364,
LOCAL_CODE LOCAL_CODE,
AREA_ID AREA_ID,
DVLP_AREA_ID MG_AREA_ID,
DVLP_ORG_ID ORG_ID,
CASE WHEN a.COLUMN_111111 = b.LOCAL_ITEM_CODE THEN 1 ELSE 0 END
|| CASE
WHEN TO_CHAR (a.COLUMN_111111) = e.LOCAL_ITEM_CODE THEN 1
ELSE 0
END
|| CASE
WHEN TO_CHAR (a.COLUMN_111111) = c.COLUMN_111111
AND c.bt_code = 1
THEN
1
ELSE
0
END
|| CASE WHEN a.COLUMN_111111 = b.LOCAL_ITEM_CODE THEN 1 ELSE 0 END
|| CASE
WHEN TO_CHAR (a.COLUMN_111111) = d.COLUMN_111111 THEN 1
ELSE 0
END
|| CASE
WHEN TO_CHAR (a.COLUMN_111111) = c.COLUMN_111111
AND c.bt_code = 2
THEN
1
ELSE
0
END
C_ALL
FROM XXXXXXXXXXX.EEEEEEEEEEEEEEEEEEEE_D_201407 PARTITION (P20140727) A,
(SELECT DISTINCT LOCAL_ITEM_CODE
FROM XXXXXXXXXXX.XXXXXXXXX
WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID = 64) b,
(SELECT DISTINCT COLUMN_111111, bt_code
FROM XXXXXXXXXXX.BBBBBBBBBBBBB
WHERE BT_CODE IN (1, 2)
AND (UPPER (NAME) LIKE '{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}4S{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}' OR NAME LIKE '{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}??{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}')) c,
(SELECT DISTINCT COLUMN_111111 FROM XXXXXXXXXXX.CCCCCCCCCCC) d,
(SELECT DISTINCT LOCAL_ITEM_CODE
FROM XXXXXXXXXXX.XXXXXXXXX A
WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID IN (85, 86, 68, 69, 70, 71)) e
WHERE LOCAL_CODE = 'XXX'
AND a.COLUMN_111111 = b.LOCAL_ITEM_CODE(+)
AND TO_CHAR (a.COLUMN_111111) = c.COLUMN_111111(+)
AND TO_CHAR (a.COLUMN_111111) = d.COLUMN_111111(+)
AND TO_CHAR (a.COLUMN_111111) = e.LOCAL_ITEM_CODE(+)
SQL> SELECT OFFER_SERV_SUM AS N37364,
2 LOCAL_CODE LOCAL_CODE,
3 AREA_ID AREA_ID,
4 DVLP_AREA_ID MG_AREA_ID,
5 DVLP_ORG_ID ORG_ID,
6 CASE WHEN a.COLUMN_111111 = b.LOCAL_ITEM_CODE THEN 1 ELSE 0 END
7 || CASE
8 WHEN TO_CHAR (a.COLUMN_111111) = e.LOCAL_ITEM_CODE THEN 1
9 ELSE 0
10 END
11 || CASE
12 WHEN TO_CHAR (a.COLUMN_111111) = c.COLUMN_111111
13 AND c.bt_code = 1
14 THEN
15 1
16 ELSE
17 0
18 END
19 || CASE WHEN a.COLUMN_111111 = b.LOCAL_ITEM_CODE THEN 1 ELSE 0 END
20 || CASE
21 WHEN TO_CHAR (a.COLUMN_111111) = d.COLUMN_111111 THEN 1
22 ELSE 0
23 END
24 || CASE
25 WHEN TO_CHAR (a.COLUMN_111111) = c.COLUMN_111111
26 AND c.bt_code = 2
27 THEN
28 1
29 ELSE
30 0
31 END
32 C_ALL
33 FROM XXXXXXXXXXX.EEEEEEEEEEEEEEEEEEEE_D_201407 PARTITION (P20140727) A,
34 (SELECT DISTINCT LOCAL_ITEM_CODE
35 FROM XXXXXXXXXXX.XXXXXXXXX
36 WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID = 64) b,
37 (SELECT DISTINCT COLUMN_111111, bt_code
38 FROM XXXXXXXXXXX.BBBBBBBBBBBBB
39 WHERE BT_CODE IN (1, 2)
40 AND (UPPER (NAME) LIKE '{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}4S{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}' OR NAME LIKE '{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}??{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}')) c,
41 (SELECT DISTINCT COLUMN_111111 FROM XXXXXXXXXXX.CCCCCCCCCCC) d,
42 (SELECT DISTINCT LOCAL_ITEM_CODE
43 FROM XXXXXXXXXXX.XXXXXXXXX A
44 WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID IN (85, 86, 68, 69, 70, 71)) e
45 WHERE LOCAL_CODE = 'XXX'
46 AND a.COLUMN_111111 = b.LOCAL_ITEM_CODE(+)
47 AND TO_CHAR (a.COLUMN_111111) = c.COLUMN_111111(+)
48 AND TO_CHAR (a.COLUMN_111111) = d.COLUMN_111111(+)
49 AND TO_CHAR (a.COLUMN_111111) = e.LOCAL_ITEM_CODE(+);
4302704 rows selected.
Elapsed: 00:00:28.65
这里可以看到29S出结果
Execution Plan
----------------------------------------------------------
Plan hash value: 274455294
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost ({ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3962K| 498M| 3487 (7)| 00:00:01 | | |
|* 1 | HASH JOIN RIGHT OUTER | | 3962K| 498M| 3487 (7)| 00:00:01 | | |
| 2 | VIEW | | 976 | 11712 | 175 (2)| 00:00:01 | | |
| 3 | HASH UNIQUE | | 976 | 37088 | 175 (2)| 00:00:01 | | |
|* 4 | TABLE ACCESS STORAGE FULL | CODE_ITEM | 976 | 37088 | 174 (1)| 00:00:01 | | |
|* 5 | HASH JOIN RIGHT OUTER | | 3962K| 453M| 3300 (7)| 00:00:01 | | |
| 6 | VIEW | | 932 | 12116 | 5 (20)| 00:00:01 | | |
| 7 | HASH UNIQUE | | 932 | 12116 | 5 (20)| 00:00:01 | | |
| 8 | TABLE ACCESS STORAGE FULL | TY_SUIT_CFG | 932 | 12116 | 4 (0)| 00:00:01 | | |
|* 9 | HASH JOIN RIGHT OUTER | | 3962K| 404M| 3283 (7)| 00:00:01 | | |
| 10 | VIEW | | 306 | 7956 | 6 (17)| 00:00:01 | | |
| 11 | HASH UNIQUE | | 306 | 39168 | 6 (17)| 00:00:01 | | |
|* 12 | TABLE ACCESS STORAGE FULL | D_DRAGON_PLAN | 306 | 39168 | 5 (0)| 00:00:01 | | |
|* 13 | HASH JOIN RIGHT OUTER | | 3962K| 306M| 3266 (6)| 00:00:01 | | |
| 14 | VIEW | | 60 | 720 | 175 (2)| 00:00:01 | | |
| 15 | HASH UNIQUE | | 60 | 2280 | 175 (2)| 00:00:01 | | |
|* 16 | TABLE ACCESS STORAGE FULL| CODE_ITEM | 60 | 2280 | 174 (1)| 00:00:01 | | |
| 17 | PARTITION RANGE SINGLE | | 3962K| 260M| 3079 (6)| 00:00:01 | 27 | 27 |
| 18 | PARTITION LIST SINGLE | | 3962K| 260M| 3079 (6)| 00:00:01 | 1 | 1 |
| 19 | TABLE ACCESS STORAGE FULL| DM_SP_SUB_OFFER_SERV_D_201407 | 3962K| 260M| 3079 (6)| 00:00:01 | 547 | 547 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."COLUMN_111111"=TO_NUMBER("B"."LOCAL_ITEM_CODE"(+)))
4 - storage("PROV_TYPE_ID"=49 AND "PROV_ITEM_ID"=64)
filter("PROV_TYPE_ID"=49 AND "PROV_ITEM_ID"=64)
5 - access("D"."COLUMN_111111"(+)=TO_NUMBER(TO_CHAR("A"."COLUMN_111111")))
9 - access("C"."COLUMN_111111"(+)=TO_NUMBER(TO_CHAR("A"."COLUMN_111111")))
12 - storage(("BT_CODE"=1 OR "BT_CODE"=2) AND (UPPER("NAME") LIKE '{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}4S{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}' OR "NAME" IS NOT NULL AND "NAME" IS NOT NULL
AND "NAME" LIKE '{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}??{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}'))
filter(("BT_CODE"=1 OR "BT_CODE"=2) AND (UPPER("NAME") LIKE '{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}4S{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}' OR "NAME" IS NOT NULL AND "NAME" IS NOT NULL
AND "NAME" LIKE '{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}??{ff392285dba4c735eaf57cbf839a563e19e0ca0b993d821097d934f85a596e2c}'))
13 - access("E"."LOCAL_ITEM_CODE"(+)=TO_CHAR("A"."COLUMN_111111"))
16 - storage("PROV_TYPE_ID"=49 AND ("PROV_ITEM_ID"=68 OR "PROV_ITEM_ID"=69 OR "PROV_ITEM_ID"=70 OR "PROV_ITEM_ID"=71
OR "PROV_ITEM_ID"=85 OR "PROV_ITEM_ID"=86))
filter("PROV_TYPE_ID"=49 AND ("PROV_ITEM_ID"=68 OR "PROV_ITEM_ID"=69 OR "PROV_ITEM_ID"=70 OR "PROV_ITEM_ID"=71 OR
"PROV_ITEM_ID"=85 OR "PROV_ITEM_ID"=86))
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
303475 consistent gets
0 physical reads
0 redo size
96017715 bytes sent via SQL*Net to client
3155830 bytes received via SQL*Net from client
286848 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4302704 rows processed
优化到此结束
SQL标量子查询改外连接的优化：等您坐沙发呢！