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

In my opinion the CBO in Oracle7 should not be used. You should change to RULE from CHOOSE for optimizer_mode. The CBO is usable starting with Oracle 8.0.4 depending on your application.

Al

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