Home » RDBMS Server » Performance Tuning » Need to tune a Query - xjd
Need to tune a Query - xjd [message #433674] Thu, 03 December 2009 11:30 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #433743 is a reply to message #433742] Thu, 03 December 2009 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
Unformatted SQL & unreadable EXPLAIN PLAN.
>This query runs more than 30 minutes
Then why does EXPLAIN PLAN show duration is 1 second (00:00:01)?

Why this discrepancy?
Re: Need to tune a Query - xjd [message #433756 is a reply to message #433742] Fri, 04 December 2009 01:57 Go to previous messageGo to next message
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

Re: Need to tune a Query - xjd [message #434374 is a reply to message #433674] Wed, 09 December 2009 00:53 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
TKPROF?
Previous Topic: Unindexing foreign key
Next Topic: Index Partitioning local or global or both??
Goto Forum:
  


Current Time: Sat Oct 01 00:22:56 CDT 2016

Total time taken to generate the page: 0.09765 seconds