calcule de stats 11g

#!/usr/bin/ksh

#

# analyze schema

#

#

#

PATH=/opt/bin:$PATH

ORACLE_HOME=/appl/oracle/11.2.0.2

export ORACLE_HOME

export  TNS_ADMIN=$ORACLE_HOME/network/admin

PATH=$ORACLE_HOME/bin:$PATH

LOG=/appl/oracle/LOG

#

#

export ADMIN=/home/oracle

#

#

sqlplus -s  sys/FGFGFGG@XXXXcrs:1521/FRPERF as  sysdba<<EOF1 set echo off set feedback off column timecol new_value timestamp column spool_extension new_value suffix select to_char(sysdate,'yyMondd_hh24mi') timecol, '.log' spool_extension from sys.dual; column output new_value instance_name select value || '_' output from v\$parameter where name = 'instance_name'; spool /appl/oracle/LOG/compute_stats_&&instance_name&&timestamp&&suffix

--select  TO_char(SYSDATE,'Mon_Dy_DD@HH@HH24:MI:SS')   from  dual;

--exec dbms_stats.gather_schema_stats(ownname=>'$1' ,cascade=>TRUE,estimate_percent=> $2,method_opt=>'For All CoLUMNS SIZE 1',OPTIONS=>'GATHER',degree=>8);

--select  TO_char(SYSDATE,'Mon_Dy_DD@HH@HH24:MI:SS')   from  dual;

set timing on

set serveroutput on

set  lines 200

alter session set "_optimizer_join_factorization"=false;

alter  session set   NLS_DATE_FORMAT='DD-MM-YYYY:HH24:MI:SS';

set  pages 200

set  feed  on

select m.table_name, m.timestamp, sum(m.inserts), sum(m.updates), sum(m.deletes), t.num_rows, t.last_analyzed 

 from sys.dba_tab_modifications m, dba_tables t  

   where m.table_owner = t.owner

and m.table_name = t.table_name

and m.table_owner in ('APEX_040200','CTRL','R','R_SEC')

 group by m.table_name, m.timestamp, t.num_rows, t.last_analyzed  

 order by 1,2;

set  feed  off

---------

---index

---------

select a.owner,a.TABLE_NAME,a.index_name,a.NUM_ROWS ,last_analyzed from sys.dba_indexes a,sys.dba_tab_modifications b where  a.index_type not in ('IOT - TOP','LOB','DOMAIN') and   a.table_name=b.table_name

and a.owner in ('APEX_040200','CTRL','R','R_SEC')  order  by  last_analyzed; begin

for z   in (select a.owner,a.index_name from sys.dba_indexes a,sys.dba_tab_modifications b where  a.index_type not in ('IOT - TOP','LOB','DOMAIN') and   a.table_name=b.table_name and a.table_owner in ('APEX_040200','CTRL','R','R_SEC'))

loop

  dbms_output.put_line('STAT IND  100 percent: '||z.owner||'.'||z.index_name);

  dbms_stats.gather_index_stats(z.owner, z.index_name,estimate_percent=>100, degree=>4); end loop; end; /

select a.owner,a.TABLE_NAME,a.index_name,a.NUM_ROWS ,last_analyzed from sys.dba_indexes a,sys.dba_tab_modifications b where  a.index_type not in ('IOT - TOP','LOB','DOMAIN') and   a.table_name=b.table_name

and a.owner in ('APEX_040200','CTRL','R','R_SEC')  order  by  last_analyzed;

--

--TABLE

--

begin

for z in ( select r.owner,b.table_name from sys.dba_segments r,sys.dba_tab_modifications b where r.owner =b.table_owner and r.segment_name=b.table_name and r.extents >64 and r.bytes/1024/1024 >300 and r.owner in ('APEX_040200','CTRL','R','R_SEC'))

loop

  sys.dbms_output.put_line('STAT TAB 15 percent : '||z.owner||'.'||z.table_name);

  sys.dbms_stats.gather_table_stats(z.owner, z.table_name,estimate_percent=>15, degree=>4,cascade=>FALSE,method_opt=>'FOR ALL COLUMNS SIZE REPEAT');

end loop;

end;

/

begin

for z in (select r.owner,b.table_name from sys.dba_segments r,sys.dba_tab_modifications b where   r.owner =b.table_owner  and  r.segment_name=b.table_name   and r.owner in ('APEX_040200','CTRL','R','R_SEC'))

loop

  dbms_output.put_line('STAT TAB  100 percent : '||z.owner||'.'||z.table_name);

  dbms_stats.gather_table_stats(z.owner, z.table_name,estimate_percent=>100, degree=>4,cascade=>FALSE,method_opt=>'FOR ALL COLUMNS SIZE REPEAT');

end loop;

end;

/

set  feed  on

select  *  from dba_tab_modifications b where b.table_owner in ('APEX_040200','CTRL','R','R_SEC');

set  feed  off

------------------------------------------------------------

--  fin

------------------------------------------------------------

exit

EOF1

find $LOG -name 'compute_stats*' -mtime  +5  -print