Monday, September 22, 2014

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

9 comments:

  1. Are you looking for remote DBA support services by smart computing concept? Our team of database administrators (DBA) for remote DBA services makes sure about software applications that run proficiently.
    DBA Metrix Solutions

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Why you didn't add the option to gather stats on whole database in your script?

    thanks,
    Arul

    ReplyDelete
    Replies
    1. I'm currently revisiting the script to add this option along with gathering system and dictionary stats as well.

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. 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

    ReplyDelete
  6. Great blog you have here..best It’s difficult to find good quality writing like yours these days. I truly appreciate people like you! Take care!!

    ReplyDelete