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

I. Library cache structure

Description of Library Cache in 1.DSI:

(1) An area in the shared pool that manages information about:

An area in a shared pool that stores information about the following objects:

-- Shared cursors (SQL and PL/SQL objects) shared cursors (sql and pl/sql objects)

-- Database objects (tables, indexes, and so on) database objects (tables, indexes, etc.)

(2) Initially created to manage PL/SQL programs and library units, therefore called library cache

Originally created to manage PL/SQL programs and library units, it is called library cache

(3) Scope was extended to include shared cursors and information about other RDBMS objects.

Expanded to include information about shared cursors and other RDBMS objects

2.Library Cache Objects (library cache object)

(1) The units of information that are stored in the library cache are called objects.

The units of information stored in the library cache are called objects

(2) There are two classes of objects:

There are two types of objects:

1) Stored objects storage object (persistent object)

-- Created and dropped with explicit SQL or PL/SQL commands. Objects created and deleted using explicit SQL or PL/SQL commands

Examples: Tables, views, packages,functions such as tables, views, packages, functions, etc.

2) Transient objects transient object (non-permanent object)

Created at execution time and live only for the duration of the instance (or aged out) is created at execution time and exists or disappears only during the life cycle of the instance

Example: Shared and nonshared cursors such as shared or non-shared cursors

3.Shared Cursors

(1) In-memory representation of an executable object: the representation of executable objects in memory

SQL statements SQL statement

Anonymous PL/SQL block anonymous PL/SQL block

PL/SQL stored procedures or functions stored procedure or function

(2) Represented by two or more objects: contains two or more objects

A parent cursor that has a name parent cursor

One or more child cursors containing the execution plan sub-cursor storage execution plan

4.Library Cache Architecture

(1) The library cache is a hash table that is accessible through an array of hash buckets.

A library cache is a hash table made up of multiple hash bucket

(2) The library cache manager (KGL) controls the access and usage of library cache objects.

The Library Cache Manager (KGL) controls the access and use of library cache objects.

(3) Memory for the library cache is allocated from the shared pool.

Storage space for the library cache is allocated from the shared pool

5. Library cache needs to solve three problems:

(1) the problem of rapid positioning: there are many objects in Library cache, how does Oracle manage these objects, so that the service process can quickly find the information they need. For example, a service process needs to quickly locate whether a SQL exists in the Library cache.

(2) the problem of relational dependency: the objects in Library cache have complex dependencies. When an objec fails, the objects that depend on it can be quickly put into a failed state. For example, a table has a structural change, and the SQL statements that depend on it need to be reparsed.

(3) concurrency control: there must be a concurrency control mechanism in Library cache, such as locking mechanism, to manage the concurrent access and modification of a large number of shared objects, for example, when a SQL is recompiled, the objects it depends on cannot be modified.

Oracle uses hash table structure to solve the problem of fast positioning in library cache. Hash table is an array of many hash bucket.

Take a look at a few related pictures first:

Library Cache saves explicit SQL, PL/SQL commands, and shared and nonshared cursors. These objects are stored in Hash table, and Hash table is made up of Hash Bucket. Hash Bucket consists of some Object Handle List, so to find an object in Hash Bucket is to search for that Handle List.

6. Object Handle

In the picture above, we can see the information saved by Object handle. Library cache object handle points to library cache object (LCO, heap 0), which contains library cache object's name, namespace, timestamp, reference list, lock object, pin object list information, and so on.

So access to all objects in Library cache is achieved by using library cache object handle, which means that if we want to access library cache object, we must first find library cache object handle. Because Object handle holds information about lock and pin, that is, to record which user has lock on this handle, or which user is waiting to get the lock. So library cache lock happens on handle. When a process requests library cache object, library cache manager will apply a hash algorithm to get a hash value and look for it in the corresponding hash bucket according to the corresponding hash value. If the library cache object is in memory, then the library cache object handle will be found. Sometimes, when the shared pool is not large enough, the library cache object handle will remain in memory, but the library cache heap will be age out due to insufficient memory, and the object heap we requested will be overloaded (hard parsing). In the worst case, when the library cache object handle is not found in memory, a new library cache object handle must be allocated and the object heap will be loaded into memory (hard parsing).

7. Library Cache Object (LCO: Heap 0)

Its structural information is shown in the following figure:

Description of DSI:

(1) Internally, most of the object identity is represented by structures of type kglob.

(2) These are the structures stored in heap 0.

(3) Object structures have the following components:

Type

Name

Flags

Tables

Datablocks

Library Cache stores SQL or shared cursors and so on. This information is saved through the LCO of Heap 0.

7.1 Object Types

(1) Objects are grouped in namespaces according to their type.

(2) Each object can only be of one type.

(3) All the objects of the same type are in the same namespace.

(4) A namespace may be used by more than one type.

(5) The most important namespace is called cursor (CRSR) and houses the shared SQL cursors.

7.2 Object Names

(1) Library cache object names have three parts:

Name of schema

Name of object

Name of database link (remote objects only)

(2) The format used is SCHEMA.NAME@DBLINK.

For example: HR.EMPLOYEES@ACME.COM

7.3 Object Flags

(1) Public flags:

Are not protected by pins or latches

Indicate in detail the type of the object

(2) Status flags:

Are protected by pins

Indicate whether the object is being created/dropped/altered/updated

(3) Special status flags:

Are protected by the library cache latch

Are related to object validity and authorization

7.4 Object Tables

(1) Dependency table

(2) Child table

(3) Translation table

(4) Authorization table

(5) Access table

(6) Read-only dependency table

(7) Schema name table

7.4.1 dependency table

Point to the object that this object depends on, such as select * from emp, the object of cursor, and depend on the table emp. Here, it points to the handle of the table emp.

7.4.2.child table

Points to a child of this object, such as a child of a cursor. To put it colloquially, a SQL has at least one parent cursor and child cursor. It is possible that some SQL cannot share child cursor for some reason, which leads to the situation of one parent cursor and multiple child cursor. That is, the version count is very high. In this case, the pointers to all the corresponding child cursor in the parent cursor are saved in the child table. Oracle is written in C, so here is the pointer.

Note that both parent cursor and child cursor are stored in Library Cache using library cache object handle. That is, their structure is exactly the same. This conclusion can be proved by library cache's dump file. We'll do a test later.

7.4.3.authorization table

The authorization information for the object.

7.5 Object Data Blocks

(1) The remainder of an object's data is stored in other independent data heaps.

(2) The object structure contains an array of data block structures.

(3) The data block structures have a pointer to a different data heap.

(4) An object structure has room for 16 data block structures but not all of them are in use.

Heap0 also saves only a structure, which does not hold the actual data. The pointer to the Heap that actually stores the data is stored in this Data Blocks. This can also be viewed through dump. The Heap structure pointed to by this Data Blocks is shown below:

It is important to note here that our SQL execution plan is stored in this Heap 6:SQL Context.

2. Dump out library cache for observation

First, generate two child cursors on a parent cursor, create a tb_test table under sys user and scott user, and execute the statement: select object_name from tb_test where object_id = 20

SQL > conn scott/tiger

Connected.

SQL > create table tb_test as select * from dba_objects where object_id

< 100; Table created. SQL>

Select object_name from tb_test where object_id = 20

OBJECT_NAME

-

ICOL$

SQL > show user

USER is "SYS"

SQL > create table tb_test as select * from dba_objects where object_id

< 100; Table created. SQL>

Select object_name from tb_test where object_id = 20

OBJECT_NAME

-

ICOL$

SQL > select sql_id,version_count from v$sqlarea where sql_text like 'select object_name from tb_test where object_id%'

SQL_ID VERSION_COUNT

--

5tq4nhdw908dy 2

SQL > select address,child_address,child_number from v$sql where sql_id='5tq4nhdw908dy'

ADDRESS CHILD_ADDRESS CHILD_NUMBER

00000000620A5990 00000000620A5510 0

00000000620A5990 0000000062290028 1

At this point, you can see that two child cursors have been generated under the parent cursor.

Bring out the library cache dump:

SQL > oradebug setmypid

Statement processed.

SQL > oradebug dump library_cache 16

Statement processed.

SQL > oradebug tracefile_name

/ opt/app/oracle/diag/rdbms/bddev/BDDEV/trace/BDDEV_ora_54420.trc

Find the parent cursor 0x620A5990 in the trace file:

Bucket: # = 8638 Mutex=0x69ac5508 (0,39,0,6)

-- memory address of object handle

LibraryHandle: Address= 0x620a5990 Hash=789021be LockMode=N PinMode=0 LoadLockMode=0 Status=VALD

-- name of object

ObjectName: Name=select object_name from tb_test where object_id = 20

-- Hash value and Namespace, Type

FullHashValue=239de4faa6a0ef3f5cd89483789021be Namespace=SQL AREA (00) Type=CURSOR (00) Identifier=2022711742 OwnerIdn=0

Statistics: InvalidationCount=0 ExecutionCount=2 LoadCount=3 ActiveLocks=2 TotalLockCount=2 TotalPinCount=1

Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 Version=0 BucketInUse=1 HandleInUse=1 HandleReferenceCount=0

Concurrency: DependencyMutex=0x620a5a40 (0,6,0,0) Mutex=0x620a5ad0 (48,119,0,6)

-- flag Information

Flags=RON/PIN/TIM/PN0/DBN/ [10012841]

-- lock, pin information

WaitersLists:

Lock=0x620a5a20 [0x620a5a20,0x620a5a20]

Pin=0x620a5a00 [0x620a5a00,0x620a5a00]

LoadLock=0x620a5a78 [0x620a5a78,0x620a5a78]

Timestamp: Current=08-03-2018 16:43:25

HandleReference: Address=0x620a5b70 Handle= (nil) Flags= [00]

ReferenceList:

Reference: Address=0x6514e3c0 Handle=0x6228fd38 Flags=ROD [21]

Reference: Address=0x6521b3c0 Handle=0x620a5220 Flags=ROD [21]

LibraryObject: Address=0x6521d0b0 HeapMask=0000-0001-0001-0000 Flags=EXS [0000] Flags2= [0000] PublicFlags= [0000]

DataBlocks:

Block: # ='0' name= KGLH0 ^ 789021be pins=0 Change=NONE

Heap=0x620a58d8 Pointer=0x6521d150 Extent=0x6521d030 Flags=I/-/P/A/-/-

FreedLocation=0 Alloc=3.289062 Size=3.976562 LoadTime=5081449990

ChildTable: size='16'

-- two child cursors

Child: id='0' Table=0x6521df60 Reference=0x6521d9b8 Handle= 0x620a5510

Child: id='1' Table=0x6521df60 Reference=0x6521dd00 Handle= 0x62290028

Children:

Child: childNum='0'

LibraryHandle: Address= 0x620a5510 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD

Name: Namespace=SQL AREA (00) Type=CURSOR (00)

Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=1 ActiveLocks=1 TotalLockCount=2 TotalPinCount=3

Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0

Concurrency: DependencyMutex=0x620a55c0 (0,0,0,0) Mutex=0x620a5ad0 (48,119,0,6)

Flags=RON/PIN/PN0/EXP/CHD/ [10012111]

WaitersLists:

Lock=0x620a55a0 [0x620a55a0,0x620a55a0]

Pin=0x620a5580 [0x620a5580,0x620a5580]

LoadLock=0x620a55f8 [0x620a55f8,0x620a55f8]

ReferenceList:

Reference: Address=0x6521d9b8 Handle=0x620a5990 Flags=CHL [02]

LibraryObject: Address=0x6521c0b0 HeapMask=0000-0001-0001-0000 Flags=EXS [0000] Flags2= [0000] PublicFlags= [0000]

Dependencies: count='1' size='16' table='0x6521cee8'

-- subcursor dependency information

Dependency: num='0'

Reference=0x6521c670 Position=24 Flags=DEP [0001]

Handle=0x6233e978 Type=TABLE (02) Parent= SYS.TB_TEST

ReadOnlyDependencies: count='1' size='16'

ReadDependency: num='0' Table=0x6521cf80 Reference=0x6521c568 Handle=0x620a5220 Flags=DEP/ROD/KPP [61]

Accesses: count='1' size='16'

Dependency: num='0' Type=0009

Translations: count='1' size='16'

Translation: num='0' Original=0x6233e978 Final=0x6233e978

DataBlocks:

Block: # ='0' name= KGLH0 ^ 789021be pins=0 Change=NONE-- Block 0, corresponding to x$kglob. KGLOBHD0

Heap=0x620a5458 Pointer=0x6521c150 Extent=0x6521c030 Flags=I/-/P/A/-/-

FreedLocation=0 Alloc=2.273438 Size=3.937500 LoadTime=5081449990

-- Block6 is equivalent to heap6 and stores specific execution plans, corresponding to x$kglob. KGLOBHD6

Block: # ='6' name= SQL ^ 789021be pins=0 Change=NONE

Heap= 0x6521d788 Pointer=0x628c1188 Extent=0x628c0548 Flags=I/-/-/A/-/E

FreedLocation=0 Alloc=6.195312 Size=7.898438 LoadTime=0

NamespaceDump:

Child Cursor: Heap0=0x6521c150 Heap6=0x628c1188 Heap0 Load Time=08-03-2018 16:43:25 Heap6 Load Time=08-03-2018 16:43:25

Child: childNum='1'

LibraryHandle: Address=0x62290028 Hash=0 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD

Name: Namespace=SQL AREA (00) Type=CURSOR (00)

Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=1 ActiveLocks=1 TotalLockCount=1 TotalPinCount=2

Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0

Concurrency: DependencyMutex=0x622900d8 (0,0,0,0) Mutex=0x620a5ad0 (48,119,0,6)

Flags=RON/PIN/PN0/EXP/CHD/ [10012111]

WaitersLists:

Lock=0x622900b8 [0x622900b8,0x622900b8]

Pin=0x62290098 [0x62290098,0x62290098]

LoadLock=0x62290110 [0x62290110,0x62290110]

ReferenceList:

Reference: Address=0x6521dd00 Handle=0x620a5990 Flags=CHL [02]

LibraryObject: Address=0x6514f0b0 HeapMask=0000-0001-0001-0000 Flags=EXS [0000] Flags2= [0000] PublicFlags= [0000]

Dependencies: count='2' size='16' table='0x6514fee8'

Dependency: num='0'

Reference=0x6514f668 Position=0 Flags=DEP [0001]

Handle=0x620ad0e8 Type=NONE (255) Parent=SCOTT

Dependency: num='1'

Reference=0x6514f6b8 Position=24 Flags=DEP [0001]

Handle=0x62fa4d00 Type=TABLE (02) Parent=SCOTT.TB_TEST

ReadOnlyDependencies: count='1' size='16'

ReadDependency: num='0' Table=0x6514ff80 Reference=0x6514f568 Handle=0x6228fd38 Flags=DEP/ROD/KPP [61]

Accesses: count='1' size='16'

Dependency: num='1' Type=0009

Translations: count='1' size='16'

Translation: num='0' Original=0x62fa4d00 Final=0x62fa4d00

DataBlocks:

Block: # ='0' name= KGLH0 ^ 789021be pins=0 Change=NONE

Heap=0x6228ff70 Pointer=0x6514f150 Extent=0x6514f030 Flags=I/-/P/A/-/-

FreedLocation=0 Alloc=2.343750 Size=3.937500 LoadTime=5081489580

Block: # ='6' name= SQL ^ 789021be pins=0 Change=NONE

Heap=0x6521dba0 Pointer=0x619d3c70 Extent=0x619d3030 Flags=I/-/-/A/-/E

FreedLocation=0 Alloc=6.195312 Size=7.898438 LoadTime=0

NamespaceDump:

Child Cursor: Heap0=0x6514f150 Heap6=0x619d3c70 Heap0 Load Time=08-03-2018 16:44:04 Heap6 Load Time=08-03-2018 16:44:04

NamespaceDump:

Parent Cursor: sql_id=5tq4nhdw908dy parent=0x6521d150 maxchild=2 plk=y ppn=n

CursorDiagnosticsNodes:

ChildNode: ChildNumber=0 ID=37 reason=Authorization Check failed (4) size=5x4 translation_table_position=0 original_handle=1660570880 temp_handle=1647569272 schema=83 synonym_object_number=0

Take a look at the heap6 dump of the subcursor 0x620a5510:

SQL > alter session set events' immediate trace name heapdump_addr level 2 addr 0x6521d788'

Session altered.

HEAP DUMP heap name= "SQL ^ 789021be" desc= 0x6521d788

Extent sz=0xfe8 alt=32767 het=368 rec=0 flg=2 opc=2

Parent=0x60001190 owner=0x6521d648 nex= (nil) xsz=0xfe8 heap= (nil)

Fl2=0x27, nex= (nil), dsxvers=1, dsxflg=0x0

Dsx first ext=0x628c0548

EXTENT 0 addr=0x628bc530

Chunk 0628bc540 sz= 1664 free ""

Dump of memory from 0x00000000628BC540 to 0x00000000628BCBC0

0628BC540 00000681 C0B38F00 00000000 00000000 [.]

0628BC550 6521D848 00000000 6521D848 00000000 [H.!e....H.!e....]

0628BC560 00000000 00000000 00000000 [.]

Repeat 1 times

0628BC580 000000E1 00B38F00 628BC4A0 00000000 [.b....]

0628BC590 0A0FD9A8 00000000 00000000 00150015 [.]

0628BC5A0 00020015 00000016 00008100 00000000 [.]

0628BC5B0 00000000 00000000 00000000 [.]

....

Oracle stores the sql execution plan in heap 6 (that is, sql area) of the child cursor of this sql, but in compiled binary format.

III. Library cache lock and Library cache pin

The two wait events have the same parameters:

SQL > select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name in ('library cache lock','library cache pin')

NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS

--

Library cache pin handle address pin address 100*mode+namespace Concurrency

Library cache lock handle address lock address 100*mode+namespace Concurrency

Parameter1: the address of the object being checked or loaded

Parameter2: address where the lock is loaded

Parameter3: contains schema + namespace

Lock and pin information is stored in Library cache handle. And there are lock and pin on both Library cache handle and child cursor. They are called library cache lock and library cache pin.

Library cache lock/pin is used to control concurrent access to library cache object. Lock manages concurrency, pin manages consistency, lock is for library cache handle, and pin is for heap.

When we want to access a library cache object, we first need to get the lock pointing to the object handle, and after getting the lock, we need to pin the heap pointing to the object.

When we compile packages, stored procedures, functions, and views, Oracle will first get a library cache lock on the handle of these objects, and then get pin on the heap of these objects, which ensures that other processes will not change the definition of these objects or delete them at compile time.

When a session hard parses a SQL statement, the session must get the librarycache lock so that other session cannot access or change the objects referenced by the SQL. If this wait event takes a long time, it usually indicates that the shared pool is too small (because the shared pool is too small, it will take a long time to search for the chunk of free, or to remove some object page out that can be removed), of course, it is also possible that another session is making changes to the object (such as the split partition), and the current session needs to reference that table, in which case we have to wait for the other session to finish.

Library Cache lock has 3 modes:

(1) Share (S): get when reading a library cache object

(2) Exclusive (X): obtained when creating / modifying a library cache object

(3) Null (N): used to ensure object dependency

For example, if a process wants to compile a view, it will get a shared lock; if we want to create/drop/alter an object, it will get exclusive lock. The Null lock is very special. We have a NULL lock on any executable cursor,function. We can break the NULL lock at any time. When the NULL lock is broken, it means that the object has been changed and needs to be recompiled.

The main purpose of NULL locks is to mark whether an object is valid. For example, a SQL statement acquires a NULL lock when parsing. If the object of the SQL is in the shared pool all the time, then the NULL lock will always exist. When the table referenced by the SQL statement is modified, the NULL lock will be broken, because the Exclusive lock will be obtained when the SQL statement is modified. Because the NULL lock is broken, the SQL needs to be recompiled next time.

Library Cache pin has two modes:

(1) Share (S): read object heap

(2) Exclusive (X): modify object heap

When a session wants to read object heap, it needs to get a shared-mode pin, and when a session wants to modify the object heap, it needs to get an exclusive pin. Of course, you have to get lock before you can get pin.

In Oracle10gR2, library cache pin is replaced by library cache mutex.

Library cache latch is used to control concurrent access to library cache object. As mentioned earlier, we must obtain library cache lock before we can access library cache object. Lock is not an atomic operation (an atomic operation is an operation that will not be broken during the operation, obviously the lock can be broken). In order to protect this lock, Oracle introduces the library cache latch mechanism, that is, we need to obtain library cache latch before obtaining library cache lock, and release library cache latch after obtaining library cache lock.

If a library cache object is not in memory, then the lock cannot be fetched, so you need to get a library cache load lock latch, then get a library cache load lock, and then release the library cache load lock latch when the load lock is obtained.

Library cache latch is controlled by the implicit parameter _ KGL_LATCH_COUNT, and the default value is greater than or equal to the minimum prime of the number of CPU in the system, but Oracle has a hard limit on it, which cannot be greater than 67. Note: we go to query _ kgl_latch_count, which sometimes displays as 0, which is a bug.

Oracle uses the following algorithm to determine which sub-latch protects the library cache object handle:

Latch#= mod (bucket#, # latches)

That is to say, which sub-latch is used to protect a handle is based on the bucket number of that handle and how many sub-latch are used to perform hash operations.

IV. Test and query of Library cache lock/pin

Create a stored procedure:

SQL > create or replace procedure calling

2 is

3 begin

4 dbms_lock.sleep (3000)

5 end

6 /

Procedure created.

Open 3 session,session1:

SQL > select * from v$mystat where rownum

< 2; SID STATISTIC# VALUE ---------- ---------- ---------- 419 0 0 session2： SQL>

Select * from v$mystat where rownum

< 2; SID STATISTIC# VALUE ---------- ---------- ---------- 773 0 0 session3： SQL>

Select * from v$mystat where rownum

< 2; SID STATISTIC# VALUE ---------- ---------- ---------- 807 0 0 session1中执行calling： SQL>

Exec calling

Recompile calling in session2, 3:

SQL > alter procedure calling compile

SQL > select saddr,sid,serial#,event,p1raw,p2raw,blocking_session,last_call_et from v$session where sid in (48, 50, 31)

SADDR SID SERIAL# EVENT P1RAW P2RAW BLOCKING_SESSION LAST_CALL_ET

000000055E4BFA20 419 14671 PL/SQL lock timer 0000 2475

000000055E99DF20 773 61023 library cache pin 00000005594D6AD8 00000005593BCC50 419 16

000000055E936160 807 1301 library cache lock 00000005594D6AD8 000000055A27BF50 773 14

View information about specific locks

SQL > select kgllkhdl handle,kgllksnm sid,kgllkreq request, kglnaobj object,user_name

2 from x$kgllk

3 where kgllkses = '000000055E936160'

4 and kgllkreq > 0

HANDLE SID REQUEST OBJECT USER_NAME

--

00000005594D6AD8 807 3 CALLING SCOTT

You can see that the session of sid= 807 is requesting an X mode lock on the CALLING

View the session information that holds the lock being requested by sid= 807:

SQL > select kgllkses saddr, kgllkhdl handle,kgllksnm sid,kgllkmod mod, kglnaobj object,user_name

2 from x$kgllk lock_a

3 where kgllkmod > 0

4 and exists (

5 select lock_b.kgllkhdl

6 from x$kgllk lock_b

7 where kgllkses = '000000055E936160' / * blocked session*/

8 and lock_a.kgllkhdl = lock_b.kgllkhdl

9 and kgllkreq > 0

10)

SADDR HANDLE SID MOD OBJECT USER_NAME

--

000000055E4BFA20 00000005594D6AD8 419 1 CALLING SCOTT

000000055E99DF20 00000005594D6AD8 773 3 CALLING SCOTT

You can see that the session of sid=773 is holding the lock in X mode, and the session of sid=419 is holding the lock in Null mode, so the session of sid=773 blocks the session of sid=807

View all blocked sessions:

SQL > select sid, serial#,sql_id,username,terminal, program

2 from v$session

3 where saddr in (

4 select kgllkses

5 from x$kgllk lock_a

6 where kgllkreq > 0

7 and exists (

8 select lock_b.kgllkhdl

9 from x$kgllk lock_b

10 where kgllkses = '000000055E4BFA20' / * blocking session*/

11 and lock_a.kgllkhdl = lock_b.kgllkhdl

12 and kgllkreq = 0

13)

14)

SID SERIAL# SQL_ID USERNAME TERMINAL PROGRAM

-

807 1301 77xa8gx8dmxh7 SCOTT pts/2 sqlplus@bd-dev-rac1 (TNS V1-V3)

View what is going on in the session that owns the library cache pin:

SQL > select s.sidwery s. Serialized.kglpnmoddl.kglpnhdl.sqlplayid.kglpnmod lmode,o.spid "os process", obj.kglnaown pin_obj_owner,obj.kglnaobj pin_obj_name.

2 from x$kglpn p, v$session s, v$process Olympus glob obj

3 where p.kglpnuse = s.saddr

4 and s.paddr = o.addr

5 and p.kglpnmod > 0

6 and p.kglpnhdl = obj.kglhdadr

SID SERIAL# KGLPNMOD KGLPNHDL SQL_ID LMODE os process PIN_OBJ_OW PIN_OBJ_NAME

419 14671 2 000000055B26FF90 5whptv3nstk28 2 17898 SYS DBMS_LOCK

419 14671 2 000000055B3486D8 5whptv3nstk28 2 17898 SYS DBMS_LOCK

419 14671 2 00000005594D6AD8 5whptv3nstk28 2 17898 SCOTT CALLING

Library cache pin Information:

SQL > desc x$kglpn

Name Typ

ADDR RAW (4)

INDX NUMBER

INST_ID NUMBER

KGLPNADR RAW (4)

KGLPNUSE RAW (4)-session address (saddr corresponding to v$session)

KGLPNSES RAW (4)-owner address

KGLPNHDL RAW (4)-handle

KGLPNLCK RAW (4)

KGLPNCNT NUMBER

KGLPNMOD NUMBER-holds the mode of pin (0: no lock/pin held,2, share,3: exclusive)

KGLPNREQ NUMBER-the mode of the request pin (0 is no lock/pin held, 2 is share,3 is exclusive)

KGLPNDMK NUMBER

KGLPNSPN NUMBER-the value of the savepoint corresponding to the trace file

Library cache lock Information:

SQL > desc x$kgllk

Name Typ

--

ADDR RAW (4)

INDX NUMBER

INST_ID NUMBER

KGLLKADR RAW (4)

KGLLKUSE RAW (4)-session address (saddr corresponding to v$session)

KGLLKSES RAW (4)-owner address

KGLLKSNM NUMBER-SID

KGLLKHDL RAW (4)-library cache object handle

KGLLKPNC RAW (4)-the address of the call pin

KGLLKPNS RAW (4)-corresponds to the session pin value in the trace file

KGLLKCNT NUMBER

KGLLKMOD NUMBER-the mode that holds the lock (0: no lock/pin held,1, null,2, share,3, exclusive)

KGLLKREQ NUMBER-the mode of requesting lock (0: no lock/pin held,1, null,2, share,3, exclusive)

KGLLKFLG NUMBER-the status of the cursor. 8 (before 10g) or 2048 (10g) indicates that the sql is running.

KGLLKSPN NUMBER-the value of the savepoint corresponding to the trace file

KGLLKHTB RAW (4)

KGLNAHSH NUMBER-the hash value of sql (corresponding to the sql_hash_value of v$session)

KGLLKSQLID VARCHAR2 (13)-sql ID,sql identifier

KGLHDPAR RAW (4)-sql address (sql_address corresponding to v$session)

KGLHDNSP NUMBER

USER_NAME VARCHAR2 (30)-the alias of the conversation

KGLNAOBJ VARCHAR2 (60)-the object name or the first 60 characters of the sql that parsed and opened the cursor

Library cache object Information:

SQL > desc x$kglob

Name Null? Type

-

ADDR RAW (8)

INDX NUMBER

INST_ID NUMBER

KGLHDADR RAW (8) # cursor address

KGLHDPAR RAW (8) # parent cursor address

KGLHDCLT NUMBER

KGLNAOWN VARCHAR2 (64) # object owner

KGLNAOBJ VARCHAR2 (1000) # object name

KGLFNOBJ CLOB

KGLNADLK VARCHAR2 (64)

KGLNAHSH NUMBER

KGLNAHSV VARCHAR2 (32)

KGLNATIM DATE

KGLNAPTM DATE

KGLHDNSP NUMBER

KGLHDNSD VARCHAR2 (64)

KGLHDLMD NUMBERk

KGLHDPMD NUMBER

KGLHDFLG NUMBER

KGLHDOBJ RAW (8)

KGLHDLDC NUMBER

KGLHDIVC NUMBER

KGLHDEXC NUMBER

KGLOBHD0 RAW (8) # heap 0

KGLOBHD6 RAW (8) # heap 6

.

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.