DBMS_STATS by Example

==============================
General
==============================
DBMS_STATS examples

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'USERNAME', 

  estimate_percent => 15);

END;

/

==============================
DBMS_STATS to gather histograms:
==============================
BEGIN
  DBMS_STATS.gather_table_stats
  ( USER, 'MY_TABLE', METHOD_OPT => 'FOR ALL COLUMNS SIZE 254' );
END;

==============================
DBMS_STATS to unlock statistics, gather stats, and lock statistics again.
==============================

Default setup

BEGIN

  DBMS_STATS.gather_table_stats

  (ownname=>i.owner, 

   tabname=>i.table_name, 

   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

   cascade => DBMS_STATS.AUTO_CASCADE );

END;

/

--DBMS_STATS.AUTO_CASCADE - default to TRUE

--TRUE/FALSE to analyze tables indexes

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'USERNAME', 

  estimate_percent => 15);

END;

/

DECLARE

 v_owner VARCHAR2(30) := 'MY_OWNER';

 v_table_name VARCHAR2(30) := 'MY_TABLE';

BEGIN

  DBMS_STATS.unlock_table_stats(v_owner ,v_table_name);

  DBMS_STATS.gather_table_stats

  (ownname=>v_owner,

   tabname=>v_table_name, 

   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

   cascade => DBMS_STATS.AUTO_CASCADE );  

  DBMS_STATS.lock_table_stats(v_owner ,v_table_name);

END;

/

See current statisctics

col TABLE_NAME for A30

SELECT table_name ,num_rows, sample_size, last_analyzed 

  FROM USER_TABLES 

WHERE table_name IN('AAA','BBB','CCC');

==============================

Use DBMS_STATS to rebuild index:
==============================

BEGIN
   DBMS_STATS.gather_index_stats('MY_OWNER','MY_INDEX');
END;

==============================
Working with Partitions
==============================

Step 1. - Gather basic stats for all the table

Step 2. - Gather detailed stats for current Partition.

Step 2. - Copy gathered detailed stats from current Partition to the next partition.

DBMS_STATS.gather_table_stats(ownname => v_user_name, 
                              tabname => p_table_name, 
                              estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
                              );
                                        
DBMS_STATS.gather_table_stats(ownname => v_user_name, 
                              tabname => p_table_name, 
                              partname => v_from_partition,
                              estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                              method_opt => 'FOR ALL COLUMNS');

DBMS_STATS.copy_table_stats(v_user_name, p_table_name, v_from_partition, v_to_partition);

==============================
DBMS_STATS on an Index after index creation. Needed or not?
==============================
Per Oracle Documentation, starting with Oracle 10.2.0.1, no need to manually gather index statistics after index creation, since this is done automatically.

==============================
METHOD_OPT
==============================
Short summary from how does the methodopt parameter work

The METHOD_OPT parameter controls the following,
- Which columns will or will not have base column statistics gathered on them
- Histogram creation,
- Creation of extended statistics

Histogram creation
METHOD_OPT default value is FOR ALL COLUMNS SIZE AUTO.
For most purposes, this should be the suitable value.


The METHOD_OPT parameter syntax is made up of two parts.
Part A - COLUMNS
Part B - SIZE

Part A - COLUMNS
COLUMNS part of the METHOD_OPT syntax controls which columns will have base column statistics (min, max, NDV, number of nulls, etc) gathered on them. 

Possible options:
FOR ALL COLUMNS - This is recommended, and default, value.
FOR ALL INDEXED COLUMNS - not recommended.

FOR ALL HIDDEN COLUMNS - to be used for case when a new virtual column was added, without affecting regular statistics.

Part B - SIZE

Possible options:
AUTO - This is default value.
Oracle will automatically determines the columns that need histograms based on the column usage information (SYS.COL_USAGE$), and the presence of a data skew. 
An integer value indicates that a histogram will be created with at most the specified number of buckets. Must be in the range [1,254]. To force histogram creation it is recommend that the number of buckets be left at 254. SIZE 1 means no histogram will be created.

REPEAT - A histogram will only be created for any column that already has one. And with the same SIZE value. If the table is a partitioned table, repeat ensures a histogram will be created for a column that already has one on the global level. 
However, this is not a recommended setting, as the number of buckets from past, might not be a good value for current data.


SKEWONLY automatically creates a histogram on any column that shows a skew in its data distribution.

A specific case I- gather base statistics for all columns, with no histograms and for one column (cust_id) gather detailed statistics.

DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID'); 


A specific case II- gather statistics only for some of the columns.
 DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR COLUMNS SIZE 254 CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD'); 

A specific case III- delete column statistics for a specific column.

DBMS_STATS.DELETE_COLUMN_STATS('SH', 'SALES', 'PROD_ID'); 

Creation of extended statistics

Extended statistics encompasses two additional types of column statistics; column groups and expression statistics. 

In the example below, a column group will be automatically created on the PROD_ID and CUST_ID columns in the SALES table. It will be given a system-generated name and will have all of the base column statistics gathered on it.

DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 254(PROD_ID, CUST_ID)');

DBMS_STATS.SET_TABLE_PREFS
In case of using a non default value for MOTHOD_OPT, rather than specifying the METHOD_OPT parameter in the statistics gathering command it is recommended that you specify value for the METHOD_OPT via DBMS_STATS.SET_TABLE_PREFS.

DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'METHOD_OPT',  'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 1 PROD_ID');

Possible values for METHOD_OPT parameter.
In the GATHER_DICTIONARY_STATS, GATHER_DATABASE_STATS, and GATHER_SCHEMA_STATS procedures only accepts 'FOR ALL [INDEXED|HIDDEN] columns' syntax. No specific column names can be specified.

When used in the GATHER_TABLE_STATS procedure, the METHOD_OPT parameter can accept an additional argument in the form of ‘FOR columns …'. 

==============================
DBA_TAB_STATISTICS and DBA_TAB_COL_STATISTICS
==============================
DBA_TAB_STATISTICSThis table holds additional info regarding Table statistics, such as last_analyzed, sample_size, stale_stats and stattype_locked indicator.

DBA_TAB_COL_STATISTICS
See histogram data:
SELECT column_name, num_distinct, histogram 
FROM   USER_TAB_COL_STATISTICS

WHERE  table_name = 'SALES'; 

SELECT * FROM V$SESSION WHERE event = 'enq: TX - row lock contention'

SELECT sql_id,event#, event, p1,p2,p3,p1text,p2text,p3text, wait_class_id , wait_class

FROM V$SESSION WHERE event = 'enq: TX - row lock contention'

SELECT * FROM V$SQL WHERE sql_id = '1xf229v3ja890'

set lines 500

 select * from (

 select sql_id,p1,p2,p3,p1text,p2text,p3text,ASH.SQL_OPNAME,count(1)

 from gv$active_session_history ash

 where ASH.EVENT ='enq: TX - row lock contention'

 group by sql_id,p1,p2,p3,p1text,p2text,p3text,SQL_OPNAME

 order by 9 desc

 )

 where rownum<12

 ;

update SGA_SUBSCRIBER_SFI_ACTIVE set COMMUNITIES=:1 , TS_LAST_MODIFIED=SYSDATE where IMSI=:2  and AFFILIATE_ID = :3 

SELECT * FROM USER_TABLES WHERE table_name = 'SGA_SUBSCRIBER_SFI'

SELECT * FROM USER_TAB_STATISTICS WHERE table_name = 'SGA_SUBSCRIBER_SFI'

SELECT USER FROM DUAL

BEGIN

  DBMS_STATS.unlock_table_stats('VRS_GROUP_SHARE','SGA_SUBSCRIBER_SFI');

END;

/

SELECT *

FROM dba_jobs

WHERE what like '%DB_STATISTICS_MANAGER.MAIN%' ;

BEGIN

DBMS_JOB.next_date(4,SYSDATE+10000);

commit;

END;

/

BEGIN    

  DBMS_STATS.gather_table_stats(ownname=>'VRS_GROUP_SHARE', tabname=>'SGA_SUBSCRIBER_SFI', estimate_percent => 5, cascade => DBMS_STATS.AUTO_CASCADE, degree=>16,GRANULARITY=> 'GLOBAL AND PARTITION');

END;

/

SELECT * FROM dba_tab_statistics WHERE table_name ='SGA_SUBSCRIBER_SFI'

=====================================

Example to gather stats for Partitioned table

=====================================

BEGIN    

  DBMS_STATS.gather_table_stats

  (ownname=>'SCHEMA_NAME', 

   tabname=>'TABLE_NAME', 

   estimate_percent => 5, 

   cascade => DBMS_STATS.AUTO_CASCADE, 

   degree=>16,

   GRANULARITY=> 'GLOBAL AND PARTITION');

END;

/

 

BEGIN

  DBMS_STATS.unlock_table_stats

  ('SCHEMA_NAME','TABLE_NAME');

END;

/

==============================
DBMS_STATS Copy from Table to Table:
==============================

Consider Example:

Same type of data is loaded to daily tables. Need to copy DBMS_STATS from table to table:

DBMS_STATS.gather_table_stats (ownname=>'USER_A', tabname=>'TABLE_A_20200101', estimate_percent => 5, cascade => DBMS_STATS.AUTO_CASCADE);

DBMS_STATS.create_stat_table  (ownname => 'USER_A' , stattab => 'temp_stat_20201109') ;

DBMS_STATS.export_table_stats (ownname => 'USER_A' , stattab => 'temp_stat_20201109', tabname =>'TABLE_A_20200101', statid => 'stats_20200101') ;

DBMS_STATS.import_table_stats (ownname => 'USER_A' , stattab => 'temp_stat', tabname => 'TABLE_A_20200102');

DBMS_STATS.import_table_stats (ownname => 'USER_A' , stattab => 'temp_stat', tabname => 'TABLE_A_20200103');

DBMS_STATS.import_table_stats (ownname => 'USER_A' , stattab => 'temp_stat', tabname => 'TABLE_A_20200104');

Appendix
DBMS_STATS Oracle reference.
GATHER_TABLE_STATS Procedure Oracle Reference
GATHER_INDEX_STATS Procedure Oracle Reference
Burlson Reference
Good block about DBMS_STATS usage and options

===================================

Example:

Copy updated stats from one partition 

to all partitions

===================================

BEGIN

DBMS_STATS.UNLOCK_TABLE_STATS(ownname => 'USER_NAME', tabname => 'GA_W_COUNTERS_HISTORY');

END;

/

BEGIN

DBMS_STATS.gather_table_stats(ownname => 'USER_NAME', 

                              tabname => 'GA_W_COUNTERS_HISTORY', 

                              partname => 'P_30',

                              estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

                              method_opt => 'FOR ALL COLUMNS');

END;

/

SELECT 'DBMS_STATS.copy_table_stats(''USER_NAME'', ''GA_W_COUNTERS_HISTORY'', ''P_30'', '''||partition_name||'''); ' FROM  USER_TAB_PARTITIONS WHERE table_name = 'GA_W_COUNTERS_HISTORY'

BEGIN

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_1'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_10'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_11'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_12'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_13'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_14'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_15'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_16'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_17'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_18'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_19'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_2'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_20'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_21'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_22'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_23'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_24'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_25'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_26'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_27'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_28'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_29'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_3'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_31'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_4'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_5'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_6'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_7'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_8'); 

DBMS_STATS.copy_table_stats('USER_NAME', 'GA_W_COUNTERS_HISTORY', 'P_30', 'P_9'); 

END;

/

==========================
Purge single sql_id from shared pool
==========================

SELECT address, hash_value FROM V$SQLAREA 

 WHERE sql_id='1qkunjg7g6twp';

BEGIN

 DBMS_SHARED_POOL.PURGE ('00000005DAAB9130, 6845332021', 'C');

END;

/

And check the explain plan Using DBMS_XPLAN.display

SET LINESIZE 200

SET PAGESIZE 0

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('1qkunjg7g6twp'));

==========================
Gather system stats
==========================

--Regular STATS collection for SYS schema
BEGIN

 DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

END;

/

--Gather STATS for ALL Database Objects!!

BEGIN

 DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

END;

/

--Collect STATS for DICTIONARY Objects

BEGIN

 DBMS_STATS.GATHER_DICTIONARY_STATS;

END;

/

--Collect STATS for in-memory Objects

BEGIN

 DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

END;

/

To rollback Statistics: (How to Restore Oracle Optimizer Statistics (Doc ID 452011.1) )

BEGIN

 DBMS_STATS.RESTORE_SCHEMA_STATS('SYS',SYSDATE-1);

END;

/

BEGIN

DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(SYSDATE-1);

END;

/

BEGIN

 DBMS_STATS.RESTORE_DICTIONARY_STATS(SYSDATE-1);

END;

/