Query Tuning/Rewrite

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