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

What are the relevant knowledge points of library cache? in view of this question, this article introduces in detail the corresponding analysis and solutions, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Shared cursor

After Sql parses for the first time, it generates a parent cursor and a child cursor (except DDL), and its shareable parts include parsing tree, execution plan, binding variables, sql text, etc.

The parent cursor mainly saves the cursor name, namely sql text, and the sql with consistent text can be shared.

The child cursor holds the remaining information and can be shared only if the execution plan / bind variables / environment variables (NLS & optimization mode) / actual reference objects are consistent. Otherwise, create a new child cursor.

Hard parsing: there is no shared cursor that can be reused, just sharing the parent cursor is also hard parsing

Soft parsing: reuse parent and child cursors

Soft parsing: the session cursor closed in the cache PGA omits the cursor open/close compared with the soft parsing, and can quickly locate the shared cursors in the library cache. It is only used by the current session.

The life cycle of the cursor

The normal condition is open-parse-bind-execute-fetch-close

According to this, the application can be divided into four types.

1 do not use binding variables

Each execution goes through open/close. Cursors are not reusable and hard parsing is performed every time. Soft parsing can be used to set cursor_sharing=force/similar.

2 use binding variables

Each execution goes through open/close, cursors can be reused, and subsequent execution performs soft parsing; set session_cached_cursors to use soft parsing, and avoid open/close cursors every time

3 open once + parse-bind-execute-fetch loop + close once

Oracle precompilers that turns on the HOLD_CURSOR=NO & RELEASE_CURSOR=NO option uses this application; soft parsing is performed every time, but frequent switching cursors are avoided

4 open once + parse + bind + execute-fetch loop + close once

Well-designed OCI or precompiler may use this application; setting cursor_space_for_time=true can improve performance (11g obsolete)

How to optimize soft parsing

Session cursor cache hits/session cursor cache count/parse count (total) in V$statname represents the number of hits / total cache / total resolution of session cursor cache respectively. When the ratio of hits/total is very low, soft parsing is very serious, and soft parsing also requires library cache latch.

1 server: adjusting session_cache_cursor

2 applications: rewrite pl/sql and adopt the fourth type of application mentioned above

Library cache

Managed by KGL, using hash table structure, each bucket consists of a linked list of handle, and each handle contains a series of heap pointing to heap 0

Handle stores object names / namespaces / flag bits

Lock & Pin

Action

Library cache lock manages concurrency between processes, whereas library cache pin manages cache coherence .

In order to access an object in library cache, a process must first lock the library cache object handle, and then pin the object data heap itself.

Acquiring a library cache lock is also the only way to locate an object in cache-a process locates and locks an object in a single operation.

If the process wants to actually examine or modify the object, then it must acquire a library cache pin on the object data heap itself (after acquiring a library cache lock on the library cache object handle.

Lock protects handle pin protects heap. To access objects in library cache, you must obtain lock and pin successively.

10202 replaced library cache pin for cursor with mutex

Pattern

Library cache lock and pin are both enqueue locks

Lock is divided into Sforme X and null (cursor can only use null to maintain dependency consistency), and pin has only S and X.

Soft parsing uses S pin, hard parsing uses X pin;, no matter hard parsing or soft parsing, cursor only uses null lock; to track http://www.dbsnake.net/library-cache-pin-and-lock-continue.html.

About 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.

X pin will invalidate the null lock, and the corresponding cursor must be re-parsed before it can be run again, that is, if the table is DDL, the corresponding cursor will fail, and it is very easy to break out library cache pin waiting.

Both pin and lock are added to handle

Library cache latch: used for serial access to objects in library cache; lock is not an atomic operation and requires latch protection before and after locking

The Library cache load lock latch/library cache load lock- object cannot be lock when it is not in memory, so it needs to be loaded first. The former prevents the object from being loaded multiple times, obtaining the former until the load lock is allocated, and the latter is responsible for loading the object into memory.

Find the blocker.

Select

Waiter.sid waiter

Waiter.event wevent

To_char (blocker_event.sid) | |','| | to_char (blocker_session.serial#) blocker

Substr (decode (blocker_event.wait_time, 0, blocker_event.event,'ON CPU'), 1pr 30) bevent

From

X$kglpn p

Gv$session blocker_session

Gv$session_wait waiter

Gv$session_wait blocker_event

Where

P.kglpnuse=blocker_session.saddr

And p.kglpnhdl=waiter.p1raw

And waiter.event in ('library cache pin',' library cache lock', 'library cache load lock')

And blocker_event.sid=blocker_session.sid

And waiter.sid! = blocker_event.sid

Order by waiter.p1raw,waiter.sid

Select

Ash.session_id sid

Ash.blocking_session bsid

Nvl (o.objectpapername.toggchar (CURRENT_OBJ#)) obj

O.object_type otype

CURRENT_FILE# filen

CURRENT_BLOCK# blockn

Ash.SQL_ID

Nvl (rc.name,to_char (ash.p3)) row_cache

From v$active_session_history ash, (select cache#, parameter name from v$rowcache) rc, all_objects o

Where event='row cache lock'

And rc.cache# (+) = ash.p1

And o.object_id (+) = ash.CURRENT_OBJ#

And ash.session_state='WAITING'

And ash.sample_time > sysdate-& minutes/ (60024)

Order by sample_time

The answers to the questions about library cache-related knowledge points are shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.