sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oralce性能优化之SQL标量子查询改外连接的优化过程

时间:2016-10-30 20:05   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库某进程占用CPU较多,分析原因为SQL语句欠优化导致,优化目的SQL标量子查询改外连接的优化步骤。
下面是一条在一体机上面运行的SQL语句,消耗了大量的CPU,SQL为精简一些列后来测试的,但是对整个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 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
Oracleoracleplus.net 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
优化到此结束

本文固定链接: http://www.htz.pw/2014/09/14/sql%e6%a0%87%e9%87%8f%e5%ad%90%e6%9f%a5%e8%af%a2%e6%94%b9%e5%a4%96%e8%bf%9e%e6%8e%a5%e7%9a%84%e4%bc%98%e5%8c%96.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oralce性能优化之SQL标量子查询改外连接的优化过程

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1115.html

Oracle研究中心

关键词:

Oracle SQL语句优化笔记

Oracle SQL语句消耗大量CPU的优化过程

如何将SQL标量子查询改为外连接查询