Niall,
Good point, and probably what I should be using.
George Barbour
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:3b3c51e6$0$8506$ed9e5944_at_reading.news.pipex.net...
> 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:32:26 CDT
