Performance Issue on small table

[bcbcheng]

I am new to Oracle, and have no knowledge on it. <br>I have a small system, in which only 2 tables get over<br>10000 records. However, when I writing SQL to retrieve <br>data ( join from 4 or 5 table ). It takes around 15 <br>minutes to retrieve the date ( around 4000 records will <br>be selected ). Can anyone tell me what should I do ?<br><br>P.S. I have try to look at the plan table, and saw that <br>amost all table using index scan, and only one table use <br>full scan. The statistics of the full scan table is ( <br>cost=60, cardinalty=24 ... ) Is it the problem of the <br>full scan table <br><br>

 

[wadewells]

In my experience, the Oracle optimizer does poorly with joins of more than 4 tables.<br>(1) Learn what all the lines in the Explain Plan mean. Use the Oracle8 Server Tuning Guide.<br>(2) Especially look for 'CARTESIAN' - a red flag. It can be caused by:<br>&nbsp;&nbsp;(2a) a poor ordering of joins.<br>&nbsp;&nbsp;(2b) not enough 'joining' criteria. If joining 5 tables, there should be at least 4 criteria that match keys across tables, like 'WHERE A.key = B.key', and each table should be included in it.<br>(3) check what kind of join is done - Nested Loops, Sort Merge, or Hash.<br>If Nested Loops, try increasing sort memory before the SQL is executed with:<br>&nbsp;&nbsp;ALTER SESSION SET SORT_AREA_SIZE = 4000000;<br>The default from Oracle is only 64k.More sort and has memory can speed up the SQL amazingly.<br>(4) Understand the problem and its details. Until you know whether there are 4 tables or 5, you're not likely to succed in tuning the statement.

 

[MikeLacey]

It will be the full table scan, always is... &lt;smile&gt; <p>Mike<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Please don't send me email questions without posting them in Tek-Tips as well. Better yet -- Post the question in Tek-Tips and send me a note saying "Have a look at so-and-so in the thingy forum would you?"

 

[bcbcheng]

Thanks a lot