Script To Ease The Job Of Gathering Statistics On Schema Or Table
https://www.dropbox.com/s/ce7ssbn6sygwmed/gather_stats.sh?dl=0
This script is part of the database administration bundle I shared in an earlier post:
http://dba-tips.blogspot.ae/2014/02/oracle-database-administration-scripts.html
Once you run the script it will check if there is multiple databases running on the server, if so it will ask you to select the number of the database you want to run the script against:
Select the Instance You Want To Run this script against:
-------------------------------------------------------------------
1) bkpefms3
2) bkpefms
3) pefms
4) tpefms
#?
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 it's statistics:
Enter the TABLE NAME: [BLANK VALUE MEANS GATHER THE WHOLE SCHEMA [sysadm] STATISTICS]
===================
statuslog
In case you provided the 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 whether you want to gather histograms along with the statics 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 it's job as 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 in order to make it easy for you to restore back the original statistics for provided SCHEMA/TABLE in case the new statistics are not performing as expected.
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 will ONLY gather statistics on the tables that have missing or stale statistics, starting with Oracle 10g Oracle introducing a DBMS SCHEDULER job that 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 has 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/ce7ssbn6sygwmed/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
