I know this question has been asked thousands of times in different contexts, but it is a really confusing subject:
I did a lot of googling and scanned through the docs, but still can't find the answer. Oracle Docs recommendation since version 9:
Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics. That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. Further, the cost-based optimizer will eventually use only statistics that have been collected by DBMS_STATS. See Oracle9i Supplied PL/SQL Packages and Types Reference for more information on this package.
However, you must use the ANALYZE statement rather than DBMS_STATS for statistics collection not related to the cost-based optimizer, such as:
- To use the
VALIDATE or LIST CHAINED ROWS clauses - To collect information on freelist blocks
I will try to post a test scenario in addition to this post sooner, because I cannot post the exec real plans due internal policies, but here's what I noticed so far:
Oracle 11.2.0.1.0 - Windows Server 2012 R2 64bits
- Increased Oracle memory settings, enabled AMM and restarted the database;
- Collected system statistics (dbms_stats.gather_system_stats), to let Oracle aware that it has more "room" to work now;
- Collected schema statistics (dbms_stats.gather_schema_stats);
// At this point, users still kept reporting performance issues. Upon investigating some queries involved, I found that there were a lot of full table scans (but yes, all indexes were available and stats should be up-to-date after schema stats, please correct me if I'm wrong). So I ran dbms_stats.delete_schema_stats in order to recollect stats manually through analyze (can't recall the paper at this moment, but I read it's not a good idea to mix up stats with dbms and analyze simultaneously), after after analyze finished, the plan overall cost was reported 125 (before it was 400), but still some tables were performing full table scan, then I delete statistics for just a specific table involved in full table scan, let's call it "A", and the execution plan now opted for table access by index rowid, and the cost for this step decreased from 30 to 5, but the overall cost know increased from +- 800 to 2000...
So my main questions are:
Has anyone worked in a specific environment/ db where only analyze worked/ benefit performance?
Can this be dictated by the Data Modeling/ Design? Whether or not dbms/ analyze will be a better option?
What further steps may I take in isolating the issue?
Is it needed to flush shared pool before collecting stats, as sometimes it's possible to see the immediate change in explain plan output?
Any suggestions/ corrections will be appreaciated.
Thanks.