PERFORMANCE IMPROVEMENT with STALE PARTITIONS

First check the list of Stale Partitions and Tables in your Database.

SQL> select TABLE_NAME,PARTITION_NAME,STALE_STATS from dba_tab_statistics where owner=upper('&OWNER') and STALE_STATS='YES';
Enter value for owner:

Enter Your Schema Name.

It will provide you the list of tables and partitions (optional if you have partitions)

after that

create file in /home/oracle/stats_gather.sh

then
#####################Copy the following in your stats_gather.sh file ###################
#!/bin/ksh
set -x

gather_stats()
{
sqlplus -s /nolog > /home/oracle/stat_gather.log <<sqlconn

connect / as sysdba

set timing on

select to_char(sysdate,'DD-MON-YYYY HH:MI:SS') from dual;

THIS IS FOR TABLES

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME ',TABNAME=>'TABLE NAME ',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME ',TABNAME=>'TABLE NAME ',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME ',TABNAME=>'TABLE NAME ',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME ',TABNAME=>'TABLE NAME ',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME ',TABNAME=>'TABLE NAME ',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');


THIS IS FOR PARTITIONS

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME',TABNAME=>'TABLE NAME ',PARTNAME=>'PARTITION NAME',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME',TABNAME=>'TABLE NAME ',PARTNAME=>'PARTITION NAME',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME',TABNAME=>'TABLE NAME ',PARTNAME=>'PARTITION NAME',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME',TABNAME=>'TABLE NAME ',PARTNAME=>'PARTITION NAME',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCHEMA NAME',TABNAME=>'TABLE NAME ',PARTNAME=>'PARTITION NAME',ESTIMATE_PERCENT=>10,DEGREE=>8,CASCADE=>true,granularity=>'PARTITION');



select to_char(sysdate,'DD-MON-YYYY HH:MI:SS') from dual;


exit
sqlconn
}

ORACLE_HOME=YOUR ORACLE HOME
PATH=$PATH:YOUR ORACLE PATH
ORACLE_SID=YOUR ORACLE SID
export ORACLE_HOME PATH ORACLE_SID
gather_stats

uuencode /home/oracle/stat_gather.log | mailx -m -s "stats gather " youremail@yourdomain.com


NOW SAVE AND EXIT THE FILE

Note how to run this

nohup sh stats_gather.sh &

press enter

press enter

press enter  press enter 3 to 4 times

now verify that its running

tail -f nohup.out

the output should be like following:-

+ ORACLE_HOME=YOUR ORACLE HOME
+ PATH=YOUR ORACLE PATH:/usr/local/bin:/usr/bin:/usr/ccs/bin:/usr/contrib/bin:/usr/contrib/Q4/bin:/opt/perl/bin:/opt/ipf/bin:/opt/gvsd/bin:/opt/nettladm/bin:/opt/fcms/bin:/
opt/wbem/bin:/opt/wbem/sbin:/opt/sas/bin:/opt/graphics/common/bin:/opt/atok/bin:/usr/bin/X11:/usr/contrib/bin/X11:/opt/sec_mgmt/bastille/bin:/opt/caliper/bin:/opt/drd/bin:/opt/dsau/bin:/opt/d
sau/sbin:/opt/resmon/bin:/opt/firefox:/opt/gnome/bin:/opt/ignite/bin:/opt/propplus/bin:/usr/contrib/kwdb/bin:/opt/mozilla:/opt/perl_32/bin:/opt/perl_64/bin:/opt/prm/bin:/opt/sfm/bin:/opt/swm/
bin:/opt/sec_mgmt/spc/bin:/opt/ssh/bin:/opt/sentinel/bin:/opt/swa/bin:/opt/hpsmh/bin:/opt/thunderbird:/opt/langtools/bin:/opt/gwlm/bin:/opt/cfg2html:.:YOUR ORACLE HOME
+ ORACLE_SID=YOUR ORACLE SID