RE: why rule based optimizer performs better than cost based?
- From: "Goulet, Dick" <DGoulet@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 28 Jun 2004 09:22:59 -0400
Dan's recommendations, from his presentation are:
Optimizer_index_caching=3D90
Optimizer_index_cost_adj=3D10 for OLTP systems
Optimizer_index_cost_adj=3D50 for DSS
Now I don't have a database around here that I'd call either an OLTP or =
DSS system. Their mostly hybrids of the above, so for me
Optimizer_index_caching=3D90
Optimizer_index_cost_adj=3D40
Seems to work the best.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Gudmundur Josepsson [mailto:gbj@xxxxxxxx]
Sent: Friday, June 25, 2004 7:33 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: why rule based optimizer performs better than cost based?
Dick,
Does Dan recommend these values for every OLTP and DSS environments or =
are
these good values to start working from? Do you know if he's published
anything on this subject that's downloadable from somewhere?
Thanks,
Gudmundur
> Mei,
>
> 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 =3D
> that a) you will never find an index in the buffer cache and b) =
scanning =3D
> an index is just as expensive as a table. If any of you are familiar =
=3D
> with Dan Hotka, he recently presented his "Index Advance Tuning" =
seminar =3D
> at the NOUG DBA SIG here in Boston. His recommendation is to set =
these =3D
> to 90 and 30(OLTP) or 50(DSS) respectively. I've tried it, it helps =
and =3D
> does not appear to have any unpleasant side effects.
>
> 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
