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;
DB_STAT_NAME TIME_SECS PCT_TIME
---------------------------------------------------------------- ---------- ----------
sql execute elapsed time 65.644 89.7
hard parse elapsed time 26.661 36.43
PL/SQL execution elapsed time 12.766 17.44
PL/SQL compilation elapsed time 6.353 8.68
soft parse time 2.15 2.94
connection management call elapsed time 1.084 1.48
hard parse (sharing criteria) elapsed time 0.448 0.61
repeated bind elapsed time 0.026 0.04
failed parse elapsed time 0.009 0.01
hard parse (bind mismatch) elapsed time 0.002 0
RMAN cpu time (backup/restore) 0 0
inbound PL/SQL rpc elapsed time 0 0
sequence load elapsed time 0 0
Java execution elapsed time 0 0
failed parse (out of shared memory) elapsed time 0 0
可以在V$SYS_TIME_MODEL视图中找到相应的主要花费时间处理的部分,然后就可以根据这些来对数据库进行相应的调整。
除了活动时间,DBA也还想知道整体的等待时间。在Oracle10g数据库之前,
DBA必须查看单独的等待事件来找出等待和瓶颈,现在Oracle10g数据库提供一个等待的概要机制。
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;
WAIT_CLASS TOTAL_WAITS PCT_WAITS TIME_WAITED_SECS PCT_TIME
---------------------- ----------- ---------- ---------------- ----------
User I/O 5748 61.71 67.57 65.79
Other 182 1.95 16.85 16.41
System I/O 2975 31.94 11.27 10.97
Concurrency 114 1.22 6.76 6.58
Commit 61 0.65 0.22 0.21
Network 233 2.5 0.03 0.03
Application 2 0.02 0 0
这样就能非常容易的找出大部分的整体等待时间。如同响应时间数据一样, 我们可以用下面的查询来及时回顾最新的一个小时等待类型：
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;
SID USERNAME WAIT_CLASS TOTAL_WAITS TIME_WAITED_SECS
----- -------------- -------------- ----------- ----------------
38 SYS User I/O 22 0.19
48 SYS User I/O 15 0.12
38 SYS Network 21 0.01
48 SYS Network 24 0
38 SYS Application 2 0
这个时候,就可以检查标准的单独等待事件就如在以前版本的Oracle数据库中查询V$SESSION_WAIT和V$SESSION_EVENT视图。
在Oracle10g数据库中DBA还将可以找出新的等待类型在这两张视图中。 如果需要找出以前哪个会话登录并且消耗了大部分的资源,
你可以使用下面的查询,下面的例子是查找午夜12点到5点的数据库活动,并且包括用户的I/O等待。
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 > '22-JAN-07 12：00：00 AM' and sample_time < '22-JAN-07 05：00：00 AM' and
