Steps To Gather Schema Stats with dbms_stats

Oracle Database

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 ....
spool off;

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

Create a shell script 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
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

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

00 00 * * * /home/oracle/

Sample Run Output:


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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.