sql 性能 “突好突坏”
环境是AIX6.1 + oracle 10.2.0.4.0情况是 ：
昨天碰见问题是，发现条件中
FROM (SELECT TRUNC(SYSDATE + ROWNUM) as DATE_DAY FROM TCODE WHERE ROWNUM < 7 ) C
细看可分析，DATE_DAY 其实和表数据无关，
然后将数据再放入function循环取数。
可是经过测试 TRUNC(SYSDATE + ROWNUM) 和去挑trunc 后效率差别非常大。
昨天分析后，也没查出有效方法。
今天再次想分析时，效率居然恢复了。
没有特别好思路，请各位大牛分析下。
我对比2个文件 看到最大的差别是 这2语句：
SELECT NVL(SUM(C.DELY_QTY), 0)
FROM
( SELECT T.DELY_GB,T.AREA_LGROUP,T.AREA_MGROUP,T.AREA_SGROUP FROM
TADMINDELIVERYQTY T, TDELYAREA B WHERE T.AREA_LGROUP = B.AREA_LGROUP AND
(T.AREA_MGROUP = '999' OR (T.AREA_MGROUP = B.AREA_MGROUP AND (T.AREA_SGROUP
= '999' OR T.AREA_SGROUP = B.AREA_SGROUP) ) ) AND B.USE_YN = '1' AND
T.YYMMDD = :B5 AND B.AREA_LGROUP = :B4 AND B.AREA_MGROUP = :B3 AND
B.AREA_SGROUP = :B2 AND ( ( :B1 <> '00' AND T.DELY_GB = :B1 ) OR ( (:B1 =
'00') AND T.DELY_GB IN ( SELECT C.DELY_GB FROM VITEM B, TDELYCOMPANYD C,
TITEMTAGINFO TI WHERE C.AREA_LGROUP = :B4 AND C.AREA_MGROUP = :B3 AND
C.AREA_SGROUP = :B2 AND TI.ITEM_CODE = B.ITEM_CODE AND C.ITEM_DELY_GB =
NVL(TI.TAG7_YN,'1') AND B.ITEM_CODE = :B8 AND C.C_CODE = :B7 AND
C.WH_DELY_CODE = DECODE(B.TAG5_YN,'0',:B6 ,'3',:B6 ,'5',:B6 ,'4',:B6 ,'991')
AND C.ORDER_TYPE = '1' UNION SELECT C.SEND_DELY_GB FROM VITEM B,
TDELYCOMPANY C, TITEMTAGINFO TI WHERE C.AREA_LGROUP = :B4 AND C.AREA_MGROUP
= :B3 AND C.AREA_SGROUP = :B2 AND TI.ITEM_CODE = B.ITEM_CODE AND
C.ITEM_DELY_GB = NVL(TI.TAG7_YN,'1') AND B.ITEM_CODE = :B8 AND C.C_CODE =
:B7 AND C.WH_DELY_CODE = DECODE(B.TAG5_YN,'0',:B6 ,'3',:B6 ,'5',:B6 ,'4',
:B6 ,'991') ) ) ) ) A, TDELYAREA B, TORDERDELYDAY C WHERE A.AREA_LGROUP =
B.AREA_LGROUP AND (A.AREA_MGROUP = '999' OR (A.AREA_MGROUP = B.AREA_MGROUP
AND (A.AREA_SGROUP = '999' OR A.AREA_SGROUP = B.AREA_SGROUP) ) ) AND
B.USE_YN = '1' AND C.DELY_HOPE_DATE = :B5 AND C.DELY_HOPE_YN = '1' AND
C.AREA_LGROUP = B.AREA_LGROUP AND C.AREA_MGROUP = B.AREA_MGROUP AND
C.AREA_SGROUP = B.AREA_SGROUP AND C.DELY_GB = A.DELY_GB
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 18 0.01 0.02 0 0 0 0
Fetch 18 2.14 3.35 0 131727 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 36 2.15 3.37 0 131727 0 18
SELECT NVL(SUM(C.DELY_QTY), 0)
FROM
( SELECT T.DELY_GB,T.AREA_LGROUP,T.AREA_MGROUP,T.AREA_SGROUP FROM
TADMINDELIVERYQTY T, TDELYAREA B WHERE T.AREA_LGROUP = B.AREA_LGROUP AND
(T.AREA_MGROUP = '999' OR (T.AREA_MGROUP = B.AREA_MGROUP AND (T.AREA_SGROUP
= '999' OR T.AREA_SGROUP = B.AREA_SGROUP) ) ) AND B.USE_YN = '1' AND
T.YYMMDD = :B5 AND B.AREA_LGROUP = :B4 AND B.AREA_MGROUP = :B3 AND
B.AREA_SGROUP = :B2 AND ( ( :B1 <> '00' AND T.DELY_GB = :B1 ) OR ( (:B1 =
'00') AND T.DELY_GB IN ( SELECT C.DELY_GB FROM VITEM B, TDELYCOMPANYD C,
TITEMTAGINFO TI WHERE C.AREA_LGROUP = :B4 AND C.AREA_MGROUP = :B3 AND
C.AREA_SGROUP = :B2 AND TI.ITEM_CODE = B.ITEM_CODE AND C.ITEM_DELY_GB =
NVL(TI.TAG7_YN,'1') AND B.ITEM_CODE = :B8 AND C.C_CODE = :B7 AND
C.WH_DELY_CODE = DECODE(B.TAG5_YN,'0',:B6 ,'3',:B6 ,'5',:B6 ,'4',:B6 ,'991')
AND C.ORDER_TYPE = '1' UNION SELECT C.SEND_DELY_GB FROM VITEM B,
TDELYCOMPANY C, TITEMTAGINFO TI WHERE C.AREA_LGROUP = :B4 AND C.AREA_MGROUP
= :B3 AND C.AREA_SGROUP = :B2 AND TI.ITEM_CODE = B.ITEM_CODE AND
C.ITEM_DELY_GB = NVL(TI.TAG7_YN,'1') AND B.ITEM_CODE = :B8 AND C.C_CODE =
:B7 AND C.WH_DELY_CODE = DECODE(B.TAG5_YN,'0',:B6 ,'3',:B6 ,'5',:B6 ,'4',
:B6 ,'991') ) ) ) ) A, TDELYAREA B, TORDERDELYDAY C WHERE A.AREA_LGROUP =
B.AREA_LGROUP AND (A.AREA_MGROUP = '999' OR (A.AREA_MGROUP = B.AREA_MGROUP
AND (A.AREA_SGROUP = '999' OR A.AREA_SGROUP = B.AREA_SGROUP) ) ) AND
B.USE_YN = '1' AND C.DELY_HOPE_DATE = :B5 AND C.DELY_HOPE_YN = '1' AND
C.AREA_LGROUP = B.AREA_LGROUP AND C.AREA_MGROUP = B.AREA_MGROUP AND
C.AREA_SGROUP = B.AREA_SGROUP AND C.DELY_GB = A.DELY_GB
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 12 0.01 0.01 0 0 0 0
Fetch 12 0.03 0.10 0 12397 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.04 0.11 0 12397 0 12
131727 /18 vs 12397 /12 以上语句似乎和你描述的DATE_DAY 没有太大关系？ 这2句是在function中的一段内容 sql如下：
SELECT C.DATE_DAY,
GRUNDF.FUN_GET_ADMINDELIVERQTYMAX('4678732014',
'2000',
'991',
'00',
'10',
'001',
'001',
C.DATE_DAY) AS DMAX,
GRUNDF.FUN_GET_ADMINDELIVERQTYUSE('4678732014',
'2000',
'991',
'00',
'10',
'001',
'001',
C.DATE_DAY) AS DUSE
FROM (SELECT TRUNC(SYSDATE + ROWNUM) as DATE_DAY FROM TCODE WHERE ROWNUM < 7 ) C
尝试诊断为什么上述SQL单次执行 逻辑读会差这么多 看看执行计划有没有发生变化
页:
[1]