Re: Cost versus Rule based Optimizer

Hi.

 From my experience:

1. CBO favors tablescans ( probably its geared to get you a best throughput instead of best response time ).
2. Don't expect CBO 7.3.2 to do the work for you - it is not that smart.
3. CBO has a "builtin" problem of dealing with bind variables ( compared with constants ). If your application has a lot of statements with bind variables - it may be a reason for performance hit you experienced.

 Anyway - in your place I would try to do some  application performance tuning before switching from  RBO to CBO.

 Is it possible for you to post following outputs :

1. Select name, value from v$parameters order by 1;
2. Select name, value from v$sysstat order by 1;
3. Select * from v$system_event order by 4 desc;

 Good luck. Michael.
In article <37E89431.5BA7DBBB_at_hotmail.com>,   Aamer <aamer_Janjua_at_hotmail.com> wrote:
> Hi All
>
> To give a background to the problem. We have a live system
which
> has running very slow and required some serious performance tuning.
Its
> running on AIX box with two processors and 1 GB of RAM. Its oracle
> 7.3.2. Using in house developed application.
>
> On checking the database it was found that the tables and schemas
where
> not analysed. So in fact it was using rule based optimisation. We
> immediately analysed all schemas .
> After analysing The buffer hit ratio which was 95% dropped to
84%
> and the sort in disk increased from 0.5% to 15%.
>
> Once we deleted the analysing information it improved the buffer hit
> ratio to 94% and sort on disk to 6% .
>
> We have not changed any parameter for buffer size or sort area
size.
>
> Its not understood why when we analysed the buffer hit ration
> dropped.
>
> Do we require to increase the size of buffer as Oracle caches the
> analysed statistics or some thing wrong with our application.
>
> Any comments or suggestions will be highly appreciated. Please if
> you could email them to
>
> Aamer_janjua_at_hotmail.com
>
> Thanks
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 22 1999 - 15:58:36 CDT