----系统的统计---
1v$sysmetric 当前系统的metric
2 v$sysmetric_history 1小时内的metric
3v$systemmetric_summary 1小时内的metric概览
4v$sys_time_model 系统各种类型所占的时间
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;5v$system_wait_class系统额等待事件
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;6查看系统等待事件
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;
7查看回话的等待事件和等待时间
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 > '21-NOV-04 12:00:00 AM' and sample_time < '21-NOV-04 05:00:00 AM' 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 > '21-NOV-04 12:00:00 AM' and sample_time < '21-NOV-04 05:00:00 AM' and b.wait_class = 'User I/O') order by 6 desc;
8查看耗io的sql
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;
9查看当前回话等待事件对应的对象信息
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 = '6gvch1xu9ca3g' and a.current_obj# = b.object_id and time_waited <> 0;