#!/bin/ksh

#@(#)---------------------------------------------------------------------------
#@(#) OBJET)  Purge et collecte le shema perfstat via une retention 
#@(#)---------------------------------------------------------------------------
#@(#) AUTEUR : Said MOTASSIM 
#@(#) DATE DE CREATION : 29/08/2007 
#@(#) VERSION : 1.0
#@(#)---------------------------------------------------------------------------
#@(#) MODIFIE PAR : 
#@(#) DATE DE MODIFICATION : 
#@(#) VERSION : 
#@(#) MODIFICATION : 
#===============================================================================
# USAGE
#    SSI_PurgePerfstatOra.ksh  <ORACLE_SID>
#===============================================================================
usage ()
{
  echo "  USAGE" >> $LOGFILE
  echo "     SSI_PurgePerfstatOra.ksh  [-f] <ORACLE_SID>" >> $LOGFILE
  exit 1
}

case $# in
   1)  Par_ORACLE_SID=$1
       Purgedate="0000"
    ;;
   2)  if [ $1 = '-f' ]
       then
          Purgedate=`date "+%H%M"`
          Par_ORACLE_SID=$2
        fi
    ;;
   *) usage
    ;;
esac

. /product/oracle/admin/etc/base.ksh $Par_ORACLE_SID
DIRLOG=`dirname $0`
LOGFILE="$DIRLOG/statspack_${ORACLE_SID}.log"
TMPLOGFILE=$DIRLOG/statspack_tmp.log

#------------------------------CALCUL DES VARIABLES SNAPID A PURGER------------------
instance_number=0
LoSnapTime=0
HiSnapTime=0
LoSnapId=0
HiSnapId=0
 
sqlplus -s perfstat/$ORA_PWD_PERFSTAT <<-FIN  > $TMPLOGFILE 
set head off pages 0 feed off lines 200
alter session set NLS_DATE_FORMAT='DD/MM/YYYY:hh24:mi:ss';
select min(snap_time),max(snap_time)
from STATS\$SNAPSHOT s, V\$INSTANCE i
where to_char(snap_time,'yyyymmddhh24mi') < to_char(sysdate - $ORA_RETENTION_PERFSTAT,'yyyymmdd"$Purgedate"')
and s.instance_number = i.instance_number;
exit
FIN

if [ `cat $TMPLOGFILE | grep 'ORA-' | wc -l` -ne 0 ]
then
   cat $TMPLOGFILE > $LOGFILE
   exit 1
fi
cat $TMPLOGFILE | awk '{print($1,$2)}' | read LoSnapTime HiSnapTime
rm $TMPLOGFILE

sqlplus -s perfstat/$ORA_PWD_PERFSTAT <<-FIN|read instance_number
set head off pages 0 feed off lines 200
select instance_number from v\$instance;
exit
FIN

sqlplus -s perfstat/$ORA_PWD_PERFSTAT <<-FIN|awk '{print($1,$2)}'|read LoSnapId HiSnapId
set head off pages 0 feed off lines 200
select nvl(min(snap_id),0),nvl(max(snap_id),0)
from STATS\$SNAPSHOT s, V\$INSTANCE i
where to_char(snap_time,'yyyymmddhh24mi') < to_char(sysdate - $ORA_RETENTION_PERFSTAT,'yyyymmdd"$Purgedate"')
and s.instance_number = i.instance_number;
exit
FIN

export instance_number  
export LoSnapTime       
export HiSnapTime       
export LoSnapId         
export HiSnapId         

echo instance_number     :$instance_number     > $LOGFILE 
echo RETENTION           :$ORA_RETENTION_PERFSTAT  >> $LOGFILE 
echo LoSnapTime          :$LoSnapTime          >> $LOGFILE 
echo HiSnapTime          :$HiSnapTime          >> $LOGFILE 
echo LoSnapId            :$LoSnapId            >> $LOGFILE 
echo HiSnapId            :$HiSnapId            >> $LOGFILE 

echo "Debut: "$(date +"%d/%m/%Y:%H:%M:%S")  >> $LOGFILE
#------------------------------MISE EN NOLOGGING--------------------------------------
echo `date +"%d/%m/%Y:%H:%M:%S"`: Mise en nologging des objets >> $LOGFILE
sqlplus -s perfstat/$ORA_PWD_PERFSTAT <<-FIN >> $LOGFILE
spool $DIRLOG/set_nologging_perfstat_objects.sql
set echo off head off feed off termout off linesize 120  pagesize 0
prompt set termout on 
prompt set echo on
select 'ALTER TABLE PERFSTAT.'||TABLE_NAME||' NOLOGGING;' from user_tables where logging='YES'
union 
select 'ALTER INDEX PERFSTAT.'||INDEX_NAME||' NOLOGGING;' from user_indexes where logging='YES';
prompt exit
spool off
exit
FIN
sqlplus  -s perfstat/$ORA_PWD_PERFSTAT @$DIRLOG/set_nologging_perfstat_objects.sql >> $LOGFILE
echo `date +"%d/%m/%Y:%H:%M:%S"`:i Fin de la mise en nologgin des objets >> $LOGFILE
rm $DIRLOG/set_nologging_perfstat_objects.sql

#------------------------------Creation d index sur STATS$SQL_SUMMARY------------------
sqlplus -s perfstat/$ORA_PWD_PERFSTAT <<-FIN|read index
set head off pages 0 feed off lines 200
select count(*) from user_indexes where index_name in('STATS\$SQL_SUMMARY_IDX','STATS\$SQL_SUMMARY_HV');
exit
FIN
if [ $index -ne 2 ]
then
echo  `date +"%d/%m/%Y:%H:%M:%S"`": Creation d index sur STATS\$SQL_SUMMARY" >> $LOGFILE

sqlplus -s perfstat/$ORA_PWD_PERFSTAT <<-FIN >> $LOGFILE 
set head off pages 0 feed on echo on

CREATE INDEX PERFSTAT.STATS\$SQL_SUMMARY_IDX ON PERFSTAT.STATS\$SQL_SUMMARY
(SNAP_ID, DBID, INSTANCE_NUMBER)
NOLOGGING
NOPARALLEL;

CREATE INDEX PERFSTAT.STATS\$SQL_SUMMARY_HV ON PERFSTAT.STATS\$SQL_SUMMARY
(HASH_VALUE, TEXT_SUBSET)
NOLOGGING
NOPARALLEL;

exit
FIN
echo `date +"%d/%m/%Y:%H:%M:%S"`": Fin creation d index sur STATS\$SQL_SUMMARY" >> $LOGFILE
fi

#-------------------------Lancement des purges si hight moins low superieur a retention------
if [ $HiSnapId -ne 0 ]; then
	#----------------------------- Purge statspack par sppurge ----------------------------------
        echo `date` " Lancement de sppurge de $LoSnapTime a $HiSnapTime des snaphots $LoSnapId a $HiSnapId" >> $LOGFILE
	sqlplus -s perfstat/$ORA_PWD_PERFSTAT <<-FIN >> $LOGFILE 
	define losnapid="$LoSnapId"
	define hisnapid="$HiSnapId"
	@?/rdbms/admin/sppurge;
	FIN
        echo `date +"%d/%m/%Y:%H:%M:%S"` " Fin de sppurges des snaphots de $LoSnapId a $HiSnapId" >> $LOGFILE	

	#------------------------------DELETE DES SQLTEXT ---------------------------------------
	echo `date +"%d/%m/%Y:%H:%M:%S"`": Debut du delete sur sql_text"  >> $LOGFILE
        sqlplus -s perfstat/$ORA_PWD_PERFSTAT <<-FIN >> $LOGFILE
	set echo on
	delete --+ index_ffs(st) 
	from stats\$sqltext st 
	where NOT EXISTS 
	(
	SELECT 'X'  from stats\$sql_summary ss 
	WHERE (snap_id < $LoSnapId  or snap_id > $HiSnapId )  
	AND (hash_value, text_subset) = ((ST.hash_value, ST.text_subset))
	)
	;
	set echo off
	FIN
	echo `date +"%d/%m/%Y:%H:%M:%S"`" :Fin du delete sur sql_text" >> $LOGFILE

	#------------------------------REBUILD DES INDEXES--------------------------------
	sqlplus -s perfstat/$ORA_PWD_PERFSTAT <<-FIN >> $LOGFILE 
	spool $DIRLOG/reorg_schema_perfstat.sql
	set echo off head off feed off termout off linesize 120  pagesize 0
	prompt set termout on timing on
	prompt set echo on
	select 
	'alter index "' ||segment_name || '"' || ' rebuild online ' || 'tablespace ' || tablespace_name  ||' NOLOGGING ;'
	from user_segments where segment_type='INDEX' order by bytes desc;
	prompt exit
	spool off
	exit
	FIN
	
	echo `date +"%d/%m/%Y:%H:%M:%S"`": Lancement de la REBUILD DES INDEXES"  >> $LOGFILE
	sqlplus  perfstat/$ORA_PWD_PERFSTAT @$DIRLOG/reorg_schema_perfstat.sql  >> $LOGFILE
        rm $DIRLOG/reorg_schema_perfstat.sql
	echo `date +"%d/%m/%Y:%H:%M:%S"`": Fin de la REBUILD DES INDEXES" >> $LOGFILE
fi

#------------------------------Lancement du collecte-----------------------------------
echo `date +"%d/%m/%Y:%H:%M:%S"`": Lancement du collecte" >> $LOGFILE 
sqlplus -s perfstat/$ORA_PWD_PERFSTAT <<-FIN >> $LOGFILE 
execute perfstat.statspack.snap(i_snap_level=>$ORA_LEVEL_PERFSTAT);
exit
FIN
echo `date +"%d/%m/%Y:%H:%M:%S"`": Fin du collecte" >> $LOGFILE 

echo "------------------------------------" >> $LOGFILE 
echo "Fin: "`date +"%d/%m/%Y:%H:%M:%S"`  >> $LOGFILE 
echo "------------------------------------" >> $LOGFILE 
exit 0
