Home » RDBMS Server » Performance Tuning » SQL Tuning
SQL Tuning [message #243994] Mon, 11 June 2007 04:11 Go to next message
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 #244094 is a reply to message #243994] Mon, 11 June 2007 10:39 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
can you post indexes and the columns names on which indexes are created.
Re: SQL Tuning [message #244161 is a reply to message #243994] Mon, 11 June 2007 15:16 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #244337 is a reply to message #243994] Tue, 12 June 2007 06:50 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
And how many rows are in FEM_PARTIES table?

Michael
Re: SQL Tuning [message #244458 is a reply to message #243994] Wed, 13 June 2007 00:30 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

FEM_PARTIES has 11203513 records.

OFSA_LEAF_DESC has only 5000+ rcords.

Brayan.
Re: SQL Tuning [message #244503 is a reply to message #243994] Wed, 13 June 2007 02:24 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
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 Go to previous message
michael_bialik
Messages: 611
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
Previous Topic: quick tuning
Next Topic: query tuning
Goto Forum:
  


Current Time: Sat Dec 03 12:14:17 CST 2016

Total time taken to generate the page: 0.07600 seconds