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

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

#!/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

Heading 2