Report of database workload and top SQL statistics

1. shell script:

#!/bin/ksh
export ORACLE_SID=
export ORACLE_HOME=
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/jdbc/lib
export PATH=$ORACLE_HOME/bin:$HOME/dba:$PATH

sqlplus  -s " u/p@prod" <<EOF
set verify off
set feed off
set pagesize 1000
set linesize 130
set head on
set markup html on entmap ON spool on preformat off
spool cap_report.xls
@cap_report.sql
spool off
EOF

BDATE=`sqlplus -s  "u/p" <<EOF
set pages 0 trimsp on feed off timing off time off
select TRUNC(SYSDATE-7) from dual;
exit
EOF
`
EDATE=`sqlplus -s  "u/p" <<EOF
set pages 0 trimsp on feed off timing off time off
select TRUNC(SYSDATE-1) from dual;
exit
EOF
`

(echo "IBMS PROD load metrics and top SQL for $BDATE to $EDATE";uuencode cap_report.xls cap_report.xls)|mail -s  "IBMS PROD load metrics and top SQL for $BDATE to $EDATE" email@email.com
mv cap_report.xls cap_report.xls.`date +%Y%m%d%H%M%S`

2. the sql cap_report.sql:

alter session set nls_date_format='yyyy-mon-dd';
define days=7
with lview1 as (
select /*+ materialize */  end1_time,round(avg(avg)) Database_time_per_sec  from
(
select  to_char(end_time,'yyyy-mon-dd') end1_time,
 round(AVERAGE/100) AVG
 from dba_hist_sysmetric_summary
 where trunc(begin_TIME)>=trunc(sysdate- &&days)
and trunc(begin_TIME)<=trunc(sysdate- 1)
 and  METRIC_NAME='Database Time Per Sec'
 and to_number(to_char(END_time,'HH24')) >=9
 and to_number(to_char(end_time,'HH24'))<=16
and snap_id between (select min(snap_id) from DBA_HIST_SNAPSHOT where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-&&days))
and (select max(snap_id) from DBA_HIST_SNAPSHOT where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate))
and to_NUMBER(TO_CHAR(END_TIME,'D')) NOT IN (1,7)
AND to_char(end_time,'yyyy-mm-dd') not in (select day from ontario_holiday)
)
group by end1_time
),
lview2 as (
select /*+ materialize */  end1_time,round(avg(avg)) Database_time_per_sec  from
(
select  to_char(end_time,'yyyy-mon-dd') end1_time,
 round(AVERAGE/100) AVG
 from dba_hist_sysmetric_summary
 where trunc(begin_TIME)>=trunc(sysdate- &&days)
and trunc(begin_TIME)<=trunc(sysdate- 1)
 and  METRIC_NAME='Database Time Per Sec'
 and to_number(to_char(END_time,'HH24')) =11
and snap_id between (select min(snap_id) from DBA_HIST_SNAPSHOT where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-&&days))
and (select max(snap_id) from DBA_HIST_SNAPSHOT where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate))
and to_NUMBER(TO_CHAR(END_TIME,'D')) NOT IN (1,7)
AND to_char(end_time,'yyyy-mm-dd') not in (select day from ontario_holiday)
)
group by end1_time
),
lview3 as (
select /*+ materialize */ to_char(COMPLETION_TIME,'yyyy-mon-dd') END1_TIME,round(sum(BLOCKS*BLOCK_SIZE/1024/1024/1024))
Daily_Log_sizeG from v$archived_log
where trunc(COMPLETION_TIME) >= trunc(sysdate-&&DAYS)
 AND   trim(to_char(COMPLETION_TIME, 'DAY')) not in ('SATURDAY', 'SUNDAY')
 AND to_char(COMPLETION_TIME,'yyyy-mm-dd') not in (select day from ontario_holiday)
and trunc(COMPLETION_TIME) <= trunc(sysdate-1)
and dest_id =1
group by to_char(COMPLETION_TIME,'yyyy-mon-dd')
),
lview4 as (
select /*+ materialize */  round(avg(sizeG)) Avg_sizeG from (
 select to_char(COMPLETION_TIME,'yyyy-mon-dd') END1_TIME,round(sum(BLOCKS*BLOCK_SIZE/1024/1024/1024)) SIZEG from v$archived_log
where trunc(COMPLETION_TIME) >= trunc(sysdate-31)
 AND   trim(to_char(COMPLETION_TIME, 'DAY')) not in ('SATURDAY', 'SUNDAY')
 AND to_char(COMPLETION_TIME,'yyyy-mm-dd') not in (select day from ontario_holiday)
and trunc(COMPLETION_TIME) <= trunc(sysdate-1)
and dest_id =1
group by to_char(COMPLETION_TIME,'yyyy-mon-dd'))
),
lview5 as (
select /*+ materialize */ round(avg(sizeG)) Avg_sizeG from (
 select to_char(COMPLETION_TIME,'yyyy-mon-dd') END1_TIME,round(sum(BLOCKS*BLOCK_SIZE/1024/1024/1024)) SIZEG from v$archived_log
where trunc(COMPLETION_TIME) >= trunc(sysdate-365)
and trunc(COMPLETION_TIME)<=trunc(sysdate- 1)
 AND   trim(to_char(COMPLETION_TIME, 'DAY')) not in ('SATURDAY', 'SUNDAY')
 AND to_char(COMPLETION_TIME,'yyyy-mm-dd') not in (select day from ontario_holiday)
and trunc(COMPLETION_TIME) <= trunc(sysdate-1)
and dest_id =1
group by to_char(COMPLETION_TIME,'yyyy-mon-dd'))
)
select lview1.end1_time,lview1.Database_time_per_sec Avg_Database_time_per_sec,
lview2.Database_time_per_sec Database_time_per_sec_11AM,
lview3.Daily_Log_sizeG,lview4.Avg_sizeG "Avg_LOG_sizeG_Past_month",lview5.Avg_sizeG "Avg_LOG_sizeG_Past_Year"
FROM lview1,lview2,lview3,lview4,lview5
where lview1.end1_time=lview2.end1_time
and lview1.end1_time=lview3.end1_time
order by to_date(lview1.end1_time,'yyyy-mon-dd');

with viewd as (
select /*+ materialize */ t.*,round(t.BUFFER_GETS /decode(t.exec,0,1,t.exec)) gets_per_exec,round(t.Ela/decode(t.exec,0,
1,t.exec)) Ela_sec_per_exec
from (
select sql_id,sum(BUFFER_GETS_DELTA) BUFFER_GETS,
sum(st.DISK_READS_DELTA) DISK_READS,
sum(st.EXECUTIONS_DELTA) exec,
round(sum(st.ELAPSED_TIME_DELTA)/1000000)  Ela
from dba_hist_sqlstat st,dba_hist_snapshot sn
where st.snap_id=sn.snap_id
and st.snap_id=sn.snap_id
 and  trunc(BEGIN_INTERVAL_TIME)>=trunc(sysdate-&&days)
 and  trunc(BEGIN_INTERVAL_TIME)<=trunc(sysdate-1)
and EXTRACT(HOUR FROM Sn.END_INTERVAL_TIME) between 9 and 16
--and to_number(to_char(END_INTERVAL_time,'HH24')) >=9
--and to_number(to_char(end_INTERVAL_time,'HH24'))<=16
and to_NUMBER(TO_CHAR(END_INTERVAL_TIME,'D')) NOT IN (1,7)
AND to_char(end_INTERVAL_time,'yyyy-mm-dd') not in (select day from ontario_holiday)
 group by sql_id
 order by sum(st.ELAPSED_TIME_DELTA) desc
  ) t
--where rownum <=40
)
select * from
(select viewd.* ,substr(sqlt.sql_text,1,90) sql_text
from viewd left join dba_hist_sqltext sqlt
on viewd.sql_id=sqlt.sql_id
where upper(sqlt.sql_text) not like 'BEGIN%' and upper(sqlt.sql_text) not like 'DECLARE%'
order by viewd.ela desc)
where rownum <=10;

the report will looks like this: