GATHER STATS for TABLE/SCHEMA/DATABASE/SYSTEM and LOCK STATS scripts
TABLE STATS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES',cascade=>TRUE);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);
exec dbms_stats.gather_table_stats(ownname=>'&Schema_name',tabname=>'&Table_name',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE,degree =>4);
exec DBMS_STATS.GATHER_TABLE_STATS (ownname => '&OWNER' , tabname => '&TAB_NAME',cascade => true, estimate_percent => 10,method_opt=>'for all indexed columns size 1',granularity => 'ALL', degree => 1);
INDEX STATS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
EXEC DBMS_STATS.gather_index_stats('HR','EMPLOYEES_PK');
exec DBMS_STATS.GATHER_INDEX_STATS(ownname => '&OWNER',indname =>'&INDEX_NAME',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);
SCHEMA STATS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);
exec dbms_stats.gather_schema_stats(ownname=>'&schema_name',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);
exec dbms_stats.gather_schema_stats(ownname=>'&schema_name', CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);
DATABASE STATS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);
exec dbms_stats.gather_database_stats(cascade=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE AUTO');
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>6);
EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,CASCADE => TRUE,degree => 4);
DICTIONARY STATS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
EXEC DBMS_STATS.gather_dictionary_stats;
EXEC DBMS_STATS.gather_system_stats;
EXEC DBMS_STATS.gather_fixed_objects_stats;
lock/unlock statistics on table
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
exec dbms_stats.lock_table_stats('&OWNER', '&TAB_NAME');
exec dbms_stats.unlock_table_stats('&OWNER', '&TAB_NAME');
SELECT stattype_locked FROM dba_tab_statistics WHERE table_name='RAJ' and owner='SH';
CHECK STALE STATS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT owner, table_name, last_analyzed, stale_stats
FROM dba_tab_statistics
WHERE table_name='EMPLOYEES'
and owner='HR';
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#!/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"