Analyze table revisite/Gather Statistics

DBMS_STATS Package
Description
The PL/SQL package DBMS_STATS lets you generate and manage statistics for cost-based optimisation. You can use this package to gather, modify, view, export, import, and delete statistics.
The DBMS_STATS package can gather statistics on indexes, tables, columns, and partitions, as well as statistics on all schema objects in a schema or database. The statistics-gathering operations can run either serially or in parallel (DATABASE/SCHEMA/TABLE only)
Procedure Name
Description
GATHER_TABLE_STATS
Collects table, column, and index statistics.
GATHER_INDEX_STATS
Collects index statistics.
GATHER_SCHEMA_STATS
Collects statistics for all objects in a schema.
GATHER_DATABASE_STATS
Collects statistics for all objects in a database.
GATHER_SYSTEM_STATS
Collects CPU and I/O statistics for the system.
Previous to 8i, you would be using the ANALYZE ... methods. However 8i onwards, using ANALYZE for this purpose is not recommended because of various restrictions; for example:
1. ANALYZE always runs serially.
2. ANALYZE calculates global statistics for partitioned tables and indexes instead of gathering them directly. This can lead to inaccuracies for some statistics, such as the number of distinct values.
3. ANALYZE cannot overwrite or delete some of the values of statistics that were gathered by DBMS_STATS.
4. Most importantly, in the future, ANALYZE will not collect statistics needed by the cost-based optimiser.

ANALYZE can gather additional information that is not used by the optimiser, such as information about chained rows and the structural integrity of indexes, tables, and clusters. DBMS_STATS does not gather this information.

SQL Sourceset echo on
set feed on
set timing on
execute dbms_stats.gather_table_stats (ownname => 'SCOTT'
, tabname => 'DEPT'
, partname=> null
, estimate_percent => 20
, degree => 5
, cascade => true);
execute dbms_stats.gather_schema_stats (ownname => 'SCOTT'
, estimate_percent => 20
, degree => 5
, cascade => true);
execute dbms_stats.gather_database_stats (estimate_percent => 20
, degree => 5
, cascade => true);
SQL Source - Dynamic MethodDECLARE
sql_stmt VARCHAR2(1024);
BEGIN
FOR tab_rec IN (SELECT owner,table_name
FROM all_tables WHERE owner like UPPER('&1')
) LOOP
sql_stmt := 'BEGIN dbms_stats.gather_table_stats (ownname => :1, tabname
=> :2,partname=> null, estimate_percent => 20, degree => 5 ,cascade => true); END;'
;
EXECUTE IMMEDIATE sql_stmt USING tab_rec.owner, tab_rec.table_name ;
END LOOP;
END;
/