As well as this approach you can also use the DBMS_STATISTICS package to
collect and delete statistics.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Margit Mester" <mesterm_at_westel.hu> wrote in message
news:3B3B4BC1.86F99ED8_at_westel.hu...
> Hi George,
>
> You can say:
> SQL> ANALYZE TABLE table_name DROP STATISTICS;
>
> If You have a lot of tables, than say:
> SQL> set heading off
> SQL> set feedback off
> SQL> set pagesize 0
> SQL> spool analyze.sql
> SQL> SELECT 'ANALYZE TABLE '|| table_name || ' DROP STATISTICS;' FROM
> dba_tables where owner=XY;
> SQL> spool off
> SQL> @analyze.sql
>
>
> Drop statistics: deletes any statistics about the analyzed object that are
> currently stored in the data dictionary. Use
> this statement when you no longer want Oracle to use
the
> statistics.
>
> When you use this clause on a table, Oracle also
> automatically removes statistics for all the table's
> indexes. When you use this clause on a cluster, Oracle
> also automatically removes statistics for all
> the cluster's tables and all their indexes, including
the
> cluster index.
>
> I don't now the reason why it's happend. I think it is just a bug :-).
>
> Margit Mester
>
> George Barbour wrote:
>
> > Hi all,
> > Oracle 8.1.5.
> > Sun Solaris Unix 2.8.
> > The situation, I have many Oracle Discoverer queries against the
database.
> > Some queries run as RULE and some ran as COST.
> > I analyzed the objects, mistake! Now all of the queries run as COST. The
> > difference in run time is spectacular.
> > Rule based = 17.9 secs.
> > Cost based = 388 mins.
> >
> > 1) How to I un-analyze objects, to get back to where I was before?
> > 2) Why doesn't the system use the blatantly faster optimisation mode?
> >
> > George Barbour.
> > .
>
Received on Fri Jun 29 2001 - 05:00:16 CDT
