请问如果理解v$active_session_history中的wait_time和time_waited

不了峰

简单说 :
    一条sql语句出现在v$active_session_history ,我能否能过wait_time+time_waited来计算他的执行的总时长.

   还是要用在v$active_session_history中这条sql出现的count(*)值 ,来近似的估算 这个sql的总时长?

谢谢!

Liu Maclean(刘相兵

odm finding:

TIME_WAITED        NUMBER        If SESSION_STATE = WAITING, then the time that the session actually spent waiting for that EVENT. This column is set for waits that were in progress at the time the sample was taken.
If a wait event lasted for more than a second and was caught waiting in more than one session sample row, then the actual time spent waiting for that wait event will be populated in the last of those session sample rows. At any given time, this information will not be available for the latest session sample.



http://docs.oracle.com/cd/B19306 ... 7/dynviews_1007.htm

Liu Maclean(刘相兵

官方文档说的很清楚
If a wait event lasted for more than a second and was caught waiting in more than one session sample row, then the actual time spent waiting for that wait event will be populated in the last of those session sample rows.

若一次等待在ash中出现多行，按照最后一行的TIME_WAITED可以计算出actual time spent waiting for that wait event

不了峰

Liu Maclean(刘相兵 发表于 2015-3-2 15:09
官方文档说的很清楚
If a wait event lasted for more than a second and was caught waiting in more than ...

我能不能理解为 "按照最后一行的TIME_WAITED可以计算出actual time spent waiting for that wait event"  只是说明这个sql_id花在这个等待事件的时间, 而不是总消耗时长.

举个例子:
如果一条sql 执行了3秒种,这三秒中发生的行为比如有ON CPU ,和waiting .两种状态 . 即有3条记录保存在v$active_session_history .
我应该不能用wait_time + time_waited(或是最后一条的time_waited) 来得到这条sql 总共执行了多少时长吧 ?

谢谢!

Liu Maclean(刘相兵

ash 并不能真正“完全意义上”追踪一条SQL的运行， 所以上面说的是 若一次等待在ash中出现多行，按照最后一行的TIME_WAITED可以计算出actual time spent waiting for that wait event， 而不是SQL的耗时。

不了峰

Liu Maclean(刘相兵 发表于 2015-3-2 20:38
ash 并不能真正“完全意义上”追踪一条SQL的运行， 所以上面说的是 若一次等待在ash中出现多行，按照最后一 ...

OK,明白了

题外话,好象11g版本后v$active_session_history中多了一些字段,
如TM_DELTA_TIME , DELTA_TIME ,根据实际测试,感觉 近似过实际的SQL耗时.

枕霜卧雪

其实，如果对视图中列不明白的话，可以仔细看看Reference，里面讲的很明白。