Oracle从10g开始已经构造了相对完整的响应时间分析方法,并且分别在系统全局,session和SQL级别做了分别的处理,已经比较完善。
回到吞吐量曲线和响应时间公式:
RT = Service Time + Queue Time(Wait Time)
视图:v$sys_time_model,提供了系统级别的响应时间,并且对于响应时间构成进行了分解。
视图:v$sess_time_model,提供了Session级别的响应时间,并且对于响应时间构成进行了分解。
视图:V$SQL,提供了SQL级别的响应时间
另外v$active_session_history事实上提供了某一时刻的Session或者某一SQL的响应时间状况。
在AWR报告中直接提供了TBA分析的内容:
Time Model Statistics DB/Inst: CRMDB/crmdb2 Snaps: 38468-38469
-> Total time in database user-calls (DB Time): 16613.5s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 16,497.9 99.3
DB CPU 6,157.6 37.1
connection management call elapsed time 63.2 .4
parse time elapsed 58.2 .4
hard parse elapsed time 42.6 .3
PL/SQL execution elapsed time 5.0 .0
hard parse (sharing criteria) elapsed time 0.8 .0
sequence load elapsed time 0.8 .0
repeated bind elapsed time 0.4 .0
hard parse (bind mismatch) elapsed time 0.2 .0
failed parse elapsed time 0.1 .0
PL/SQL compilation elapsed time 0.1 .0
DB time 16,613.5 N/A
background elapsed time 600.0 N/A
background cpu time 512.9 N/A
-------------------------------------------------------------
DB time + background elapsed time构成了数据库的响应时间消耗
DB CPU + background cpu time构成了服务时间
一般情况下,background elapsed time和background cpu time可以不用考虑,并不会影响到业务流程的响应时间。
这样的RT公式为:
DB time = DB CPU + (DB time - DB CPU)
service time = DB CPU
queue time = DB time - DB CPU
DB time(响应时间可以进行进一步的阶段分解):
Connect
Parse
Execute
Connect:
connection management call elapsed time
对于数据库登录流程,这里将会反映的比较明显。
Parse
Parse elapsed time
Hard Parse elapsed time
Execute
SQL Execute elapsed time
PLSQL Execute elapsed time
从以上计算出来的Queue time可能和Oracle记录waiting可能不是完全一致,其中可能会包含些非SQL,非PLSQL,包含在DB time中,但没有包含在DB CPU中,比如10046 trace跟踪导致的额外开销。
响应时间引擎的精确度要远远超过独立事件计算的精确度,在事件模型中小于10ms的操作将不计数,而时间模型以微秒进行计算。
我们从实际数据上看看:
5分钟的快照
DB time:= 69.72
DB CPU:= 6.99
我们通过以下查询计算总的事件消耗时间:
select /*B.NAME,*/B.WAIT_CLASS,sum(time_waited) from (select s2.event_id,s2.time_waited_micro_fg-s1.time_waited_micro_fg time_waited from wrh$_system_event s1,wrh$_system_event s2
where s2.event_id=s1.event_id(+) and s2.snap_id=12 and s1.snap_id=11) a,v$event_name b where a.event_ID=B.EVENT_ID GROUP BY /*B.NAME,*/B.WAIT_CLASS;
1 Concurrency 0
2 User I/O 57939866
3 System I/O 259302
4 Administrative 0
5 Other 202998
6 Application 203120
7 Idle 18704031458
8 Network 7880
9 Commit 2925940
合计的Queue Time:= 61539106 = 61.53
计算的Queue Time:= 69.72-6.99 = 62.73
有必要通过更多的案例来验证两者是否一致,注意v$system_event包含两个字段:
SQL> desc v$system_event
Name Type Nullable Default Comments
-------------------- ------------ -------- ------- --------
EVENT VARCHAR2(64) Y
TOTAL_WAITS NUMBER Y
TOTAL_TIMEOUTS NUMBER Y
TIME_WAITED NUMBER Y
AVERAGE_WAIT NUMBER Y
TIME_WAITED_MICRO NUMBER Y 微秒
TOTAL_WAITS_FG NUMBER Y
TOTAL_TIMEOUTS_FG NUMBER Y
TIME_WAITED_FG NUMBER Y 10ms
AVERAGE_WAIT_FG NUMBER Y
TIME_WAITED_MICRO_FG NUMBER Y
EVENT_ID NUMBER Y
WAIT_CLASS_ID NUMBER Y
WAIT_CLASS# NUMBER Y
WAIT_CLASS VARCHAR2(64) Y
40 Other 2013/10/29 21:26:39 2013/10/29 21:27:38 0.274780281645145 2.2191
41 Application 2013/10/29 21:26:39 2013/10/29 21:27:38 0.00539877440391525 0.0436
42 Configuration 2013/10/29 21:26:39 2013/10/29 21:27:38 0 0
43 Administrative 2013/10/29 21:26:39 2013/10/29 21:27:38 0 0
44 Concurrency 2013/10/29 21:26:39 2013/10/29 21:27:38 1.07319215042967 8.667
45 Commit 2013/10/29 21:26:39 2013/10/29 21:27:38 3.72340840477732 30.0699
46 Idle 2013/10/29 21:26:39 2013/10/29 21:27:38 0 470169.9435
47 Network 2013/10/29 21:26:39 2013/10/29 21:27:38 0.0335194548426573 0.2707
48 User I/O 2013/10/29 21:26:39 2013/10/29 21:27:38 82.7941678872726 668.638
49 System I/O 2013/10/29 21:26:39 2013/10/29 21:27:38 10.6458754416067 85.9751
50 Scheduler 2013/10/29 21:26:39 2013/10/29 21:27:38 0 0
51 Cluster 2013/10/29 21:26:39 2013/10/29 21:27:38 0 0
52 Queueing 2013/10/29 21:26:39 2013/10/29 21:27:38 0 0
可以看到,在业务运行期间,dbtime_in_wait和time_waited有所差异,一般是time_waiteg比较dbtimewso小
回到吞吐量曲线和响应时间公式:
RT = Service Time + Queue Time(Wait Time)
视图:v$sys_time_model,提供了系统级别的响应时间,并且对于响应时间构成进行了分解。
视图:v$sess_time_model,提供了Session级别的响应时间,并且对于响应时间构成进行了分解。
视图:V$SQL,提供了SQL级别的响应时间
另外v$active_session_history事实上提供了某一时刻的Session或者某一SQL的响应时间状况。
在AWR报告中直接提供了TBA分析的内容:
Time Model Statistics DB/Inst: CRMDB/crmdb2 Snaps: 38468-38469
-> Total time in database user-calls (DB Time): 16613.5s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 16,497.9 99.3
DB CPU 6,157.6 37.1
connection management call elapsed time 63.2 .4
parse time elapsed 58.2 .4
hard parse elapsed time 42.6 .3
PL/SQL execution elapsed time 5.0 .0
hard parse (sharing criteria) elapsed time 0.8 .0
sequence load elapsed time 0.8 .0
repeated bind elapsed time 0.4 .0
hard parse (bind mismatch) elapsed time 0.2 .0
failed parse elapsed time 0.1 .0
PL/SQL compilation elapsed time 0.1 .0
DB time 16,613.5 N/A
background elapsed time 600.0 N/A
background cpu time 512.9 N/A
-------------------------------------------------------------
DB time + background elapsed time构成了数据库的响应时间消耗
DB CPU + background cpu time构成了服务时间
一般情况下,background elapsed time和background cpu time可以不用考虑,并不会影响到业务流程的响应时间。
这样的RT公式为:
DB time = DB CPU + (DB time - DB CPU)
service time = DB CPU
queue time = DB time - DB CPU
DB time(响应时间可以进行进一步的阶段分解):
Connect
Parse
Execute
Connect:
connection management call elapsed time
对于数据库登录流程,这里将会反映的比较明显。
Parse
Parse elapsed time
Hard Parse elapsed time
Execute
SQL Execute elapsed time
PLSQL Execute elapsed time
从以上计算出来的Queue time可能和Oracle记录waiting可能不是完全一致,其中可能会包含些非SQL,非PLSQL,包含在DB time中,但没有包含在DB CPU中,比如10046 trace跟踪导致的额外开销。
响应时间引擎的精确度要远远超过独立事件计算的精确度,在事件模型中小于10ms的操作将不计数,而时间模型以微秒进行计算。
我们从实际数据上看看:
5分钟的快照
DB time:= 69.72
DB CPU:= 6.99
我们通过以下查询计算总的事件消耗时间:
select /*B.NAME,*/B.WAIT_CLASS,sum(time_waited) from (select s2.event_id,s2.time_waited_micro_fg-s1.time_waited_micro_fg time_waited from wrh$_system_event s1,wrh$_system_event s2
where s2.event_id=s1.event_id(+) and s2.snap_id=12 and s1.snap_id=11) a,v$event_name b where a.event_ID=B.EVENT_ID GROUP BY /*B.NAME,*/B.WAIT_CLASS;
1 Concurrency 0
2 User I/O 57939866
3 System I/O 259302
4 Administrative 0
5 Other 202998
6 Application 203120
7 Idle 18704031458
8 Network 7880
9 Commit 2925940
合计的Queue Time:= 61539106 = 61.53
计算的Queue Time:= 69.72-6.99 = 62.73
有必要通过更多的案例来验证两者是否一致,注意v$system_event包含两个字段:
SQL> desc v$system_event
Name Type Nullable Default Comments
-------------------- ------------ -------- ------- --------
EVENT VARCHAR2(64) Y
TOTAL_WAITS NUMBER Y
TOTAL_TIMEOUTS NUMBER Y
TIME_WAITED NUMBER Y
AVERAGE_WAIT NUMBER Y
TIME_WAITED_MICRO NUMBER Y 微秒
TOTAL_WAITS_FG NUMBER Y
TOTAL_TIMEOUTS_FG NUMBER Y
TIME_WAITED_FG NUMBER Y 10ms
AVERAGE_WAIT_FG NUMBER Y
TIME_WAITED_MICRO_FG NUMBER Y
EVENT_ID NUMBER Y
WAIT_CLASS_ID NUMBER Y
WAIT_CLASS# NUMBER Y
WAIT_CLASS VARCHAR2(64) Y
40 Other 2013/10/29 21:26:39 2013/10/29 21:27:38 0.274780281645145 2.2191
41 Application 2013/10/29 21:26:39 2013/10/29 21:27:38 0.00539877440391525 0.0436
42 Configuration 2013/10/29 21:26:39 2013/10/29 21:27:38 0 0
43 Administrative 2013/10/29 21:26:39 2013/10/29 21:27:38 0 0
44 Concurrency 2013/10/29 21:26:39 2013/10/29 21:27:38 1.07319215042967 8.667
45 Commit 2013/10/29 21:26:39 2013/10/29 21:27:38 3.72340840477732 30.0699
46 Idle 2013/10/29 21:26:39 2013/10/29 21:27:38 0 470169.9435
47 Network 2013/10/29 21:26:39 2013/10/29 21:27:38 0.0335194548426573 0.2707
48 User I/O 2013/10/29 21:26:39 2013/10/29 21:27:38 82.7941678872726 668.638
49 System I/O 2013/10/29 21:26:39 2013/10/29 21:27:38 10.6458754416067 85.9751
50 Scheduler 2013/10/29 21:26:39 2013/10/29 21:27:38 0 0
51 Cluster 2013/10/29 21:26:39 2013/10/29 21:27:38 0 0
52 Queueing 2013/10/29 21:26:39 2013/10/29 21:27:38 0 0
可以看到,在业务运行期间,dbtime_in_wait和time_waited有所差异,一般是time_waiteg比较dbtimewso小
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/92650/viewspace-775284/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/92650/viewspace-775284/