OraFAQ Forum: Performance Tuning » Optimize large SQL query with multiple joins

Home » RDBMS Server » Performance Tuning » Optimize large SQL query with multiple joins

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Optimize large SQL query with multiple joins [message #287485]

Wed, 12 December 2007 06:28

ramya.pathak
Messages: 15
Registered: August 2007

Junior Member

Hi all,

Am using a big SQL query in order to retrieve my data in a procedure.
The query is like:

SELECT A.COL1,.....,K.COL100
FROM A,B,C,D,E,F,G,H,I,J,K
WHERE A.KEY = B.KEY (+)
AND A.KEY = C.KEY (+)
AND A.KEY = D.KEY (+)
AND A.KEY = E.KEY (+)
AND A.KEY = F.KEY (+)
AND A.KEY = G.KEY (+)
AND A.KEY = H.KEY (+)
AND A.KEY = I.KEY (+)
AND A.KEY = J.KEY (+)
AND A.KEY = K.KEY (+)

'A' is the main table. Parent table of all.
Table 'A' contains around 150,000 records.
The query is taking too long to run.
Any way I can optimize the query..??

Report message to a moderator

Re: Optimize large SQL query with multiple joins [message #287493 is a reply to message #287485]

Wed, 12 December 2007 06:38

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

1. Do have any index on joining tables ?
2. can you post here execution plan , autotrace output.

Report message to a moderator

Re: Optimize large SQL query with multiple joins [message #287511 is a reply to message #287485]

Wed, 12 December 2007 07:52

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

If (as I'm suspecting) you have 1:M relationship between A and all other tables, then for each line of A your "resultset"
will contain

M(B) * M(C) * M(D) * M(E) * M(F) * M(G) * M(H) * M(I) * M(J) * M(K)

rows.

Michael

Report message to a moderator

Re: Optimize large SQL query with multiple joins [message #287513 is a reply to message #287493]

Wed, 12 December 2007 08:08

ramya.pathak
Messages: 15
Registered: August 2007

Junior Member

Mohammad,

Yes the indexes are there. The execution plan is attached.

Micheal,
The relationship is mostly 1:1 except for a few places but some columns from the child tables are required.

[Updated on: Thu, 13 December 2007 00:46]

Report message to a moderator

Re: Optimize large SQL query with multiple joins [message #287516 is a reply to message #287513]

Wed, 12 December 2007 08:25

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Statistics
----------------------------------------------------------
        154  recursive calls
         26  db block gets
    2486906  consistent gets
      58980  physical reads
          0  redo size
       4842  bytes sent via SQL*Net to client
       1428  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
          9  rows processed

1. According to me your execution plan is correct, but wait someone more exprience/expert member will advice you.

2. query makes lots of consistent gets and also sort in disk.
it seems your buffer cache and pga is small.
can you post here your sga configuration.

Report message to a moderator

Re: Optimize large SQL query with multiple joins [message #287567 is a reply to message #287485]

Wed, 12 December 2007 12:54

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I cannot see your query plan, the file must be missing.

However, if your query is truly like this, you are looking at all rows in all tables subject to the correct joins, then using indexes (and presumably nested loops) would likely be a real bad thing.

I suggest you enable hash joins (may require the setting of multiple parameters so do some research), compute statistics on all the tables involved (dbms_stats... cascade=>true), and then see if the query plan shows full table scans and hash joins. This would be much faster than nested loop via index lookups, especially if there is more than just a few rows in these tables. You should look for improvement somewhere between 2 and 10 times faster depending upon the number of rows being joined.

As always, your mileage may vary.

Good luck, Kevin

Report message to a moderator

Re: Optimize large SQL query with multiple joins [message #287584 is a reply to message #287485]

Wed, 12 December 2007 15:24

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Quote:

except for a few places

may mean a lot of rows...

2. IMHO - Kevin is right. You need to exchange nested loop joins for hash joins.

Michael

Report message to a moderator

Re: Optimize large SQL query with multiple joins [message #287597 is a reply to message #287584]

Wed, 12 December 2007 20:27

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

The plan contains a COUNT STOPKEY line, which means that query contains a ROWNUM clause somewhere. Your original query does not contain a ROWNUM clause, so this tells me the Explain Plan is for a different query.

Post the REAL explain plan.

Ross Leishman

Report message to a moderator

Re: Optimize large SQL query with multiple joins [message #287651 is a reply to message #287597]

Thu, 13 December 2007 01:12

trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam

Senior Member

Consider about

Quote:

Statistics
----------------------------------------------------------
154 recursive calls
.....

Post your TKPROF or Trace(Explain or Only) here. I guess the executions << parse

Report message to a moderator

Previous Topic:	takes more time to open lov
Next Topic:	This query make much recursive call

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 01 12:10:26 CDT 2024