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

From: John Newman <jnewman_at_mfa.org>
Date: 22 Apr 2004 05:58:18 -0700
Message-ID: <cbd5fe42.0404220458.1b2b1e1@posting.google.com>

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 Received on Thu Apr 22 2004 - 07:58:18 CDT