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

关于一个sql的优化分析

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

如下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物化视图呢,这样可以减少一次物化视图的扫描。

原始的执行计划如下:

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

| Id    |                Operation                |             Name              |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read  | Read  | Mem | Activity |             Activity Detail         | Progress |

|       |                                         |                               | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes |     |   (%)    |               (# samples)           |          |

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

|  -> 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的优化分析