OraFAQ Forum: Performance Tuning » can any one help to tune the query

Home » RDBMS Server » Performance Tuning » can any one help to tune the query (solaris 10 oracle 10.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

can any one help to tune the query [message #320059]

Tue, 13 May 2008 23:36

skumar.dba
Messages: 82
Registered: October 2007

Member

Experts,

How are you doing. i am facing one problem with my query. i found that it is taking full table scans twice. can any one help me fine tune this query. could you please suggest necessary modifications.

please find the query along with explain plan

select  b.type||b.method as "TypeMethod", count(distinct b.SID) as "Revenue Event User SIDs" 
from (select min(date_created) as date_created, sid from cdsuscc.txn partition(txn_200711) group by sid) a,
cdsuscc.txn partition(txn_200711)b  
where 
b.ads_name=704
and 
b.date_created=a.date_created 
and
b.sid=a.sid
and
b.platformid <> 500
  and b.type not in ( 'DA' , 'TR' )
  and b.method in ( '1' , '2' , '3' , '4' , '5' )
  and b.pc_partno NOT IN
 ('QC00082438',
'QC00082440',
'QC00082452',
'QC00082515',
'QC00083121',
'QC00083122',
'QC00083717',
'QC00083262',
'QC00085877',
'QC00087517',
'QC00088498',
'QC00091865',
'QC00092030',
'QC00092200',
'QC00093643',
'QC00098011',
'QC00098225',
'QC00105060',
'QC00106605',
'QC00107852',
'QC00113674',
'QC00115392',
'QC00117347', 
'QC00117348',
'QC00117448',
'QC00118133',
'QC00118959',
'QC00119424',
'QC00119508',
'QC00120540',
'QC00128711',
'QC00132856',
'MFR54240',
'MFR54241',
'MFR56801',
'MFR56820',
'MFR56822',
'MFR57180',
'MFR50420',
'MFR50680',
'MFR52280',
'MFR57420',
'MFR58360',
'MFR60020',
'MFR60884',
'MFR61540',
'QC00131369', 
'QC00108447',
'MFR51720',
'QC00108472')
group by type||method

here is explain plan


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1559959371

-------------------------------------------------------------------------------
---------------------

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time
    | Pstart| Pstop |

-------------------------------------------------------------------------------
---------------------

|   0 | SELECT STATEMENT            |      |     1 |   367 | 89366   (3)| 00:17
:53 |       |       |

|   1 |  SORT GROUP BY              |      |     1 |   367 | 89366   (3)| 00:17
:53 |       |       |

|   2 |   VIEW                      |      |     1 |   367 | 89365   (3)| 00:17
:53 |       |       |

|*  3 |    FILTER                   |      |       |       |            |
    |       |       |

|   4 |     HASH GROUP BY           |      |     1 |    96 | 89365   (3)| 00:17
:53 |       |       |

|*  5 |      HASH JOIN              |      | 31493 |  2952K| 89361   (3)| 00:17
:53 |       |       |

|   6 |       PARTITION RANGE SINGLE|      |  7897 |   532K| 44718   (3)| 00:08
:57 |    23 |    23 |

|*  7 |        TABLE ACCESS FULL    | TXN  |  7897 |   532K| 44718   (3)| 00:08
:57 |    23 |    23 |

|   8 |       PARTITION RANGE SINGLE|      |  3174K|    81M| 44601   (2)| 00:08
:56 |    23 |    23 |

|   9 |        TABLE ACCESS FULL    | TXN  |  3174K|    81M| 44601   (2)| 00:08
:56 |    23 |    23 |

-------------------------------------------------------------------------------
---------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$DD8D4BD4
   2 - SEL$3DD9CB74 / $vm_view@SEL$DD8D4BD4
   3 - SEL$3DD9CB74
   7 - SEL$3DD9CB74 / B@SEL$1
   9 - SEL$3DD9CB74 / TXN@SEL$2

Report message to a moderator

Re: can any one help to tune the query [message #320163 is a reply to message #320059]

Wed, 14 May 2008 03:46

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Oracle is performing a full scan of each table (well, one partition of each table).

Two things:

1) You have a number of filter clauses on table alias b. What proportion of that table does it filter out (as a percentage)? If it is only a small proportion, indexing may help, but it's a tricky query to index.

2) You seem to want the latest type/method for each SID. You could try an alternate syntax:

SELECT tm, count(distinct sid)
FROM (
    SELECT sid, max(type||method) KEEP (DENSE_RANK LAST ORDER BY date_created) AS tm
    FROM   cdsuscc.txn partition(txn_200711)
    WHERE .... filter clauses ....
    GROUP BY sid
)
GROUP BY tm

Ross Leishman

Report message to a moderator

Previous Topic:	What the statistics in Explain plan suggests? (merged)
Next Topic:	Need info regarding DATABASE PARTITIONING

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 01:33:43 CST 2024