| Query Tuning Problem (tkprof o/p,query & explain plan attached) [message #268347] |
Tue, 18 September 2007 05:05  |
dksampat
Messages: 12
Registered: December 2006
|
Junior Member |
|
|
The following Query will help us to find the latest date (based on a set of conditions). It will return only 1 row and the base table
volume is not that big. But still this query takes arnd 30 secs to execute which is very HIGH . Kindly please help to check the execution
plans and tkprof output and provide me some suggestions for tuning the same.
Note: From the TKPROF Output, I noticed the following : To retrieve one ROW, this SQL is doing 16189 consistent gets (i.e Query for
Fetch Operation is 16189). Is this the real culprit ?
No of Rows in Table A : 35000
No of Rows in Table B : 7000
No of Rows in Table C : 22000
No of Rows in Table D : 1000
Original Query:
***************
SELECT app_date, CNT
FROM (
SELECT kmd.app_date, COUNT(kmd.countrycode) cnt
FROM A kmd, B ruca, C krvd, D krpgv
WHERE ruca.user_id = 'DKSAMPAT'
AND kmd.app_id = 'ID'
AND KRPGV.app_PROD_GROUP_CODE= 0
AND krvd.app_view_code = KRPGV.app_view_code
AND krvd.countrycode = ruca.countrycode
AND kmd.countrycode = krvd.countrycode
GROUP BY kmd.app_date
ORDER BY cnt DESC, kmd.app_date DESC)
WHERE ROWNUM < 2;
Output:
*******
30-JUN-07 15548
Elapsed: 00:00:27.64
Execution Plan:
***************
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1039 Card=1 Bytes=10186)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=1039 Card=463 Bytes=10186)
3 2 SORT (ORDER BY STOPKEY) (Cost=1039 Card=463 Bytes=16205)
4 3 SORT (GROUP BY) (Cost=1039 Card=463 Bytes=16205)
5 4 HASH JOIN (Cost=17 Card=319417 Bytes=11179595)
6 5 NESTED LOOPS (Cost=10 Card=727 Bytes=14540)
7 6 HASH JOIN (Cost=10 Card=4820 Bytes=48200)
8 7 TABLE ACCESS (FULL) OF 'D' (Cost=5 Card=41 Bytes=205)
9 7 INDEX (FAST FULL SCAN) OF 'IDX3_C' (NON-UNIQUE) (Cost=4 Card=20181 Bytes=100905)
10 6 INDEX (UNIQUE SCAN) OF 'PK_B' (UNIQUE)
11 5 INDEX (FAST FULL SCAN) OF 'IDX1_A' (NON-UNIQUE) (Cost=6 Card=17575 Bytes=263625)
Statistics:
***********
5 recursive calls
0 db block gets
16194 consistent gets
0 physical reads
0 redo size
440 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
TKPROF Output
*************
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 26.04 29.13 0 16189 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 26.05 29.14 0 16189 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 30
Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=16189 r=0 w=0 time=29136520 us)
1 VIEW (cr=16189 r=0 w=0 time=29136483 us)
1 SORT ORDER BY STOPKEY (cr=16189 r=0 w=0 time=29136478 us)
463 SORT GROUP BY (cr=16189 r=0 w=0 time=29135464 us)
7190918 HASH JOIN (cr=16189 r=0 w=0 time=11142559 us)
15924 NESTED LOOPS (cr=16034 r=0 w=0 time=210193 us)
15924 HASH JOIN (cr=108 r=0 w=0 time=91674 us)
204 TABLE ACCESS FULL D (cr=21 r=0 w=0 time=693 us)
20181 INDEX FAST FULL SCAN IDX3_C (cr=87 r=0 w=0 time=23383 us)(object id 124101)
15924 INDEX UNIQUE SCAN PK_B (cr=15926 r=0 w=0 time=74161 us)(object id 87781)
17575 INDEX FAST FULL SCAN IDX1_A (cr=155 r=0 w=0 time=35914 us)(object id 28206)
|
|
|
|
|
|
|
|
| Re: Query Tuning Problem (tkprof o/p,query & explain plan attached) [message #268543 is a reply to message #268347] |
Wed, 19 September 2007 01:10  |
dksampat
Messages: 12
Registered: December 2006
|
Junior Member |
|
|
Thank you very much Michael for your valuable suggestions. As per your suggestion, I focussed on "7190918 HASH JOIN (cr=16189 r=0 w=0 time=11142559 us)" and fine tuned my query.
Now the query is executing in milliseconds. Thank you once again for your valuable eforts. I am posting the new query so that it will be helpful for other OraFAQ users:
with
kmd as
( select kmd.app_date
, kmd.countrycode
, count(kmd.countrycode) cnt
, sum(rownum) dummy_to_prevent_view_merging
from A1 kmd
where kmd.app_id = '1KIM01'
group by kmd.app_date
, kmd.countrycode
) ,
cd as
(
select a.countrycode
from A a, B b, C c
where
a.user_id = 'RS00425'
and a.countrycode = b.countrycode
and c.app_prod_group_code = 0
and c.app_view_code = b.app_view_code
group by a.countrycode
)
select max(app_date) keep (dense_rank last order by sum(cnt)) app_date
, max(sum(cnt))
from kmd
, cd
where kmd.countrycode = cd.countrycode
group by kmd.app_date
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
