Re: Analyze Table Compute Statistics but slowed down performance ??

<small rant>

IMHO Cost based optimizer was (is) a mistake... Well at least having it be
so darned persistent... Rule based doesnt change over time, and you can tune
your SQL once and for all...

My rule is NEVER analyze tables :)

</small rant>

Randy

Raymond wrote:

> Please help investigate my problem :-
>
> Oracle 7.3.4 running on SCO UNIX with 1GB memory and a lot of free
> disk space.
>
> Aims at improving performance, I have done the following:-
> (1) drop the database and recreate and import back the data (aims at
> defragmentation)
> (2) increased the db_block_buffer
> (3) collect statistics from utlbstat & utlestat and found that
> db_block_buffer hit ratio = 95%
> librarycache hit ratio = 99.7%
> percent memory sorts = 99.88%
> (4) run the posting job and it takes 25mins to complete.
>
> After one week, I have
> (5) performed the command "Analyze table ... compute statistics;" for
> all the tables in the database. The optimizer_mode is CHOOSE.
>
> However, the same posting job as point (4) ran for almost 6 hours to
> complete! It caused the system slowed down dramatically.
>
> I have checked the hit ratio of db buffer and library cache and
> percent memory sorts are almost the same as point (3) above.
>
> Please tell me why it happens and how to trace the problem. Thanks a
> lot!
>
> Phoebe
Received on Thu Jun 01 2000 - 00:00:00 CDT