Dec 01 2010
Kshell
Collection of shell scripts that I use in my day to day support of database environments.
**Kshell Script to dynamically collect statistics. This script replaces the automated stats gathering script for larger databases, but DOES NOT replace the gather stale step that populates the SYS.DBA_TAB_MODIFICATIONS table. This script is dependent on the SYS.DBA_TAB_MODIFICATIONS table.
#!/usr/bin/ksh
######################################################################
#gather stats script to use dba_tab_modifications
#Execution: ./gather_stats_<ver>.ksh $ORACLE_SID $TABCNT_THRSHLD
#Author: Kellyn Pedersen
######################################################################
<set environment>
ORACLE_SID=$1
export ORACLE_SID
TABCNT_THRSHLD=$2
export TABCNT_THRSHLD
export ORACLE_HOME=`grep -i ^"$ORACLE_SID" /etc/oratab | awk -F: '{print $2}'`
export PATH=$PATH:${ORACLE_HOME}/bin
export EXEC_DIR==<DIR PATH>/admin
export SQL_DIR==<DIR PATH>/sql
export LOG_DIR=<DIR PATH>/logs
STATS_LOG=${LOG_DIR}/gather_missing_dw_${ORACLE_SID}.log
TBL_STATS=${SQL_DIR}/${ORACLE_SID}_dw_tbl_stats.sql
TBL_STATS2=${SQL_DIR}/${ORACLE_SID}_dw_tbl_stats2.sql
TBL_STATS3=${SQL_DIR}/${ORACLE_SID}_dw_tbl_stats3.sql
SYS_STATS=${SQL_DIR}/${ORACLE_SID}_upd_sys_stats.sql
export STATS_LOG TBL_STATS TBL_STATS2 SYS_STATS
export ML_LIST="<email addresses>"
#Remove previous scripts which is dynamically generated.
rm -f ${STATS_LOG}
rm -f ${TBL_STATS}
rm -f ${TBL_STATS2}
rm -f ${SYS_STATS}
sqlplus -s << EOF | read GET_TABCNT
/ as sysdba
set feedback off;
set head off;
set pagesize 0;
select count(*)
from sys.dba_tab_modifications
where table_owner in (select schema_nms from dba_schma_stats
where db_name='${ORACLE_SID}')
and truncated='NO'
and timestamp >=sysdate-1;
EOF
echo $GET_TABCNT
#DBA_TAB_MODIFICATIONS can *dump* 1000's of partitions, (considering env)
#into this table, script is designed with check threshold!
if
[ $GET_TABCNT -gt $TABCNT_THRSHLD ]
then
echo "Table Count from DBA_TAB_MODIFICATIONS too Large at ${GET_TABCNT}, Exiting"
echo|mail -s "${ORACLE_SID}: $GET_TABCNT are too many
objects to update stats on tonight,
holding off till weekend!" $ML_LIST
EXIT
else
#create and Run scripts
#Note the parameters for partition stats.
#All partitions here have an index on the partitioning key,
$ORACLE_HOME/bin/sqlplus '/as sysdba' <<EOF
set head off;
set linesize 500;
set pagesize 5000;
set echo off;
set feedback off;
set serverout off;
set term off;
SELECT 'Exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'''||table_owner||''',
tabname=>'''||table_name||''', partname=>'''||partition_name||''',
method_opt=>''FOR ALL COLUMNS SIZE 1'',
estimate_percent=>10, CASCADE=>TRUE, DEGREE=>6);'
from DBA_TAB_MODIFICATIONS
where table_owner in (select schema_nms from dba_schma_stats
where db_name='${ORACLE_SID}')
and PARTITION_NAME is not null
and table_name not in (select tbl_exclds from dba_schma_stats
where db_name='${ORACLE_SID}')
and timestamp >=sysdate-1;
spool ${TBL_STATS};
/
spool off;
#This is Non-partitioned tables
SELECT 'Exec DBMS_STATS.GATHER_TABLE_STATS
(ownname=>'''||table_owner||''',
tabname=>'''||table_name||''', method_opt=>''FOR ALL INDEXED COLUMNS'',
estimate_percent=>10,
CASCADE=>TRUE, DEGREE=>6);' from DBA_TAB_MODIFICATIONS
where table_owner in (select schema_nms from dba_schma_stats
where db_name='${ORACLE_SID}')
and PARTITION_NAME is null
and timestamp >=sysdate-1;
spool ${TBL_STATS2};
/
spool off;
#Collect Sys/System Stats
SELECT 'Exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>''
'||table_owner||''', tabname=>'''||table_name||''',
method_opt=>''FOR ALL COLUMNS SIZE 1'',estimate_percent=>70,
CASCADE=>TRUE, DEGREE=>6);' from sys.DBA_TAB_MODIFICATIONS
where table_owner in (select sys_schemas from dba_schma_stats
where db_name='${ORACLE_SID}')
and PARTITION_NAME is null
and timestamp >=sysdate-1;
spool ${SYS_STATS};
/
spool off;
exit;
EOF
#Run Scripts
$ORACLE_HOME/bin/sqlplus '/as sysdba' <<EOF>>${STATS_LOG}
set head off;
set linesize 500;
set pagesize 5000;
set echo on;
set feedback on;
set timing on;
@${TBL_STATS};
@${TBL_STATS2};
@${TBL_STATS3};
@${SYS_STATS};
exit
EOF
echo|mail -s "Daily $ORACLE_SID Stats Updated" $ML_LIST <${STATS_LOG}
fi
