Re: why rule based optimizer performs better than cost based?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 26 Jun 2004 08:15:16 +0100
Message-ID: <002701c45b4d$56abb700$7102a8c0@Primary>

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

• Original Message -----
  > Take a serious look at two init parameters:
  >
  > Optimizer_index_caching
  > Optimizer_index_cost_adj
  >
  > The default values are 0 and 100 respectively which inform the optimizer =
  > that a) you will never find an index in the buffer cache and b) scanning =
  > an index is just as expensive as a table. If any of you are familiar =
  > with Dan Hotka, he recently presented his "Index Advance Tuning" seminar =
  > at the NOUG DBA SIG here in Boston. His recommendation is to set these =
  > to 90 and 30(OLTP) or 50(DSS) respectively. I've tried it, it helps and =
  > does not appear to have any unpleasant side effects.
  >
  

There is a side-effect to worry about with OICA, especially at low values. see:

    http://www.dbazine.com/jlewis18.shtml

> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat Jun 26 2004 - 02:12:14 CDT