【性能调整】等待事件(一)

最新推荐文章于 2024-10-08 15:41:48 发布

cixlv48826

最新推荐文章于 2024-10-08 15:41:48 发布

阅读量121

点赞数

文章标签：数据库

分析数据库性能的一个很重要的指标就是分析数据库中各种动作的运行时间和等待运行的时间。

前面提到过的几个视图，例如v$session_wait,v$session_event,v$system_event，提供了哪些资源正被等待的信息，如果还设置了参数timed_statistics=true，则也有每种资源的等待时间的信息。

（注：数据库的各项时间统计信息在设置了statistics_level=typical或者all的时候，会自动收集，如果这个蚕食设置为basic，那么必须设置timed_statistics=true来允许收集时间信息。

如果明确的设置下列参数或者使用alter session/system设置，那么statistics_level也将会被覆盖：

SQL> show parameter DB_CACHE_ADVICE;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_cache_advice string ON

SQL> show parameter TIMED_STATISTICS;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

timed_statistics boolean TRUE

SQL> show parameter TIMED_OS_STATISTICS

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

timed_os_statistics integer 0

SQL>

）

v$session_wait 是一个当前状态的视图，列出了每个session当前wait或者上一次wait的event

v$session_event 列出的是每个session的event wait的历史累计值，当一个session推出以后，这个session上的等待事件信息将会从这个视图中移除。

V$ssystem_event 列出系统所有等待的事件和时间，是从instance startup开始起的(也即所有的session wait 事件数据的总计)

因为v$session_wait是一个当前状态视图，因此包含了更细粒度的信息，它包含了当前事件的一些属性数据，列：P1,P2,P3。

例如，v$session_event给出了某个session下有大量的db file scattered read event等待，但是并没有给出是哪个file或者block号码，但v$session_wait就给出了file的number(P1)号，block的number(P2)号,和block的数量。

而v$event_name则是所有event的说明，包括了event#，每个event的p1,p2,p3字段的解释，等待的wait_class_id,wait_class#和wait class说明(10g新增字段)。

10g开始还提供一些新的视图，也提供等待的相关信息：

V$system_wait_class

V$session_wait_class

V$session_wait_history

V$event_histogram

V$active_session_history

可以查看按照wait的分类(10g)：

SQL> select distinct wait_class#,wait_class from v$event_name a order by wait_class# ;

WAIT_CLASS# WAIT_CLASS

----------- ----------------------------------------------------------------

0 Other

1 Application

2 Configuration

3 Administrative

4 Concurrency

5 Commit

6 Idle

7 Network

8 User I/O

9 System I/O

10 Scheduler

11 Cluster

12 rows selected.

SQL> desc v$system_event

Name Null? Type

----------------------------------------- -------- ----------------------------

EVENT VARCHAR2(64)

TOTAL_WAITS NUMBER

TOTAL_TIMEOUTS NUMBER 百分之一秒

TIME_WAITED NUMBER 百分之一秒

AVERAGE_WAIT NUMBER 百分之一秒

TIME_WAITED_MICRO NUMBER 9i开始(百万分之一秒)

EVENT_ID NUMBER 10g

WAIT_CLASS_ID NUMBER 10g

WAIT_CLASS# NUMBER 10g

WAIT_CLASS VARCHAR2(64) 10g

其中total_waits包括了session在此事件上等待的次数，total_timeouts列记录了session在初始的wait之后没能获得请求的资源的次数，time_waited是在event上花费的总时间，average_wait则是每次等待的平均时间： time_waited/total_waits。

如果需要做个快速的系统性能check，可以查看v$system_event，哪怕对系统或者应用并不完全熟悉。可以定位自数据库实例启动以来的top n瓶颈。

注意到：

1，V$system_event视图提供instance级别的wait event信息。在这个级别上并不提供主要瓶颈发生的session信息，并不能将视图中某个很差的event与某个具体的session当前运行非常缓慢来关联。

2，应该总是使用time_waited列来排序，而不是使用total_waited来排序，因为性能问题中绝对的时间大小总是关注的重点。当然，一些等待有很高的total_waits，而每个wait很短，比如latch free,这时候time_waited可能不是最重要的。而一些等待有很低的total_waits，但是每个等待的时间可能较长，比如enqueue（入队）。

3，注意instance startup time，来评估wait time是否合理，不需要被很大的time_waited值吓倒。

给出几个sql：

通常查看v$system_event需要加上instance的startup_time

select a.*, b.startup_time

from v$system_event a, v$instance b

order by a.TIME_WAITED desc;

select c.WAIT_CLASS,

c.name,

a.TOTAL_WAITS,

trunc(a.TOTAL_TIMEOUTS/100)||' S' TOTAL_TIMEOUTS_s,

trunc(a.TIME_WAITED/100)||' S' time_waited_s,

trunc(a.AVERAGE_WAIT/100)||' S' AVERAGE_WAIT,

b.startup_time

from v$system_event a, v$instance b, v$event_name c

where a.EVENT = c.name

order by a.TIME_WAITED desc;

注意到average_wait一列的相关events(user i/o和system i/o)，这些指标能够体现io子系统的速度，更重要的，它体现了系统的平均io cost。这意味着当有更高的io calls的时候，如果平均的io cost相对降低，那么也是可以接受的。

SQL> select a.EVENT,a.TOTAL_WAITS,a.TIME_WAITED,a.AVERAGE_WAIT,a.WAIT_CLASS

2 from v$system_event a where a.EVENT in

3 ('db file sequential read','db file scattered read');

EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT WAIT_CLASS

---------------------------------------------------------------- ----------- ----------- ------------ -----------

db file sequential read 8313 8347 1 User I/O

db file scattered read 1249 1395 1.12 User I/O

可以看到系统的平均单块读和多块读的数量，1/100=10ms，1.12/100=11.2ms

可以稍稍看看这个数字的变化，下面的测试使用的t_policy有1万条数据，且policy_id上有唯一索引，强制走索引，看看序列读的相关值的变化：

SQL> set autot on

SQL> select /*+ use_nl(a,b) index(a pk_t_policy) */

2 count(*)

3 from test.t_policy a, test.t_policy b

4 where a.policy_id = b.policy_id;

COUNT(*)

----------

9999

执行计划

----------------------------------------------------------

Plan hash value: 2662632309

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 12 | 53851 (50)| 00:10:47 |

| 1 | SORT AGGREGATE | | 1 | 12 | | |

| 2 | NESTED LOOPS | | 9964K| 114M| 53851 (50)| 00:10:47 |

| 3 | INDEX FAST FULL SCAN| PK_T_POLICY | 9964K| 57M| 5077 (5)| 00:01:01 |

|* 4 | INDEX UNIQUE SCAN | PK_T_POLICY | 1 | 6 | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("A"."POLICY_ID"="B"."POLICY_ID")

统计信息

----------------------------------------------------------

1 recursive calls

0 db block gets

10026 consistent gets

17 physical reads

0 redo size

409 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select a.EVENT,a.TOTAL_WAITS,a.TIME_WAITED,a.AVERAGE_WAIT,a.WAIT_CLASS

2 from v$system_event a where a.EVENT in

3 ('db file sequential read','db file scattered read');

EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT WAIT_CLASS

---------------------------------------------------------------- ----------- ----------- ------------ -------------

db file sequential read 8664 8524 .98 User I/O

db file scattered read 1255 1398 1.11 User I/O

收集一下系统统计信息：

begin

dbms_stats.gather_system_stats('start');

end;

再运行一下这个查询：

select /*+ use_nl(a,b) */

from test.t_policy a, test.t_policy b

where a.policy_id = b.policy_id

;

使得a全表，b索引，运行完毕之后：

begin

dbms_stats.gather_system_stats('stop');

end;

SQL> select a.EVENT,a.TOTAL_WAITS,a.TIME_WAITED,a.AVERAGE_WAIT,a.WAIT_CLASS

2 from v$system_event a where a.EVENT in

3 ('db file sequential read','db file scattered read');

EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT WAIT_CLASS

---------------------------------------------------------------- ----------- ----------- ------------ -----------

db file sequential read 8805 8548 .97 User I/O

db file scattered read 1267 1405 1.11 User I/O

可以看到这个值仍然在下降。（注：这个测试之前，数据库刚启动）

视图v$system_event的值是instance生命周期内的累计值，而且也不像v$log_history那样记录很多个不同时间的值，但是如果知道两个时间点的一些值信息，则可以判断时间段内的大致的数据库情况，这也是许多调优工具的原理，因此，我们也可以写一些程序来抓取一些瞬时值为我们自己所用，或者写一些job定期保存系统视图的信息，供日常检查和监控。（在分析调优工具的时候再详述）

例如，假设两次snap的值分别放在begin_system_event和end_system_event（实际上可以在同一个表标示snap_id），那么这个差值很容易得到：

select t1.event,

(t2.total_waits - nvl(t1.total_waits, 0)) "Delta_Waits",

(t2.total_timeouts - nvl(t1.total_timeouts, 0)) "Delta_Timeouts",

(t2.time_waited - nvl(t1.time_waited, 0)) "Delta_Time_Waited"

from begin_system_event t1, end_system_event t2

where t2.event = t1.event(+)

order by (t2.time_waited - nvl(t1.time_waited, 0)) desc;

一些创建方法也可以在这里找到：$ORACLE_HOME/rdbms/admin/ spctab.sql，而一些具体的分析方法后续详述。

再来看看v$session_event

当已经知道某个session的sid的时候，要分析这个session上的一些性能问题，这个视图就非常适宜了，比如，某个用户报说某个job或者过程跑的特别慢(通过username来找到相应的sid)。看看具体的字段：

SQL> desc v$session_event

名称是否为空? 类型

----------------------------------------- -------- -------------

SID NUMBER

EVENT VARCHAR2(64)

TOTAL_WAITS NUMBER

TOTAL_TIMEOUTS NUMBER

TIME_WAITED NUMBER

AVERAGE_WAIT NUMBER

MAX_WAIT NUMBER

TIME_WAITED_MICRO NUMBER

EVENT_ID NUMBER

WAIT_CLASS_ID NUMBER

WAIT_CLASS# NUMBER

WAIT_CLASS VARCHAR2(64)

（可以看到多出了sid和max_wait两个字段）

通过看time_waited值可以判断一下是否有性能问题，并找出主要的瓶颈所在。

要注意的是：

通过这个视图，也只能找到时哪类wait正在影响性能，而不能定位到是那个sql导致的，比如现在知道了db file scattered read在这个session上值很大，判断很可能是全表扫描导致，这个时候就需要使用sid关联v$session视图，而v$session视图中有sql_id,sql_address,sql_hash_value来和具体的sql对应，然后分析具体的sql来找出问题所在。

另外，单单用这个视图也很难定位出问题的根源所在，只能说具体session下的某类问题可以大致判断出来，比如，所有的latch waits都汇总到了latch free的等待事件，很难说哪一种latch wait是对当前session性能影响最大的。

可以看看当前系统中定义的latch等待事件：

select * from v$event_name a where a.NAME like '%latch%';

不过10g中很少会有latches有大量的独立等待事件，往往都是一起增加的。

另一个有用的字段是max_wait，记录着每个event每个session达到过的最大值，则很快可以发现这个session中event上发生过的最长的等待时间。使用dbms_system.kcfrms()可以将这个字段清零。

V$session_wait

SQL> desc v$session_wait

名称是否为空? 类型

----------------------------------------- -------- --------------------

SID NUMBER

SEQ# NUMBER

EVENT VARCHAR2(64)

P1TEXT VARCHAR2(64)

P1 NUMBER

P1RAW RAW(4)

P2TEXT VARCHAR2(64)

P2 NUMBER

P2RAW RAW(4)

P3TEXT VARCHAR2(64)

P3 NUMBER

P3RAW RAW(4)

WAIT_CLASS_ID NUMBER

WAIT_CLASS# NUMBER

WAIT_CLASS VARCHAR2(64)

WAIT_TIME NUMBER

SECONDS_IN_WAIT NUMBER

STATE VARCHAR2(19)

P1,p2,p3三个字段包含了事件的详细信息，不同的的event代表不同的意义。

当发现某个应用运行很慢的时候，可能需要去了解一下当前的会话都在做什么事情，在什么事件上花费了时间，可以使用这个视图来看看一些瞬时值（多次查询，如果这些值在不断变化，或者周期性的变化，可能就意味着当前的应用目前是活动的，或正在工作；如果发现应用一直在等待相同的event，这个可能就意味着idle或者正在等待一些必须的资源，例如，锁，latch，磁盘空间，或者某些resumeable会话）。

显然，这个视图只是在查看当前的wait时非常有用，并不能体现过去某段时间的历史值，要查看会话的历史值，可以查看v$session_wait_history。

当监控v$session_wait这个视图的时候，尤其需要注意哪些state=WAITING的事件，这个表明session目前正在这个event上等待。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-674902/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-674902/