Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to solve the problems of library cache pin and library cache lock. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

A certain customer suddenly has a stutter in the front-end retail business. Because I was dealing with other customers at that time, there was no monitor at that time. It was handled by another colleague and immediately restarted the application.

Problem solved. But the business had to be interrupted.

After the investigation of the reason, from the time period of Catton, we found that there were a large number of library cache pin waiting events, accompanied by library cache lock.

In addition, colleagues also saved session information related to blocking.

Create table block_sess1018 as

Select * from v$session where blocking_session is not null

Combination

Select * from dba_hist_active_sess_history h where h.sample_time > to_date ('20171018 15 where h.sample_time 25 to_date,' yyyymmdd hh34:mi:ss')

And h.sample_time

< to_date('20171018 16:00:00', 'yyyymmdd hh34:mi:ss') and event like 'library cache%' library cache pin几个参数的意思： p1=handle address p2=pin address p3=namespace&&encoded mode 对于p1raw可以对应于x$kglob中的KGLHDADR字段x$kglpn中的KGLPNHDL字段，x$kgllk中的KGLLKHDL字段，后边有sql关联。 dba_hist_active_sess_history中的P1可以转换成16进制之后，再去v$object_dependency通过to_address定位到pin的对象 SQL>

Select s.sid, s.saddr, sw.p1raw

From v$session_wait sw, v$session s

Where sw.sid = s.sid and sw.event='library cache pin'

SID SADDR P1RAW

53 0000000077204A80 000000006DBC5BE8

Now using the P1RAW and SADDR values find the the address of the user session that is holding the lock

SQL > select b.KGLLKUSE from dba_kgllock w, dba_kgllock b

Where w.KGLLKHDL = b.KGLLKHDL

And w.KGLLKREQ > 0 and b.KGLLKMOD > 0

And w.KGLLKTYPE = b.KGLLKTYPE

And w.KGLLKUSE = '0000000077204A80'-SADDR

And w.KGLLKHDL = '000000006DBC5BE8'-P1RAW

Select to_owner, to_name from v$object_dependency

Where to_address = '000000016B500270'

Select * from dba_ddl_locks where owner = 'HR'

Found the object of library cache pin.

Further discovery (through logminer mining logs) that a developer colleague DDL a underlying table before the failure starts, triggering a series of compiled actions (many objects depend on this table).

Many objects, such as triggers (much of the logic of the product is done with triggers), procedures, functions, views, etc., depend on this table.

Due to the peak of business at this time, there was a large area of stutter.

The following is the DDL chronological order

DDL statement executed:

The above is the editor for you to share how to solve library cache pin and library cache lock failures, if there happen to be similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.