> What's the difference between Analyze and Dbms_Stats.Gather_XXX_Stats?

I can't say better than Metalink Note:237293.1, Note:237537.1 and other notes referenced in there. 
One small lesson I learned not mentioned in those notes is that if you run 
dbms_stats.gather_table_stats on a partitioned table, and later you run analyze on that partitioned 
table, some stats are not updated. I just did a test in Oracle 9.2. DBMS_STATS was run earlier. 
After a batch insert and ANALYZE, user_tables.num_rows is smaller than the sum of rows in all my 3 
partitions:

SQL> select num_rows from user_tab_partitions where table_name = 'PART';

NUM_ROWS
----------
132
131
32

SQL> select num_rows from user_tables where table_name = 'PART';

NUM_ROWS
----------
195 <-- old value here. Should be 132+131+32=295

So don't mix dbms_stats and analyze on a partitioned table. If you want to revert to the old 
analyze, delete statistics and start over.

The problem is really the mix of analyze and dbms_stats. Somebody can spend time listing all the 
missing statistics under all conditions when you mix the two stats collection methods. It may not 
be worth the effort though. Analyze delete after dbms_stats.delete_table_stats doesn't delete all 
stats. So the list should also include anomalies of NOT deleting stats under such and such 
conditions.

[20070728 update] Found Note:258687.1 "Last_analyzed Column Does Not Change After Analyzing tables"

----------------------------------------------------------------------------------------------------

Other less known or talked about differences between Dbms_Stats.Gather_XXX_Stats and Anaylyze:

* Analyze uses much less space in temporary tablespace for sorting than Dbms_Stats, even when the 
latter doesn't include cascade=>true.

* After starting to monitor index usage, Dbms_Stats.Gather_(Schema|Table)_Stats with cascade=>true 
will turn v$object_usage.used to 'YES' as does Dbms_Stats.Gather_Index_Stats, unless the index is 
empty. Analyze doesn't have this problem. (see Bug:4615996 and Bug:4432354)

* (from Jonathan Lewis "Cost-Based Oracle" p.373, 454) Avg_col_len created by Dbms_Stats includes 
length byte but that from Analyze does not. The two methods also use a number of different default 
values.

* Analyze estimate percentage ranges from 1 to 99, while Dbms_Stats.Gather_xxx_Stats 
estimate_percent ranges from 0.000001 to 100. So if you insist on using percentage for estimate, 
only dbms_stats is feasible for a huge table or index. But of course, Oracle may silently increase
the sampling percentage if it thinks it's too small (Ref:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i42181 and
paragraph starting with "At step 204" of http://www.freepatentsonline.com/6732085.html); and
Oracle's auto_sample_size may well be better than a manual setting.

* LAST_ANALYZED of DBA_TABLES|INDEXES is approximately the beginning time for ANALYZE but ending 
time for DBMS_STATS.GATHER_TABLE|INDEX_STATS. (If you test GATHER_TABLE_STATS, for obvious reason, 
set CASCADE to false.)

----------------------------------------------------------------------------------------------------

Other interesting things

* Analyze is a DDL which implicitly commits a transaction at the beginning moment of issuing the 
command (implicit means 'user commits' statistic is not incremented). But unlike most other DLLs, 
this DDL does not update xxx_objects.last_ddl_time. Similarly, dbms_stats.gather_xxx_stats can also 
be considered as a DDL because it commits, and it doesn't update last_ddl_time either.

* (Create index | alter index rebuild) compute statistics also computes statistics for the table of 
the index (behavior can be disabled by setting _optimizer_compute_index_stats to false in 10g). 
Oracle probably calls analyze (or something equivalent) for the index and dbms_stats for 
the table behind the scenes, judging by the fact that xxx_indexes.global_stats is 'NO' and 
xxx_tables.global_stats is 'Y'; global_stats column can be used to check this difference even if 
the index or table is not partitioned. (Note: when you do the test, make sure the table has at 
least 1 row.)

Yong Huang