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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
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
|
|
|
Goto Forum:
Current Time: Sun Aug 18 21:08:36 CDT 2024
|