How I usually Gather Stats in Ad-Hoc

Notes for How I Usually Collect Statistics in Ad-Hoc

---

Gather Database Stats

begin DBMS_STATS.GATHER_DATABASE_STATS (
    ESTIMATE_PERCENT 		=> DBMS_STATS.AUTO_SAMPLE_SIZE,
    BLOCK_SAMPLE 			=> FALSE,
    GRANULARITY 			=> 'AUTO',
    CASCADE 				=> TRUE,
    OPTIONS 				=> 'GATHER STALE',
    GATHER_SYS 				=> FALSE,
    NO_INVALIDATE 			=> TRUE,            -- FALSE will invalidate the plans
    -- DEGREE 				=> 8,               -- ENTERPRISE EDITION
    METHOD_OPT 				=> 'FOR ALL COLUMNS SIZE SKEWONLY'
); end;
/

---

Gather Table Stats in Exception Mode

begin DBMS_STATS.UNLOCK_TABLE_STATS('&owner', '&table_name'); end;
/

begin DBMS_STATS.GATHER_TABLE_STATS (
    OWNNAME					=> '&owner', 
    TABNAME					=> '&table_name',
    ESTIMATE_PERCENT 		=> DBMS_STATS.AUTO_SAMPLE_SIZE,
    BLOCK_SAMPLE 			=> FALSE,
    GRANULARITY 			=> 'AUTO',
    CASCADE 				=> TRUE,
    OPTIONS 				=> 'GATHER',
    NO_INVALIDATE 			=> TRUE,           -- FALSE will invalidate the plans
    -- DEGREE 				=> 16,             -- ENTERPRISE EDITION
    METHOD_OPT 				=> 'FOR ALL COLUMNS SIZE AUTO'
); end;
/

begin DBMS_STATS.LOCK_TABLE_STATS('&owner', '&table_name'); end;
/

---

Other Commands to Lock/Unlock Stats

begin DBMS_STATS.lock_schema_stats('&owner'); end;
/
begin DBMS_STATS.lock_table_stats('&owner', '&table_name'); end;
/
begin DBMS_STATS.lock_partition_stats('&owner', '&table_name', '&table_name_PART1'); end;
/

begin DBMS_STATS.unlock_schema_stats('&owner'); end;
/
begin DBMS_STATS.unlock_table_stats('&owner', '&table_name'); end;
/
begin DBMS_STATS.unlock_partition_stats('&owner', '&table_name', '&table_name_PART1'); end;
/

---

Other Usual Ways to Gather Stats

Gathering Database Stats

begin DBMS_STATS.gather_database_stats; end;
/
begin DBMS_STATS.gather_database_stats(estimate_percent => 15); end;
/
begin DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE); end;
/

Gathering Schema Stats

begin DBMS_STATS.gather_schema_stats('&owner'); end;
/
begin DBMS_STATS.gather_schema_stats('&owner', estimate_percent => 15); end;
/
begin DBMS_STATS.gather_schema_stats('&owner', estimate_percent => 15, cascade => TRUE); end;
/

Gathering Table Stats

begin DBMS_STATS.gather_table_stats('&owner', '&table_name'); end;
/
begin DBMS_STATS.gather_table_stats('&owner', '&table_name', estimate_percent => 15); end;
/
begin DBMS_STATS.gather_table_stats('&owner', '&table_name', estimate_percent => 15, cascade => TRUE); end;
/

Gathering Index Stats

begin DBMS_STATS.gather_index_stats('&owner', '&index_name'); end;
/
begin DBMS_STATS.gather_index_stats('&owner', '&index_name', estimate_percent => 15); end;
/

---

Here is how to check for missing stats in your tables.