GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : staName7918
Tuning Task Owner : VEASMS201501
Tuning Task ID : 65478
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 05/22/2015 15:43:31
Completed at : 05/22/2015 15:43:35
Number of SQL Restructure Findings: 1
-------------------------------------------------------------------------------
Schema Name: VEASMS201501
SQL ID : 95vtg3rzsb59b
SQL Text : select DECODE(xl.xlly, '2', '3', '1') xljh,
j.lx,
j.CJ_COMPID compmc,
j.JHBH,
j.XLBH,
j.XLMC,
j.cfcs,
j.MDCS MDDMC,
j.JTGJ jtgjmc,
'0' from_tdd_jh,
'' tddh,
jr.cfrq CFRQ,
'' jzrq,
j.CJ_USERID jdyxm,
j.CJ_USERID jdyid,
xl.gysid,
j.ts,
xl.GYS_DEPTID,
xl.GYS_USERID,
xl.kdyje_zz,
xl.kdyje_dz,
j.by3,
j.jtzq,
j.jtgz,
xl.xlgg,
xl.bzbz,
j.rdxl,
j.tjxl,
j.yhxl,
0 zrs,
0 bwrs,
0 bmrs,
0 zwrs,
0 xzwrs
from LY_JH j,
ly_xl xl,
LY_XLJH_QW jq,
(select r.jhbh, min(r.cfrq) cfrq
from ly_jhrq r
where 1 = 1
AND r.CFRQ >= '2015-01-20'
AND r.CFRQ <= '2015-06-19'
and (zt = '0' or zt = '1')
and NOT EXISTS
(SELECT 1
FROM LY_TDD tdd
WHERE DJBH = R.JHBH
AND tdd.cfrq = r.cfrq
AND (DDZT = '7' OR
DDZT = '8'))
group by r.jhbh) jr
where j.xlbh = xl.xlbh
and (xl.xlly = '2' or
(xl.xlly = '1' and j.by3 = '0'))
AND XL.ZT <> '2'
and exists (select 1
from LY_JHJG g
where g.jhbh = j.jhbh
and g.JSLX = '1008001')
and j.ZGS = 'XYJ'
and j.jhlx = '1'
and jq.jhbh = j.jhbh
and contains(jq.qw, '线路') > 0
and jr.JHBH = j.jhbh
order by xlmc, cfrq
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
在执行计划的行 ID 6 处发现开销很大的笛卡尔积操作。
Recommendation
--------------
- 考虑从此语句中移去断开连接的表或视图, 或者添加引用它的联接条件。
Rationale
---------
应尽可能避免笛卡尔积操作, 因为它的开销很大, 并且会产生大量数据。
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3609082328
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 3087 | 92 (7)| 00:00:02 |
| 1 | SORT ORDER BY | | 3 | 3087 | 92 (7)| 00:00:02 |
| 2 | NESTED LOOPS | | 3 | 3087 | 91 (6)| 00:00:02 |
|* 3 | HASH JOIN | | 6 | 5676 | 86 (6)| 00:00:02 |
|* 4 | HASH JOIN | | 16 | 14720 | 38 (6)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | LY_JH | 1251 | 151K| 21 (0)| 00:00:01 |
| 6 | MERGE JOIN CARTESIAN | | 20910 | 15M| 16 (7)| 00:00:01 |
| 7 | SORT UNIQUE | | 3044 | 85232 | 15 (0)| 00:00:01 |
|* 8 | INDEX FAST FULL SCAN | UNI_LY_JHJG | 3044 | 85232 | 15 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 102 | 78336 | 1 (100)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | LY_XLJH_QW | 102 | 78336 | 16 (7)| 00:00:01 |
|* 11 | DOMAIN INDEX | IDX_LY_XLJH_QW | | | 0 (0)| 00:00:01 |
| 12 | VIEW | | 433 | 11258 | 47 (5)| 00:00:01 |
| 13 | HASH GROUP BY | | 433 | 27712 | 47 (5)| 00:00:01 |
|* 14 | HASH JOIN RIGHT ANTI | | 593 | 37952 | 46 (3)| 00:00:01 |
| 15 | INLIST ITERATOR | | | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID| LY_TDD | 1 | 32 | 4 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | IDX_LYTDD_DDZT | 21 | | 2 (0)| 00:00:01 |
|* 18 | INDEX FAST FULL SCAN | IDX_LY_JHRQ1 | 593 | 18976 | 42 (3)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID | LY_XL | 1 | 83 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_LY_XL | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$6590669A
5 - SEL$6590669A / J@SEL$1
8 - SEL$6590669A / G@SEL$4
10 - SEL$6590669A / JQ@SEL$1
11 - SEL$6590669A / JQ@SEL$1
12 - SEL$BE5C8E5F / JR@SEL$1
13 - SEL$BE5C8E5F
16 - SEL$BE5C8E5F / TDD@SEL$3
17 - SEL$BE5C8E5F / TDD@SEL$3
18 - SEL$BE5C8E5F / R@SEL$2
19 - SEL$6590669A / XL@SEL$1
20 - SEL$6590669A / XL@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("JR"."JHBH"="J"."JHBH")
4 - access("JQ"."JHBH"="J"."JHBH" AND "G"."JHBH"="J"."JHBH")
5 - filter("J"."ZGS"='XYJ' AND "J"."JHLX"='1')
8 - filter("G"."JSLX"='1008001')
11 - access("CTXSYS"."CONTAINS"("JQ"."QW",'线路')>0)
14 - access("DJBH"="R"."JHBH" AND "TDD"."CFRQ"="R"."CFRQ")
16 - filter("TDD"."CFRQ"<='2015-06-19' AND "TDD"."CFRQ">='2015-01-20')
17 - access("DDZT"='7' OR "DDZT"='8')
18 - filter("R"."CFRQ"<='2015-06-19' AND "R"."CFRQ">='2015-01-20' AND ("ZT"='0' OR
"ZT"='1'))
19 - filter("XL"."ZT"<>'2' AND ("XL"."XLLY"='1' OR "XL"."XLLY"='2') AND ("XL"."XLLY"='2'
OR "XL"."XLLY"='1' AND "J"."BY3"='0'))
20 - access("J"."XLBH"="XL"."XLBH")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=2) "J"."XLMC"[VARCHAR2,200], "JR"."CFRQ"[VARCHAR2,10],
DECODE("XL"."XLLY",'2','3','1')[1], "J"."LX"[VARCHAR2,10], "J"."CJ_COMPID"[VARCHAR2,10],
"J"."JHBH"[VARCHAR2,30], "J"."XLBH"[VARCHAR2,30], 0[1], "J"."CFCS"[VARCHAR2,10],
"J"."MDCS"[VARCHAR2,10], "J"."JTGJ"[VARCHAR2,10], '0'[1], ''[0], 0[1], ''[0],
"J"."CJ_USERID"[VARCHAR2,20], "J"."CJ_USERID"[VARCHAR2,20], "XL"."GYSID"[VARCHAR2,20],
"J"."TS"[NUMBER,22], "XL"."GYS_DEPTID"[VARCHAR2,20], "XL"."GYS_USERID"[VARCHAR2,20],
"XL"."KDYJE_ZZ"[NUMBER,22], "XL"."KDYJE_DZ"[NUMBER,22], "J"."BY3"[VARCHAR2,100],
"J"."JTZQ"[VARCHAR2,1], "J"."JTGZ"[VARCHAR2,4000], "XL"."XLGG"[VARCHAR2,1000],
"XL"."BZBZ"[VARCHAR2,200], "J"."RDXL"[VARCHAR2,1], "J"."TJXL"[VARCHAR2,1],
"J"."YHXL"[VARCHAR2,1], 0[1], 0[1], 0[1]
2 - (#keys=0) "J"."JHBH"[VARCHAR2,30], "J"."JHBH"[VARCHAR2,30], "J"."YHXL"[VARCHAR2,1],
"J"."XLBH"[VARCHAR2,30], "J"."XLMC"[VARCHAR2,200], "J"."LX"[VARCHAR2,10],
"J"."TS"[NUMBER,22], "J"."JTZQ"[VARCHAR2,1], "J"."JTGZ"[VARCHAR2,4000],
"J"."RDXL"[VARCHAR2,1], "J"."TJXL"[VARCHAR2,1], "J"."CFCS"[VARCHAR2,10],
"J"."MDCS"[VARCHAR2,10], "J"."JTGJ"[VARCHAR2,10], "J"."CJ_USERID"[VARCHAR2,20],
"J"."CJ_COMPID"[VARCHAR2,10], "J"."BY3"[VARCHAR2,100], "JR"."CFRQ"[VARCHAR2,10],
"XL"."BZBZ"[VARCHAR2,200], "XL"."XLLY"[VARCHAR2,1], "XL"."GYSID"[VARCHAR2,20],
"XL"."GYS_DEPTID"[VARCHAR2,20], "XL"."GYS_USERID"[VARCHAR2,20], "XL"."XLGG"[VARCHAR2,1000],
"XL"."KDYJE_ZZ"[NUMBER,22], "XL"."KDYJE_DZ"[NUMBER,22]
3 - (#keys=1) "J"."JHBH"[VARCHAR2,30], "J"."JHBH"[VARCHAR2,30], "J"."YHXL"[VARCHAR2,1],
"J"."XLBH"[VARCHAR2,30], "J"."XLMC"[VARCHAR2,200], "J"."LX"[VARCHAR2,10],
"J"."TS"[NUMBER,22], "J"."JTZQ"[VARCHAR2,1], "J"."JTGZ"[VARCHAR2,4000],
"J"."RDXL"[VARCHAR2,1], "J"."TJXL"[VARCHAR2,1], "J"."CFCS"[VARCHAR2,10],
"J"."MDCS"[VARCHAR2,10], "J"."JTGJ"[VARCHAR2,10], "J"."CJ_USERID"[VARCHAR2,20],
"J"."CJ_COMPID"[VARCHAR2,10], "J"."BY3"[VARCHAR2,100], "JR"."CFRQ"[VARCHAR2,10]
4 - (#keys=2) "J"."JHBH"[VARCHAR2,30], "JQ"."JHBH"[VARCHAR2,30], "J"."JHBH"[VARCHAR2,30],
"G"."JHBH"[VARCHAR2,30], "J"."YHXL"[VARCHAR2,1], "J"."JHLX"[VARCHAR2,10],
"J"."XLBH"[VARCHAR2,30], "J"."XLMC"[VARCHAR2,200], "J"."LX"[VARCHAR2,10],
"J"."TS"[NUMBER,22], "J"."JTZQ"[VARCHAR2,1], "J"."JTGZ"[VARCHAR2,4000],
"J"."RDXL"[VARCHAR2,1], "J"."TJXL"[VARCHAR2,1], "J"."CFCS"[VARCHAR2,10],
"J"."MDCS"[VARCHAR2,10], "J"."JTGJ"[VARCHAR2,10], "J"."CJ_USERID"[VARCHAR2,20],
"J"."CJ_COMPID"[VARCHAR2,10], "J"."ZGS"[VARCHAR2,10], "J"."BY3"[VARCHAR2,100],
"JQ"."QW"[LOB,4000], "JQ".ROWID[ROWID,10]
5 - "J"."JHBH"[VARCHAR2,30], "J"."JHLX"[VARCHAR2,10], "J"."XLBH"[VARCHAR2,30],
"J"."XLMC"[VARCHAR2,200], "J"."LX"[VARCHAR2,10], "J"."TS"[NUMBER,22],
"J"."JTZQ"[VARCHAR2,1], "J"."JTGZ"[VARCHAR2,4000], "J"."RDXL"[VARCHAR2,1],
"J"."TJXL"[VARCHAR2,1], "J"."CFCS"[VARCHAR2,10], "J"."MDCS"[VARCHAR2,10],
"J"."JTGJ"[VARCHAR2,10], "J"."CJ_USERID"[VARCHAR2,20], "J"."CJ_COMPID"[VARCHAR2,10],
"J"."ZGS"[VARCHAR2,10], "J"."BY3"[VARCHAR2,100], "J"."YHXL"[VARCHAR2,1]
6 - (#keys=0) "G"."JHBH"[VARCHAR2,30], "JQ".ROWID[ROWID,10], "JQ"."JHBH"[VARCHAR2,30],
"JQ"."QW"[LOB,4000]
7 - (#keys=1) "G"."JHBH"[VARCHAR2,30]
8 - "G"."JHBH"[VARCHAR2,30], "G"."JSLX"[VARCHAR2,10]
9 - (#keys=0) "JQ".ROWID[ROWID,10], "JQ"."JHBH"[VARCHAR2,30], "JQ"."QW"[LOB,4000]
10 - "JQ".ROWID[ROWID,10], "JQ"."JHBH"[VARCHAR2,30], "JQ"."QW"[LOB,4000]
11 - "JQ".ROWID[ROWID,10], "JQ"."JHBH"[VARCHAR2,30], "JQ"."QW"[LOB,4000]
12 - "JR"."JHBH"[VARCHAR2,30], "JR"."CFRQ"[VARCHAR2,10]
13 - (#keys=1) "R"."JHBH"[VARCHAR2,30], MIN("R"."CFRQ")[10]
14 - (#keys=2) "R"."JHBH"[VARCHAR2,30], "R"."CFRQ"[VARCHAR2,10]
15 - "TDD"."CFRQ"[VARCHAR2,10], "DJBH"[VARCHAR2,30]
16 - "TDD"."CFRQ"[VARCHAR2,10], "DDZT"[VARCHAR2,1], "DJBH"[VARCHAR2,30]
17 - "TDD".ROWID[ROWID,10], "DDZT"[VARCHAR2,1]
18 - "R"."JHBH"[VARCHAR2,30], "R"."CFRQ"[VARCHAR2,10]
19 - "XL"."BZBZ"[VARCHAR2,200], "XL"."XLLY"[VARCHAR2,1], "XL"."GYSID"[VARCHAR2,20],
"XL"."GYS_DEPTID"[VARCHAR2,20], "XL"."GYS_USERID"[VARCHAR2,20], "XL"."XLGG"[VARCHAR2,1000],
"XL"."KDYJE_ZZ"[NUMBER,22], "XL"."KDYJE_DZ"[NUMBER,22]
20 - "XL".ROWID[ROWID,10]
-------------------------------------------------------------------------------