SQL Script

Analyze Tables Revisited / Gather Statistics

Updated: 26-Feb-2002
Version : 3.0

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:

ANALYZE always runs serially.
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.
ANALYZE cannot overwrite or delete some of the values of statistics that were gathered by DBMS_STATS.
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 Source

set 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 Method

DECLARE

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;
/

Previous Oracle Version Links

Three ways to Analyze tables

Return to Index of SQL Scripts