Steps To Gather Schema Stats with dbms_stats

Oracle provides the dbms_stats utility for estimating statistics for large partitioned tables and for better statistics which result in faster SQL execution plans altemately improving the performance.
If you have a high usage database and want to automate the process of gather stats each day or week with nightly jobs then you can follow below steps to quicky complete the same.
Note: DBMS_STATS.GATHER_SCHEMA_STATS job takes long period of time with very high CPU usage during the run, so it is suggested only to run it when there is minimal usage of database. Unfortunately, doing a complete analysis on a large database could take days.

Create a sql script as gatherstats.sql with below content:

spool gather_stats.log
prompt Starting Gather Stats ....
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME',DBMS_STATS.AUTO_SAMPLE_SIZE);
spool off;
quit;

Note: Update SCHEMA_NAME with your schema’s username in uppercase.

Create a shell script gatherstat.sh with below content after updating the variables according to your env.

export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_2/
export ORACLE_SID=db12cstd
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus sys/Welcome1 as sysdba @gather_stats.sql

Note: Change ORACLE_BASE, ORACLE_HOME and ORACLE_SID according to your env.

Provide execute privileges to the script.

chmod 0755 gatherstat.sh

Schedule it to run each night at 12AM via contab.

00 00 * * * /home/oracle/gatherstat.sh

Sample Run Output:

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.