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