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

Just remember when you run the analyse on all the tables, they need to be populated with a similar quantity of data as is to be used when the job is running. If any one of the tables in a sql query has not been optimised, the cost method will not be used. Also temp/perm tables - i.e. tables that exist but are populated as part of the job and cleared after it completes will not have accurate stats generated by analyse since the stats are based on no rows in these tables. The best you can do here is run analyse on these tables while the job has populated them.

Leigh.

Big Al <db-guru_at_att.net> wrote in article <3936CF6A.30F2EF8C_at_att.net>...
> 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 08 2000 - 00:00:00 CDT