Sharing pool Eight: Soft parsing, hard parsing, soft and soft parsing a SQL in the library cache to parse the locks involved

Let's take a big picture first:

Combine to illustrate the locks involved in each step of the resolution.

A simple explanation of the difference between soft parsing, hard parsing and soft and soft parsing:

To translate the user-written SQL text into Oracle-aware and executable statements, this process is called the parsing process.

Parsing is divided into hard parsing and soft parsing, the first parsing of SQL statements must be hard parsing

In a word, the difference between hard parsing and soft parsing is:

Hard parse = need to generate execution plan soft parse = Do not need to generate execution plan

There are two types of SQL statements in Oracle, one for DDL statements and one for non-shared use, that is, every execution requires hard parsing. There is also a DML statement that can be hard-parsed or soft-parsed.

hard parse becomes soft parse: bound variable

soft analysis into soft and soft parsing: set session_cached_cursors, see

The various structures involved in the diagram are briefly described:

A brief description of the various structures involved: see:

Parent cursor: Save hash value, SQL text--same SQL statement, only one parent cursor

The parent cursor contains two main types of information: SQL text and optimization targets. The parent cursor is locked when it is first opened, and is not unlocked until all other sessions have closed the cursor. When the parent cursor is locked, it cannot be swapped out for Librarycache, and the library cache will not be exchanged until it is unlocked. All child cursors corresponding to the parent cursor are also swapped out of the library cache when the parent cursor is swapped out of memory.

Child cursor: There will be more than one child cursor under a parent cursor, and the execution plan for each child cursor is different-multiple versions. A parent must have a child cursor that can have more than one child cursor.

Child cursors include all information about the cursor, such as the specific execution plan, binding variables, and so on. A child cursor can be swapped at any time by the library cache, and when a child cursor is swapped out of the library cache, Oracle can reconstruct a child cursor out of the information of the parent cursor, a process called reload (overload).

Parent handle, which has the address of the parent cursor heap 0:

Parent Cursor Heap 0: Handle handle address to one or more child cursors

Handle of child cursors: with child cursor heap 0 address

Child Cursor Heap 0: Has an SQL statement dependency and points to the child cursor of the heap 6

Child cursor Heap 6: Execution plan with SQL statements

The first check procedure that SQL statements perform at the library cache:

1. Access to the library cache through SQL statements such as syntax semantics permissions
2. Convert SQL Text to ASCII value (different case ASCII) and perform hash function operation
4. Get a hash value corresponding to the hash bucket number
############### #以上检查通过后, enter the following parsing process

Soft and soft analysis:

--by setting the Session_cached_cursors parameter implementation-
SQL execution = 3 times (can not be a session execution of SQL), heap 6 of the ds-heap descriptor address is put into the UGA (belonging to the PGA)--lock into a 1-null tag statement for the cache and ensure that the memory is not freed (heap 6 frees heap 0 to be freed).
That is, SQL executes 3 times and is cached. The fourth time, is soft and soft analysis.
-Optimized target: 90% more hits.
Session Initiation SQL will first look for cursor information in the user Global area.
At this point, null 1 is used on the parent cursor handle, child cursor handle, and child cursor heap 6.

####################################

Soft parsing:

1. Access to the library Cachelatch---if not obtained will be generated: Latch:library cache
2. Obtain the library cache lock, retrieve the parent cursor handle on the bucket, and get the memory address of the parent Leng 0-lco pointed to.
3. Obtain the library cache pin, read the parent Leng 0-lco, and get the child cursor handle address.
4. Obtain the library cache lock, retrieve the child cursor handle, and get the memory address of the child cursor heap 0-Sub Lco pointed to.
5. Obtain the library cache pin and read the child cursor heap 0-Sub Lco to get the child cursor heap 6 address.
6. Read the child cursor heap 6 to get the SQL execution plan.
# # # #找到child Lco, increase the Parsecount (total) statistic value.
SQL begins execution: the library cache lock and library cache pin are obtained in shared mode, and SQL is executed.
Fetch phase: When execution is completed into the fetch phase, SqlCursor converts the library cache lock to null mode and releases the library cache pin.
###############################################################

When trying soft parsing:
--hard parsing occurs if the same parent cursor LCO or child cursor Lco is not retrieved.
--If the child cursor heap 6 cannot be shared with the library Cachepin or the information in the LCO is incomplete, the execution plan needs to be rebuilt-recorded as hard parsing.

Hard Parse:

Hard parsing occurs if the same parent cursor LCO or child cursor Lco is not retrieved. The process will always hold the library cache latch until the hard parse is finished.
1. Get the shared pool latch and find the right size chunk from the freelist bucket.
There is no suitable size chunk will split the large chunk, the remaining will enter the corresponding bucket.
If you cannot find a suitable size chunk from the bucket on the free list, enter the LRU list, and if you still cannot get to chunk, allocate from the remaining inner of the shared pool. If the cursor reaches the size standard of the _shared_pool_reserved_min_alloc implied parameter (4400 in 11.2.0.4), the chunk is allocated from the retention pool, and if the assignment chunk fails, the error: ORA-04031. If the bucket list is too long or the fragment is severe, latch:shared pool contention is generated.

2. After assigning to chunk. Gets the library cachelock--exclusive mode, creating the parent cursor handle
3. Obtain the library cache pin and create the parent cursor heap 0-Parent Lco information. --library cache Lock is converted to null
4. Obtain the library cache lock and create a child cursor handle
5. Obtain the library cache pin and create the child cursor heap 0-child Lco information.
6.library cache pin, creating child cursor heap 6-Execution plan information (an optimal execution plan is created from the optimizer, which calculates the optimal execution plan based on the statistics of the objects recorded in the data dictionary, which involves a large amount of computation and consumes CPU resources).
SQL begins execution: the library cache lock and library cache pin are obtained in shared mode, and SQL is executed.
Fetch phase: When execution is completed into the fetch phase, SqlCursor converts the library cache lock to null mode and releases the library cache pin.

About the mutex corresponds to the Librarycache latch/pin/lock in the diagram:

With respect to mutexes, the library cache latch/pin/lock can be simply used as a different mode of the mutex for this step.

In 10G, mutexes primarily protect handle and LCO---alternate library cache Pin/lock

In 11G, mutexes can protect Bufket lists, handle and Lco---alternate library cache Latch/pin/lock

Access contention on handle: Cursor:mutex heap Access competition: Cursor:pin

Correlation contention for mutex at parse time

1. Related locks

(1), calculate the hash value, find buckets, search the hash list, find the handle.

LIbrary Cache Latch (replaced by mutex after 11G)

(2), finding the parent cursor heap in the parent cursor handle 0

Mutex (replace library cache lock latch), library cache lock

(3), in the parent cursor heap 0, find the child cursor handle.

two times mutexes. Two types of mutexes. Replaces the Librarycache pin latch and the library cache pin.

(4), in the child cursor handle, find the child cursor heap 0 address.

Mutex (replace library cache lock latch), library Cachelock

(5), in the child cursor heap 0, find the child cursor heap 6 address.

One mutex. Replaces the Librarycache pin latch and the library cache pin.

(6), in child cursor heap 6, read the SQL execution plan.

One mutex. Replaces the Librarycache pin latch and the library cache pin.

zhuanzai:http://blog.csdn.net/haibusuanyun/article/details/21402787

Sharing pool Eight: Soft parsing, hard parsing, soft and soft parsing a SQL in the library cache to parse the locks involved