Home » RDBMS Server » Performance Tuning » Query Tuning Problem (tkprof o/p,query & explain plan attached)
Query Tuning Problem (tkprof o/p,query & explain plan attached) [message #268347] Tue, 18 September 2007 05:05 Go to next message
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 #268369 is a reply to message #268347] Tue, 18 September 2007 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Query Tuning Problem (tkprof o/p,query & explain plan attached) [message #268413 is a reply to message #268347] Tue, 18 September 2007 08:35 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Post table and indexes definitions as well.
2. It doesn't matter that you select only 1 row: the in-line select must select all data and sort it).
3. Look at:
7190918 HASH JOIN (cr=16189 r=0 w=0 time=11142559 us)

That step return over 7M rows and all these rows must be sorted...
Are you missing a join condition?

Michael

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 Go to previous messageGo to next message
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 
Re: Query Tuning Problem (tkprof o/p,query & explain plan attached) [message #268547 is a reply to message #268543] Wed, 19 September 2007 01:22 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: Help - Creating Stored Outlines
Next Topic: Heavy query with GL
Goto Forum:
  


Current Time: Sat Dec 03 22:14:24 CST 2016

Total time taken to generate the page: 0.12372 seconds