复杂SQL 查询调优

nomic 2015-12-23 04:52:06
原本写好的SQL脚本是这样的：
SELECT /*+ PARALLEL (PR, 5)*/
PR.PURCH_REQ_ID,
PR.PURCH_REQ_ITM_NR,
PO.PO_NR,
PO.PO_ITM_NR,
PR.PURCH_REQ_ITM_SRC_SYS_KY,
PR.EFF_FRM_GMT_TS AS PREQ_EFF_FRM_GMT_TS,
PR.PURCH_REQ_CRT_TS,
PR.H_LGCL_DEL_IND AS PREQ_H_LGCL_DEL_IND,
PR.D_LGCL_DEL_IND AS PREQ_D_LGCL_DEL_IND,
PO.MFG_PTNR_SITE_ID,
PR.PROD_ID,
PR.PRE_DTF_CUST_CR_CHK_STAT_CD,
PR.PURCH_REQ_PART_STAT_CD,
PR.PLNT_CD,
PR.SO_ID,
PR.SO_LN_ITM_ID,
PR.SCH_LN_ITM_ID,
PR.PURCH_REQ_ITM_QT,
PO.SRC_SYS_KY AS PO_SRC_SYS_KY,
PO.EFF_FRM_GMT_TS AS PO_EFF_FRM_GMT_TS,
PO.PURCH_ORG_CD,
PO.PO_CRT_TS,
PO.PO_ITM_QT,
PO.PO_LN_ITM_DLVR_TX,
PO.H_LGCL_DEL_IND AS PO_H_LGCL_DEL_IND,
PO.D_LGCL_DEL_IND AS PO_D_LGCL_DEL_IND,
ACK_ASN_MAX.PO_ACK_ITM_EXT_DOC_ID,
ACK_ASN_MAX.PO_ACK_EFF_FRM_GMT_TS,
ACK_ASN_MAX.PACK_ID,
ACK_ASN_MAX.ADV_SHIP_NOTIF_ID,
ACK_ASN_MAX.PO_LA_CRT_DT ,
ACK_ASN_MAX.PO_LA_DT ,
ACK_ASN_MAX.PO_LA_QT ,
ACK_AB.PO_AB_CRT_DT ,
ACK_AB.PO_AB_DT ,
ACK_AB.PO_AB_QT ,
ACK_WI.PO_WI_CRT_DT ,
ACK_WI.PO_WI_DT ,
ACK_WI.PO_WI_QT
FROM
SCO.PURCH_REQ_LC_LE PR
JOIN
SCO.PO_LC_LE PO
ON
PR.PURCH_REQ_ID = PO.H_PURCH_REQ_ID
AND PR.PURCH_REQ_ITM_SRC_SYS_KY = PO.PURCH_REQ_ITM_SRC_SYS_KY
JOIN
(
SELECT /*+ PARALLEL (ASN, 5)*/
ACK_MAX.PO_NR,
ACK_MAX.PO_ITM_NR,
ACK_MAX.PO_ACK_ITM_EXT_DOC_ID,
ACK_MAX.EFF_FRM_GMT_TS AS PO_ACK_EFF_FRM_GMT_TS,
ACK_MAX.SRC_SYS_KY,
ASN.PACK_ID,
ASN.ADV_SHIP_NOTIF_ID,
TRUNC(ASN.ADV_SHIP_NOTIF_CRT_TS) AS PO_LA_CRT_DT,
TRUNC(ASN.ADV_SHIP_NOTIF_DLVR_TS) AS PO_LA_DT,
ASN.ADV_SHIP_NOTIF_ITM_QT AS PO_LA_QT
FROM
SCO.ADV_SHIP_NOTIF_LC_LE ASN
JOIN
(
SELECT
PO_NR,
PO_ITM_NR,
SRC_SYS_KY,
MAX(PO_ACK_ITM_EXT_DOC_ID) AS PO_ACK_ITM_EXT_DOC_ID,
EFF_FRM_GMT_TS
FROM
SCO.PO_ACK_LC_LE
WHERE
(
PO_NR, PO_ITM_NR, EFF_FRM_GMT_TS) IN
(
SELECT
PO_NR,
PO_ITM_NR,
MAX(EFF_FRM_GMT_TS)
FROM
SCO.PO_ACK_LC_LE
GROUP BY
PO_NR,
PO_ITM_NR)
GROUP BY PO_NR,
PO_ITM_NR,
SRC_SYS_KY,
EFF_FRM_GMT_TS) ACK_MAX
ON
ASN.PO_NR = ACK_MAX.PO_NR
AND ASN.PO_ITM_NR = ACK_MAX.PO_ITM_NR
AND ASN.SRC_SYS_KY = ACK_MAX.SRC_SYS_KY) ACK_ASN_MAX
ON
PO.PO_NR = ACK_ASN_MAX.PO_NR
AND PO.PO_ITM_NR = ACK_ASN_MAX.PO_ITM_NR
AND PO.SRC_SYS_KY = ACK_ASN_MAX.SRC_SYS_KY
JOIN
(
SELECT /*+ PARALLEL (ACK, 5)*/
ACK.PO_NR,
ACK.PO_ITM_NR,
SRC_SYS_KY,
TRUNC(ACK.PO_ACK_ITM_CRT_TS) AS PO_AB_CRT_DT,
TRUNC(ACK.PO_ACK_ITM_VNDR_CONF_TS) AS PO_AB_DT,
ACK.PO_ACK_ITM_QT AS PO_AB_QT
FROM
SCO.PO_ACK_LC_LE ACK
WHERE
ACK.PO_ACK_ITM_CONF_CATG_CD = 'AB') ACK_AB
ON
PO.PO_NR = ACK_AB.PO_NR
AND PO.PO_ITM_NR = ACK_AB.PO_ITM_NR
AND PO.SRC_SYS_KY = ACK_AB.SRC_SYS_KY
JOIN
(
SELECT /*+ PARALLEL (ACK, 5)*/
ACK.PO_NR,
ACK.PO_ITM_NR,
SRC_SYS_KY,
TRUNC(ACK.PO_ACK_ITM_CRT_TS) AS PO_WI_CRT_DT,
TRUNC(ACK.PO_ACK_ITM_VNDR_CONF_TS) AS PO_WI_DT,
ACK.PO_ACK_ITM_QT AS PO_WI_QT
FROM
SCO.PO_ACK_LC_LE ACK
WHERE
ACK.PO_ACK_ITM_CONF_CATG_CD = 'WI') ACK_WI
ON
PO.PO_NR = ACK_WI.PO_NR
AND PO.PO_ITM_NR = ACK_WI.PO_ITM_NR
AND PO.SRC_SYS_KY = ACK_WI.SRC_SYS_KY;

执行计划是这样的：
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8136 | 3456K| | 35106 (13)| 00:05:36 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | | | | | | Q1,01 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | | | | | | Q1,01 | PCWP | |
| 4 | NESTED LOOPS | | 8136 | 3456K| | 35106 (13)| 00:05:36 | Q1,01 | PCWP | |
| 5 | NESTED LOOPS | | 737 | 245K| | 34142 (14)| 00:05:27 | Q1,01 | PCWP | |
| 6 | NESTED LOOPS | | 347 | 99K| | 33945 (14)| 00:05:25 | Q1,01 | PCWP | |
| 7 | NESTED LOOPS | | 294 | 73206 | | 33778 (14)| 00:05:23 | Q1,01 | PCWP | |
|* 8 | HASH JOIN | | 510 | 73950 | | 33595 (14)| 00:05:21 | Q1,01 | PCWP | |
| 9 | BUFFER SORT | | | | | | | Q1,01 | PCWC | |
| 10 | PX RECEIVE | | 1676 | 127K| | 32966 (14)| 00:05:15 | Q1,01 | PCWP | |
| 11 | PX SEND BROADCAST | :TQ10000 | 1676 | 127K| | 32966 (14)| 00:05:15 | | S->P | BROADCAST |
| 12 | VIEW | | 1676 | 127K| | 32966 (14)| 00:05:15 | | | |
| 13 | HASH GROUP BY | | 1676 | 92180 | | 32966 (14)| 00:05:15 | | | |
| 14 | VIEW | VM_NWVW_2 | 1676 | 92180 | | 32966 (14)| 00:05:15 | | | |
|* 15 | FILTER | | | | | | | | | |
| 16 | HASH GROUP BY | | 1676 | 114K| | 32966 (14)| 00:05:15 | | | |
|* 17 | HASH JOIN | | 31M| 2104M| 505M| 29531 (4)| 00:04:43 | | | |
| 18 | INDEX FAST FULL SCAN| PO_ACK_LC_LE_XIE8 | 14M| 341M| | 2615 (6)| 00:00:25 | | | |
| 19 | INDEX FAST FULL SCAN| PO_ACK_LC_LE_XIE9 | 14M| 614M| | 3177 (5)| 00:00:31 | | | |
| 20 | PX BLOCK ITERATOR | | 3471K| 221M| | 622 (6)| 00:00:06 | Q1,01 | PCWC | |
| 21 | TABLE ACCESS FULL | ADV_SHIP_NOTIF_LC_LE | 3471K| 221M| | 622 (6)| 00:00:06 | Q1,01 | PCWP | |
| 22 | TABLE ACCESS BY INDEX ROWID | PO_LC_LE | 1 | 104 | | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 23 | INDEX UNIQUE SCAN | PO_LC_LE_XPK | 2 | | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 24 | TABLE ACCESS BY INDEX ROWID | PO_ACK_LC_LE | 1 | 46 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 25 | INDEX RANGE SCAN | PO_ACK_LC_LE_XIE9 | 1 | | | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 26 | TABLE ACCESS BY INDEX ROWID | PO_ACK_LC_LE | 2 | 92 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 27 | INDEX RANGE SCAN | PO_ACK_LC_LE_XIE9 | 1 | | | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 28 | INDEX RANGE SCAN | PURCH_REQ_LC_LE_XIE8 | 15 | | | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 29 | TABLE ACCESS BY INDEX ROWID | PURCH_REQ_LC_LE | 11 | 1034 | | 11 (0)| 00:00:01 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------

由于这些表的数量都非常大，需要进一步调优这个查询，请大家给点建议。
多谢
...全文
276 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
lhdz_bj 2016-01-05
  • 打赏
  • 举报
 回复
去掉parallel hint，用use_hash试试吧。
nomic 2016-01-05
  • 打赏
  • 举报
 回复
引用 4 楼 LHDZ_BJ 的回复:
不好意思，一直不能SQL回帖，今天试了下，只能图片回帖，这样麻烦些。麻烦反馈如下SQL的结果：
SELECT COUNT(1) FROM SCO.PO_ACK_LC_LE ACK WHERE ACK.PO_ACK_ITM_CONF_CATG_CD = 'AB'; 5977478 returned SELECT COUNT(1) FROM SCO.PO_ACK_LC_LE ACK WHERE ACK.PO_ACK_ITM_CONF_CATG_CD = 'WI'; 4151120 returned
lhdz_bj 2016-01-04
  • 打赏
  • 举报
 回复
不好意思，一直不能SQL回帖，今天试了下，只能图片回帖，这样麻烦些。麻烦反馈如下SQL的结果：
lhdz_bj 2015-12-25
  • 打赏
  • 举报
 回复
1、你用了parallel hint有用吗？可能不管用，有时会适得其反。 2、提出每张表的数据量，还有计划贴全，下面还有谓词部分。
nomic 2015-12-25
  • 打赏
  • 举报
 回复
然后表中的数据，这是测试环境，生产环境数据要多的多： TABLE NAME ROW COUNT PURCH_REQ_LC_LE 5065161 PO_LC_LE 5494439 ADV_SHIP_NOTIF_LC_LE 5461160 PO_ACK_LC_LE 10128871 表的存储大小： SEGMENT_NAME Used space(M) PO_ACK_LC_LE 2051 PO_LC_LE 1216 PURCH_REQ_LC_LE 1024 ADV_SHIP_NOTIF_LC_LE 600
nomic 2015-12-25
  • 打赏
  • 举报
 回复
多谢回复。 下面是整个执行计划： Plan hash value: 3652587496 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8136 | 3456K| | 42634 (12)| 00:06:48 | | 1 | NESTED LOOPS | | | | | | | | 2 | NESTED LOOPS | | 8136 | 3456K| | 42634 (12)| 00:06:48 | | 3 | NESTED LOOPS | | 737 | 245K| | 38297 (13)| 00:06:06 | | 4 | NESTED LOOPS | | 347 | 99K| | 37409 (13)| 00:05:58 | | 5 | NESTED LOOPS | | 294 | 73206 | | 36657 (13)| 00:05:51 | |* 6 | HASH JOIN | | 510 | 73950 | | 35834 (14)| 00:05:43 | | 7 | VIEW | | 1676 | 127K| | 32966 (14)| 00:05:15 | | 8 | HASH GROUP BY | | 1676 | 92180 | | 32966 (14)| 00:05:15 | | 9 | VIEW | VM_NWVW_2 | 1676 | 92180 | | 32966 (14)| 00:05:15 | |* 10 | FILTER | | | | | | | | 11 | HASH GROUP BY | | 1676 | 114K| | 32966 (14)| 00:05:15 | |* 12 | HASH JOIN | | 31M| 2104M| 505M| 29531 (4)| 00:04:43 | | 13 | INDEX FAST FULL SCAN| PO_ACK_LC_LE_XIE8 | 14M| 341M| | 2615 (6)| 00:00:25 | | 14 | INDEX FAST FULL SCAN| PO_ACK_LC_LE_XIE9 | 14M| 614M| | 3177 (5)| 00:00:31 | | 15 | TABLE ACCESS FULL | ADV_SHIP_NOTIF_LC_LE | 3471K| 221M| | 2834 (7)| 00:00:28 | | 16 | TABLE ACCESS BY INDEX ROWID| PO_LC_LE | 1 | 104 | | 2 (0)| 00:00:01 | |* 17 | INDEX UNIQUE SCAN | PO_LC_LE_XPK | 2 | | | 1 (0)| 00:00:01 | |* 18 | TABLE ACCESS BY INDEX ROWID | PO_ACK_LC_LE | 1 | 46 | | 3 (0)| 00:00:01 | |* 19 | INDEX RANGE SCAN | PO_ACK_LC_LE_XIE9 | 1 | | | 2 (0)| 00:00:01 | |* 20 | TABLE ACCESS BY INDEX ROWID | PO_ACK_LC_LE | 2 | 92 | | 3 (0)| 00:00:01 | |* 21 | INDEX RANGE SCAN | PO_ACK_LC_LE_XIE9 | 1 | | | 2 (0)| 00:00:01 | |* 22 | INDEX RANGE SCAN | PURCH_REQ_LC_LE_XIE8 | 15 | | | 2 (0)| 00:00:01 | | 23 | TABLE ACCESS BY INDEX ROWID | PURCH_REQ_LC_LE | 11 | 1034 | | 11 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("ASN"."PO_NR"="ACK_MAX"."PO_NR" AND "ASN"."PO_ITM_NR"="ACK_MAX"."PO_ITM_NR" AND "ASN"."SRC_SYS_KY"="ACK_MAX"."SRC_SYS_KY") 10 - filter("EFF_FRM_GMT_TS"=MAX("EFF_FRM_GMT_TS")) 12 - access("PO_NR"="PO_NR" AND "PO_ITM_NR"="PO_ITM_NR") 17 - access("PO"."PO_NR"="ACK_MAX"."PO_NR" AND "PO"."PO_ITM_NR"="ACK_MAX"."PO_ITM_NR" AND "PO"."SRC_SYS_KY"="ACK_MAX"."SRC_SYS_KY") 18 - filter("ACK"."PO_ACK_ITM_CONF_CATG_CD"='WI') 19 - access("PO"."PO_NR"="ACK"."PO_NR" AND "PO"."PO_ITM_NR"="ACK"."PO_ITM_NR" AND "PO"."SRC_SYS_KY"="SRC_SYS_KY") 20 - filter("ACK"."PO_ACK_ITM_CONF_CATG_CD"='AB') 21 - access("PO"."PO_NR"="ACK"."PO_NR" AND "PO"."PO_ITM_NR"="ACK"."PO_ITM_NR" AND "PO"."SRC_SYS_KY"="SRC_SYS_KY") 22 - access("PR"."PURCH_REQ_ID"="PO"."H_PURCH_REQ_ID" AND "PR"."PURCH_REQ_ITM_SRC_SYS_KY"="PO"."PURCH_REQ_ITM_SRC_SYS_KY")
DB2 SQL性能调优秘笈
《DB2 SQL性能调优秘笈》不仅详尽阐述了100余条SQL语句优化的技巧和最佳实践、编写高性能SQL语句的标准和原则，以及DB2数据库性能优化的“15步法”，而且还包含大量案例，为解决各种复杂的DB2性能问题提供了解决方案...
SQL 数据库性能调优方面的总结
数据库调优可以使数据库应用运行...数据库调优技术可以在不同的数据库系统中使用,它不必纠缠于复杂的公式和规则，然而它需要对程序的应用、数据库管理系统、查询处理、并发控制、操作系统以及硬件有广泛而深刻的理解。
第二章：Spark SQL 综合实战
1.Spark SQL JDBC服务介绍 2.Spark SQL MetaStore Server构建 3.深度了解Spark SQL运行计划与调优 4.Spark SQL DataSets API实践完成数据分析 5.JSON数据源复杂综合案例实战
SQLServer性能调优之执行计划第一次实践
为了使得大家易于理解，这里的例子不会太复杂，随着课程的不断深入，后续的示例也会越来越复杂。同时，如果大家也想跟着一起动手实践，那么希望朋友们安装SQL2005或更高版本，同时记得安装Adventur
数据库 性能调优 技术
数据库性能调优是知识密集型的学科，需要综合考虑各种复杂的因素：数据库缓冲区的大小、索引的创建、语句改写等等。总之，数据库性能调优的目的在于使系统运行得更快。 调优需要有广泛的知识，这使得它既简单又复杂...
Oracle 高级技术

3,491

社区成员

18,714

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章