| SQL Tuning [message #243994] |
Mon, 11 June 2007 04:11  |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi,
Can anybody tell, how we can tune this query. It takes around 60 Mins to execute the query.
Ver - 9.2.0.7
SELECT MONTHS_BETWEEN(BOI_ALM_REPORTS_MV.AS_OF_DATE,BOI_ALM_REPORTS_MV.ACCOUNT_OPEN_DATE),
MONTHS_BETWEEN(BOI_ALM_REPORTS_MV.AS_OF_DATE,BOI_ALM_REPORTS_MV.ACCOUNT_OPEN_DATE)/12,
BOI_ALM_REPORTS_MV.TABLE_NAME, BOI_ALM_REPORTS_MV.CIF_KEY, BOI_ALM_REPORTS_MV.AS_OF_DATE,
OFSA_LEAF_DESC.DESCRIPTION, FEM_PARTIES.FULL_NAME
FROM BOIDW.BOI_ALM_REPORTS_MV BOI_ALM_REPORTS_MV,
BOIDW.OFSA_LEAF_DESC OFSA_LEAF_DESC,
BOIDW.FEM_PARTIES FEM_PARTIES
WHERE ( OFSA_LEAF_DESC.LEAF_NODE = BOI_ALM_REPORTS_MV.ORG_UNIT_ID )
AND ( FEM_PARTIES.CIF_KEY(+) = BOI_ALM_REPORTS_MV.CIF_KEY )
AND ( MONTHS_BETWEEN(BOI_ALM_REPORTS_MV.AS_OF_DATE,BOI_ALM_REPORTS_MV.ACCOUNT_OPEN_DATE) > 20 )
AND ( OFSA_LEAF_DESC.LEAF_NUM_ID = 1)
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3509K| 344M| | 1052K|
| 1 | HASH JOIN | | 3509K| 344M| | 1052K|
| 2 | TABLE ACCESS FULL | OFSA_LEAF_DESC | 809 | 22652 | | 6 |
| 3 | MERGE JOIN OUTER | | 4294K| 307M| | 1031K|
| 4 | SORT JOIN | | 4294K| 172M| 462M| 489K|
| 5 | TABLE ACCESS FULL| BOI_ALM_REPORTS_MV | 4294K| 172M| | 294K|
| 6 | SORT JOIN | | 11M| 352M| 946M| 542K|
| 7 | TABLE ACCESS FULL| FEM_PARTIES | 11M| 352M| | 123K|
------------------------------------------------------------------------------------
All the tables are analyzed, BOI_ALM_REPORTS_MV contains 85M records.
Brayan.
|
|
|
|
|
|
| Re: SQL Tuning [message #244161 is a reply to message #243994] |
Mon, 11 June 2007 15:16  |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. How many rows are in BOI_ALM_REPORTS_MV table?
2. How many rows have:
MONTHS_BETWEEN(BOI_ALM_REPORTS_MV.AS_OF_DATE,BOI_ALM_REPORTS_MV.ACCOUNT_OPEN_DATE) > 20
If only a small percentage correspond to that condition - consider creating a function based index on it:
CREATE INDEX ... ON BOI_ALM_REPORTS_MV (MONTHS_BETWEEN(BOI_ALM_REPORTS_MV.AS_OF_DATE,BOI_ALM_REPORTS_MV.ACCOUNT_OPEN_DATE))...
Consider increasing HASH_AREA_SIZE (either in INIT.ORA or using ALTER SESSION or using hint) to enforce HASH join instead of SORT_MERGE.
HTH.
Michael
|
|
|
|
| Re: SQL Tuning [message #244234 is a reply to message #244161] |
Tue, 12 June 2007 02:01 |
amol_umbarkar
Messages: 3
Registered: June 2007
|
Junior Member |
|
|
If the percentage of rows coming out of MONTHS_BETWEEN condition is too high, you can try creating a temporary table using PARALLEL clause. Drop the table after use.
I am not sure about your environment but this should work well with more than 8 CPUs and during off peak hours.
|
|
|
|
| Re: SQL Tuning [message #244334 is a reply to message #243994] |
Tue, 12 June 2007 06:41 |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
DreamzZ,
The indexes on
BOI_ALM_REPORTS_MV
-------------------
AS_OF_DATE+ACCOUNT_OPEN_DATE
FEM_PARTIES
-----------
CIF_KEY
Michael,
Total records on BOI_ALM_REPORTS_MV : 85million
With condition
MONTHS_BETWEEN(BOI_ALM_REPORTS_MV.AS_OF_DATE,BOI_ALM_REPORTS_MV.ACCOUNT_OPEN_DATE) > 20
there are 62Million records.
Brayan.
|
|
|
|
|
|
|
|
| Re: SQL Tuning [message #244503 is a reply to message #243994] |
Wed, 13 June 2007 02:24 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi, Brayan.
I have another question/suggestion:
Is it possible to use regular join instead of outer join in your statement?
If it's possible (check if FK exists - if yes - then you can use regular join) then you have to try enforcing HASH join and to increase hash_area_size value.
IMHO - Oracle will hash FEM_PARTIES table into memory and probe the BOI_ALM_REPORTS_MV for it.
You are supposed to get following explain:
SELECT STATEMENT
HASH JOIN
TABLE ACCESS FULL OFSA_LEAF_DESC
HASH JOIN
TABLE ACCESS FULL FEM_PARTIES
TABLE ACCESS FULL BOI_ALM_REPORTS_MV
If outer join must be used then (the only solution in my opinion) is to increase sort_area_size and sort_area_retained size parameters.
Do you have other queries with WHERE clauses like:
WHERE AS_OF_DATE = :p1 AND ACCOUNT_OPEN_DATE = :p2 ...
WHERE AS_OF_DATE >= :p1 AND ACCOUNT_OPEN_DATE = :p2 ...
or something else?
If not - then I don't see the need for index on these 2 columns.
HTH.
Michael
|
|
|
|
| Re: SQL Tuning [message #244554 is a reply to message #243994] |
Wed, 13 June 2007 05:35 |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi,
I have to use Outer join.
| Quote: | Do you have other queries with WHERE clauses like:
WHERE AS_OF_DATE = :p1 AND ACCOUNT_OPEN_DATE = :p2 ...
WHERE AS_OF_DATE >= :p1 AND ACCOUNT_OPEN_DATE = :p2 ...
or something else?
|
I do not have conditions on the above columns.
I'm using PGA_AGGREGATE_TARGET parameter.
Brayan.
[Updated on: Wed, 13 June 2007 07:10] Report message to a moderator |
|
|
|
| Re: SQL Tuning [message #244693 is a reply to message #243994] |
Wed, 13 June 2007 15:29 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Can you try:
ALTER SESSION SET workarea_policy = maual;
ALTER SESSION SET sort_area_size = 200M; -- You have to supply the number
ALTER SESSION SET sort_area_retained_size = 100M;
<Your query>
I tried to force HASH OUTER join:
SELECT STATEMENT
HASH JOIN
TABLE ACCESS FULL OFSA_LEAF_DESC
HASH JOIN OUTER
TABLE ACCESS FULL FEM_PARTIES
TABLE ACCESS FULL BOI_ALM_REPORTS_MV
but couldn't do it with 9i (it worked with 10g).
May be you will succeed. I think it will give you the best possible performance.
| Quote: | I do not have conditions on the above columns.
|
In that case I don't think you need the index at all.
HTH.
Michael
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
