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


本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/bluestream/archive/2008/09/28/2990631.aspx