paobusi_wu 2012-09-19 14:10
浏览 150

SQL查询调优问题

各位看官,现有一个View如下:

SELECT REQUEST_ID,
SO_NUM,
JOB_NUM,
MAIN_JOB_NUM,
SO_TYPE,
IBAS_EVENT_ID,
END_CUST_NAME,
MO_SHIP_SET_NAME,
CASE
WHEN BILLING_MILESTONE='PARTS SHIPMENT'
THEN UPPER(DECODE(SHIP_SET_STATUS,'BOX RELEASE',UPPER(FS_APP_133_GET_CURRENT_STEP(N_PROCESS_INST)),UPPER(SHIP_SET_STATUS)))
WHEN BILLING_MILESTONE IN ('ADVANCE PAYMENT','POST SHIPMENT')
THEN UPPER(DECODE(SHIP_SET_STATUS,NULL,FS_APP_133_GET_CURRENT_STEP(N_PROCESS_INST),SHIP_SET_STATUS))
END SHIP_SET_STATUS,
LINES_COUNT,
AMT_USD,
LOT_PROMISED_DATE,
CWD,
BILLING_MILESTONE,
N_PROCESS_INST,
--FS_APP_069_GET_PROCESS_INST(HAND_OFF_REQUEST_ID) HAND_OFF_REQUEST_ID,
HAND_OFF_REQUEST_ID,
CWD_FLAG
FROM
(SELECT REQUEST_ID,
SO_NUM,
FS_APP_101_HAND_OFF_REQ(PROCESS_INST,'JOB_NUM') JOB_NUM,
FS_APP_101_HAND_OFF_REQ(PROCESS_INST,'MAIN_JOB_NUM') MAIN_JOB_NUM,
FS_APP_101_HAND_OFF_SUB_TYPE(PROCESS_INST) SO_TYPE,
FS_APP_111_GET_IBAS_EVNT_ID(PROCESS_INST) IBAS_EVENT_ID,
END_CUST_NAME,
MO_SHIP_SET_NAME,
DECODE(LENGTH(SHIP_SET_STATUS),1,NULL,SHIP_SET_STATUS) SHIP_SET_STATUS,
SO_LINES_COUNT LINES_COUNT,
CASE
WHEN ((REQUEST_TYPE LIKE 'COMMERCIAL%'
AND BILLING_MILESTONE='PARTS SHIPMENT')
OR (REQUEST_TYPE ='PROFORMA'))
THEN TO_CHAR(MO_SHIP_SET_AMOUNT)
ELSE DECODE(NEW_INV_AMOUNT,NULL,TO_CHAR(FS_APP_110_GET_EVNT_AMT(PROCESS_INST)),NEW_INV_AMOUNT)
END AMT_USD,
LOT_PROMISED_DATE,
CWD,
BILLING_MILESTONE,
PROCESS_INST N_PROCESS_INST,
--FS_APP_069_GET_PROCESS_INST(HAND_OFF_REQUEST_ID) HAND_OFF_REQUEST_ID,
HAND_OFF_REQUEST_ID,
(
CASE
WHEN TO_DATE(CWD) BETWEEN SYSDATE AND (SYSDATE+30)
THEN 'Y'
WHEN SYSDATE>TO_DATE(CWD)
THEN 'R'
ELSE 'N'
END) AS CWD_FLAG
FROM
(SELECT N_PROCESS_INST PROCESS_INST,
D_INS,
D_UPD,
MAX(DECODE(MY_ATT,'REQUEST_ID', MY_ATT_VAL)) REQUEST_ID,
MAX(DECODE(MY_ATT,'SO_NUM', MY_ATT_VAL)) SO_NUM,
MAX(DECODE(MY_ATT,'SHIP_SET_STATUS', MY_ATT_VAL)) SHIP_SET_STATUS,
MAX(DECODE(MY_ATT,'TOTAL_AMOUNT', MY_ATT_VAL)) MO_SHIP_SET_AMOUNT,
MAX(DECODE(MY_ATT,'SO_LINES_COUNT', MY_ATT_VAL)) SO_LINES_COUNT,
MAX(DECODE(MY_ATT,'END_CUST_NAME', MY_ATT_VAL)) END_CUST_NAME,
MAX(DECODE(MY_ATT,'MO_SHIP_SET_NAME', MY_ATT_VAL)) MO_SHIP_SET_NAME,
MAX(DECODE(MY_ATT,'SHIP_SET_PROMISED_DATE', MY_ATT_VAL)) LOT_PROMISED_DATE,
MAX(DECODE(MY_ATT,'CWD', MY_ATT_VAL)) CWD,
MAX(DECODE(MY_ATT,'BILLING_MILESTONE', MY_ATT_VAL)) BILLING_MILESTONE,
MAX(DECODE(MY_ATT,'HAND_OFF_REQUEST_ID', MY_ATT_VAL)) HAND_OFF_REQUEST_ID,
MAX(DECODE(MY_ATT,'REQUEST_TYPE',MY_ATT_VAL)) REQUEST_TYPE,
MAX(DECODE(MY_ATT,'NEW_INV_AMOUNT',MY_ATT_VAL)) NEW_INV_AMOUNT
FROM
(SELECT TAI.N_PROCESS_INST,
TA.C_ATTRIBUTE MY_ATT,
TAI.C_ATTRIBUTE_VALUE MY_ATT_VAL,
TPI.D_INS D_INS,
TPI.D_UPD D_UPD
FROM Table_AAA TAI
INNER JOIN Table_BBB ATTRIBUTE TA
ON TA.C_ATTRIBUTE = TAI.C_ATTRIBUTE
INNER JOIN Table_CCC TSP
ON TSP.C_SUB_PROCESS = TA.C_SUB_PROCESS
INNER JOIN Table_DDD TPI
ON TPI.N_PROCESS_INST = TAI.N_PROCESS_INST
WHERE TPI.C_CURRENT_STATUS NOT LIKE 'otr.shp.Close'
AND TA.C_SUB_PROCESS IN ('SH')
AND TPI.C_SUB_PROCESS IN ('SH')
)
GROUP BY N_PROCESS_INST,
D_UPD
HAVING FS_APP_137_IS_SPLIT_REQ(N_PROCESS_INST)='N'
)
ORDER BY D_UPD DESC
)

该查询的执行计划如下:
Plan hash value: 25602094


| Id | Operation | Name |

| 0 | SELECT STATEMENT | |
| 1 | VIEW | |
|* 2 | FILTER | |
| 3 | SORT GROUP BY | |
| 4 | MERGE JOIN | |
| 5 | SORT JOIN | |
| 6 | NESTED LOOPS | |
| 7 | NESTED LOOPS | |
|* 8 | TABLE ACCESS FULL | Table_DDD |
| 9 | TABLE ACCESS BY INDEX ROWID| Table_AAA |
|* 10 | INDEX RANGE SCAN | TS_APP_006_IDX1 |
|* 11 | INDEX UNIQUE SCAN | TS_APP_PK |
|* 12 | SORT JOIN | |

| 13 | TABLE ACCESS FULL | Table_CCC |

Predicate Information (identified by operation id):

2 - filter("FS_APP_137_IS_SPLIT_REQ"("TAI"."N_PROCESS_INST")='N')
8 - filter("TPI"."C_SUB_PROCESS"='SH' AND "TPI"."C_CURRENT_STATUS"
NOT LIKE 'otr.shp.Close')
10 - access("TPI"."N_PROCESS_INST"="TAI"."N_PROCESS_INST")
11 - access("TA"."C_ATTRIBUTE"="TAI"."C_ATTRIBUTE" AND
"TA"."C_SUB_PROCESS"='SH')
12 - access("TSP"."C_SUB_PROCESS"="TA"."C_SUB_PROCESS")
filter("TSP"."C_SUB_PROCESS"="TA"."C_SUB_PROCESS")

Note

  • rule based optimizer used (consider using cbo)

老板认为这个View的执行效率太低了,要求我去优化:
现有如下问题:
1). 这个View可以查询出来11000多条记录,但是需要耗费120到150秒中的时间,请问这个正常吗?
2). 从执行计划上看,该查询的效率低的瓶颈在哪里?
3). 如果你认为有调优的空间,请给出你的思路

  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
    • ¥15 C#调用python代码(python带有库)
    • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
    • ¥15 活动选择题。最多可以参加几个项目?
    • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
    • ¥15 vs2019中数据导出问题
    • ¥20 云服务Linux系统TCP-MSS值修改?
    • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
    • ¥20 怎么在stm32门禁成品上增加查询记录功能
    • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面