ANALYZE & DBMS_STATS

ANALYZE:
analyze table owner.table_name compute statistics for table for all indexes for all indexed columns size 254;

analyze table owner.table_name partition(partition_name) compute statistics for table for all LOCAL indexes for all indexed columns size 254;

DBMS_STATS:
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'owner', TABNAME => 'table_name', PARTNAME => null, ESTIMATE_PERCENT => 100, BLOCK_SAMPLE => null, METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 254', DEGREE => null, GRANULARITY => 'ALL', CASCADE => TRUE, STATTAB => null, STATID => null, STATOWN => null);

ownname Schema of table to analyze.
tabname Name of table.
partname Name of partition.
estimate_percent Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the best sample size for good statistics.
block_sample Whether or not to use random block sampling instead ofrandom row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.
method_opt Accepts:

• FOR ALL [INDEXED HIDDEN] COLUMNS [size_clause]
• FOR COLUMNS [size clause] columnattribute[size_clause] [,columnattribute [size_clause]...], where size_clause is defined as: ize_clause := SIZE {integer REPEAT AUTO SKEWONLY}
• integer—Number of histogram buckets. Must be in the range [1,254].
• REPEAT—Collects histograms only on the columns that already have histograms.
• AUTO—Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
• SKEWONLY—Oracle determines the columns to collect histograms based on the data distribution of the columns.

degree Degree of parallelism. NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters.
granularity Granularity of statistics to collect (only pertinent if the table is partitioned).

• DEFAULT: Gather global- and partition-level statistics.
• SUBPARTITION: Gather subpartition-level statistics.
• PARTITION: Gather partition-level statistics.
• GLOBAL: Gather global statistics.
• ALL: Gather all (subpartition, partition, and global) statistics.

cascade Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS procedure on each of the table’s indexes.
stattab User stat table identifier describing where to save the current statistics.
statid Identifier (optional) to associate with these statistics within stattab
statown Schema containing stattab (if different than ownname).
no_invalidate Dependent cursors are not invalidated if this parameter is set to TRUE. When the ’cascade’ argument is specified, this parameter is not relevant with certain types of indexes.

PROCEDURE TO RECREATE PARTITIONED TABLE STATS

exec DBMS_SESSION.SET_NLS('NLS_SORT','BINARY');    -- necessary for a AIX Bug

-- Backup statistics (my backup table is BAKIM.STATTAB)
truncate table bakim.stattab;
exec DBMS_STATS.EXPORT_TABLE_STATS({owner},{table_name},null,'STATTAB','AAA',TRUE,'BAKIM');

declare

cursor cr_x is
select TABLE_owner,table_name,partition_name from dba_tab_partitions where
table_name in ('{table_name}') --and last_analyzed <>trunc(sysdate)
;
begin
for l in cr_x loop
DBMS_STATS.EXPORT_TABLE_STATS(l.TABLE_owner,l.table_name,l.partition_name,'STATTAB','AAA',TRUE,'BAKIM');
 end loop;
end;

-- Delete general table statistics. We want partition statistics to be escalated to table statistics
exec DBMS_STATS.DELETE_TABLE_STATS ({owner},{table_name},null,null,null,TRUE,TRUE,TRUE,null);

-- Gather partition statitstics
declare

cursor cr_x is
select TABLE_owner,table_name,partition_name from dba_tab_partitions where
table_name in ('{table_name}') --and last_analyzed <>trunc(sysdate)
;
Begin
for l in cr_x loop
DBMS_STATS.GATHER_TABLE_STATS ( l.TABLE_owner,l.table_name,l.partition_name,null,null,'FOR ALL INDEXED COLUMNS SIZE 254', NULL, 'PARTITION',TRUE, null, null, null);
end loop;
end;