| Query Tuning [message #358193] |
Mon, 10 November 2008 00:59  |
manoj12
Messages: 210
Registered: March 2008
Location: India
|
Senior Member |
|
|
Dear Sir
Dear Sir
This query is taking 3 seconds to execute it
select distinct
atcf.base_entity_id
, be.entity_name_full As Cutomer_Name
, mbf.bank_facility_name AS Account_No
, atcf.proposed_limit AS Amount_Sanction
, atcf.os_balance AS Amount_Outstand
, rfdt.filter_date
, bd.mst_borrower_state_id
from aud_trn_c3_fac atcf
, rvw_cas_getchkfacdt rfdt
, base_entity be
, rvw_cas_stmt_borr_dtls bd
, mst_bank_facility mbf
Where atcf.trans_datetime = rfdt.facility_dt and
atcf.base_entity_id = rfdt.base_entity_id and
atcf.base_entity_id = be.id and
atcf.id = rfdt.trn_c3_fac_id and
rfdt.base_entity_id = bd.base_entity_id and
rfdt.sub_process_id = bd.sub_process_id and
rfdt.filter_date = bd.filter_date and
rfdt.base_entity_id = be.id and
atcf.mst_bank_facility_id = mbf.id and
mbf.mst_facilitytype_id = 1 and
(atcf.base_entity_id, atcf.id) not in
(
select distinct tss.base_entity_id,
tss.trn_prop_facility_id
from
rvw_cas_stmt_sanc_sec_dtls tss)
Please check the explain Plan
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=292 Card=1 Bytes=153
)
1 0 SORT (UNIQUE) (Cost=228 Card=1 Bytes=153)
2 1 FILTER
3 2 MERGE JOIN (Cost=226 Card=1 Bytes=153)
4 3 SORT (JOIN) (Cost=64 Card=1 Bytes=105)
5 4 MERGE JOIN (Cost=63 Card=1 Bytes=105)
6 5 SORT (JOIN) (Cost=14 Card=48 Bytes=3696)
7 6 HASH JOIN (Cost=12 Card=48 Bytes=3696)
8 7 HASH JOIN (Cost=7 Card=48 Bytes=2352)
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'MST_BANK
_FACILITY' (Cost=2 Card=29 Bytes=754)
10 9 INDEX (RANGE SCAN) OF 'IDX_MST_BANK_FACI
LITY' (NON-UNIQUE) (Cost=1 Card=29)
11 8 TABLE ACCESS (FULL) OF 'AUD_TRN_C3_FAC' (C
ost=4 Card=48 Bytes=1104)
12 7 TABLE ACCESS (FULL) OF 'BASE_ENTITY' (Cost=4
Card=1567 Bytes=43876)
13 5 SORT (JOIN) (Cost=49 Card=1 Bytes=28)
14 13 VIEW OF 'RVW_CAS_GETCHKFACDT' (Cost=48 Card=1
Bytes=28)
15 14 SORT (GROUP BY) (Cost=48 Card=1 Bytes=63)
16 15 MERGE JOIN (Cost=46 Card=1 Bytes=63)
17 16 SORT (JOIN) (Cost=10 Card=1 Bytes=26)
18 17 HASH JOIN (Cost=8 Card=1 Bytes=26)
19 18 TABLE ACCESS (FULL) OF 'TRN_C3_FAC'
(Cost=3 Card=328 Bytes=2952)
20 18 TABLE ACCESS (FULL) OF 'AUD_TRN_C3_F
AC' (Cost=4 Card=951 Bytes=16167)
21 16 FILTER
22 21 SORT (JOIN)
23 22 VIEW (Cost=44 Card=97 Bytes=3589)
24 23 UNION-ALL
25 24 SORT (UNIQUE) (Cost=30 Card=89 B
ytes=2492)
26 25 FILTER
27 26 TABLE ACCESS (FULL) OF 'AUD_
TRN_PROC_STATE' (Cost=28 Card=89 Bytes=2492)
28 27 SORT (AGGREGATE)
29 28 TABLE ACCESS (FULL) OF '
MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)
30 26 TABLE ACCESS (FULL) OF 'MSTW
F_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)
31 24 SORT (UNIQUE) (Cost=6 Card=8 Byt
es=176)
32 31 FILTER
33 32 TABLE ACCESS (FULL) OF 'AUD_
TRN_DATA_STATE' (Cost=4 Card=8 Bytes=176)
34 33 SORT (AGGREGATE)
35 34 TABLE ACCESS (FULL) OF '
MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)
36 32 TABLE ACCESS (FULL) OF 'MSTW
F_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)
37 3 SORT (JOIN) (Cost=162 Card=3 Bytes=144)
38 37 VIEW OF 'RVW_CAS_STMT_BORR_DTLS' (Cost=162 Card=3
Bytes=144)
39 38 UNION-ALL
40 39 SORT (UNIQUE) (Cost=56 Card=1 Bytes=51)
41 40 MERGE JOIN (Cost=54 Card=1 Bytes=51)
42 41 SORT (JOIN) (Cost=49 Card=1 Bytes=24)
43 42 VIEW (Cost=47 Card=1 Bytes=24)
44 43 SORT (GROUP BY) (Cost=47 Card=1 Bytes=
45)
45 44 MERGE JOIN (Cost=44 Card=1 Bytes=45)
46 45 SORT (JOIN) (Cost=8 Card=4 Bytes=7
6)
47 46 HASH JOIN (Cost=6 Card=4 Bytes=7
6)
48 47 TABLE ACCESS (FULL) OF 'TRN_C3
_CORPORATE' (Cost=2 Card=110 Bytes=660)
49 47 TABLE ACCESS (FULL) OF 'AUD_TR
N_C3_CORPORATE' (Cost=3 Card=358 Bytes=4654)
50 45 FILTER
51 50 SORT (JOIN)
52 51 VIEW (Cost=44 Card=97 Bytes=25
22)
53 52 UNION-ALL
54 53 SORT (UNIQUE) (Cost=30 Car
d=89 Bytes=2492)
55 54 FILTER
56 55 TABLE ACCESS (FULL) OF
'AUD_TRN_PROC_STATE' (Cost=28 Card=89 Bytes=2492)
57 56 SORT (AGGREGATE)
58 57 TABLE ACCESS (FULL
) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)
59 55 TABLE ACCESS (FULL) OF
'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)
60 53 SORT (UNIQUE) (Cost=6 Card
=8 Bytes=176)
61 60 FILTER
62 61 TABLE ACCESS (FULL) OF
'AUD_TRN_DATA_STATE' (Cost=4 Card=8 Bytes=176)
63 62 SORT (AGGREGATE)
64 63 TABLE ACCESS (FULL
) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)
65 61 TABLE ACCESS (FULL) OF
'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)
66 41 SORT (JOIN) (Cost=6 Card=358 Bytes=9666)
67 66 TABLE ACCESS (FULL) OF 'AUD_TRN_C3_CORPO
RATE' (Cost=3 Card=358 Bytes=9666)
68 39 SORT (UNIQUE) (Cost=53 Card=1 Bytes=51)
69 68 MERGE JOIN (Cost=51 Card=1 Bytes=51)
70 69 SORT (JOIN) (Cost=48 Card=3 Bytes=72)
71 70 VIEW (Cost=46 Card=3 Bytes=72)
72 71 SORT (GROUP BY) (Cost=46 Card=3 Bytes=
135)
73 72 MERGE JOIN (Cost=43 Card=49 Bytes=22
05)
74 73 SORT (JOIN) (Cost=7 Card=30 Bytes=
570)
75 74 HASH JOIN (Cost=5 Card=30 Bytes=
570)
76 75 TABLE ACCESS (FULL) OF 'TRN_C3
_BANK' (Cost=2 Card=4 Bytes=24)
77 75 TABLE ACCESS (FULL) OF 'AUD_TR
N_C3_BANK' (Cost=2 Card=68 Bytes=884)
78 73 FILTER
79 78 SORT (JOIN)
80 79 VIEW (Cost=44 Card=97 Bytes=25
22)
81 80 UNION-ALL
82 81 SORT (UNIQUE) (Cost=30 Car
d=89 Bytes=2492)
83 82 FILTER
84 83 TABLE ACCESS (FULL) OF
'AUD_TRN_PROC_STATE' (Cost=28 Card=89 Bytes=2492)
85 84 SORT (AGGREGATE)
86 85 TABLE ACCESS (FULL
) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)
87 83 TABLE ACCESS (FULL) OF
'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)
88 81 SORT (UNIQUE) (Cost=6 Card
=8 Bytes=176)
89 88 FILTER
90 89 TABLE ACCESS (FULL) OF
'AUD_TRN_DATA_STATE' (Cost=4 Card=8 Bytes=176)
91 90 SORT (AGGREGATE)
92 91 TABLE ACCESS (FULL
) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)
93 89 TABLE ACCESS (FULL) OF
'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)
94 69 SORT (JOIN) (Cost=4 Card=68 Bytes=1836)
95 94 TABLE ACCESS (FULL) OF 'AUD_TRN_C3_BANK'
(Cost=2 Card=68 Bytes=1836)
96 39 SORT (UNIQUE) (Cost=53 Card=1 Bytes=45)
97 96 MERGE JOIN (Cost=51 Card=1 Bytes=45)
98 97 SORT (JOIN) (Cost=48 Card=3 Bytes=72)
99 98 VIEW (Cost=46 Card=3 Bytes=72)
100 99 SORT (GROUP BY) (Cost=46 Card=3 Bytes=
135)
101 100 MERGE JOIN (Cost=43 Card=29 Bytes=13
05)
102 101 SORT (JOIN) (Cost=7 Card=18 Bytes=
342)
103 102 HASH JOIN (Cost=5 Card=18 Bytes=
342)
104 103 TABLE ACCESS (FULL) OF 'TRN_C3
_SOVEREIGN' (Cost=2 Card=3 Bytes=18)
105 103 TABLE ACCESS (FULL) OF 'AUD_TR
N_C3_SOVEREIGN' (Cost=2 Card=53 Bytes=689)
106 101 FILTER
107 106 SORT (JOIN)
108 107 VIEW (Cost=44 Card=97 Bytes=25
22)
109 108 UNION-ALL
110 109 SORT (UNIQUE) (Cost=30 Car
d=89 Bytes=2492)
111 110 FILTER
112 111 TABLE ACCESS (FULL) OF
'AUD_TRN_PROC_STATE' (Cost=28 Card=89 Bytes=2492)
113 112 SORT (AGGREGATE)
114 113 TABLE ACCESS (FULL
) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)
115 111 TABLE ACCESS (FULL) OF
'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)
116 109 SORT (UNIQUE) (Cost=6 Card
=8 Bytes=176)
117 116 FILTER
118 117 TABLE ACCESS (FULL) OF
'AUD_TRN_DATA_STATE' (Cost=4 Card=8 Bytes=176)
119 118 SORT (AGGREGATE)
120 119 TABLE ACCESS (FULL
) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)
121 117 TABLE ACCESS (FULL) OF
'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)
122 97 SORT (JOIN) (Cost=4 Card=53 Bytes=1113)
123 122 TABLE ACCESS (FULL) OF 'AUD_TRN_C3_SOVER
EIGN' (Cost=2 Card=53 Bytes=1113)
124 2 MERGE JOIN (Cost=64 Card=1 Bytes=69)
125 124 SORT (JOIN) (Cost=60 Card=1 Bytes=50)
126 125 VIEW (Cost=59 Card=1 Bytes=50)
127 126 SORT (GROUP BY) (Cost=59 Card=1 Bytes=47)
128 127 MERGE JOIN (Cost=56 Card=1 Bytes=47)
129 128 SORT (JOIN) (Cost=7 Card=1 Bytes=28)
130 129 HASH JOIN (Cost=5 Card=1 Bytes=28)
131 130 TABLE ACCESS (FULL) OF 'TRN_SANC_SECURIT
Y' (Cost=2 Card=270 Bytes=2430)
132 130 TABLE ACCESS (FULL) OF 'AUD_TRN_SANC_SEC
URITY' (Cost=2 Card=428 Bytes=8132)
133 128 FILTER
134 133 SORT (JOIN)
135 134 VIEW OF 'RVW_CAS_GETCHKFACDT' (Cost=48 C
ard=1 Bytes=19)
136 135 SORT (GROUP BY) (Cost=48 Card=1 Bytes=
63)
137 136 MERGE JOIN (Cost=46 Card=1 Bytes=63)
138 137 SORT (JOIN) (Cost=10 Card=1 Bytes=
26)
139 138 HASH JOIN (Cost=8 Card=1 Bytes=2
6)
140 139 TABLE ACCESS (FULL) OF 'TRN_C3
_FAC' (Cost=3 Card=328 Bytes=2952)
141 139 TABLE ACCESS (FULL) OF 'AUD_TR
N_C3_FAC' (Cost=4 Card=951 Bytes=16167)
142 137 FILTER
143 142 SORT (JOIN)
144 143 VIEW (Cost=44 Card=97 Bytes=35
89)
145 144 UNION-ALL
146 145 SORT (UNIQUE) (Cost=30 Car
d=89 Bytes=2492)
147 146 FILTER
148 147 TABLE ACCESS (FULL) OF
'AUD_TRN_PROC_STATE' (Cost=28 Card=89 Bytes=2492)
149 148 SORT (AGGREGATE)
150 149 TABLE ACCESS (FULL
) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)
151 147 TABLE ACCESS (FULL) OF
'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)
152 145 SORT (UNIQUE) (Cost=6 Card
=8 Bytes=176)
153 152 FILTER
154 153 TABLE ACCESS (FULL) OF
'AUD_TRN_DATA_STATE' (Cost=4 Card=8 Bytes=176)
155 154 SORT (AGGREGATE)
156 155 TABLE ACCESS (FULL
) OF 'MSTWF_PROCESS_ITEM' (Cost=2 Card=1 Bytes=16)
157 153 TABLE ACCESS (FULL) OF
'MSTWF_MKR_CHKR_STATE' (Cost=2 Card=1 Bytes=18)
158 124 SORT (JOIN) (Cost=4 Card=1 Bytes=19)
159 158 TABLE ACCESS (FULL) OF 'AUD_TRN_SANC_SECURITY' (Co
st=2 Card=1 Bytes=19)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
35867 consistent gets
22459 physical reads
0 redo size
3309 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
964 sorts (memory)
0 sorts (disk)
68 rows processed
Please help to tune the below query?
Regards
|
|
|
|
| Re: Query Tuning [message #358201 is a reply to message #358193] |
Mon, 10 November 2008 01:36  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Hmmmm. 29 lines of SQL, 159 lines of Explain Plan.
My spidey-senses detect some VIEWS nearby.
Furthermore, I see a number of UNION and SORT(GROUP BY) steps in the plan but none in the SQL. This tells me that your views are probably complex non-mergeable views.
Complex views generally cannot be efficiently joined. You can TRY to tune this SQL, but you will almost certainly FAIL.
What you need to do is to write it from first-principals using the base-tables. And I don't just mean copying the SQL of the views into your statement - that will give the same result. You have to analyse this one from scratch and get rid of redundant functionality.
Build it up table by table, tuning as you go. When you strike a problem, you will then know what caused it.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
