Oct 142016
 Posted by on October 14, 2016 at 10:28 pm Database, Oracle Database Tagged with:  Add comments
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 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
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:


© Incase of any copyright infringements please check copyrights page for faster resolutions.

Leave a Reply

Show Buttons
Hide Buttons