关于一个sql的优化分析

首页 > 代码库 > 关于一个sql的优化分析

2024-08-30 13:39:36 224人阅读

应用这边新上线了一个查询，正在跑，让我看下状态以及分析下能不能再快点。

如下sql:

SELECT x.order_no ,
order_date+7/24 AS order_date,
address
||
district AS ADDRESS,
city ,
extn_style_number
||
‘-‘
||
extn_color_number ,
SUM(line_total) ,
SUM(ORDERED_QTY) ,
CASE
WHEN division=‘55‘
THEN ‘SWOOSH‘
ELSE ‘‘
END AS SWOOSH,
CASE
WHEN lower (MRKTNG_CHNNL_ORDR_EXTN_MV.LAST_TOUCH_TRACKING_CODE) LIKE ‘%cnns_ksk%‘
THEN ‘SEAMLESS‘
ELSE ‘‘
END AS SEAMLESS,
CASE
WHEN line_type=‘NIKEID‘
THEN ‘NIKEID‘
ELSE ‘‘
END AS NIKEID
FROM
(
SELECT DISTINCT l.line_type ,
s.status ,
order_DATE ,
order_no ,
retail_price ,
ORDERED_QTY ,
division ,
l.line_total ,
l.item_description ,
l.extn_size_description ,
l.extn_style_number ,
l.extn_color_number ,
l.list_price ,
pp.address_line1 AS address ,
pp.address_line4 AS district,
pp.city
FROM dom.yfs_order_header h ,
dom.yfs_ordeR_line l ,
dom.yfs_order_release_status s,
dom.yfs_person_info pp
WHERE h.order_header_key = l.order_header_key
AND l.order_line_key = s.order_line_key
AND pp.person_info_key = h.bill_to_key
AND h.enterprise_key = ‘NIKECN‘
AND s.status_quantity > 0
AND document_type = ‘0001‘
AND l.LINE_TOTAL >0
AND s.status <> ‘9000‘
AND h.division IN (‘55‘,‘400‘)
AND order_date +7/24 >= to_date(‘01-Jun-15‘,‘DD-MON-YY‘)
AND order_date +7/24 < to_date(‘01-Nov-15‘,‘DD-MON-YY‘)
AND h.order_type <>‘legacy‘
AND
(
order_purpose <>‘REFUND‘
OR order_purpose IS NULL
)
AND
(
ORDER_TYPE <>‘ProductVoucher‘
OR ORDER_TYPE IS NULL
)
)
X
LEFT OUTER JOIN NTCOM.MRKTNG_CHNNL_ORDR_EXTN_MV MRKTNG_CHNNL_ORDR_EXTN_MV
ON X.ORDER_NO=MRKTNG_CHNNL_ORDR_EXTN_MV.ORDER_NO
GROUP BY x.order_no ,
order_date+7/24,
address
||
district,
city ,
extn_style_number
||
‘-‘
||
extn_color_number,
CASE
WHEN division=‘55‘
THEN ‘SWOOSH‘
ELSE ‘‘
END,
CASE
WHEN lower (MRKTNG_CHNNL_ORDR_EXTN_MV.LAST_TOUCH_TRACKING_CODE) LIKE ‘%cnns_ksk%‘
THEN ‘SEAMLESS‘
ELSE ‘‘
END,
CASE
WHEN line_type=‘NIKEID‘
THEN ‘NIKEID‘
ELSE ‘‘
END
UNION ALL
SELECT y.order_no ,
order_date+7/24 AS order_date,
address
||
district AS ADDRESS,
city ,
extn_style_number
||
‘-‘
||
extn_color_number ,
SUM(line_total) ,
SUM(ORDERED_QTY) ,
CASE
WHEN division=‘55‘
THEN ‘SWOOSH‘
ELSE ‘‘
END AS SWOOSH,
CASE
WHEN lower (MRKTNG_CHNNL_ORDR_EXTN_MV.LAST_TOUCH_TRACKING_CODE) LIKE ‘%cnns_ksk%‘
THEN ‘SEAMLESS‘
ELSE ‘‘
END AS SEAMLESS,
CASE
WHEN line_type=‘NIKEID‘
THEN ‘NIKEID‘
ELSE ‘‘
END AS NIKEID
FROM
(
SELECT DISTINCT l.line_type ,
s.status ,
order_DATE ,
order_no ,
retail_price ,
ORDERED_QTY ,
division ,
l.line_total ,
l.item_description ,
l.extn_size_description ,
l.extn_style_number ,
l.extn_color_number ,
l.list_price ,
pp.address_line1 AS address ,
pp.address_line4 AS district,
pp.city
FROM dom.yfs_order_header_H h ,
dom.yfs_ordeR_line_H l ,
dom.yfs_order_release_status_H s,
dom.yfs_person_info pp
WHERE h.order_header_key = l.order_header_key
AND l.order_line_key = s.order_line_key
AND pp.person_info_key = h.bill_to_key
AND h.enterprise_key = ‘NIKECN‘
AND s.status_quantity > 0
AND document_type = ‘0001‘
AND l.LINE_TOTAL >0
AND s.status <> ‘9000‘
AND h.division IN (‘55‘,‘400‘)
AND order_date +7/24 >= to_date(‘01-Jun-15‘,‘DD-MON-YY‘)
AND order_date +7/24 < to_date(‘01-Nov-15‘,‘DD-MON-YY‘)
AND h.order_type <>‘legacy‘
AND
(
order_purpose <>‘REFUND‘
OR order_purpose IS NULL
)
AND
(
ORDER_TYPE <>‘ProductVoucher‘
OR ORDER_TYPE IS NULL
)
)
Y
LEFT OUTER JOIN NTCOM.MRKTNG_CHNNL_ORDR_EXTN_MV MRKTNG_CHNNL_ORDR_EXTN_MV
ON Y.ORDER_NO=MRKTNG_CHNNL_ORDR_EXTN_MV.ORDER_NO
GROUP BY 1,2,3,4，......；

大致的结构就是两个select语句做union的操作

一个select现在的表，一个select历史表

来继续看下执行计划，

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 227K(100)| |
| 1 | UNION-ALL | | | | | |
| 2 | HASH GROUP BY | | 169 | 24674 | 93719 (1)| 00:35:56 |
|* 3 | HASH JOIN OUTER | | 169 | 24674 | 93718 (1)| 00:35:56 |
| 4 | VIEW | | 169 | 17914 | 20470 (1)| 00:07:51 |
| 5 | HASH UNIQUE | | 169 | 53235 | 20470 (1)| 00:07:51 |
|* 6 | FILTER | | | | | |
| 7 | NESTED LOOPS | | 169 | 53235 | 20469 (1)| 00:07:51 |
| 8 | NESTED LOOPS | | 846 | 53235 | 20469 (1)| 00:07:51 |
| 9 | NESTED LOOPS | | 94 | 26226 | 19659 (1)| 00:07:33 |
| 10 | NESTED LOOPS | | 50 | 8100 | 19428 (1)| 00:07:27 |
|* 11 | TABLE ACCESS BY INDEX ROWID| YFS_ORDER_HEADER | 50 | 4900 | 19278 (1)| 00:07:24 |
|* 12 | INDEX RANGE SCAN | YFS_ORDER_HEADER_I2 | 1151 | | 18386 (1)| 00:07:03 |
| 13 | TABLE ACCESS BY INDEX ROWID| YFS_PERSON_INFO | 1 | 64 | 3 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | YFS_PERSON_INFO_PK | 1 | | 2 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_LINE | 2 | 234 | 6 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | YFS_ORDER_LINE_I1 | 3 | | 3 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | YFS_ORDER_RELEASE_STATUS_I2 | 9 | | 3 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_RELEASE_STATUS | 2 | 72 | 11 (0)| 00:00:01 |
| 19 | MAT_VIEW ACCESS FULL | MRKTNG_CHNNL_ORDR_EXTN_MV | 41M| 1590M| 73144 (1)| 00:28:03 |
| 20 | HASH GROUP BY | | 1591 | 231K| 133K (1)| 00:51:21 |
|* 21 | HASH JOIN OUTER | | 1591 | 231K| 133K (1)| 00:51:21 |
| 22 | VIEW | | 1591 | 169K| 60705 (1)| 00:23:17 |
| 23 | HASH UNIQUE | | 1591 | 531K| 60705 (1)| 00:23:17 |
|* 24 | FILTER | | | | | |
| 25 | NESTED LOOPS | | 1591 | 531K| 60704 (1)| 00:23:17 |
| 26 | NESTED LOOPS | | 1591 | 531K| 60704 (1)| 00:23:17 |
| 27 | NESTED LOOPS | | 1591 | 461K| 54351 (1)| 00:20:51 |
| 28 | NESTED LOOPS | | 477 | 85860 | 52237 (1)| 00:20:02 |
|* 29 | TABLE ACCESS BY INDEX ROWID| YFS_ORDER_HEADER_H | 477 | 55332 | 50806 (1)| 00:19:29 |
|* 30 | INDEX RANGE SCAN | YFS_ORDER_HEADER_H_I2 | 12374 | | 38482 (1)| 00:14:46 |
| 31 | TABLE ACCESS BY INDEX ROWID| YFS_PERSON_INFO | 1 | 64 | 3 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | YFS_PERSON_INFO_PK | 1 | | 2 (0)| 00:00:01 |
|* 33 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_LINE_H | 3 | 351 | 6 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | YFS_ORDER_LINE_H_I1 | 4 | | 3 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | YFS_ORDER_RELEASE_STATUS_H_I2 | 1 | | 3 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_RELEASE_STATUS_H | 1 | 45 | 4 (0)| 00:00:01 |
| 37 | MAT_VIEW ACCESS FULL | MRKTNG_CHNNL_ORDR_EXTN_MV | 41M| 1590M| 73144 (1)| 00:28:03 |
-----------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SET$1
2 - SEL$97EC0181
4 - SEL$7C87FA5C / X@SEL$2
5 - SEL$7C87FA5C
11 - SEL$7C87FA5C / YFS_ORDER_HEADER@SEL$4
12 - SEL$7C87FA5C / YFS_ORDER_HEADER@SEL$4
13 - SEL$7C87FA5C / PP@SEL$3
14 - SEL$7C87FA5C / PP@SEL$3
15 - SEL$7C87FA5C / YFS_ORDER_LINE@SEL$5
16 - SEL$7C87FA5C / YFS_ORDER_LINE@SEL$5
17 - SEL$7C87FA5C / S@SEL$3
18 - SEL$7C87FA5C / S@SEL$3
19 - SEL$97EC0181 / MRKTNG_CHNNL_ORDR_EXTN_MV@SEL$1
20 - SEL$6FC1811E
22 - SEL$A5E413B3 / Y@SEL$8
23 - SEL$A5E413B3
29 - SEL$A5E413B3 / YFS_ORDER_HEADER_H@SEL$10
30 - SEL$A5E413B3 / YFS_ORDER_HEADER_H@SEL$10
31 - SEL$A5E413B3 / PP@SEL$9
32 - SEL$A5E413B3 / PP@SEL$9
33 - SEL$A5E413B3 / YFS_ORDER_LINE_H@SEL$11
34 - SEL$A5E413B3 / YFS_ORDER_LINE_H@SEL$11
35 - SEL$A5E413B3 / S@SEL$9
36 - SEL$A5E413B3 / S@SEL$9
37 - SEL$6FC1811E / MRKTNG_CHNNL_ORDR_EXTN_MV@SEL$7

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("X"."ORDER_NO"="MRKTNG_CHNNL_ORDR_EXTN_MV"."ORDER_NO")
6 - filter(TO_DATE(‘01-Nov-15‘,‘DD-MON-YY‘)>TO_DATE(‘01-Jun-15‘,‘DD-MON-YY‘))
11 - filter((INTERNAL_FUNCTION("DIVISION") AND ("ORDER_PURPOSE" IS NULL OR "ORDER_PURPOSE"<>‘REFUND‘) AND
"ORDER_TYPE"<>‘ProductVoucher‘ AND "ORDER_TYPE"<>‘legacy‘))
12 - access("DOCUMENT_TYPE"=‘0001‘ AND "ENTERPRISE_KEY"=‘NIKECN‘)
filter((INTERNAL_FUNCTION("ORDER_DATE")+.2916666666666666666666666666666666666667>=TO_DATE(‘01-Jun-15‘,‘
DD-MON-YY‘) AND INTERNAL_FUNCTION("ORDER_DATE")+.2916666666666666666666666666666666666667<TO_DATE(‘01-Nov-15‘,‘
DD-MON-YY‘)))
14 - access("PP"."PERSON_INFO_KEY"="BILL_TO_KEY")
15 - filter("LINE_TOTAL">0)
16 - access("ORDER_HEADER_KEY"="ORDER_HEADER_KEY")
17 - access("ORDER_LINE_KEY"="S"."ORDER_LINE_KEY")
18 - filter(("S"."STATUS_QUANTITY">0 AND "S"."STATUS"<>‘9000‘))
21 - access("Y"."ORDER_NO"="MRKTNG_CHNNL_ORDR_EXTN_MV"."ORDER_NO")
24 - filter(TO_DATE(‘01-Nov-15‘,‘DD-MON-YY‘)>TO_DATE(‘01-Jun-15‘,‘DD-MON-YY‘))
29 - filter((INTERNAL_FUNCTION("DIVISION") AND "ORDER_TYPE"<>‘ProductVoucher‘ AND ("ORDER_PURPOSE" IS NULL
OR "ORDER_PURPOSE"<>‘REFUND‘) AND "ORDER_TYPE"<>‘legacy‘))
30 - access("DOCUMENT_TYPE"=‘0001‘ AND "ENTERPRISE_KEY"=‘NIKECN‘)
filter((INTERNAL_FUNCTION("ORDER_DATE")+.2916666666666666666666666666666666666667>=TO_DATE(‘01-Jun-15‘,‘
DD-MON-YY‘) AND INTERNAL_FUNCTION("ORDER_DATE")+.2916666666666666666666666666666666666667<TO_DATE(‘01-Nov-15‘,‘
DD-MON-YY‘)))
32 - access("PP"."PERSON_INFO_KEY"="BILL_TO_KEY")
33 - filter("LINE_TOTAL">0)
34 - access("ORDER_HEADER_KEY"="ORDER_HEADER_KEY")
35 - access("ORDER_LINE_KEY"="S"."ORDER_LINE_KEY")
36 - filter(("S"."STATUS"<>‘9000‘ AND "S"."STATUS_QUANTITY">0))

======================

在做了稍微的分析，得出如下三点可以改进的意见：

1.看了下原始sql的执行计划，里面有filter的步骤，如下：

AND order_date +7/24 >= to_date(‘01-Dec-14‘,‘DD-MON-YY‘)

AND order_date +7/24 < to_date(‘01-Jun-15‘,‘DD-MON-YY‘)

Predicate Information (identified by operation id):

filter((INTERNAL_FUNCTION("ORDER_DATE")+.2916666666666666666666666666666666666667>=TO_DATE(‘01-Jun-15‘,‘DD-MON-YY‘) AND

INTERNAL_FUNCTION("ORDER_DATE")+.2916666666666666666666666666666666666667<TO_DATE(‘01-Nov-15‘,‘DD-MON-YY‘)))

于是想着应该这样写，可以走索引：

AND order_date >= to_date(‘01-Dec-14‘,‘DD-MON-YY‘)-7/24

AND order_date < to_date(‘01-Jun-15‘,‘DD-MON-YY‘)-7/24

改成这样之后的执行计划：

10 - access("DOCUMENT_TYPE"=‘0001‘ AND "H"."ENTERPRISE_KEY"=‘NIKECN‘ AND "ORDER_DATE">=TO_DATE(‘01-Dec-14‘,‘DD-MON-YY‘)-.291666666666666666666666666666

6666666667 AND "ORDER_DATE"<TO_DATE(‘01-Jun-15‘,‘DD-MON-YY‘)-.2916666666666666666666666666666666666667)

当然为什么写7/24，因为这里的日期没有小时，也不知道app的人怎么想的，如下：

SQL> select to_date(‘01-Dec-14‘,‘DD-MON-YY‘) from dual;
01-DEC-14

SQL> select to_date(‘01-Dec-14‘,‘DD-MON-YY‘)-7/24 from dual;
30-NOV-14

SQL> select to_date(‘01-Dec-14‘,‘DD-MON-YY‘)-1 from dual;
30-NOV-14

因为to_date这里只有DD-MON-YY格式

2.这个sql是两个select分别去join一张物化视图，然后再去union all

为何不先union all再去join物化视图呢，这样可以减少一次物化视图的扫描。

原始的执行计划如下：

===================================================================================================================================================================================================================================

| -> 0 | SELECT STATEMENT | | | | 1610 | +84 | 1 | 86400 | | | | | | |

| -> 1 | UNION-ALL | | | | 1610 | +84 | 1 | 86400 | | | | |

19 | MAT_VIEW ACCESS FULL | MRKTNG_CHNNL_ORDR_EXTN_MV | 42M | 73144 | 17 | +68 | 1 | 42M | 2854 | 3GB

37 | MAT_VIEW ACCESS FULL | MRKTNG_CHNNL_ORDR_EXTN_MV | 42M | 73144 | 37 | +1456 | 1 | 42M | 2852 | 3GB |

可以看到第19步和第37步都做了物化视图的全扫描，最后才去union all

所以可以改成两个select语句先去union all再去join这个物化视图

3.物化视图的索引没用上

可以看到物化视图走的是全表扫描这样的执行计划，但是check一下上面是有索引的，

连接条件如下

LEFT OUTER JOIN NTCOM.MRKTNG_CHNNL_ORDR_EXTN_MV MRKTNG_CHNNL_ORDR_EXTN_MV ON X.ORDER_NO=MRKTNG_CHNNL_ORDR_EXTN_MV.ORDER_NO

select table_name, owner,num_rows, blocks, last_analyzed,partitioned

from dba_tables

where table_name =‘MRKTNG_CHNNL_ORDR_EXTN_MV‘;

MRKTNG_CHNNL_ORDR_EXTN_MV NTCOM 41692893 362436 24-DEC-16 NO

select table_name, index_name, column_name, column_position

from dba_ind_columns

where table_name =‘MRKTNG_CHNNL_ORDR_EXTN_MV‘

order by 1, 2, 4;

TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION

------------------------------ ------------------------------ ---------------------------------------- ---------------

MRKTNG_CHNNL_ORDR_EXTN_MV I_SNAP$_MRKTNG_CHNNL_ORDR_ SYS_NC00024$ 1

MRKTNG_CHNNL_ORDR_EXTN_MV I_SNAP$_MRKTNG_CHNNL_ORDR_ SYS_NC00025$ 2

MRKTNG_CHNNL_ORDR_EXTN_MV I_SNAP$_MRKTNG_CHNNL_ORDR_ SYS_NC00026$ 3

MRKTNG_CHNNL_ORDR_EXTN_MV I_SNAP$_MRKTNG_CHNNL_ORDR_ SYS_NC00027$ 4

select TABLE_NAME,COLUMN_NAME,COLUMN_ID from dba_tab_cols where table_name=‘MRKTNG_CHNNL_ORDR_EXTN_MV‘;

TABLE_NAME COLUMN_NAME COLUMN_ID

------------------------------ ---------------------------------------- ----------

MRKTNG_CHNNL_ORDR_EXTN_MV ORDER_REPORT_DT_DIM_ID 4

MRKTNG_CHNNL_ORDR_EXTN_MV LAST_TOUCH_TRACKING_CODE 3

MRKTNG_CHNNL_ORDR_EXTN_MV GEO_KEY 2

MRKTNG_CHNNL_ORDR_EXTN_MV ORDER_NO 1

select index_type from dba_indexes where table_name =‘MRKTNG_CHNNL_ORDR_EXTN_MV‘;

INDEX_TYPE

---------------------------

FUNCTION-BASED NORMAL

select table_name, column_name, num_distinct, histogram

from dba_tab_col_statistics

where table_name =‘MRKTNG_CHNNL_ORDR_EXTN_MV‘

order by 1, 2;

TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM

------------------------------ ---------------------------------------- ------------ ---------------

MRKTNG_CHNNL_ORDR_EXTN_MV ORDER_NO 41692847 HEIGHT BALANCED

SELECT dbms_metadata.get_ddl(‘TABLE‘, ‘MRKTNG_CHNNL_ORDR_EXTN_MV‘,‘NTCOM‘) FROM DUAL;可以看到物化视图的定义

可以让这个物化视图走下索引看看效率，要是效率不行的话，那就只能全表扫描了。当然可以专门为这个物化视图加个parallel hint从而全表扫的时候更快。

走索引并没有做测试

后面测了其中一个select的执行时间，大约30S左右，提升了很多。估计整个union做完就1-3mins。比之前的45mins快多了。

以上三点就是看了这个sql的执行计划得出来的结论，总结如下：

1.索引没走因为谓词的条件写的不规范

2.select join与union的逻辑，稍微重写下在逻辑不变的情况下可以减少一次物化视图全表扫描

3.物化视图也可以走索引，从而看看有没有加快查询的可能性，这里走了全表扫描。

=======================================ENDED================

关于一个sql的优化分析

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 关于一个sql的优化分析

关于一个sql的优化分析

看完仍有疑问？有类似问题直接问程序猿