在 Oracle 数据库 10g中轻松进行响应时间分析（转载）

来源：http://testingtop.com/bbs/viewthread.php?tid=3838

 

从全局或系统级出发，数据库管理员通常想解决以下问题：

总的来讲，我的数据库运行状况如何？哪些因素影响效率？
我的用户获得的平均响应时间是多少？
哪些活动对总响应时间的影响最大？

在 Oracle 数据库 10g 推出之前，数据库管理员很难准确回答这些问题，但现在如果您碰巧正在使用最新、最可靠的 Oracle 数据库，则可以轻松获得这样的度量。

首先，通过在 Oracle 数据库 10g 中发出以下查询可以大体上获知数据库的运行状况：

select METRIC_NAME,
VALUE
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);
 

METRIC_NAME VALUE
------------------------------ ----------
Database Wait Time Ratio 6
Database CPU Time Ratio 94
 

Oracle 数据库 10g V$SYSMETRIC 视图包含几个优秀的响应时间度量，其中包括 Database Wait Time Ratio（数据库等待时间比）和 Database CPU Time Ratio（数据库 CPU 时间比）这两个时间度量。以上查询显示了这两个统计信息的最新快照，它们可帮助您确定数据库当前是正经历高等待/瓶颈比还是平稳运行的操作。数据库 CPU 时间比是用数据库消耗的 CPU 量除以“数据库时间”量 — 数据库在用户级调用（不包括实例后台进程活动）上花费的时间。高值（90%-95% 以上）比较理想并显示较少的等待/瓶颈操作，但由于每个系统各有不同，因此只可将此阈值看作是一个粗略的值。

还可以使用以下查询快速了解数据库在前一个小时运行时整体性能是否下降过：

select end_time,
value
from sys.v_$sysmetric_history
where metric_name = 'Database CPU Time Ratio'
order by 1;

END_TIME VALUE
-------------------- ----------
22-NOV-2004 10:00:38 98
22-NOV-2004 10:01:39 96
22-NOV-2004 10:02:37 99
22-NOV-2004 10:03:38 100
22-NOV-2004 10:04:37 99
22-NOV-2004 10:05:38 77
22-NOV-2004 10:06:36 100
22-NOV-2004 10:07:37 96
22-NOV-2004 10:08:39 100
.
.

同时，您可以使用如下查询来查询 V$SYSMETRIC_SUMMARY 视图以充分了解整个数据库效率的最小值、最大值和平均值：

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
 

METRIC_NAME MINIMUM MAXIMUM AVERAGE
------------------------------ ---------- ---------- ----------
CPU Usage Per Sec 0 7 1
CPU Usage Per Txn 1 29 8
Database CPU Time Ratio 61 100 94
Database Wait Time Ratio 0 39 5
Executions Per Sec 2 60 8
Executions Per Txn 16 164 41
Response Time Per Txn (secs) 0 .28 .08
SQL Service Response Time (sec 0 0 0
User Transaction Per Sec 0 1 0
 

除数据库 CPU 时间比和数据库等待时间比以外，以上查询还包含其他响应时间度量（稍后将对其进行介绍），但您可以认识到能够获取此信息的好处。对于这个特殊的实例，平均数据库 CPU 时间比为 94，它正好位于可接受的范围之内。
数据库管理员在系统级提出的下一个问题涉及其用户获得的响应时间的平均级别。（在 Oracle 数据库 10g 之前，很难获得此类数据，但现在情况就不同了。）通过以上所示的查询 V$SYSMETRIC_SUMMARY 视图的查询，我们了解到了需要了解的信息。如果用户对无法接受的响应时间产生抱怨，则数据库管理员可以检查 Response Time Per Txn 和 SQL Service Response Time 度量以查明是否存在数据库问题。例如，以上所示的统计信息报告每个用户事务的最大响应时间只有 .28/秒，而平均响应时间却为 .08/秒。这种情况下，Oracle 肯定不会受到指责。
 

但如果响应时间比所需的时间长，则数据库管理员需要了解哪些用户活动类型使数据库运行如此困难。同样，在 Oracle 数据库 10g 之前，此信息更难获得，但现在只需查询一下即可：
 

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;

DB_STAT TIME_SECS PCT_TIME
----------------------------- --------- --------
sql execute elapsed time 13263.707 45.84
PL/SQL execution elapsed time 13234.738 45.74
hard parse elapsed time 1943.687 6.72
soft parse time 520.584 1.8
.
.

以上的示例输出显示了一个将大部分时间用于处理 SQL 和 PL/SQL 请求的数据库。可以在0找到有关 V$SYS_TIME_MODEL 视图支持的所有统计信息的完整描述。
除活动时间外，数据库管理员还需要知道全局等待时间。在 Oracle 数据库 10g 之前，数据库管理员必须查看一个一个的等待事件来了解等待和瓶颈，但 Oracle 现在通过等待类为等待提供了一个摘要/汇总机制：

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 2245204 7.48 4839.43 54.39
System I/O 2438387 8.12 2486.21 27.94
Application 920385 3.07 513.56 5.77
Other 39962 .13 422.36 4.75
Commit 200872 .67 284.76 3.2
Network 24133213 80.38 162.26 1.82
Concurrency 6867 .02 102.63 1.15
Configuration 39377 .13 86.21 .97
 

例如，现在揭示批量总等待时间是由用户 I/O 等待而导致要比尝试和测量单个等待事件来获得全局映像要容易得多。与响应时间度量一样，您还可以使用如下所示的查询按时间回顾上一小时的情况：

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;
 

END_TIME WAIT_CLASS TIME_WAITED
-------------------- --------------- -----------
22-NOV-2004 11:28:37 Application 0
22-NOV-2004 11:28:37 Commit .02
22-NOV-2004 11:28:37 Concurrency 0
22-NOV-2004 11:28:37 Configuration 0
22-NOV-2004 11:28:37 Network .01
22-NOV-2004 11:28:37 Other 0
22-NOV-2004 11:28:37 System I/O .05
22-NOV-2004 11:28:37 User I/O 0