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

The analyze table command should cause you to use Cost Based Optimazation. This can be a good thing and this can be a bad thing. Your stats for utlbstat look pretty good. What you really need to do is run Explain Plans on the SQL that is running for the posting job. Check to see if you are using your indexes or is it doing full table scans. You can re-analyze the tables and delete the statistics so you can run them under Rule based also. It may be that 25 mins. may be the best you can expect. From what I have seen, the Cost Based Optimazation is good when your SQL is not written very well, but if you have good SQL the Rule based optimazation is better.

John Jones
Senior Oracle DBA
Duke University OIT
john.jones_at_duke.edu
Raymond <kudoshizuka_at_hotmail.com> wrote in message news:9qpcjsk3cgcbntvm7dbvv5hj7ffcmaqaas_at_4ax.com...
>
> 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