oracle动态性能视图

----系统的统计---

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_timefrom(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_TIMEfrom(select WAIT_CLASS,        TOTAL_WAITS,        TIME_WAITEDfrom    V$SYSTEM_WAIT_CLASSwhere   WAIT_CLASS != 'Idle'),(select  sum(TOTAL_WAITS) SUM_WAITS,        sum(TIME_WAITED) SUM_TIMEfrom    V$SYSTEM_WAIT_CLASSwhere   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 bwhere   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_waitedfrom(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_timefrom    sys.v_$active_session_history a,        sys.v_$event_name b,        sys.dba_users cwhere   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_timefrom    sys.v_$active_session_history a,        sys.v_$event_name bwhere   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_timefrom    sys.v_$sqlareaorder 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;