Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Optimiser inconsistency
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