Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Hints in 3rd party SQL ignored after running dbms_stats.gather_schema_stats()

Re: Hints in 3rd party SQL ignored after running dbms_stats.gather_schema_stats()

From: John Newman <jnewman_at_mfa.org>
Date: 23 Apr 2004 05:46:53 -0700
Message-ID: <cbd5fe42.0404230446.35adcfce@posting.google.com>


Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1082646149.295026_at_yasure>...
> John Newman wrote:
>
> > A package we use with a Ora9.2 database (optimzer mode=RULE) has many
> > queries with INDEX hints that worked very well. I ran a job that had
> > not been run since conversion from 8.1.7 which performs
> > dbms_stats.delete_schema_stats() and dbms_stats.gather_schema_stats().
> > Since then the queries are ignoring the INDEX hints and taking 50-100
> > times as long. I have tried deleting the statistics with:
> >
> > 1.dbms_stats.delete_database_stats() ! as SYS
> > 2.dbms_stats.delete_schema_stats('SA') ! SA is the relevant schema
> > 3.analyze table || table_name || delete statistics !for every table
> > name
> > 4.dbms_stats.delete_index_stats ('SA', 'table_name') !for every table
> > name
> > 5.analyze index || index_name || delete statistics !for every index
> > name
> >
> > I have bounced the database after each step - no luck!
> >
> > Does anyone know how to completely get rid of gathered statistics in
> > 9.2?
> >
> > We had a similar problem going to 8i (when the default became CHOOSE
> > we had performance problems), but dbms_stats.delete_database_stats()
> > and switching back to RULE seemd to resolve it in 8i.
> >
> > Oracle seems to want it to solve it on a query by query basis - they
> > don't seem to want to answer the BIG question of how to completely get
> > rid of the statistics, so all the queries go back to behaving the way
> > they did before the statistics were gathered.
> >
> > Thanks,
> >
> > JCN
>
> I have never had a problem removing stats with DBMS_STATS. Which
> version and edition of 9.2?

This occurs in both Enterprise (Production) and Standard (Test) editions of Oracle 9i Database Release 2 (9.2.0.1.0) for Microsoft Windows (2000 SP4). The stats certainly appear to be deleted (totally clean statistics tabs in Enterprise Manager, nothing returned from last_analyzed_date is not null queries), but the application queries are not going back to using their hints...Thanks. Received on Fri Apr 23 2004 - 07:46:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US