Blog

Do you know why stats gather is required in Oracle Database?
i)As a DBA we may see that, highly transactional tables often gets stale. So stats gather can improve its performance
ii)Sometimes we may face some slowness in database also . This may be improved too by gathering statistics.
Today we will learn on this how to perform stats gather in Oracle. We can gather stats on the table level, schema level, database level, index level etc.

Step 1 :

By issuing the below command we can find if any table stats is stale.

Column table_name for a30
select LAST_ANALYZED,STATTYPE_LOCKED,table_name from dba_tab_statistics where owner='SH' and stale_stats ='YES';

Step 2 : Here we are assuming CUSTOMERS table is stale , so we will gather the stats on the CUSTOMER table .

select owner,table_name from dba_tables where owner='SH';

Step 3 : Unlock the stats if it locked, gather the stats, and lock again if you want

Syntax :
EXEC DBMS_STATS.unlock_table_stats('<SCHEMA_NAME>','TABLE_NAME');
Example : 
EXEC DBMS_STATS.unlock_table_stats('SH','CUSTOMERS');
Syntax : 
EXEC DBMS_STATS.gather_table_stats('<SCHEMA_NAME>','TABLE_NAME');
Example : 
EXEC DBMS_STATS.gather_table_stats('SH','CUSTOMERS');
Syntax : 
EXEC DBMS_STATS.lock_table_stats('<SCHEMA_NAME>','TABLE_NAME');
Example : 
EXEC DBMS_STATS.lock_table_stats('SH','CUSTOMERS');

We can gather the stats on the schema level and database level as well.

Syntax : 
EXEC DBMS_STATS.gather_table_stats('<SCHEMA_NAME>');
Example : 
EXEC DBMS_STATS.gather_table_stats('SH','CUSTOMERS');

So here are the steps how we can perform stats gather in oracle database. Hope this helps!!

Also you can check the link to get how to perform stats gather on objects owned by SYS