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

作者：Robin Schumacher

 

过去，在努力获得最佳数据库性能的过程中，Oracle 数据库管理员和性能分析员为获得系统和用户会话活动的可靠响应时间度量历尽艰辛。数据库管理员所面临的问题通常包括两个方面：第一，准确确定数据库或用户会话将时间用在“何处”；第二，确定用户体验的客观本质。

对数据库中所有可能的活动和交互而言，这些任务是非常重要的。Oracle Wait Interface（早在多个版本前就引入了）一直是了解其用法的管理员的得力助手，但它在告知数据库管理员系统或用户会话处理事务或查询的效率方面还是不够理想。启用并研究跟踪文件可以获得此级别的详细信息，但对于大多数必须管理大型数据库群、工作负担较重的数据库管理员而言，此做法有点奢侈。

幸运的是，那些已经升级到 Oracle 数据库 10g 的 Oracle 数据库管理员将看到重大的响应时间改进，从而可以更好地描绘系统和会话级响应时间度量。最显著的是，Oracle 自动数据库诊断监视器 (ADDM) 通过自动分析收集的统计信息、标识问题区域甚至通过 Oracle 企业管理器网格控制 GUI 提供建议，使您详细了解响应时间及更多内容。

此外，与本文关系最为密切的是，Oracle 数据库 10g 中的历史机制使数据库管理员能够按时间回顾以执行他们自己的响应时间趋势分析，这些分析可以帮助他们确定高峰和非高峰事务/系统时间以及定位延长批处理周期或 ETL 作业的恶性进程和 SQL。

在本文中，我将探讨一些这种历史机制在系统级、会话级和 SQL 级中的使用。有关 ADDM 的更多信息，请参阅 以及 Arup Nanda 的“Oracle 数据库 10g：为 DBA 提供的 20 个最重要的特性”的“”部分。

系统级响应时间分析

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

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

在 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 请求的数据库。可以在找到有关 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
.
.

当然，您可以使用 V$SESS_TIME_MODEL 视图只专注于一个 SID，并获取会话的所有统计数据。还可以使用以下查询查看使用新等待类的当前会话：

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
--- ---------- --------------- ----------- ----------------
257 SYSMAN     Application          356104            75.22
255 SYSMAN     Commit                14508            25.76
257 SYSMAN     Commit                25026            22.02
257 SYSMAN     User I/O              11924            19.98
.
.
.

在此阶段过后，您可以像在 Oracle 的早期版本中那样使用 V$SESSION_WAIT 和 V$SESSION_EVENT 检查标准单个等待事件。您还将在 Oracle 数据库 10g 提供的两个修改过的视图中发现新等待类。

如果需要按时间回顾以发现哪些会话已被登录并正使用最多的资源，可以使用以下查询。在以下示例中，我们将查看从 2004 年 11 月 21 日午夜至凌晨 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 > '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_time
from    sys.v_$active_session_history a,
sys.v_$event_name b
where   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;

SESS_ID USERNAME PROGRAM    WAIT_EVENT                SESS_TIME PCT_TIME_WAITED
------- -------- ---------- ------------------------- ---------- -------------
242 SYS      exp@RHAT9K db file scattered read       3502978         33.49
242 SYS      oracle@RHA db file sequential read      2368153         22.64
242 SYS      oracle@RHA db file scattered read       1113896         10.65
243 SYS      oracle@RHA db file sequential read       992168          9.49

Oracle 数据库 10g V$ACTIVE_SESSION_HISTORY 视图在此处的作用是根据给定时间段提供一个会话执行期间的具有洞察性的按时间回顾的信息。此视图为您提供了大量宝贵的信息，而且不需要繁重的跟踪活动。我们将在下一节（介绍如何分析 SQL 语句的响应时间）中看到它的更多用法。

SQL 响应时间分析

详细研究 SQL 语句的响应时间在 Oracle9i 中变得更容易。使用 Oracle 数据库 10g，数据库管理员有很多工具可以使用来帮助他们跟踪低效的数据库代码。

过去，此处适用的 V$ 视图现在是 V$SQLAREA。从 Oracle9i 开始，Oracle 添加了 ELAPSED_TIME 和 CPU_TIME 列，它对于确定 SQL 语句执行的实际最终用户体验是一个巨大的帮助（至少是在按 EXECUTIONS 列 — 生成每次执行的平均时间长度 — 对其进行划分时是这样）。

在 Oracle 数据库 10g 中，已向 V$SQLAREA 中添加了六个新的与等待相关的时间列。

• APPLICATION_WAIT_TIME
• CONCURRENCY_WAIT_TIME
• CLUSTER_WAIT_TIME
• USER_IO_WAIT_TIME
• PLSQL_EXEC_TIME
• JAVA_EXEC_TIME

例如，这些新列有助于确定过程在 PL/SQL 代码和标准 SQL 执行中用去的时间量以及 SQL 语句是否经历了任何特殊的用户 I/O 等待。例如，一个可用于查找具有最高用户 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;

SQL_TEXT                  SQL_ID       ELAPSED_TIME CPU_TIME  USER_IO_WAIT_TIME
------------------------- ------------ ------------ ---------- ---------------
select /*+ rule */ bucket db78fxqxwxt7     47815369   19000939            3423
SELECT :"SYS_B_0" FROM SY agdpzr94rf6v     36182205   10170226            2649
select obj#,type#,ctime,m 04xtrk7uyhkn     28815527   16768040            1345
select grantee#,privilege 2q93zsrvbdw4     28565755   19619114             803
select /*+ rule */ bucket 96g93hntrzjt      9411028    3754542             606

当然，获得用时最多或等待时间最长的 SQL 语句是很好的，但您需要更多详细信息才能了解事情的本质 — 这正是 V$ACTIVE_SESSION_HISTORY 视图的另一个用武之地。使用此视图，您可以发现哪些实际的等待事件延迟了 SQL 语句以及导致等待的实际文件、对象和对象块（如果适用）。

例如，假设您发现了一个在用户 I/O 等待时间方面显得非常不足的特殊 SQL 语句。您可以发出以下查询来获取与查询关联的单个等待事件以及相应的等待时间、文件以及作为这些等待的源的对象：

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;

EVENT                     TIME_WAITED OWNER  OBJECT_NAME           file  block
------------------------- ----------- ------ --------------------- ---- ------
db file sequential read         27665 SYSMAN MGMT_METRICS_1HOUR_PK    3  29438
db file sequential read          3985 SYSMAN SEVERITY_PRIMARY_KEY     3  52877

当然，您可以按以往的方式使用 V$ACTIVE_SESSION_HISTORY 对一个特定时间段缩小未优化的 SQL 语句。但问题是相对于耗时的跟踪和摘要方法，Oracle 数据库 10g 使您可以更容易地使用简化的数据词典视图对 SQL 语句执行响应时间分析。

结论

管理 Oracle 数据库 10g 性能的数据库管理员和性能分析员将在 Oracle 的王牌数据库的最新版本中发现，他们多年来盼望已久的许多响应时间度量现在可唾手可得。此类统计信息将帮助加快在大型、复杂的数据库环境下查找所需信息的过程。