oracle数据库分析响应时间

訾梓婷

--查看数据库响应状况
select *
  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)

--查看数据库的总性能
select *
  from sys.v_$sysmetric_history
where metric_name = 'Database CPU Time Ratio'
order by 1

--获得数据库整体性能效率的最大、最小和平均值
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

--查看究竟是什么类型的用户活动影响数据库的响应速度,找到相应的主要花费时间处理的部分
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

--等待时间中的等待事件，等待的文件，等待的对象
select sql_id,
       event as "等待事件",
       time_waited,
       owner,
       object_name as "等待的对象",
       current_file# as "等待的文件",
       current_block#
  from sys.v_$active_session_history a, sys.dba_objects b
where a.current_obj# = b.object_id
   and time_waited <> 0

--前5位用户I/O等待最高的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

--查看等待事件来找出等待和瓶颈,找出大部分的整体等待时间
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

--最新的一个小时等待类型
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

--找出耗费资源比较多的SQL语句
SELECT first_load_time,
       SQL_TEXT,
       buffer_gets,
       executions,
       buffer_gets/executions AVG
FROM   v$sqlarea
WHERE  executions>0
AND    buffer_gets > 100000
ORDER BY 5

--找出需要大量缓冲读取（逻辑读）操作的查询
select *
  from (select sql_text,
               buffer_gets,
               dense_rank() over(order by buffer_gets desc) buffer_gets_rank,optimizer_cost
          from v$sql)
where buffer_gets_rank <= 10

--V$SQL是内存共享SQL区域中已经解析的SQL语句。
--列出使用频率最高的5个查询
select sql_text, executions
  from (select sql_text,
               executions,
               rank() over(order by executions desc) exec_rank
          from v$sql)
where exec_rank <= 5

--消耗磁盘读取最多的sql
select disk_reads, sql_text
  from (select sql_text,
               disk_reads,
               dense_rank() over(order by disk_reads desc) disk_reads_rank
          from v$sql)
where disk_reads_rank <= 5


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/bluestream/archive/2008/09/28/2990631.aspx