Database Response Time

select to_char(begin_time,'hh24:mi') time, round( value * 10, 2) "Response Time (ms)"
from v$sysmetric
where metric_name='SQL Service Response Time';

Database Response Time Standard Deviation

with epsilon
as
(select avg(average - STANDARD_DEVIATION ) m1,
avg(average + STANDARD_DEVIATION ) m2
from dba_hist_sysmetric_summary
where metric_name='User Calls Per Sec')
select avg(a.average - a.STANDARD_DEVIATION) "A - Good",
avg(a.average) "Average",
avg(a.average + a.STANDARD_DEVIATION) "B - Bad"
from dba_hist_sysmetric_summary a,
dba_hist_sysmetric_summary b,
epsilon e
where a.metric_name='SQL Service Response Time'
and b.metric_name='User Calls Per Sec'
and a.snap_id = b.snap_id
and b.average between e.m1 and e.m2;

Database Throughput

select a.begin_time, a.end_time, round(((a.value + b.value)/131072),2) "GB per sec"
where a.metric_name = 'Logical Reads Per Sec'
and b.metric_name = 'Physical Reads Direct Per Sec'
and a.begin_time = b.begin_time;

Database Throughput Standard Deviation

with epsilon
as
(select avg(average - STANDARD_DEVIATION ) m1,
avg(average + STANDARD_DEVIATION ) m2
from dba_hist_sysmetric_summary
where metric_name='User Calls Per Sec')
select avg( ((a.average-a.standard_deviation)*10)
/
(((c.average-c.standard_deviation) + (d.average-d.standard_deviation))/131072)) A
,
avg( (a.average*10)
/
((c.average + d.average)/131072)) "Average"
,
avg( ((a.average+a.standard_deviation)*10)
/
(((c.average+c.standard_deviation) + (d.average+d.standard_deviation))/131072)) B
from dba_hist_sysmetric_summary a,
dba_hist_sysmetric_summary b,
dba_hist_sysmetric_summary c,
dba_hist_sysmetric_summary d,
epsilon e
where a.metric_name='SQL Service Response Time'
and b.metric_name='User Calls Per Sec'
and c.metric_name='Logical Reads Per Sec'
and d.metric_name='Physical Reads Direct Per Sec'
and a.snap_id = b.snap_id
and a.snap_id = c.snap_id
and a.snap_id = d.snap_id
and b.average between e.m1 and e.m2
order by 1.

Database Efficiency

with epsilon
as
(select avg(average - STANDARD_DEVIATION ) m1,
avg(average + STANDARD_DEVIATION ) m2
from dba_hist_sysmetric_summary
where metric_name='User Calls Per Sec')
select avg(round(a.average + a.STANDARD_DEVIATION)) + stddev(round(a.average + a.STANDARD_DEVIATION)) A,
avg(round(a.average + (a.STANDARD_DEVIATION/2))) + stddev(round(a.average + (a.STANDARD_DEVIATION/2))) B,
avg(round(a.average)) C,
avg(round(a.average - (a.STANDARD_DEVIATION/2))) - stddev(round(a.average - (a.STANDARD_DEVIATION/2))) D,
avg(round(a.average - a.STANDARD_DEVIATION)) - stddev(round(a.average - a.STANDARD_DEVIATION)) E
from dba_hist_sysmetric_summary a,
dba_hist_sysmetric_summary b,
epsilon e
where a.metric_name='Database CPU Time Ratio'
and b.metric_name='User Calls Per Sec'
and a.snap_id = b.snap_id
and b.average between e.m1 and e.m2;
#!/bin/sh
#
vmstat 5 5
iostat

sqlplus -s??EOF
connect / as sysdba
set echo off
set feedback off
set lines 300
@$ORACLE_HOME/rdbms/admin/utllockt
EOF

adrci ??EOF
set base /udd001/app/oracle/admin/pbancs set home diag/rdbms/pbancs/pbancs show alert -p "message_text like 'ORA-%'"
EOF

sqlplus -s??EOF
connect / as sysdba
set lines 300
set feedback off
col start_time form a16
col end_time form a16
col time_taken form a5
col compress_ratio form a5
select status,
input_type,
output_device_type,
round(to_number(input_bytes)/1024/1024/1024) input_bytes,
to_char(round(compression_ratio,1),'99D9') compress_ratio,
to_char(start_time,'DD-MM-YYYY HH24:MI') start_time,
to_char(end_time,'DD-MM-YYYY HH24:MI') end_time,
substr(time_taken_display,1,5) time_taken
from V\$RMAN_BACKUP_JOB_DETAILS;
EOF

ps -ef | grep "expdp" | grep -v "grep"
ps -ef | grep "compress" | grep -v "grep"

sqlplus -s??EOF
connect / as sysdba
set lines 300
SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
clear column
set lines 400
Col opname form a30
col status format a9
set feedback off
select sid, serial#, sofar, totalwork, opname,
round(sofar/totalwork*100,2) "% Complete"
from v\$session_longops
where opname LIKE 'RMAN%'
and opname NOT LIKE '%aggregate%'
and totalwork != 0
and sofar <> totalwork;
EOF

sqlplus -s??EOF
connect / as sysdba
set lines 3000
clear columns
set feedback off
SELECT *
FROM
(SELECT status,
username,
sql_id,
sql_exec_id,
TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
ROUND(elapsed_time/1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000) AS "CPU (s)",
buffer_gets,
ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)",
ROUND(physical_write_bytes/(1024*1024)) AS "Phys writes (MB)"
FROM v\$sql_monitor
ORDER BY elapsed_time DESC
)
WHERE rownum<=20;
EOF

sqlplus -s??EOF
connect / as sysdba
set lines 300
set pages 30
clear columns
set feedback off
SELECT *
FROM v\$resource_limit;
EOF
HOME