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

Leave Comment

Your email address will not be published. Required fields are marked *