Oracle Performance and Availability System-Level Response-Time Analysis

Oracle Performance and Availability System-Level Response-Time Analysis

Oracle Database Performance
Oracle SYSMETRIC
general database health


select METRIC_NAME, VALUE from SYS.V_$SYSMETRIC
where METRIC_NAME IN ('Database CPU Time Ratio','Database Wait Time Ratio')
AND INTSIZE_CSEC = (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);


METRIC_NAME VALUE
------------------------------ ----------
Database Wait Time Ratio 6
Database CPU Time Ratio 94



prompt experienced any dips in overall performance by using this query

select end_time, value from sys.v_$sysmetric_history
where metric_name = 'Database CPU Time Ratio'
order by 1;


prompt overall database efficiency minimum, maximum, and average values by querying the V$SYSMETRIC_SUMMARY

select CASE METRIC_NAME
WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'
WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2)
WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2)
WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2)
WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2)
ELSE AVERAGE
END AVERAGE
from SYS.V_$SYSMETRIC_SUMMARY
where METRIC_NAME in ('CPU Usage Per Sec',
'CPU Usage Per Txn',
'Database CPU Time Ratio',
'Database Wait Time Ratio',
'Executions Per Sec',
'Executions Per Txn',
'Response Time Per Txn',
'SQL Service Response Time',
'User Transaction Per Sec')
ORDER BY 1





prompt what types of user activities are responsible

select case db_stat_name
when 'parse time elapsed' then
'soft parse time'
else db_stat_name
end db_stat_name,
case db_stat_name
when 'sql execute elapsed time' then
time_secs - plsql_time
when 'parse time elapsed' then
time_secs - hard_parse_time
else time_secs
end time_secs,
case db_stat_name
when 'sql execute elapsed time' then
round(100 * (time_secs - plsql_time) / db_time,2)
when 'parse time elapsed' then
round(100 * (time_secs - hard_parse_time) / db_time,2)
else round(100 * time_secs / db_time,2)
end pct_time
from
(select stat_name db_stat_name,
round((value / 1000000),3) time_secs
from sys.v_$sys_time_model
where stat_name not in('DB time','background elapsed time','background cpu time','DB CPU')),
(select round((value / 1000000),3) db_time
from sys.v_$sys_time_model
where stat_name = 'DB time'),
(select round((value / 1000000),3) plsql_time
from sys.v_$sys_time_model
where stat_name = 'PL/SQL execution elapsed time'),
(select round((value / 1000000),3) hard_parse_time
from sys.v_$sys_time_model
where stat_name = 'hard parse elapsed time')
order by 2 desc;



prompt global wait times


select WAIT_CLASS,
TOTAL_WAITS,
round(100 * (TOTAL_WAITS / SUM_WAITS),2) PCT_WAITS,
ROUND((TIME_WAITED / 100),2) TIME_WAITED_SECS,
round(100 * (TIME_WAITED / SUM_TIME),2) PCT_TIME
from
(select WAIT_CLASS,
TOTAL_WAITS,
TIME_WAITED
from V$SYSTEM_WAIT_CLASS
where WAIT_CLASS != 'Idle'),
(select sum(TOTAL_WAITS) SUM_WAITS,
sum(TIME_WAITED) SUM_TIME
from V$SYSTEM_WAIT_CLASS
where WAIT_CLASS != 'Idle')
order by 5 desc;



prompt wait class


select to_char(a.end_time,'DD-MON-YYYY HH:MI:SS') end_time,
b.wait_class,
round((a.time_waited / 100),2) time_waited
from sys.v_$waitclassmetric_history a,
sys.v_$system_wait_class b
where a.wait_class# = b.wait_class# and
b.wait_class != 'Idle'
order by 1,2;


select a.sid,
b.username,
a.wait_class,
a.total_waits,
round((a.time_waited / 100),2) time_waited_secs
from sys.v_$session_wait_class a,
sys.v_$session b
where b.sid = a.sid and
b.username is not null and
a.wait_class != 'Idle'
order by 5 desc;






prompt at given time

set linesize 200
col WAIT_EVENT format a20
col WAIT_EVENT format a30
col USERNAME format a15

select sess_id,
username,
program,
wait_event,
sess_time,
round(100 * (sess_time / total_time),2) pct_time_waited
from
(select a.session_id sess_id,
decode(session_type,'background',session_type,c.username) username,
a.program program,
b.name wait_event,
sum(a.time_waited) sess_time
from sys.v_$active_session_history a,
sys.v_$event_name b,
sys.dba_users c
where a.event# = b.event# and
a.user_id = c.user_id and
sample_time > to_date('16-AUG-11 12:00:00','dd-mon-yy hh:mi:ss') and
sample_time < to_date('17-AUG-11 07:00:00','dd-mon-yy hh:mi:ss') and
b.wait_class = 'User I/O'
group by a.session_id, decode(session_type,'background',session_type,c.username),a.program, b.name),
(select sum(a.time_waited) total_time
from sys.v_$active_session_history a,
sys.v_$event_name b
where a.event# = b.event#
and sample_time > to_date('16-AUG-11 12:00:00','dd-mon-yy hh:mi:ss')
and sample_time < to_date('17-AUG-11 07:00:00','dd-mon-yy hh:mi:ss')
and b.wait_class = 'User I/O')
order by 6 desc;





select *
from
(select sql_text,
sql_id,
elapsed_time,
cpu_time,
user_io_wait_time
from sys.v_$sqlarea
order by 5 desc)
where rownum < 6;





select event,
time_waited,
owner,
object_name,
current_file#,
current_block#
from sys.v_$active_session_history a,
sys.dba_objects b
where sql_id = '&sql_id'
and a.current_obj# = b.object_id
and time_waited <> 0;