Mutex----I'm a porter.

Look at Lu DA's book, wherein the introduction of the mutex is so exciting, so I do a porter.

mutexes differ from latch:

Mutex	Latch
No wait queue, no hold queue, preemption mechanism	Using queues
spin25 5 times, in Span style= "font-family: ' Times New Roman '; font-size:2px;" >spin   You can't get it, go to sleep, wake up.	spin times, in spin
using reference counters(reference count), in -occupied in bits8bytes, in fact before4to beSID, after4for reference counters, if the reference counter is0, indicatingMutexfor exclusive mode	in latch Span style= "font-family: ' The song body '; font-size:2px;" > pool, each of the   latch Span style= "font-family: ' The song body '; font-size:2px;" >   latch Span style= "font-family: ' The song body '; font-size:2px;" > composition   latch Span style= "font-family: ' The song body '; font-size:2px;" > pool
Implant inside the object	Detach from Object

V$mutex_sleep_history.mutex_value View Mutex value

Select Sid, Event, P1raw, P2raw, state, Wait_class

From V$session

where Wait_class <> ' Idle '

Order by event;

If event is the library Cache:mutex X, then P2raw is the current mutex_value of the mutex that blocks the process, where the front end is the SID and the blocked session can be found.

Mutex type:

Common types are: cursor parent, library cache, hash table, cursor pin, cursor stats

The Mutex_type column in V$mutex_sleep and v$mutex_sleep_history corresponds to the mutex type

Different mutex types correspond to different wait events, usually as follows:

hash table, cursor parent, cursor stats type mutex corresponding to Cursor:mutex

Library cache type mutex corresponds to library Cache:mutex

Cursor PIN type mutex corresponds to Cursor:pin

Hard parse all mutexes will appear, soft soft parse only the cursor pin type mutex

Library Cache:mutex X

11g before using the library cache latch to protect the hash bucket and the subsequent linked list, 11g began no longer used, but instead of the library Cache:mutex

Regardless of soft parsing or hard parsing, the process must be exclusive way to obtain the library Cache:mutex, and then to access the hash chain, if the competition, the generation of waiting events, the waiting event here is the library Cache:mutex X

In the location column of V$mutex_sleep or v$mutex_sleep_history, you can see that the mutex miss is kglhdgn1 or large mutex sleep in the AWR report Summary section See location for kglhdgn1 More competition, indicating in the search hash bucket after the linked list encountered competition

The purpose of the search hash chain is to locate the parent cursor handle, and after the parent cursor handle is found, to request the exclusive holding of the library Cache:mutex again, the information within the parent cursor handle can be accessed successfully. If there is a competition at the time of application, the waiting event is generated, and the waiting event is also the library Cache:mutex x. The mutex miss for this processing is usually kgldhgn2 106 . Under the protection of the mutex, the process obtains the library cache lock on the parent cursor handle, and after successful, the mutex is freed. In other words, the mutex here is replaced with the previous version of the library cache lock latch (11g before using the library cache lock latch,11g after replacing the library Cache:mutex)

The same mutex and library cache lock are found on the handle of the child cursor, as well as on the handles of other objects.

Hash Table:mutex

The parent cursor handle is found and the library cache lock is added, then the address of the parent cursor heap 0 is fetched from the parent cursor handle and the parent cursor heap 0 is accessed. The parent cursor heap 0 contains the child cursor handle addresses that make up the cursor list, such as:

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m00/57/ae/wkiom1sifc6s6vghaagjnjtat1i316.jpg "title=" qq picture 20141230110049.png "alt=" Wkiom1sifc6s6vghaagjnjtat1i316.jpg "/>

The purpose of accessing the parent cursor heap 0 is to find the child cursor handle in the child cursor list, which of course holds the mutex. There are two types of mutexes held here: one is the cursor parent and the other is a hash table

Oracle first holds a mutex of the cursor parent type, accesses additional information from the parent cursor heap 0, and then releases it. Then hold the hash table type of mutex, search the child cursor list, find the child cursor handle, find and release. These two types of mutexes correspond to wait events that are Cursor:mutex S

Search for a list of child cursors, inferred from the type of mutex to be hash table:mutex. It only protects the list of child cursors if this type of mutex encounters a competition, stating that there are too many versions of an SQL statement. In fact, the hash Table:mutex undertook the previous version of the library cache pin and the library cache pin latch role.

Cursor PIN

The entire parsing process accesses the hash list first, then accesses the parent cursor handle, the parent cursor heap 0, the child cursor handle, and finally the child cursor heap 0 and the heap that contains the execution plan 6

The type of mutex added to the child cursor heap 0 and heap 6 is the cursor pin, and the corresponding wait event is Cursor:pin s or Cursor:pin s wait on X

The mutex for the cursor pin type of heap 6 is special, and this mutex is not in the child cursor heap 6, which is in the parent cursor heap 0 because the DS of the child cursor heap 6 is in the parent cursor heap 0.

Hard parse requires exclusive, shared mode to hold the mutex on the parent cursor heap 0 and child cursor heap 6 multiple times

Soft parsing usually does not require access to child cursor heap 0, you can find the child cursor heap 6 from the parent cursor heap 0, direct access to the execution plan in child cursor heap 6

The DS address of the child cursor heap 6 is saved in the PGA when soft and soft parsing

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m00/57/ab/wkiol1sifszwh0dzaagxaxrmjqs191.jpg "title=" qq picture 20141230110120.png "alt=" Wkiol1sifszwh0dzaagxaxrmjqs191.jpg "/>

Resolving problems through Mutex judgment

Hard parse:

Require all mutexes, apply multiple shared pool latch, soft parse requires only a small amount of shared pool latch, soft and soft parsing does not require a shared pool latch

Therefore: if the shared pool latch is highly competitive, there must be too much hard parsing, and there is a situation where there are too many versions.

Simultaneous hard parsing of multiple child cursors under the same parent cursor will cause the library cache lock to compete with the mutex that protects the child cursor list, as well as the hash table type mutex

Therefore: if the library cache lock and the hash table type mutex appear simultaneously, too much hard parsing

If there is only a hash table type of mutex, too many versions

In Awr, the mutex sleep summary can be viewed and judged accordingly.

Soft parsing:

When searching for a hash bucket list, you need to add the library cache type mutex

You also need to add the library cache mutex when accessing the parent cursor handle

A hash table-type mutex is required to access the parent cursor heap 0 and search the list of child cursor handles

When accessing a child cursor handle, use a mutex that needs to be added to the library cache type

A cursor pin-type mutex is required to access the child cursor heap 6 and read the execution plan

Add: Dynamic cursor, after parsing, if the variable value is passed to the shared pool using a bound variable, this step is exclusive to the library cache mutex

Static cursors, binding variables do not pass into the shared pool, and no library cache mutex is required

Soft and soft analysis:

Search for sub-Leng 6DS addresses in the PGA cache cursor list, no mutex, latch required

Depending on the DS address of the resulting child cursor heap 6, accessing the shared pool sub-cursor heap 6, reading the execution plan, requires a shared-mode cursor pin mutex, where the wait event may be encountered as Cursor:pin S

If you use bound variables, pass the value of the bound variable to the shared pool, which is exclusive to the library cache mutex. Similarly, if you are a static cursor, the binding variable does not pass into the shared pool, and you do not need the library cache mutex

After execution, crawl ends, you need to release the shared cursor pin-type mutex, where you may also encounter a wait event Cursor:pin S

Summarize:

If only the cursor pin type and the library cache type mutex competition, it is soft and soft parsing

If there are other mutexes waiting, that's soft parsing

If there is a shared pool latch waiting, it is hard to parse

Resolve the resolution phase of the competition:

Hard parsing: There are generally 3 reasons

Binding variable not used

Parent cursor version is too high

Small shared pool

First, there is no bound variable, query V$sqlarea.sql_text, see if similar statements too much. Modifying the application is the best policy, the cursor_sharing parameter is not a good way

The second, the parent cursor version is too high, query v$sql_shared_cursor. 11g Adaptive cursor Sharing (ACS), which may cause an excessive version of the problem in an OLTP environment, may consider turning off this feature.

Third, the shared pool is small, may use instantaneous LRU and periodic LRU ratio to judge, such as small, then increase

Soft-parsing: two methods of solving

Adjust applications to reduce soft parsing

Adjust session_cached_cursors, soft analysis for soft and soft analysis

Summarize:

Too much hard parsing, you can use bound variables, increase the shared pool, hardened to soft parsing

Too much soft analysis, can be adjusted large session_cached_cursors, soft analysis for soft and soft analysis

Soft and soft parsing a small test:

Establish two connections

Sql> select Sid from V$mystat where Rownum=1;

Sid
----------
226

Sql> select Sid from V$mystat where Rownum=1;

Sid
----------
242

In each session, do the following:

Declare
Mcur number;
Mstat number;
V_name VARCHAR2 (40);
Begin
Mcur:=dbms_sql.open_cursor;
For I in 1.. 10000000 Loop
Dbms_sql.parse (mcur, ' select * from Moe where Id=1 ', dbms_sql.native);
Mstat:=dbms_sql.execute (mcur);
End Loop;
Dbms_sql.close_cursor (mcur);
End
/

To view the wait events for the previous two sessions:

Select Sid,event,p1raw,p2raw,p3raw from V$session where SID in (' 226 ', ' 242 ');

SID EVENT P1raw P2raw P3raw
---------- --------------- ---------------- ---------------- ----------------
226 Cursor:pin S 00000000d756dda2 0000000000000001 0000000400000000
242 cursor:pin S 00000000d756dda2 0000000000000002 0000000900000000

Resolved as follows:

In 226, do the following:

Declare
Mcur number;
Mstat number;
V_name VARCHAR2 (40);
Begin
Mcur:=dbms_sql.open_cursor;
For I in 1.. 10000000 Loop
Dbms_sql.parse (mcur, ' SELECT/* sess_226 */* from Moe where Id=1 ', dbms_sql.native);
Mstat:=dbms_sql.execute (mcur);
End Loop;
Dbms_sql.close_cursor (mcur);
End
/

In 242, do the following:

Declare
Mcur number;
Mstat number;
V_name VARCHAR2 (40);
Begin
Mcur:=dbms_sql.open_cursor;
For I in 1.. 10000000 Loop
Dbms_sql.parse (mcur, ' SELECT/* sess_242 */* from Moe where Id=1 ', dbms_sql.native);
Mstat:=dbms_sql.execute (mcur);
End Loop;
Dbms_sql.close_cursor (mcur);
End
/

To view the wait for two sessions:

Sql> Select Sid,event,p1raw,p2raw,p3raw from v$session where SID in (' 226 ', ' 242 ');

SID EVENT P1raw P2raw P3raw
---------- ----------------------------- ---------------- ---------------- ----------------
226 Sql*net Message from client 0000000062657100 0000000000000001 00
242 sql*net Message from client 0000000062657100 0000000000000001 00

To be Continued ...

#end

Collation from: Oracle kernel technology revealed Geomechanics

This article is from the "just out of the shell of the Birds" blog, please be sure to keep this source http://qhd2004.blog.51cto.com/629417/1597647

Mutex----I am a porter