Inconsistent statistics usage

[chikkodi]

After running dbms_stats.gather_schema_stats, queries seem to be very slow. It is doing full table scans. While the query is running, if I do analyze table compute statistics on the table where the scan is happening, the query speeds up immediately. After a while it is again slow.

Somehow, it looks like the statistics are becoming stale after a while and execution plans of queries dont use them anymore. Is that possible? The Session details in OEM says "Cached Plan, Cost All Rows". Is there a way we can clear out the cache of these execution plans and start over?" DB has been restarted, still the same problem.

[tomcat]

What version of Oracle are you on? (10g has statistics_level parm for auto stats monitoring, 9i has dbms_stats gather_stale).

Restarting database does "clear out the cache". Redoing stats also flushes the shared pool, unless you specify noinvalidate=>true.

Since the (same exact?) query changes in performance, then the explain plan must be changing from run1 to run2. I would see what those look like; also - see if the stats are getting updated automatically - check last_analyzed in dba_tables and dba_indexes right after you analyze; check them again after the queries slow down.

What's the data change frequency like on your data? Is it a roller-coaster of up and down inserts & deletes? Or more like a steady climb?

See if tables have auto monitoring turned on:
select table_name, monitoring from dba_tables where owner='OWNER';

Check all_tab_modifications for info on changed monitored tables.

Sorry to send so many questions back at you, but this is a tough one... good luck to you.

[dbtoo]

After running dbms_stats.gather_schema_stats

What is the actual command you are using, that is, what are the parameters you used for the dbms_Stats.gather_Schema_stats command?

I've seen situations where gathering for a finer granualrity seems to create issues, but I cannot determine why we are seeing this. I've also seen the use of an index stop being used when statistics are gathered on the index (index, not schema). Drop the index, recreate it, and don't gather stats, it gets used?? What the???

[pando]

Drop the index, recreate it, and don't gather stats, it gets used?? What the???

because no stats = RBO

[dbtoo]

because no stats = RBO

That may make sense, if I had deleted statistics on the table and just not collected them on the index.