Oracle时间统计模型中的一些相对高级的概念（重要）
过去，在努力获得最佳数据库性能的过程中，Oracle 数据库管理员和性能分析员为获得系统和用户会话活动的可靠响应时间度量历尽艰辛。数据库管理员所面临的问题通常包括两个方面：第一，准确确定数据库或用户会话将时间用在“何处”；第二，确定用户体验的客观本质。 对数据库中所有可能的活动和交互而言，这些任务是非常重要的。Oracle Wait Interface（早在多个版本前就引入了）一直是了解其用法的管理员的得力助手，但它在告知数据库管理员系统或用户会话处理事务或查询的效率方面还是不够理想。启用并研究跟踪文件可以获得此级别的详细信息，但对于大多数必须管理大型数据库群、工作负担较重的数据库管理员而言，此做法有点奢侈。 幸运的是，那些已经升级到 Oracle 数据库 10g 的 Oracle 数据库管理员将看到重大的响应时间改进，从而可以更好地描绘系统和会话级响应时间度量。最显著的是，Oracle 自动数据库诊断监视器 (ADDM) 通过自动分析收集的统计信息、标识问题区域甚至通过 Oracle 企业管理器网格控制 GUI 提供建议，使您详细了解响应时间及更多内容。 此外，与本文关系最为密切的是，Oracle 数据库 10g 中的历史机制使数据库管理员能够按时间回顾以执行他们自己的响应时间趋势分析，这些分析可以帮助他们确定高峰和非高峰事务/系统时间以及定位延长批处理周期或 ETL 作业的恶性进程和 SQL。 在本文中，我将探讨一些这种历史机制在系统级、会话级和 SQL 级中的使用。有关 ADDM 的更多信息，请参阅 Oracle 文档以及 Arup Nanda 的“Oracle 数据库 10g：为 DBA 提供的 20 个最重要的特性”的“ADDM 和 SQL Tuning Advisor”部分。 系统级响应时间分析 从全局或系统级出发，数据库管理员通常想解决以下问题：[*]总的来讲，我的数据库运行状况如何？哪些因素影响效率？
[*]我的用户获得的平均响应时间是多少？
[*]哪些活动对总响应时间的影响最大？
在 Oracle 数据库 10g 推出之前，数据库管理员很难准确回答这些问题，但现在如果您碰巧正在使用最新、最可靠的 Oracle 数据库，则可以轻松获得这样的度量。 首先，通过在 Oracle 数据库 10g 中发出以下查询可以大体上获知数据库的运行状况： select
METRIC_NAME,VALUEfrom
SYS.V_$SYSMETRICwhere
METRIC_NAME IN ('Database CPU Time Ratio','Database Wait Time Ratio') ANDINTSIZE_CSEC = (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC); METRIC_NAME
VALUE------------------------------ ----------Database Wait Time Ratio
6Database CPU Time Ratio
94Oracle 数据库 10g V$SYSMETRIC 视图包含几个优秀的响应时间度量，其中包括 Database Wait Time Ratio（数据库等待时间比）和 Database CPU Time Ratio（数据库 CPU 时间比）这两个时间度量。以上查询显示了这两个统计信息的最新快照，它们可帮助您确定数据库当前是正经历高等待/瓶颈比还是平稳运行的操作。数据库 CPU 时间比是用数据库消耗的 CPU 量除以“数据库时间”量 — 数据库在用户级调用（不包括实例后台进程活动）上花费的时间。高值（90%-95% 以上）比较理想并显示较少的等待/瓶颈操作，但由于每个系统各有不同，因此只可将此阈值看作是一个粗略的值。 还可以使用以下查询快速了解数据库在前一个小时运行时整体性能是否下降过： select
end_time,valuefrom
sys.v_$sysmetric_historywhere
metric_name = 'Database CPU Time Ratio'order by 1; END_TIME
VALUE-------------------- ----------22-NOV-2004 10:00:38
9822-NOV-2004 10:01:39
9622-NOV-2004 10:02:37
9922-NOV-2004 10:03:38
10022-NOV-2004 10:04:37
9922-NOV-2004 10:05:38
7722-NOV-2004 10:06:36
10022-NOV-2004 10:07:37
9622-NOV-2004 10:08:39
100..同时，您可以使用如下查询来查询 V$SYSMETRIC_SUMMARY 视图以充分了解整个数据库效率的最小值、最大值和平均值： select
CASE METRIC_NAMEWHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'ELSE METRIC_NAMEEND METRIC_NAME,
CASE METRIC_NAMEWHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2)WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2)ELSE MINVALEND MININUM,
CASE METRIC_NAMEWHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2)WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2)ELSE MAXVALEND MAXIMUM,
CASE METRIC_NAMEWHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2)WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2)ELSE AVERAGEEND AVERAGEfrom
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
1CPU Usage Per Txn
1
29
8Database CPU Time Ratio
61
100
94Database Wait Time Ratio
0
39
5Executions Per Sec
2
60
8Executions Per Txn
16
164
41Response Time Per Txn (secs)
0
.28
.08SQL Service Response Time (sec
0
0
0User 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_namewhen 'parse time elapsed' then 'soft parse time'else db_stat_nameend db_stat_name,case db_stat_namewhen 'sql execute elapsed time' then time_secs - plsql_time when 'parse time elapsed' then time_secs - hard_parse_timeelse time_secsend time_secs,case db_stat_namewhen '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_timefrom(select stat_name db_stat_name,round((value / 1000000),3) time_secsfrom sys.v_$sys_time_modelwhere 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_TIMEfrom(select WAIT_CLASS,TOTAL_WAITS,TIME_WAITEDfrom
V$SYSTEM_WAIT_CLASSwhere
WAIT_CLASS != 'Idle'),(select
sum(TOTAL_WAITS) SUM_WAITS,sum(TIME_WAITED) SUM_TIMEfrom
V$SYSTEM_WAIT_CLASSwhere
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.39System I/O
2438387
8.12
2486.21
27.94Application
920385
3.07
513.56
5.77Other
39962
.13
422.36
4.75Commit
200872
.67
284.76
3.2Network
24133213
80.38
162.26
1.82Concurrency
6867
.02
102.63
1.15Configuration
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 bwhere
a.wait_class# = b.wait_class# andb.wait_class != 'Idle'order by 1,2; END_TIME
WAIT_CLASS
TIME_WAITED-------------------- --------------- -----------22-NOV-2004 11:28:37 Application
022-NOV-2004 11:28:37 Commit
.0222-NOV-2004 11:28:37 Concurrency
022-NOV-2004 11:28:37 Configuration
022-NOV-2004 11:28:37 Network
.0122-NOV-2004 11:28:37 Other
022-NOV-2004 11:28:37 System I/O
.0522-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_secsfrom
sys.v_$session_wait_class a,sys.v_$session bwhere
b.sid = a.sid andb.username is not null anda.wait_class != 'Idle'order by 5 desc; SID USERNAME
WAIT_CLASS
TOTAL_WAITS TIME_WAITED_SECS--- ---------- --------------- ----------- ----------------257 SYSMAN
Application
356104
75.22255 SYSMAN
Commit
14508
25.76257 SYSMAN
Commit
25026
22.02257 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_waitedfrom(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_timefrom
sys.v_$active_session_history a,sys.v_$event_name b,sys.dba_users cwhere
a.event# = b.event# anda.user_id = c.user_id andsample_time > '21-NOV-04 12:00:00 AM' and sample_time < '21-NOV-04 05:00:00 AM' andb.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_timefrom
sys.v_$active_session_history a,sys.v_$event_name bwhere
a.event# = b.event# andsample_time > '21-NOV-04 12:00:00 AM' and sample_time < '21-NOV-04 05:00:00 AM' andb.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.49242 SYS
oracle@RHA db file sequential read
2368153
22.64242 SYS
oracle@RHA db file scattered read
1113896
10.65243 SYS
oracle@RHA db file sequential read
992168
9.49Oracle 数据库 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_timefrom
sys.v_$sqlareaorder by 5 desc)where rownum < 6; SQL_TEXT
SQL_ID
ELAPSED_TIME CPU_TIME
USER_IO_WAIT_TIME------------------------- ------------ ------------ ---------- ---------------select /*+ rule */ bucket db78fxqxwxt7
47815369
19000939
3423SELECT :"SYS_B_0" FROM SY agdpzr94rf6v
36182205
10170226
2649select obj#,type#,ctime,m 04xtrk7uyhkn
28815527
16768040
1345select grantee#,privilege 2q93zsrvbdw4
28565755
19619114
803select /*+ 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' anda.current_obj# = b.object_id andtime_waited <> 0; EVENT
TIME_WAITED OWNER
OBJECT_NAME
file
block------------------------- ----------- ------ --------------------- ---- ------db file sequential read
27665 SYSMAN MGMT_METRICS_1HOUR_PK
3
29438db file sequential read
3985 SYSMAN SEVERITY_PRIMARY_KEY
3
52877当然，您可以按以往的方式使用 V$ACTIVE_SESSION_HISTORY 对一个特定时间段缩小未优化的 SQL 语句。但问题是相对于耗时的跟踪和摘要方法，Oracle 数据库 10g 使您可以更容易地使用简化的数据词典视图对 SQL 语句执行响应时间分析。 结论 管理 Oracle 数据库 10g 性能的数据库管理员和性能分析员将在 Oracle 的王牌数据库的最新版本中发现，他们多年来盼望已久的许多响应时间度量现在可唾手可得。此类统计信息将帮助加快在大型、复杂的数据库环境下查找所需信息的过程。
页:
[1]