Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

So I recently updated an Oracle Database 10g from to, as advised by Oracle Support, due to stability issues (a known issue under some systems where the instance would freeze randomly under high load).

After applying the patch, stability problems are gone, but queries are A LOT slower because there are constant FULL SCANS on tables, even though I recalculated statistics for all tables in all schemas and the indexes are apparently OK. I also set the optimizer version value to the previous one (this was advised by Oracle Support too), but it hasn't improved so far.

Any ideas on this?

share|improve this question
What does Oracle support have to say about this issue? – jscott Apr 26 '12 at 23:40
They made me drop & recalculate statistics of two tables (not the only ones, but as an example) that are affected by this issue. I also rebuilt the indexes. However, the execution plan is still the same. It just skips the index and proceeds to a full scan, as if it had less cost. I updated the ticked and I am waiting for an answer now. – Joan Serra Apr 27 '12 at 15:49
I have also set optimizer_features_enable value back to – Joan Serra Apr 30 '12 at 15:35
up vote 0 down vote accepted

Finally it looks like the problem was caused by poorly optimized SQL. Also it seems like the instance only did full scans only when the cost of these was inferior to the cost of an index scan, so everything is apparently OK.

share|improve this answer

I run the following sql to schedule a job to run stats on stale tables every 2 hours. This prevents a lot of sql queries from going bad in 10g and 11g. If the table has not had more than 10% of the rows modified, then the runnning of stats will be skipped until 10% or more of the rows have been modified.

-- YOu must commit when you are finished to add the line to sys.job$/dba_jobs
-- You must run this as sys to get the jobs to run as sys to get the correct path to run the job
variable jobno number;
variable instno number;
  select max(job)+1 into :jobno from dba_jobs;
  select instance_number into :instno from v$instance;
'dbms_stats.gather_database_stats(options=>''gather stale'',estimate_percent=>100,degree=>4,cascade=>true);',
trunc(sysdate)+15/24, 'sysdate+2/24',
 TRUE, :instno);
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.