Oracle Study之--Oracle等待事件（7）

Oracle Study之--Oracle等待事件（7）

Free buffer waits
当一个会话将数据块从磁盘读到内存中时，它需要到内存中找到空闲的内存空间来存放这些数据块，当内存中没有空闲的空间时，就会产生这个等待；除此之外，还有一种情况就是会话在做一致性读时，需要构造数据块在某个时刻的前映像（p_w_picpath），此时需要申请内存来存放这些新构造的数据块，如果内存中无法找到这样的内存块，也会发生这个等待事件。
当数据库中出现比较严重的free buffer waits等待事件时，可能的原因是：
（1）data buffer 太小，导致空闲空间不够
（2）内存中的脏数据太多，DBWR无法及时将这些脏数据写到磁盘中以释放空间
这个等待事件包含2个参数：
File#： 需要读取的数据块所在的数据文件的文件号。
Block#： 需要读取的数据块块号。
案例分析：

11:14:33 SYS@ prod>show parameter cacheNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_16k_cache_size                    big integer 24Mdb_cache_advice                      string      ONdb_cache_size                        big integer 16Mdb_keep_cache_size                   big integer 0db_recycle_cache_size                big integer 12M

11:21:17 SYS@ prod>conn scott/tigerConnected.11:23:16 SCOTT@ prod>begin11:25:04   2    for i in 1..100000 loop11:25:04   3    insert into t1 values (i);11:25:04   4    end loop;11:25:04   5    end;11:25:04   6    /PL/SQL procedure successfully completed.11:23:29 SYS@ prod>conn tom/tomConnected.11:23:38 TOM@ prod>create table t1 as select * from scott.t1;Table created.Elapsed: 00:00:02.1911:23:52 TOM@ prod>begin11:24:59   2    for i in 1..100000 loop11:24:59   3    insert into t1 values (i);11:24:59   4    end loop;11:24:59   5    end;11:24:59   6    /PL/SQL procedure successfully completed.11:25:12 SYS@ prod>select event,TOTAL_WAITS,AVERAGE_WAIT,EVENT_ID from v$system_event  2*  where event like '%buffer%'EVENT                                                            TOTAL_WAITS AVERAGE_WAIT   EVENT_ID---------------------------------------------------------------- ----------- ------------ ----------latch: cache buffers chains                                                3           .2 2779959231free buffer waits                                                         14          .86 2701153470buffer busy waits                                                          2          .23 2161531084log buffer space                                                           7        40.42 3357856061latch: cache buffers lru chain                                            17          .32 3401628503buffer deadlock                                                           11          .03  2189929286 rows selected.

Latch free
在10g之前的版本里，latch free 等待事件代表了所有的latch等待，在10g以后，一些常用的latch事件已经被独立了出来：

11:25:2name1 SYS@ prod>select  name from v$event_name where name like 'latch%' order by 1;NAME----------------------------------------------------------------latch activitylatch freelatch: Change Notification Hash table latchlatch: In memory undo latchlatch: MQL Tracking Latchlatch: PX hash array latchlatch: Undo Hint Latchlatch: WCR: processes HTlatch: WCR: synclatch: cache buffer handleslatch: cache buffers chainslatch: cache buffers lru chainlatch: call allocationlatch: change notification client cache latchlatch: checkpoint queue latchlatch: enqueue hash chainslatch: gc elementNAME----------------------------------------------------------------latch: gcs resource hashlatch: ges resource hash listlatch: lob segment dispenser latchlatch: lob segment hash table latchlatch: lob segment query latchlatch: messageslatch: object queue header operationlatch: parallel query alloc bufferlatch: redo allocationlatch: redo copylatch: redo writinglatch: row cache objectslatch: session allocationlatch: shared poollatch: undo global datalatch: virtual circuit queues33 rows selected.11:39:21 SYS@ prod>select EVENT#,EVENT_ID,NAME,PARAMETER1,PARAMETER2,PARAMETER3  from v$event_name  2* where name like '%latch free%'    EVENT#   EVENT_ID NAME                           PARAMETER1 PARAMETER2           PARAMETER3---------- ---------- ------------------------------ ---------- -------------------- ------------------------------       402 3474287957 latch free                     address    number               tries       409 2530878290 wait list latch free           address    number               tries       11:32:33 SYS@ prod>desc v$latchname Name                                                              Null?    Type ----------------------------------------------------------------- -------- -------------------------------------------- LATCH#                                                                     NUMBER NAME                                                                       VARCHAR2(64) HASH                                                                       NUMBER所以latch free 等待事件在10g以后的版本中并不常见，而是以具体的Latch 等待事件出现。这个等待事件有三个参数：Address: 会话等待的latch 地址。Number： latch号，通过这个号，可以从v$latchname 视图中找到这个latch 的相关的信息，Tries: 会话尝试获取Latch 的次数。11:34:25 SYS@ prod>select * from v$latchname11:34:36   2  where name like '%buffer%';    LATCH# NAME                                                                   HASH---------- ---------------------------------------------------------------- ----------        33 SGA IO buffer pool latch                                         2719726273        63 IPC stats buffer allocation latch                                1449990452       106 KJC global post event buffer                                     3098969798       145 cache buffers lru chain                                          3559635447       146 buffer pool                                                       510014793       150 cache buffers chains                                             3563305585       151 cache buffer handles                                              892398878       196 media recovery process out of buffers                            2731251867       197 mapped buffers lru chain                                           93631960       208 lock DBA buffer during media recovery                            3620457631       350 virtual circuit buffers                                          1577520421       378 parallel query alloc buffer                                       291345605       416 p_w_picpath handles of buffered messages latch                         3223585260       476 buffer pin latch                                                 392551935514 rows selected.