How to Gather stats & Export import stats in Oracle

Hello Friend's,

In this post we will see some example for stats gather in oracle database.

So Let's get started

To gather stats in oracle we require to use the DBMS_STATS package. It will collect the statistics in parallel with collecting the global statistics for partitioned objects.The DBMS_STATS package specialy used only for optimizer statistics. dbms_stats is very vital for good SQL performance.

Stats Gather Parameters : 

ownname	          This is nothing but the schema name
tabname	          Name of table for gathering stats
estimate_percent  Estimate of percentage of rows (NULL means compute). Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have                                         Oracle determine the appropriate sample size for good statistics. This is the default.
method_opt        This is also default parameter which indicates FOR ALL COLUMNS SIZE AUTO.
cascade	          This statement is used to Gather statistics on the indexes for this table.
degree	          This indicates degree of parallelism. The default for degree is NONE.

--------------------Gather stats for Database------------------

BEGIN
DBMS_STATS.gather_database_stats(estimate_percent => dbms_stats.auto_sample_size, degree=>4 );
END;
/

--------Gather stats for Database with dictionary and fixed object stats-------

Always prefer to keep the degree cpu - 2 even in non buisness hours

BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
DBMS_STATS.gather_dictionary_stats;
DBMS_STATS.gather_database_stats(estimate_percent => dbms_stats.auto_sample_size, degree=> 8 );
END;
/

--------------------Gather stats for schema------------------

Begin 
dbms_stats.gather_schema_stats( 
ownname => 'OWNER', --- schema name  
options => 'GATHER AUTO', 
estimate_percent => dbms_stats.auto_sample_size, 
method_opt => 'for all columns size repeat', 
degree => 24 
); 
END; 
/

-------------------Gather stats for a table-----------------

BEGIN 
DBMS_STATS.GATHER_TABLE_STATS ( 
ownname => 'OWNER', 
tabname => 'TAB', 
cascade => true, ---- For collecting stats for respective indexes  
method_opt=>'for all indexed columns size 1', 
granularity => 'ALL', 
estimate_percent =>dbms_stats.auto_sample_size,  
degree => 8); 
END; 
/ 

-- For a single table partition

 
BEGIN 
DBMS_STATS.GATHER_TABLE_STATS ( 
ownname => 'OWNER', 
tabname => 'TAB', --- TABLE NAME 
partname => 'TAB_JAN2021' --- PARTITOIN NAME 
method_opt=>'for all indexed columns size 1', 
GRANULARITY => 'APPROX_GLOBAL AND PARTITION', 
degree => 8); 
END; 
/

-----------------------Query to check running stats gather process-------------------

SELECT *
FROM v$session_longops
WHERE opname LIKE '%Gather%' AND time_remaining != 0
ORDER BY SID

-----------------------Lock/unlock statistics-------------------

--- Lock statistics :

EXEC DBMS_STATS.lock_schema_stats('OWNER'); 
EXEC DBMS_STATS.lock_table_stats('OWNER', 'TAB'); 
EXEC DBMS_STATS.lock_partition_stats('OWNER', 'TAB', 'TAB_JAN2021'); 

-- Unlock statistics :

EXEC DBMS_STATS.unlock_schema_stats('OWNER'); 
EXEC DBMS_STATS.unlock_table_stats('OWNER', 'TAB'); 
EXEC DBMS_STATS.unlock_partition_stats('OWNER', 'TAB', 'TAB_JAN2021'); 

--- check stats status:

SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TAB' and owner = 'OWNER';

-----------------------Export import statistics-------------------

--- Create staging table to store the statistics data :

exec dbms_stats.create_stat_table(ownname => 'OWNER', stattab => 'STAT_BACKUP',tblspace=>'USERS'); 

-- Export stats :

exec dbms_stats.export_table_stats(ownname=>'OWNER', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>true); 

-- Import stats :

exec dbms_stats.export_table_stats(ownname=>'OWNER', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>true); 

Gather stats other examples :

GATHER_DATABASE_STATS
GATHER_DICTIONARY_STATS
GATHER_FIXED_OBJECTS_STATS
GATHER_INDEX_STATS
GATHER_SCHEMA_STATS
GATHER_SYSTEM_STATS
GATHER_TABLE_STATS

Hope This Helps, If there is any suggestions kindly comment.

Regards

Sultan Khan

• Tweet
• Share
• Share
• Share
• Share

Related Post