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.

• Gather Table, Index and Schema Statistics

• Gather Other Database Objects Statistics

• Check Stale Statistics

• Linux Script to Gather Stats

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);

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

#!/bin/bash

. /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');
EOF

echo "Stats gathered succeeded"

Further Read

• Scheduling Stats Gather Job with DBMS_SCHEDULER