Re: How do you un-analyze tables?

From: Margit Mester <mesterm_at_westel.hu>
Date: Thu, 28 Jun 2001 17:22:41 +0200
Message-ID: <3B3B4BC1.86F99ED8@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 Thu Jun 28 2001 - 10:22:41 CDT