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