Skip to content

Generate Gather Statistics Statements (gather_stats.gen)

What This Script Does

This generator script creates DBMS_STATS.GATHER_TABLE_STATS procedure calls to collect optimizer statistics on tables. Uses CASCADE to include indexes and AUTO_SAMPLE_SIZE for optimal sampling.

The Script

rem  gather_stats.gen
rem
select 'exec dbms_stats.gather_table_stats(''' || owner || ''',''' ||
       table_name || ''',cascade=>true,estimate_percent=>dbms_stats.auto_sample_size);'
  from sys.dba_tables
 where owner like upper('&owner')
   and table_name like upper('&table_name')
   and temporary = 'N'
   and owner not in ('SYS','SYSTEM','MDSYS','CTXSYS','XDB','ORDDATA','APEX_040200')
 order by owner, table_name
/

Usage

-- Basic usage
@gather_stats.gen

-- When prompted, enter:
-- owner: Schema owner pattern (use % for wildcard)
-- table_name: Table name pattern (use % for wildcard)

Parameters

The script prompts for:

&owner - Schema owner pattern (use % for wildcard)
&table_name - Table name pattern (use % for wildcard)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_TABLES
-- Plus ANALYZE ANY privilege or ownership of tables

Sample Output

exec dbms_stats.gather_table_stats('HR','EMPLOYEES',cascade=>true,estimate_percent=>dbms_stats.auto_sample_size);
exec dbms_stats.gather_table_stats('HR','DEPARTMENTS',cascade=>true,estimate_percent=>dbms_stats.auto_sample_size);
exec dbms_stats.gather_table_stats('SCOTT','EMP',cascade=>true,estimate_percent=>dbms_stats.auto_sample_size);

Key Features

CASCADE Option: Automatically gathers statistics on associated indexes
AUTO_SAMPLE_SIZE: Oracle determines optimal sample size
Temporary Table Exclusion: Skips temporary tables
System Schema Protection: Excludes critical system schemas

Common Use Cases

Gather Stats for All Tables in Schema

@gather_stats.gen
-- Enter: HR for owner, % for table_name

Gather Stats After Data Load

@gather_stats.gen
-- Enter: % for owner, %_STAGING for table_name

Table Analysis - View current table statistics
Rebuild Index Generator - Rebuild indexes before gathering stats