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

Oracle Study-- Oracle wait event (8)

The objects in the library cache are cut into multiple memory blocks in the library cache, and another object handle records the address of each memory block and other information. When you want to modify the information in the handle, you need to add an exclusive lock on the handle, and if another process happens to request to read and write the information in the handle, it must wait. The wait at this time is recorded in the Library cache lock event by Oracle. And read and write object memory block can not be carried out at the same time, if someone is writing, your read operation must wait, read and write memory block wait event is Library cache pin. If there are too many waiting events, it also indicates that the repository cache is too small or there is no shared execution plan. Or, when you use DDL when the database is busy, there will be these two wait events.

Library cache lock

This wait event occurs when different users compete for resources caused by concurrent operations on the same database object in the sharing. For example, when a user is doing DDL operations on a table, if other users want to access the table, a library cache lock wait event will occur. It will wait until the DDL operation is completed before continuing the operation.

This event contains four parameters:

Handle address: the address of the object being loaded.

Lock address: the address of the lock.

Mode: the data fragment of the loaded object.

Namespace: the namespace name of the loaded object in the v$db_object_cache view.

10gr2 rac:

Sys@ORCL > select name from v$event_name where name like 'library%' order by 1 * name Muhami Muhami library cache load locklibrary cache locklibrary cache pinlibrary cache revalidationlibrary cache shutdown

Library cache pin

This wait event, like library cache lock, is an event caused by concurrent operations that occur in a shared pool. In general, if Oracle is to recompile objects such as PL/SQL or views, you need to pin those objects into the shared pool. If this object is unique to other users at this time, a library cache pin wait will be generated.

This wait event also contains four parameters:

Handle address: the address of the object being loaded.

Lock address: the address of the lock.

Mode: the data fragment of the loaded object.

Namespace: the namespace name of the loaded object in the v$db_object_cache view.

Case study:

12:03:15 SCOTT@ prod > begin12:03:38 2 for i in 1.. 100000 loop execute immediate 'insert into T1 values (' | | I | |')'; 12:03:38 3 12:03:38 4 end loop;12:03:38 5 end;12:03:39 6 / 11:25:39 TOM@ prod > begin12:03:43 2 for i in 1.. 100000 loop12:03:43 3 execute immediate 'insert into T1 values (' | | I | |')'; 1203loop12:03:43 43 4 end loop 12:03:43 5 end 12:03:43 6 / 12:04:33 SYS@ prod > r 1 select event,TOTAL_WAITS,AVERAGE_WAIT EVENT_ID from v$system_event 2 * where event like'% lib%'EVENT TOTAL_WAITS AVERAGE_WAIT EVENT_ID -- library cache load lock 517.8 2952162927library cache: mutex X 142.27 1646780882Elapsed: 00.03

Query statement:

Method 1. Only librarycache pin related information can be queried.

SQL > SELECT distinct decode (kglpnreq,0,'holding_session:' | | s. Sidgrad waiting sw,x$kglob x4 WHERE p.kglpnuse=s.saddr5 AND kglpnhdl=sw.p1raw6 and kglhdadr=sw.p1raw7 and event like 'library cache%'8 and (a.hash_value, a.address) IN (a.hash_value, a.address) sid,2 s. SERIA sw,x$kglob, kglpnmod "Pin Mode", kglpnreq "ReqPin", a.sqlaccountext.kglnaown "Owner", kglnaobj "Object" 3 FROM x$kglpn p, v$session sdome vaccounsessionwait sw,x$kglob x4 session (a.hash_value, a.address) 11 0 prev_hash_value,13 sql_hash_value14 12 prev_hash_value,13 sql_hash_value14), 15 DECODE (sql_hash_value, 0, prev_sql_addr, sql_address) 16 from v$session s217 where s2.sid=s.sid18) 19

Method 2. You can query the information of library cache pin and library cache lock

Select Distinct / * + ordered * / w1.sid waiting_session,h2.sid holding_session, w.kgllktype lock_or_pin, od.to_owner object_owner, od.to_name object_name, oc.Type, decode (h.kgllkmod, 0, 'None', 1,' Null', 2, 'Share', 3,' Exclusive', 'Unknown') mode_held, decode (w.kgllkreq, 0,' None', 1, 'Null', 2,' Share', 3, 'Exclusive' 'Unknown') mode_requested,xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql from dba_kgllock w, dba_kgllock h, v$session W1, v$session H2 X$kgllk xhwhere (h.kgllkmod! = 0) and (h.kgllkmod! = 1) and ((h.kgllkreq = 0) or (h.kgllkreq = 1)) and ((W. kgllkmod = 0) or (w.kgllkmod = 1)) and ((w.kgllkreq! = 0) and (w.kgllkreq! = 1) and w.kgllktype = h.kgllktype and w.kgllkhdl = h.kgllkhdl and w.kgllkuse = w1.saddr and h.kgllkuse = h2.saddr And od.to_address = w .kgllkhdl And od.to_name=oc.Name And od.to_owner=oc.owner And w1.sid=xw.KGLLKSNMAnd h2.sid=xh.KGLLKSNM And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH) And (h2.SQL_ADDRESS=xh.KGLHDPAR And h2.SQL_HASH_VALUE=xh.KGLNAHSH)

Note:

Case study of production environment: (transferred from: http://www.itpub.net/thread-1504538-1-1.html)

Today, I received a call from a colleague saying that one of his stored procedures had been run for more than an hour and was still run. He felt extremely abnormal and should not have been run for such a long time.

I said I'll go and have a look.

This library is 10.2.0.4 on an AIX, and I collected the AWR report for the problem period:

Begin Snap:

13302

11-Jun-10 12:00:56

one hundred and nine

4.7

End Snap:

13303

11-Jun-10 13:00:02

ninety-seven

4.9

Elapsed:

59.10 (mins)

DB Time:

113.98 (mins)

The Top 5 events are:

Event

Waits

Time (s)

Avg Wait (ms)

% Total Call Time

Wait Class

Library cache pin

1252

3656

2920

53.5

Concurrency

Library cache lock

nine hundred and eighty nine

2890

2922

42.3

Concurrency

CPU time

two hundred and nineteen

3.2

Db file sequential read

5694

twelve

two

. 2

User I/O

Log file parallel write

1467

eleven

eight

. 2

System I/O

From the results of the AWR report, we can see that during the time when something went wrong, the system experienced severe library cache pin and library cache lock waiting.

From Load Profile, we can see again:

Per Second

Per Transaction

Parses:

12.83

65.83

Hard parses:

0.05

0.25

In other words, it is not hard parse that causes the above library cache pin and library cache lock.

For library cache pin waiting, the role of AWR reports is limited, and the most effective way is to find the session that holds the library cache pin and wait for the library cache pin, and then see what they are doing:

SQL > SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process"

2 FROM v$session_wait w, x$kglpn p, v$session s, v$process o

3 WHERE p.kglpnuse=s.saddr

4 AND kglpnhdl=w.p1raw

5 and w.event like'% library cache pin%'

6 and s.paddr=o.addr

7 /

SID Mode Req OS Process

--

396 0 2 6381970

396 0 2 6381970

396 0 2 6381970

396 0 2 6381970

341 2 0 4092132

341 2 0 4092132

341 2 0 4092132

341 2 0 4092132

363 0 2 3514690

363 0 2 3514690

363 0 2 3514690

363 0 2 3514690

304 0 2 3977478

304 0 2 3977478

304 0 2 3977478

304 0 2 3977478

354 0 3 3137874

354 0 3 3137874

354 0 3 3137874

354 0 3 3137874

20 rows selected

The session of my colleague in the run stored procedure is 396. from the above results, we can see that 396 now wants to hold library cache pin in Share mode (that is, Req=2), while the person who now holds the above library cache pin is session 341, and the holding mode of 341 is also Share (that is, Mode=2).

Originally, Share and Share could be shared, but unfortunately, before 396th, session 354 wanted to hold the above library cache pin in Exclusive mode (that is, Req=3), which directly led to the need for 396 to be in the waiting Queue, as well as 363,304 in the Queue.

Why do I say this, because there is a very classic sentence in oracle's interpretation of library cache pin:

An X request (3) will be blocked by any pins held S mode (2) on the object.

An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.

Therefore, from the AWR report and the above query results, we can draw the following conclusions:

1. Why does my colleague in the run stored procedure run for more than an hour? the reason is that the stored procedure is going through a serious library cache pin wait.

2. The reason for the serious library cache pin wait is that session 341 and 354 work together to achieve this effect, that is, 341 holds the library cache pin in Share mode, and then 354 wants to hold it in Exclusive mode, which directly causes all subsequent requests to be placed in the waiting Queue. In other words, 341 blocks 354, while 354 indirectly blocks 396.

Now that we know why, let's take a look at what session 341 is doing:

SQL > select decode (sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=341

DECODE (SQL_HASH_VALUE,0,PREV_H

-

784727971

Execute again after an interval of 10 seconds:

SQL > select decode (sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=341

DECODE (SQL_HASH_VALUE,0,PREV_H

-

784727971

Execute again after an interval of 10 seconds:

SQL > select decode (sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session where sid=341

DECODE (SQL_HASH_VALUE,0,PREV_H

-

784727971

SQL > select sql_text from v$sqltext where hash_value=784727971 order by piece

SQL_TEXT

Begin-- Call the procedure p_adj_rrp_main (o_vc_flag = > _ vc_flag); end

It can be seen from the results that 341 has been run a stored procedure.

I called the eldest sister with 341 and asked her what she was doing. She told me that she had started the run stored procedure last night and found it dead this morning, so she left it.

It was easy to deal with after knowing the reason. When I killed the session 341s, the library cache pin of the whole system dropped suddenly, and then my colleague's run finished run after more than an hour of stored procedures.

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.