Aamer,
i have seen this problem also.
BEFORE changing from nothing being analyzed and then analyzing everything is
a mistake
i have seen many administrators make. i am glad to see that you deleted the
statistics and
i hope that overall performance went up. this change to your system is
unfortunately one that
must be approached with caution and a systematic approach where you need to
explain
all sql BEFORE analyzing and also AFTER and then compare what is better.
you will most
often find out that you will have to change some sql but also have a mixture
of analyzed tables
and un-analyzed tables. i would also just like to point out that a high
buffer hit ratio and low sorts
is obviously what we want , but the ultimate goal is response time which
will correlate to low i/o rates.
as an extreme example suppose we have a query SELECT name FROM employee
WHERE id = :b1;
if this is the only sql in our system with no index and :b1 changes
depending on the person we are
looking up in the table, we will do a full table scan with most data being
in the buffer after the first
execution and we will have buffer hit ratio near 100%, but our response is
slow. by contrast if we
put in index on this table data will fill the buffer slowly and will not be
getting anywhere close to 100%
hit ratio until quite a few executions of this sql BUT our response will be
extremely fast because I/O
will have been reduced.
you probably know all this and your e-mail suggests you do, but i wanted to
post for more junior
administrators that need a push in the right direction to overcoming the
cost/rule problem.
good day,
james koopmann
jkoopmann_at_servman.com
www.servman.com
Aamer <aamer_Janjua_at_hotmail.com> wrote in message
news:37E89431.5BA7DBBB_at_hotmail.com...
> 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
>
>
>
Received on Wed Sep 22 1999 - 09:23:26 CDT
