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?
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Thu Apr 22 2004 - 10:02:37 CDT
