Posted in Shell script, SQL

Grab stats for partitions

Useful scripts to grab stats for partitions:

set serveroutput on
DECLARE
LV_SQL VARCHAR2(1000);
CURSOR C1 IS 
SELECT TABLE_NAME T , PARTITION_NAME P , LAST_ANALYZED L  FROM USER_TAB_PARTITIONS WHERE LAST_ANALYZED is null AND 
TABLE_NAME = '&TABLE_NAME' and partition_name like ('&PART_NAME');
BEGIN
  FOR I IN C1 LOOP
   dbms_output.put_line('Gathering stats for :'||I.P);
   LV_SQL:= 'BEGIN  ';
   LV_SQL:= LV_SQL ||'dbms_stats.gather_table_stats (''OWNER'','||':1'||',partname=>'||':2'||',granularity=>''partition'',estimate_percent => 1);' ;
   LV_SQL:= LV_SQL ||' END ;';
   EXECUTE IMMEDIATE LV_SQL USING  I.T,I.P;
  END LOOP;
END;
/

Or we can put it in a shell script:

#!/bin/bash

export ORACLE_SID=DB_NAME
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3
export PATH=$PATH:$ORACLE_HOME/bin

cd /u01/app/oracle/admin/

sqlplus -S /nolog << EOF > gather_part_stats.log 2>> gather_part_stats.log
conn user/passwd

set serveroutput on
DECLARE
LV_SQL VARCHAR2(1000);
CURSOR C1 IS 
SELECT TABLE_NAME T , PARTITION_NAME P , LAST_ANALYZED L  FROM USER_TAB_PARTITIONS WHERE (LAST_ANALYZED is null or LAST_ANALYZED < sysdate -7) AND 
order by LAST_ANALYZED;
BEGIN
  FOR I IN C1 LOOP
   dbms_output.put_line('Gathering stats for :'||I.P);
   BEGIN
   LV_SQL:= 'BEGIN  ';
   LV_SQL:= LV_SQL ||'dbms_stats.gather_table_stats (''OWNER'','||':1'||',partname=>'||':2'||',granularity=>''partition'',estimate_percent => 1,CASCADE => TRUE, method_opt => ''FOR ALL COLUMNS SIZE 1'');' ;
   LV_SQL:= LV_SQL ||' END ;';
   EXECUTE IMMEDIATE LV_SQL USING  I.T,I.P;
   END;
  END LOOP;
END;
/
exit
EOF
Advertisements

Author:

I am an Oracle DBA with experience in Maximum Availability technology such as RAC, Data Guard, Grid Control and ASM. I worked with version 9i through 12c on a number of operating systems. Next to that I have an extensive experience with PL/SQL, Oracle eBusiness Suite and shell scripting. Specialties: Oracle RAC & ASM (Configuration/Monitoring/Tuning) Oracle Data Guard (Configuration/Monitoring/Tuning) EM12c (Installation / troubleshooting) SQL & PLSQL *nix Shell scripting

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.