Gather Statistics in Oracle
Improve query performance by gathering optimizer statistics.
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
#!/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
