| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Very Urgent - Please help
--0-1804289383-957406927=:22529
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
aHi Oracle Gurus
Please help me in tuning a query. The query is really giving me very much trouble. I am in trouble with the timing. I am sending the query as well as package using the query along with Explain Plan
I know several good people are in this group like Jared,Steve,Dick,Earl,Testa and several others.I had also attached the files for your convenience.
Thanks in advance for prompt reply
Navtejbir
SELECT
DISTINCT TO_CHAR(lld.ld_leg_detl_id)
,ts.srvc_cd
,sp.cdty_cd
,a.ctry_cd
,a.sta_cd
,a.cty_name
,a.st_name
,a.blk_bldg
,a.unit
,NULL
FROM ld_leg_t ll, ld_leg_detl_t lld, tff_srvc_t ts, chrg_detl_t cd, shpm_t sp, addr_t a WHERE ll.ld_leg_id = 122625 AND NVL(lld.ld_leg_id,0) = 122625 AND ts.tff_id = NVL(lld.tff_id,0) AND ts.srvc_cd = NVL(lld.srvc_cd,'null') AND ts.carrcsld_ctl_enu <> 1 AND NVL(cd.ld_leg_detl_id,0) = lld.ld_leg_detl_id AND cd.chrg_lvl_enu = 2 AND NVL(cd.mnly_ovrd_unit,0) = NVL(cd.chgd_unit,0) AND NVL(cd.mnlyovrdunitrate,0) = NVL(cd.chgd_unit_rate,0) AND NVL(cd.mnly_ovrd_dlr,0) = NVL(cd.chrg_amt_dlr,0) AND ts.csld_prcnlvl_enu = 1 AND lld.to_addr_id = a.addr_id AND sp.shpm_id = lld.shpm_id UNION SELECT DISTINCT TO_CHAR(lld.ld_leg_detl_id)
,ts.srvc_cd
,sp.cdty_cd
,a.ctry_cd
,a.sta_cd
,a.cty_name
,a.st_name
,a.blk_bldg
,NULL
,NULL
FROM ld_leg_t ll, ld_leg_detl_t lld, tff_srvc_t ts, chrg_detl_t cd, shpm_t sp, addr_t a WHERE ll.ld_leg_id = 122625 AND NVL(lld.ld_leg_id,0) = 122625 AND ts.tff_id = NVL(lld.tff_id,0) AND ts.srvc_cd = NVL(lld.srvc_cd,'null') AND ts.carrcsld_ctl_enu <> 1 AND NVL(cd.ld_leg_detl_id,0) = lld.ld_leg_detl_id AND cd.chrg_lvl_enu = 2 AND NVL(cd.mnly_ovrd_unit,0) = NVL(cd.chgd_unit,0) AND NVL(cd.mnlyovrdunitrate,0) = NVL(cd.chgd_unit_rate,0) AND NVL(cd.mnly_ovrd_dlr,0) = NVL(cd.chrg_amt_dlr,0) AND ts.csld_prcnlvl_enu = 2 AND lld.to_addr_id = a.addr_id AND sp.shpm_id = lld.shpm_id UNION SELECT DISTINCT TO_CHAR(lld.ld_leg_detl_id)
,ts.srvc_cd
,sp.cdty_cd
,a.ctry_cd
,a.sta_cd
,a.cty_name
,NULL --a.st_name
,NULL --a.blk_bldg
,NULL --a.unit
,NULL --a.pstl_cd
FROM ld_leg_t ll, ld_leg_detl_t lld, tff_srvc_t ts, chrg_detl_t cd, shpm_t sp, addr_t a WHERE ll.ld_leg_id = 122625 AND NVL(lld.ld_leg_id,0) = 122625 AND ts.tff_id = NVL(lld.tff_id,0) AND ts.srvc_cd = NVL(lld.srvc_cd,'null') AND ts.carrcsld_ctl_enu <> 1 AND NVL(cd.ld_leg_detl_id,0) = lld.ld_leg_detl_id AND cd.chrg_lvl_enu = 2 AND NVL(cd.mnly_ovrd_unit,0) = NVL(cd.chgd_unit,0) AND NVL(cd.mnlyovrdunitrate,0) = NVL(cd.chgd_unit_rate,0) AND NVL(cd.mnly_ovrd_dlr,0) = NVL(cd.chrg_amt_dlr,0) AND ts.csld_prcnlvl_enu = 3 AND lld.to_addr_id = a.addr_id AND sp.shpm_id = lld.shpm_id UNION SELECT DISTINCT TO_CHAR(lld.ld_leg_detl_id)
,ts.srvc_cd
,sp.cdty_cd
,a.ctry_cd
,a.sta_cd
,NULL --a.cty_name
,NULL --a.st_name
,NULL --a.blk_bldg
,NULL --a.unit
,a.pstl_cd
FROM ld_leg_t ll, ld_leg_detl_t lld, tff_srvc_t ts, chrg_detl_t cd, shpm_t sp, addr_t a WHERE ll.ld_leg_id = 122625 AND NVL(lld.ld_leg_id,0) = 122625 AND ts.tff_id = NVL(lld.tff_id,0) AND ts.srvc_cd = NVL(lld.srvc_cd,'null') AND ts.carrcsld_ctl_enu <> 1 AND NVL(cd.ld_leg_detl_id,0) = lld.ld_leg_detl_id AND cd.chrg_lvl_enu = 2 AND NVL(cd.mnly_ovrd_unit,0) = NVL(cd.chgd_unit,0) AND NVL(cd.mnlyovrdunitrate,0) = NVL(cd.chgd_unit_rate,0) AND NVL(cd.mnly_ovrd_dlr,0) = NVL(cd.chrg_amt_dlr,0) AND ts.csld_prcnlvl_enu = 4 AND lld.to_addr_id = a.addr_id AND sp.shpm_id = lld.shpm_id UNION SELECT DISTINCT TO_CHAR(lld.ld_leg_detl_id)
,ts.srvc_cd
,sp.cdty_cd
,a.ctry_cd
,a.sta_cd
,NULL --a.cty_name
,NULL --a.st_name
,NULL --a.blk_bldg
,NULL --a.unit
,NULL --a.pstl_cd
FROM ld_leg_t ll, ld_leg_detl_t lld, tff_srvc_t ts, chrg_detl_t cd, shpm_t sp, addr_t a WHERE ll.ld_leg_id = 122625 AND NVL(lld.ld_leg_id,0) = 122625 AND ts.tff_id = NVL(lld.tff_id,0) AND ts.srvc_cd = NVL(lld.srvc_cd,'null') AND ts.carrcsld_ctl_enu <> 1 AND NVL(cd.ld_leg_detl_id,0) = lld.ld_leg_detl_id AND cd.chrg_lvl_enu = 2 AND NVL(cd.mnly_ovrd_unit,0) = NVL(cd.chgd_unit,0) AND NVL(cd.mnlyovrdunitrate,0) = NVL(cd.chgd_unit_rate,0) AND NVL(cd.mnly_ovrd_dlr,0) = NVL(cd.chrg_amt_dlr,0) AND ts.csld_prcnlvl_enu = 5 AND lld.to_addr_id = a.addr_id AND sp.shpm_id = lld.shpm_id ORDER BY 2, 3, 4, 5, 6, 7, 8, 9, 10 call count cpu elapsed disk query current rows
Parse 4 1.53 2.78 0 2068 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 4 186.07 295.96 354192 12102520 40 0
total 12 187.60 298.74 354192 12104588 40 0
Misses in library cache during parse: 2
Optimizer goal: CHOOSE
Parsing user id: 28 (SUROHMHAAS42)
Rows Execution Plan
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (UNIQUE)
0 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
2 NESTED LOOPS
147093 NESTED LOOPS
1 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF 'PK_LDLEG'
(UNIQUE)
478922 TABLE ACCESS GOAL: ANALYZED (FULL)
OF
'CHRG_DETL_T'
147035 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF
'LD_LEG_DETL_T'
147093 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF
'PK_LDLEGDETL' (UNIQUE)
2 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF
'TFF_SRVC_T'
2 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF 'PK_TFFSRVC'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
OF 'SHPM_T'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_SHPM'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
OF 'ADDR_T'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_ADDR'
(UNIQUE)
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
2 NESTED LOOPS
147093 NESTED LOOPS
1 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF 'PK_LDLEG'
(UNIQUE)
478922 TABLE ACCESS GOAL: ANALYZED (FULL)
OF
'CHRG_DETL_T'
147035 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF
'LD_LEG_DETL_T'
147093 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF
'PK_LDLEGDETL' (UNIQUE)
2 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF
'TFF_SRVC_T'
2 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF 'PK_TFFSRVC'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
OF 'SHPM_T'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_SHPM'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
OF 'ADDR_T'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_ADDR'
(UNIQUE)
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
2 NESTED LOOPS
147093 NESTED LOOPS
1 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF 'PK_LDLEG'
(UNIQUE)
478922 TABLE ACCESS GOAL: ANALYZED (FULL)
OF
'CHRG_DETL_T'
147035 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF
'LD_LEG_DETL_T'
147093 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF
'PK_LDLEGDETL' (UNIQUE)
2 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF
'TFF_SRVC_T'
2 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF 'PK_TFFSRVC'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
OF 'SHPM_T'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_SHPM'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
OF 'ADDR_T'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_ADDR'
(UNIQUE)
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
2 NESTED LOOPS
147093 NESTED LOOPS
1 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF 'PK_LDLEG'
(UNIQUE)
478922 TABLE ACCESS GOAL: ANALYZED (FULL)
OF
'CHRG_DETL_T'
147035 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF
'LD_LEG_DETL_T'
147093 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF
'PK_LDLEGDETL' (UNIQUE)
2 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF
'TFF_SRVC_T'
2 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF 'PK_TFFSRVC'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
OF 'SHPM_T'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_SHPM'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
OF 'ADDR_T'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_ADDR'
(UNIQUE)
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
2 NESTED LOOPS
147093 NESTED LOOPS
1 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF 'PK_LDLEG'
(UNIQUE)
478922 TABLE ACCESS GOAL: ANALYZED (FULL)
OF
'CHRG_DETL_T'
147035 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF
'LD_LEG_DETL_T'
147093 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF
'PK_LDLEGDETL' (UNIQUE)
2 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF
'TFF_SRVC_T'
2 INDEX GOAL: ANALYZED (UNIQUE SCAN)
OF 'PK_TFFSRVC'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
OF 'SHPM_T'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_SHPM'
(UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
OF 'ADDR_T'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_ADDR'
(UNIQUE)
********************************************************************************
__________________________________________________
Do You Yahoo!?
Content-Type: text/plain; name="package.txt" Content-Transfer-Encoding: base64 Content-Description: package.sql Content-Disposition: attachment; filename="package.txt"Received on Wed May 03 2000 - 21:22:07 CDT
 |
 |