Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Optimiser inconsistency

RE: Optimiser inconsistency

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Wed, 11 Oct 2000 11:25:51 +1000
Message-Id: <10645.118917@fatcity.com>


Hi Grant,

It looks like your 'optimizer_index_caching' parameter is defaulting to zero, whereas the performance of the second plan would in fact be worse unless you were able to cache at least 91% of the IGM_I_SEC index partitions. In fact I suspect that you are able to do much more caching than that.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
From: grant.g.holyoake_at_centrelink.gov.au [mailto:grant.g.holyoake_at_centrelink.gov.au] Sent: Wednesday, 11 October 2000 9:35
To: Multiple recipients of list ORACLE-L Subject: Optimiser inconsistency

Greetings,

we have only recently installed Oracle 8i within our Organisation and have experienced some rather strange behavioural patterns associated with the optimiser's interpretation.

Attached is a printout which demonstrates Oracle's decision to go for a full hash join of two tables A and B even when table A is very small and table B is indexed on the join key and is very large.

I have read a lot of relevant documentation (esp. Oracle 8i Designing & Tuning for Performance, The Optimiser, Chapter 4) and altered all session variables mentioned therein ... with no result ... ie. I do not believe the behaviour is caused by our setup.

I would appreciate your thoughts.

cheers,
Grant Received on Tue Oct 10 2000 - 20:25:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US