cron job for top oracle sql waits

-bash-3.00$ cat /export/home/oracle/scripts/topsqlwait.sh

#! /bin/ksh

export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=cccdb

sqlplus -s /nolog <<-EOF set pagesize 0 connect / as sysdba -- set pagesize 100 lines 120 set lines 120 set feed off set heading on set long 500000 spool topsql4.alert select '**************************************************************' from dual; select '........ Oracle Wait Report On C Database...................' from dual; select '**************************************************************' from dual; select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Report Time" from dual; set linesize 90 -- set pagesize 4000 prompt SQL to identify heavy SQL (Get the SQL with heavy BUFFER_GETS) set heading on prompt ================================================================== select sql_text ,executions ,disk_reads ,buffer_gets from v\$sqlarea where decode(executions,0,buffer_gets,buffer_gets/executions) >
(select avg(decode(executions,0,buffer_gets,buffer_gets/executions))
+ stddev(decode(executions,0,buffer_gets,buffer_gets/executions)) from v\$sqlarea)
and PARSING_SCHEMA_NAME='PROD1'
/
prompt ==================================================================

prompt SQL to identify heavy SQL (Get the SQL with heavy DISK_READS)

select sql_text ,executions ,disk_reads ,buffer_gets from v\$sqlarea
where decode(executions ,0,disk_reads,disk_reads/executions) > (select
avg(decode(executions,0,disk_reads,disk_reads/executions)) +
stddev(decode(executions,0,disk_reads,disk_reads/executions)) from v\$sqlarea)
and PARSING_SCHEMA_NAME='PROD1'
/
prompt ==================================================================


Prompt 20+++++++ SQL FULL TABLE SCAN


select * from (select t.SQL_FULLTEXT,t.HASH_VALUE,t.executions from v\$sqlarea t, v\$sql_plan p
where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
and p.options='FULL'
and p.object_owner in ('PROD1')
order by EXECUTIONS desc )
where rownum <=20 / prompt ================================================================== prompt Top 10 by Buffer Gets: SELECT * FROM (SELECT sql_text, buffer_gets, executions, buffer_gets/executions "Gets/Exec", hash_value,address FROM V\$SQLAREA WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <= 10 / prompt ================================================================== prompt Top 10 by Physical Reads SELECT * FROM (SELECT sql_text, disk_reads, executions, disk_reads/executions "Reads/Exec", hash_value,address FROM V\$SQLAREA WHERE disk_reads > 1000
and PARSING_SCHEMA_NAME='PROD1'
ORDER BY disk_reads DESC)
WHERE rownum <=10 / prompt ================================================================== prompt Top 10 by Executions SELECT * FROM (SELECT sql_text,executions, rows_processed, rows_processed/executions "Rows/Exec", hash_value,address FROM V\$SQLAREA WHERE executions > 100
and PARSING_SCHEMA_NAME='PROD1'
ORDER BY executions DESC)
WHERE rownum <10 / prompt ================================================================== prompt Top 10 by Parse Calls: SELECT * FROM (SELECT sql_text, parse_calls, executions, hash_value,address FROM V\$SQLAREA WHERE parse_calls > 1000
and PARSING_SCHEMA_NAME='PROD1'
ORDER BY parse_calls DESC)
WHERE rownum <=10 / prompt ================================================================== prompt Top 10 by Sharable Memory: SELECT * FROM (SELECT sql_text, sharable_mem, executions, hash_value,address FROM V\$SQLAREA WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <= 10 / prompt ================================================================== prompt Top 10 by Version Count: SELECT * FROM (SELECT sql_text, version_count, executions, hash_value,address FROM V\$SQLAREA WHERE version_count > 20
and PARSING_SCHEMA_NAME='PROD1'
ORDER BY version_count DESC)
WHERE rownum <= 10 / prompt ================================================================== set heading on spool off exit EOF cat topsql4.alert >anuj.txt

EMAIL=`grep -v '^[ ]*$' topsql4.alert`

if [ "$EMAIL" ]
then
# echo "test"
#echo "$EMAIL" |/usr/bin/mailx -s "Oracle WAIT ALERT for C DATABASE " -r oracle@josa anuj08@gmail.co.uk
echo "$EMAIL">topsql4.tmp
fi