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

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
You're hopefully aware that using the 'index' hint automatically invokes the cost based optimizer. If you had no stats, then the cbo will assume some (probably very inappropriate) defaults.

eg

SQL> create table t ( x number primary key, y number );

Table created.

SQL> alter session set optimizer_goal = rule;

Session altered.

SQL> set autotrace traceonly explain
SQL> select * from t where x = 1;

Execution Plan

   0 SELECT STATEMENT Optimizer=RULE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'    2 1 INDEX (UNIQUE SCAN) OF 'SYS_C004668' (UNIQUE) SQL> select /*+ INDEX(t) */ * from t where x = 1;

Execution Plan

   0 SELECT STATEMENT Optimizer=RULE (Cost=1 Card=20 Bytes=520)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=20 Bytes=520)

   2 1 INDEX (RANGE SCAN) OF 'SYS_C004668' (UNIQUE) (Cost=1 Card=8)

See how the index hint means CBO - even with no stats

hth
connor

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"