下面是一条在一体机上面运行的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 ‘%4S%’ OR NAME LIKE ‘%??%’)) 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 ‘%4S%’ OR NAME LIKE ‘%??%’)) 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 ‘%4S%’ OR NAME LIKE ‘%??%’)) 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 ‘%4S%’ OR NAME LIKE ‘%??%’)) 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 (%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 ‘%4S%’ OR "NAME" IS NOT NULL AND "NAME" IS NOT NULL AND "NAME" LIKE ‘%??%’)) filter(("BT_CODE"=1 OR "BT_CODE"=2) AND (UPPER("NAME") LIKE ‘%4S%’ OR "NAME" IS NOT NULL AND "NAME" IS NOT NULL AND "NAME" LIKE ‘%??%’)) 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标量子查询改外连接的优化：等您坐沙发呢！