Query Tuning/Rewrite
- From: dfd dfdd <dbaprimatics@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 20 Aug 2010 11:48:58 -0400
Hi All,
Hope all you folks are doing great debugging new stuff.
I am little bit weak in sql writing and looking to tune a query.
select Outinthistory.Clientinstrumentid,Nvl((Select
Sum(Nvl(Int.Totalinterest,0))
From Pncuatcsmolap.Instrumenthistory Ih, Pncuatcsmolap.Interestfact Int
where Ih.Versionid = Int.Inventoryversionid
And Ih.Clientinstrumentid = Outinthistory.Clientinstrumentid
And Ih.Lastupdatedpostingdate = '31-JUL-10'),0) +
Nvl((Select Sum(Nvl(Fee.Totalunam,0))
From Pncuatcsmolap.Instrumenthistory Ih, Pncuatcsmolap.Loanfeefact Fee
where Ih.Versionid = Fee.Inventoryversionid
And Ih.Clientinstrumentid = Outinthistory.Clientinstrumentid
And Ih.Lastupdatedpostingdate ='31-JUL-10' ),0) +
Nvl((Select Sum(Nvl(Upb.Totalupb,0))
From Pncuatcsmolap.Instrumenthistory Ih, Pncuatcsmolap.Loanupbfact Upb
Where Ih.Versionid = upb.Inventoryversionid
And Ih.Clientinstrumentid = Outinthistory.Clientinstrumentid
And Ih.Lastupdatedpostingdate = '31-JUL-10'),0) As "RI"
From Pncuatcsmolap.Instrumenthistory Outinthistory
Where Outinthistory.Lastupdatedpostingdate='31-JUL-10'
group by Outinthistory.Clientinstrumentid;
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 121K| 4156K| | 7477 (1)| 00:01:30 |
| 1 | SORT AGGREGATE |
| 1 | 60 | | | |
|* 2 | HASH JOIN |
| 14 | 840 | | 7012 (2)| 00:01:25 |
|* 3 | TABLE ACCESS BY INDEX ROWID | INSTRUMENTHISTORY
| 1 | 45 | | 7 (0)| 00:00:01 |
|* 4 | INDEX SKIP SCAN | IND_INSTRUMENT_HISTORY_INSTID
| 4 | | | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | INTERESTFACT
| 1759K| 25M| | 6997 (2)| 00:01:24 |
| 6 | SORT AGGREGATE |
| 1 | 58 | | | |
|* 7 | HASH JOIN |
| 1 | 58 | | 902 (2)| 00:00:11 |
|* 8 | TABLE ACCESS BY INDEX ROWID | INSTRUMENTHISTORY
| 1 | 45 | | 7 (0)| 00:00:01 |
|* 9 | INDEX SKIP SCAN | IND_INSTRUMENT_HISTORY_INSTID
| 4 | | | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | LOANFEEFACT
| 238K| 3025K| | 893 (2)| 00:00:11 |
| 11 | SORT AGGREGATE |
| 1 | 61 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | LOANUPBFACT
| 2 | 32 | | 4 (0)| 00:00:01 |
| 13 | NESTED LOOPS |
| 2 | 122 | | 11 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| INSTRUMENTHISTORY
| 1 | 45 | | 7 (0)| 00:00:01 |
|* 15 | INDEX SKIP SCAN | IND_INSTRUMENT_HISTORY_INSTID
| 4 | | | 3 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | IND_UPBFACT_5
| 2 | | | 2 (0)| 00:00:01 |
| 17 | HASH GROUP BY |
| 121K| 4156K| 12M| 7477 (1)| 00:01:30 |
|* 18 | TABLE ACCESS FULL | INSTRUMENTHISTORY
| 179K| 6149K| | 6153 (1)| 00:01:14 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("IH"."VERSIONID"="INT"."INVENTORYVERSIONID")
3 - filter("IH"."LASTUPDATEDPOSTINGDATE"='31-JUL-10')
4 - access("IH"."CLIENTINSTRUMENTID"=:B1)
filter("IH"."CLIENTINSTRUMENTID"=:B1)
7 - access("IH"."VERSIONID"="FEE"."INVENTORYVERSIONID")
8 - filter("IH"."LASTUPDATEDPOSTINGDATE"='31-JUL-10')
9 - access("IH"."CLIENTINSTRUMENTID"=:B1)
filter("IH"."CLIENTINSTRUMENTID"=:B1)
14 - filter("IH"."LASTUPDATEDPOSTINGDATE"='31-JUL-10')
15 - access("IH"."CLIENTINSTRUMENTID"=:B1)
filter("IH"."CLIENTINSTRUMENTID"=:B1)
16 - access("IH"."VERSIONID"="UPB"."INVENTORYVERSIONID")
18 - filter("OUTINTHISTORY"."LASTUPDATEDPOSTINGDATE"='31-JUL-10')
how can i rewrite this query to perform better.
Facts: all the table are fact table with min of 3 million rows and i dont
have parallel option.
i am running stats dails with gather auto option which creates histograms
for some colums
Let me know if you need any more information.
Thanks,
Vamshi .D
Other related posts:
- » Query Tuning/Rewrite - dfd dfdd