-
SQL Tuning
Guys/Gals,
Would like your help here. I am not a SQL Tuning guru but I have to tune this SQL. Please help ..
SELECT a.operator
, a.run_id
, a.f_batch_prefix
, b.oprid
FROM batch_vg a
, oprdefn b
WHERE b.oprid = a.oprid
AND substr(a.run_id,1,2)='04';
Tell me in which way can this SQL be written better. Right now when this SQL is run it times out.
All input will be highly appreciated.
Chintzs
-
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
post the explain plan plz
I'm stmontgo and I approve of this message
-
Here is the explain plan:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=165695768 Card=2 Byt
es=100)
1 0 MERGE JOIN (Cost=165695768 Card=2 Bytes=100)
2 1 INDEX (FULL SCAN) OF 'AB_PSOPRDEFN' (UNIQUE) (Cost=14 Ca
rd=3821 Bytes=26747)
3 1 SORT (JOIN) (Cost=165695754 Card=2 Bytes=86)
4 3 VIEW OF 'BATCH_VG' (Cost=165695750 Card=2 By
tes=86)
5 4 SORT (UNIQUE) (Cost=165695750 Card=2 Bytes=205)
6 5 UNION-ALL
7 6 MERGE JOIN (Cost=106769835 Card=1 Bytes=102)
8 7 SORT (JOIN) (Cost=106769796 Card=1 Bytes=95)
9 8 MERGE JOIN (Cost=106769791 Card=1 Bytes=95)
10 9 SORT (JOIN) (Cost=15901 Card=23 Bytes=1173
)
11 10 HASH JOIN (Cost=15896 Card=23 Bytes=1173
)
12 11 INDEX (FAST FULL SCAN) OF 'ADJ_ER
N_CTL' (UNIQUE) (Cost=8 Card=206 Bytes=4944)
13 11 VIEW OF 'ADJERN_TRNTTL' (Cost=158
86 Card=1025100 Bytes=27677700)
14 13 SORT (GROUP BY) (Cost=15886 Card=102
5100 Bytes=18451800)
15 14 INDEX (FAST FULL SCAN) OF 'PR
_ADJUSTMNTS' (UNIQUE) (Cost=369 Card=1025100 Bytes=18451800)
16 9 SORT (JOIN) (Cost=106753891 Card=903012257
Bytes=39732539308)
17 16 VIEW OF 'ADJERN_ACCESS' (Cost=52603
57708 Card=903012257 Bytes=39732539308)
18 17 SORT (GROUP BY) (Cost=66159401 Card=90
3012257 Bytes=68628931532)
19 18 FILTER
20 19 HASH JOIN (Cost=1483438 Card=90301
2257 Bytes=68628931532)
21 20 TABLE ACCESS (FULL) OF 'PSOPRDEF
N' (Cost=5 Card=3821 Bytes=57315)
22 20 NESTED LOOPS (Cost=1481440 Card=
98076443 Bytes=5982663023)
23 22 TABLE ACCESS (FULL) OF 'SCR
TY_TBL_DEPT' (Cost=4 Card=1914 Bytes=63162)
24 22 TABLE ACCESS (FULL) OF 'P
R_ADJUSTMNTS' (Cost=774 Card=51255 Bytes=1435140)
25 19 FILTER
26 25 FILTER
27 26 TABLE ACCESS (BY INDEX ROWID)
OF 'TREENODE' (Cost=3 Card=1 Bytes=42)
28 27 BITMAP CONVERSION (TO ROWIDS
)
29 28 BITMAP AND
30 29 BITMAP CONVERSION (FROM
ROWIDS)
31 30 INDEX (RANGE SCAN) OF
'CPSTREENODE' (NON-UNIQUE) (Cost=1 Card=1)
32 29 BITMAP CONVERSION (FROM
ROWIDS)
33 32 INDEX (RANGE SCAN) OF
'FPSTREENODE' (NON-UNIQUE) (Cost=3 Card=1)
34 25 FILTER
35 34 TABLE ACCESS (BY INDEX ROWID)
OF 'SCRTY_TBL_DEPT' (Cost=3 Card=1 Bytes=25)
36 35 INDEX (RANGE SCAN) OF '3SC
RTY_TBL_DEPT' (NON-UNIQUE) (Cost=2 Card=2)
37 7 SORT (JOIN) (Cost=40 Card=3821 Bytes=26747)
38 37 INDEX (FAST FULL SCAN) OF 'BPSOPRDEFN' (NO
N-UNIQUE) (Cost=2 Card=3821 Bytes=26747)
39 6 MERGE JOIN (Cost=58925905 Card=1 Bytes=103)
40 39 SORT (JOIN) (Cost=58925866 Card=1 Bytes=96)
41 40 MERGE JOIN (Cost=58925861 Card=1 Bytes=96)
42 41 SORT (JOIN) (Cost=8864 Card=18 Bytes=936)
43 42 HASH JOIN (Cost=8860 Card=18 Bytes=936)
44 43 INDEX (FAST FULL SCAN) OF 'ADJ_DE
D_CTL' (UNIQUE) (Cost=4 Card=68 Bytes=1700)
45 43 VIEW OF 'ADJDED_TRNTTL' (Cost=885
5 Card=566944 Bytes=15307488)
46 45 SORT (GROUP BY) (Cost=8855 Card=5669
44 Bytes=10204992)
47 46 INDEX (FAST FULL SCAN) OF 'AD
J_DEDUCTNS' (UNIQUE) (Cost=272 Card=566944 Bytes=10204992)
48 41 SORT (JOIN) (Cost=58916997 Card=499421892
Bytes=21974563248)
49 48 VIEW OF 'ADJDED_ACCESS' (Cost=29091
80155 Card=499421892 Bytes=21974563248)
50 49 SORT (GROUP BY) (Cost=36465720 Card=49
9421892 Bytes=37956063792)
51 50 FILTER
52 51 HASH JOIN (Cost=695895 Card=499421
892 Bytes=37956063792)
53 52 TABLE ACCESS (FULL) OF 'OPRDEF
N' (Cost=5 Card=3821 Bytes=57315)
54 52 NESTED LOOPS (Cost=694786 Card=5
4242367 Bytes=3308784387)
55 54 TABLE ACCESS (FULL) OF 'SCR
TY_TBL_DEPT' (Cost=4 Card=1914 Bytes=63162)
56 54 TABLE ACCESS (FULL) OF 'A
DJ_DEDUCTNS' (Cost=363 Card=28347 Bytes=793716)
57 51 FILTER
58 57 FILTER
59 58 TABLE ACCESS (BY INDEX ROWID)
OF 'TREENODE' (Cost=3 Card=1 Bytes=42)
60 59 BITMAP CONVERSION (TO ROWIDS
)
61 60 BITMAP AND
62 61 BITMAP CONVERSION (FROM
ROWIDS)
63 62 INDEX (RANGE SCAN) OF
'CPSTREENODE' (NON-UNIQUE) (Cost=1 Card=1)
64 61 BITMAP CONVERSION (FROM
ROWIDS)
65 64 INDEX (RANGE SCAN) OF
'FPSTREENODE' (NON-UNIQUE) (Cost=3 Card=1)
66 57 FILTER
67 66 TABLE ACCESS (BY INDEX ROWID)
OF 'SCRTY_TBL_DEPT' (Cost=3 Card=1 Bytes=25)
68 67 INDEX (RANGE SCAN) OF '3SC
RTY_TBL_DEPT' (NON-UNIQUE) (Cost=2 Card=2)
69 39 SORT (JOIN) (Cost=40 Card=3821 Bytes=26747)
70 69 INDEX (FAST FULL SCAN) OF 'BPSOPRDEFN' (NO
N-UNIQUE) (Cost=2 Card=3821 Bytes=26747)
Let me know if you want it in a different format.
Thanks in advance.
Chintzs
-
If this query is based on two separate views, you may want to tune the views separately then tune them together. Be careful of joining views together that are based on the same underlying tables, that could cause a performance issue. Also be careful of writing views on views on views calling the same tables in the various layers.
-
and learn how to format posts properly, impossible to read otherwise
-
Based on some of your input I started looking at the underlying objects closely and I see that the Select statement is making a join between a view(BATCH_VG) and a table(oprdefn). The View is very complex and does query the "oprdefn" table too. The code for the view is:
CREATE OR REPLACE VIEW "SCOTT"."BATCH_VG" ("OPERATOR",
"RUN_ID","F_BATCH_PREFIX","OPRID") AS
select a.operator, a.run_id, a.f_batch_prefix, zz1.oprid
from adj_ern_ctl a, adjern_access zz1,
adjern_trnttl zz2, oprdefn y
where a.run_id = zz1.run_id
and a.run_id = zz2.run_id
and zz1.run_id = zz2.run_id
and zz1.batch_run_id = zz2.batch_run_id
and zz1.f_adjern_accessttl = zz2.f_adj_ern_tran_ttl
and a.batch_run_id = zz1.batch_run_id
and a.batch_run_id = zz2.batch_run_id
and y.oprid = zz1.oprid union
select a1.operator, a1.run_id, a1.f_batch_prefix, zz11.oprid
from adj_ded_ctl a1, adjded_access zz11,
adjded_trnttl zz21, oprdefn y1
where a1.run_id = zz11.run_id
and a1.run_id = zz21.run_id
and zz11.run_id = zz21.run_id
and zz11.batch_run_id = zz21.batch_run_id
and zz11.f_adjded_accessttl = zz21.f_adj_ded_tran_ttl
and a1.batch_run_id = zz11.batch_run_id
and a1.batch_run_id = zz21.batch_run_id
and y1.oprid = zz11.oprid;
I look forward to hearing your input on this and pointing me in the direction this query should be.
Thanks in advance,
Chintzs
-
Re: SQL Tuning
Originally posted by Chintz
Guys/Gals,
Would like your help here. I am not a SQL Tuning guru but I have to tune this SQL. Please help ..
SELECT a.operator
, a.run_id
, a.f_batch_prefix
, b.oprid
FROM batch_vg a
, oprdefn b
WHERE b.oprid = a.oprid
AND substr(a.run_id,1,2)='04';
Tell me in which way can this SQL be written better. Right now when this SQL is run it times out.
All input will be highly appreciated.
Chintzs
This is a simple sql select statement that could be just derives from views with maybe from underlying views/table(as what gandolf is trying to say).
You might just need to rewrite your code.
can you post the descriptions of tables involve?
-
Try to code it using the original table name and be sure you
had analyzed it and got index both on oprid Did someone said it yet?
What I mean is u can use smaller temporary tables. Just extract the
basic info from each tables and insert/join into smaller products
Ms C3
Last edited by kris123; 04-29-2005 at 01:10 AM.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|