Shulou(Shulou.com)06/01 Report--

Oracle Study-- Oracle wait event (7)

Free buffer waits

When a session reads blocks of data from disk into memory, it needs to find free memory space in memory to store those blocks. This wait occurs when there is no free space in memory. In addition, there is another situation in which a session needs to construct a p_w_picpath of a data block at a certain time when doing a consistent read, and memory is required to store these newly constructed data blocks. This wait event will also occur if such a memory block cannot be found in memory.

When a serious free buffer waits wait event occurs in the database, the possible reasons are:

(1) data buffer is too small, resulting in insufficient free space

(2) there is so much dirty data in memory that DBWR cannot write the dirty data to disk in time to free space.

This wait event contains two parameters:

The file number of the data file in which the data block needs to be read by File#:.

The block number that Block#: needs to read.

Case study:

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 12M11: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: 0002.1911 end loop;11:25:04 2352 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

In versions prior to 10g, latch free wait events represented all latch waits, and after 10g, some commonly used latch events have been isolated:

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-- -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, so latch free waits Events are not common in versions after 10g Instead, it is a specific Latch waiting event to occur. This wait event takes three parameters: Address: the latch address of the session wait. Number: latch number. With this number, you can find information about this latch in the v$latchname view. Tries: the number of times the session attempted to get the 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 145cache 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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.