Home » RDBMS Server » Performance Tuning » can any one help to tune the query (solaris 10 oracle 10.2.0.3)
can any one help to tune the query [message #320059] Tue, 13 May 2008 23:36 Go to next message
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
Re: can any one help to tune the query [message #320163 is a reply to message #320059] Wed, 14 May 2008 03:46 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: What the statistics in Explain plan suggests? (merged)
Next Topic: Need info regarding DATABASE PARTITIONING
Goto Forum:
  


Current Time: Wed Dec 07 16:50:17 CST 2016

Total time taken to generate the page: 0.10577 seconds