Home / / Gather Statistics Script

Gather Statistics Script

The following script will backup the current statistics and gather a new ones on a schema or table.
To download:
https://www.dropbox.com/s/fku4hf082xah1bm/gather_stats.sh?dl=0

This script is part of the database administration bundle, to know more about this bundle please visit:
http://dba-tips.blogspot.ae/2014/02/oracle-database-administration-scripts.html

How it works:
Once you execute the script it will ask you to select the database you want to run against (in case you have multiple databases on the same server):
Select the Instance You Want To Run this script against:
——————————————————————-
1) orcl1
2) orcl2
3) salesdb
#? 

Next, It will ask you to provide the SCHEMA NAME in case you want to gather statistics on a whole schema or TABLE OWNER in case you just want to gather statistics on a single table:
Enter the SCHEMA NAME/TABLE OWNER:
==================================
sysadm

Next selection will ask you to provide the TABLE NAME, in case you make your mind to gather statistics on the whole schema just leave it BLANK and hit enter, or just provide the table name you want to gather its statistics:
Enter the TABLE NAME: [BLANK VALUE MEANS GATHER THE WHOLE SCHEMA [sysadm] STATISTICS]
===================
statuslog

In case you provide a TABLE NAME it will check if the table is already exist or not then will display the LAST STATISTICS DATE on that table and will ask you if you want to gather histograms along with statistics or not [Default is YES].
GATHER HISTOGRAMS ALONG WITH STATISTICS? [Y|N] [Y]
=========================================

Next question will ask you whether you want to gather statistics on the table’s indexes or not [Default is YES]:
GATHER STATISTICS ON ALL TABLES’S INDEXES? [Y|N] [Y]
=========================================

Now the script will start to do the following:
> It will backup the current statistics on the provided SCHEMA/TABLE to a statistics table called BACKUP_STATS under SYS schema.
> It will gather statistics on the provided SCHEMA/TABLE as per your previous selections.
> Once it finish, it will provide you with the SQL command you need to run to restore the original statistics in case you are not OK with the new statistics.

You may ask a question, why you didn’t include gather database option in that script?
Actually I’m not in favor of the approach of gathering statistics on the whole database, unless you are doing so for the purpose of gather statistics on the tables having stale statistics, starting with Oracle 10g Oracle introducing an auto-tune job to gather statistics on tables that have missing or stale statistics:
You can check the status of that automated job using:
SQL> select status from dba_autotask_client where client_name = ‘auto optimizer stats collection’;

In case it is in DISABLE status, you can Enable it through this command:
SQL> BEGIN
     DBMS_AUTO_TASK_ADMIN.ENABLE(
     client_name => ‘auto optimizer stats collection’, 
     operation => NULL, 
     window_name => NULL);
     END;
     /

For more information about Statistics in Oracle you can read this post:
http://dba-tips.blogspot.ae/2012/11/all-about-statistics-in-oracle.html

Wrapping up:

To download gather statistics shell script click on the following link:
https://www.dropbox.com/s/fku4hf082xah1bm/gather_stats.sh?dl=0

In case you want to try using the whole database administration bundle, please click this link:
http://dba-tips.blogspot.ae/2014/02/oracle-database-administration-scripts.html

About Mahmmoud ADEL

View all posts by Mahmmoud ADEL →

24 thoughts on “Gather Statistics Script”

  5. Thank you for excellent article.

    Please refer below if you are looking for best project center in coimbatore

    soft skill training in coimbatore
    final year projects in coimbatore
    Spoken English Training in coimbatore
    final year projects for CSE in coimbatore
    final year projects for IT in coimbatore
    final year projects for ECE in coimbatore
    final year projects for EEE in coimbatore
    final year projects for Mechanical in coimbatore
    final year projects for Instrumentation in coimbatore

    Reply

  7. This is the exact information I am been searching for, Thanks for sharing the required infos with the clear update and required points. To appreciate this I like to share some useful information regarding Microsoft Azure which is latest and newest,

    Regards,
    Ramya

    Azure Training in Chennai
    Azure Training Center in Chennai
    Best Azure Training in Chennai
    Azure Devops Training in Chenna
    Azure Training Institute in Chennai
    Azure Training in Chennai OMR
    Azure Training in Chennai Velachery
    Azure Online Training
    Azure Training in Chennai Credo Systemz
    DevOps Training in Chennai Credo Systemz

    Reply

  19. After looking at a handful of the blog posts on your web site, I really appreciate your way of writing a blog. I added it to my bookmark webpage list and will be checking back soon. Please check out my website too and tell me your opinion.
    Cloud tech

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *