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

22 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 blog is the general information for the feature. You got a good work for these blog.We have a developing our creative content of this mind.Thank you for this blog. This for very interesting and useful.

    Oracle DBA Training in Chennai

    ReplyDelete
  3. Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
    DevOps Training in chennai

    DevOps Training in Bangalore

    DevOps Training in Pune

    DevOps Online Training

    ReplyDelete
  4. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...

    DevOps Training in Bangalore

    DevOps Training in Pune

    DevOps Online Training

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

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

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

    thanks,
    Arul

    ReplyDelete
  8. Outstanding blog thanks for sharing such wonderful blog with us ,after long time came across such knowlegeble blog. keep sharing such informative blog with us.

    Check out : big data training in chennai chennai tamilnadu | big data hadoop training in velachery | big data training in velachery | big data hadoop interview quesions and answers pdf| mapreduce interview questions

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

    ReplyDelete
  10. I really enjoy reading this article. Hope that you would do great in upcoming time.A perfect post. Thanks for sharing.devops training in bangalore

    ReplyDelete
  11. Good activity in supplying the suitable content material with the clear clarification.
    The content material looks real with legitimate data.

    click here for more info.

    ReplyDelete
  12. 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
  13. 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

  14. That is nice article from you , this is informative stuff . Hope more articles from you . I also want to share some information about devops training in pune

    ReplyDelete