| Need to tune a Query - xjd [message #433674] |
Thu, 03 December 2009 11:30  |
marlon_loyolite
Messages: 66
Registered: July 2006
|
Member |
|
|
Hi All,
Please find the query below which needs to be tuned.
SELECT * FROM TABLE T1,TABLE_TWO T2,(
SELECT I.CREDIT_REQUEST_ID, I.IRREGULARITY_ID, I.DOL_REQUEST_ID
FROM IRREGULARITY I
WHERE I.IRREGULARITY_TYPE_CODE IN ('UNACTIONED_DOL', 'DECLINED_DOL')
UNION ALL
SELECT IH.CREDIT_REQUEST_ID, IH.IRREGULARITY_ID, IH.DOL_REQUEST_ID
FROM IRREGULARITY_HISTORY IH
WHERE IH.IRREGULARITY_TYPE_CODE IN ('UNACTIONED_DOL', 'DECLINED_DOL')
) T3
WHERE T1.COL1 = T2.COL1
AND T2.COL2 = T3.COL2
.....
This query runs more than 30 minutes with that UNION ALL query as an inline view(More than 1 Million records).
The tables involved IRREGULARITY (Contains current month data) and IRREGULARITY_HISTORY(Contains other than
currrent month data).
These two tables has got many indexes which I was not able to utilize since its has been used as inline view.
The requirement is to have both CURRENT MONTH data and HISTORY DATA for some validations.
How do I restructure this or do we have any other alternative.
Please advice me.
Thanks & Regards,
Marlon
|
|
|
|
| Re: Need to tune a Query - xjd [message #433677 is a reply to message #433674] |
Thu, 03 December 2009 11:36  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
You provide us
No Operating System name or version
No Oracle version number (SELECT * from v$version).
No error message (not your interpretation of the actual full and complete message).
No DDL for your table(s)
No DML for test data
No actual SQL to produce your results.
Therefore no assistance for you is possible at this time.
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
[Updated on: Thu, 03 December 2009 11:37] Report message to a moderator |
|
|
|
| Re: Need to tune a Query - xjd [message #433690 is a reply to message #433674] |
Thu, 03 December 2009 12:20 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
marlon_loyolite wrote on Thu, 03 December 2009 18:30
These two tables has got many indexes which I was not able to utilize since its has been used as inline view.
Performance tuning based on (false) assumptions can be a hazardous sport.
|
|
|
|
| Re: Need to tune a Query - xjd [message #433742 is a reply to message #433690] |
Thu, 03 December 2009 23:14 |
marlon_loyolite
Messages: 66
Registered: July 2006
|
Member |
|
|
Sorry for not posting it correctly.
This is the explain plan. Oracle DB version - 10g
-----------------------------------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)| TIME |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 151 | 27 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 151 | 27 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 127 | 22 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 77 | 18 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | HIST_RIL_IRR_DETAIL | 1 | 61 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 13407 | 209K| 16 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | XIE_CREDIT_REQUEST_3 | 13407 | 209K| 16 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | DOL_REQUEST | 1 | 50 | 4 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | XIE_DOL_REQUEST_1 | 3 | | 0 (0)| 00:00:01 |
| 9 | VIEW | | 1 | 24 | 5 (0)| 00:00:01 |
| 10 | UNION ALL PUSHED PREDICATE | | | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID| IRREGULARITY | 1 | 50 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | XIE_IRREGULARITY_10 | 1 | | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| IRREGULARITY_HISTORY | 1 | 56 | 3 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | XPK_IRREGULARITY_HISTORY | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
6 - ACCESS("CR"."STATUS_CODE"='ACTIVE')
7 - FILTER(("DR"."LAST_STATUS_CODE"='UNACTIONED' OR
NVL("DR"."LATEST_ADJ_DECISION_CODE","DR"."ADJUDICATOR_DECISION_CODE")='DECLINED') AND
"HRID"."RIL_DATE"<>"DR"."CREATE_DTTM")
8 - ACCESS("DR"."ICAP_CREDIT_REQUEST_ID"="CR"."CREDIT_REQUEST_ID")
11 - FILTER("I"."IRREGULARITY_ID"="HRID"."IRREGULARITY_ID" AND
"I"."CREDIT_REQUEST_ID"="DR"."ICAP_CREDIT_REQUEST_ID" AND
("I"."IRREGULARITY_TYPE_CODE"='DECLINED_DOL' OR "I"."IRREGULARITY_TYPE_CODE"='UNACTIONED_DOL'))
12 - ACCESS("I"."DOL_REQUEST_ID"="DR"."DOL_REQUEST_ID")
13 - FILTER("IH"."CREDIT_REQUEST_ID"="DR"."ICAP_CREDIT_REQUEST_ID" AND
"IH"."DOL_REQUEST_ID"="DR"."DOL_REQUEST_ID" AND ("IH"."IRREGULARITY_TYPE_CODE"='DECLINED_DOL' OR
"IH"."IRREGULARITY_TYPE_CODE"='UNACTIONED_DOL'))
14 - ACCESS("IH"."IRREGULARITY_ID"="HRID"."IRREGULARITY_ID")
Regards,
Marlon.
|
|
|
|
|
|
| Re: Need to tune a Query - xjd [message #433756 is a reply to message #433742] |
Fri, 04 December 2009 01:57 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
TABLE ACCESS FULL | HIST_RIL_IRR_DETAIL
Did not see the table in your sql.
what is the output for the below sql
select count(*) from HIST_RIL_IRR_DETAIL
wht are the indexes created for this table?
>This query runs more than 30 minutes
Is the query taking 30 minutes when you ran in your env. or it is in PRODUCTION database?
Regards,
Ved
Edit: Typo
[Updated on: Fri, 04 December 2009 02:02] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
