So I recently updated an Oracle Database 10g from 10.2.0.3.0 to 10.2.0.4.0, 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?
Best Answer
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.