• Arun Kumar

Gather Statistics in Oracle

Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. As a DBA, you must gather stats periodically using DBMS_STATS package.

Stats gathering must be done on regular basis

Gather Table, Index and Schema Statistics

DBMS_STATS.GATHER_TABLE_STATS is used to gather stats for a single table

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES',cascade=>TRUE);
Cascade will gather Index stats associated with the table

DBMS_STATS.GATHER_INDEX_STATS is used to gather index stats

EXEC DBMS_STATS.gather_index_stats('HR','EMPLOYEES_PK');

DBMS_STATS.GATHER_SCHEMA_STATS package is used to gather entire schema stats

EXEC DBMS_STATS.gather_schema_stats('SCOTT');

Gather Other Database Objects Statistics

DBMS_STATS.GATHER_DATABASE_STATS package is used to gather entire database stats

EXEC DBMS_STATS.gather_database_stats;

DBMS_STATS.GATHER_DICTIONARY_STATS package will gather dictionary statistics

EXEC DBMS_STATS.gather_dictionary_stats;

Gather System and Fixed Object Stats: There is no specific need to gather system (hardware, CPU, memory) and Fixed objects (X$ views) stats on a regular basis. You should only gather system & fixed objects stats when there is a major change on server hardware or major update to the database

EXEC DBMS_STATS.gather_system_stats;
EXEC DBMS_STATS.gather_fixed_objects_stats;

Check Stale Statistics

DBA_TAB_STATISTICS allows you to check stale statistics on a specific table

SELECT owner, table_name, last_analyzed, stale_stats
FROM dba_tab_statistics
WHERE table_name='EMPLOYEES'
and owner='HR';

DBA_IND_STATISTICS allows you to check stale statistics on specific index

SELECT owner, table_name, index_name last_analyzed, stale_stats FROM dba_ind_statistics 
WHERE table_name='EMPLOYEES'
and owner = 'HR';

Linux Script to Gather Stats

Here is a Linux shell script to gather HR schema stats and generates a log file under /tmp location


. /home/oracle/.bash_profile

export ORACLE_HOME=/u01/app/oracle/product/19.3/db_home
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=orcl
export DATE=$(date +%y-%m-%d_%H%M%S)

#### Gather HR schema stats ####
sqlplus / as sysdba << EOF > /tmp/HR_stats_gather_$DATE.log
EXEC DBMS_STATS.gather_schema_stats('HR');

echo "Stats gathered succeeded"

Further Read


Recent Posts

See All

Imagine an application user gave accidental salary hike of 50% instead of 5% to all employees! To initiate a database point-in-time recovery, we must know the exact time the query was executed against

The first step is to understand how instance size is configured. There are two main configurations: One Instance Size Separate SGA and PGA One Instance Size Oracle MEMORY_TARGET parameter defines the