[20190402]Library Cache mutex.txt

lfree發表於2019-04-02

[20190402]Library Cache mutex.txt


1.環境:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


$ cat m2.txt

set verify off

column a noprint new_value v_a;

--select mod ( &&3 ,3) a  from dual ;

--alter session set optimizer_index_cost_adj= &&3;

host sleep $(echo &&3/50| bc -l )

insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,'&&2') ;

commit ;

declare

v_id number;

v_d date;

begin

    for i in 1 .. &&1 loop

        --select  1 into v_id from dual ;

        --select  sysdate into v_d from dual ;

        select deptno into v_id from dept where deptno=10;

    end loop;

end ;

/

update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method='&&2';

commit;

--quit


--//執行許多次,就可以在 x$mutex_sleep_history有記錄,出現MUTEX_TYPE='Library Cache'的情況

$ seq 150 | xargs -I {}  -P 150 bash -c  "sqlplus -s -l scott/book @m2.txt 1e6 f2_150 {} >/dev/null"


SYS@book> @ mutexy 16 a31kd5tkdvvmm

      HASH SUM_SLEEPS   SUM_GETS LOCATION                       MUTEX_TYPE           MUTEX_ADDR       SQLID         KGLNAOWN C100

---------- ---------- ---------- ------------------------------ -------------------- ---------------- ------------- -------- ---------------------------------------

1692266099  161187946  600188278 kksfbc [KKSCHLPIN1]            Cursor Pin           000000007C88E330 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099    9215322 1.8927E+11 kksLockDelete [KKSCHLPIN6]     Cursor Pin           000000007C88E330 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099    5019588 1.0546E+11 kksfbc [KKSCHLFSP2]            Cursor Pin           000000007C88E330 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099    1351526   63672414 kgllkc1   57                   Library Cache        000000007C957128 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099     499588    9089718 kkslce [KKSCHLPIN2]            Cursor Pin           000000007C957128 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099     212212    3838112 kglGetHandleReference 124      Library Cache        000000007C957128 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099     131248    1919076 kgllkdl1  85                   Library Cache        000000007C957128 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099      10118    1814754 kglhdgn2 106                   Library Cache        000000007C957128 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

8 rows selected.


--//探究MUTEX_TYPE='Library Cache',MUTEX_ADDR='000000007C957128'在哪裡,還有1個LOCATION問題?

--//我執行許多次才出現這樣的情況.


2.分析:


SYS@book> @ fcha 000000007C957128

Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007C957128 resides...

WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention

in systems under load and with large shared pool. This may even completely hang

your instance until the query has finished! You probably do not want to run this in production!

Press ENTER to continue, CTRL+C to cancel...


LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR

--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------

SGA 000000007C956FB8          1          1 KGLHD                   560 recr             80 00


--//執行如下,也可以獲得類似結果:

SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007C957128', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ -1;

ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR

---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------

00007F3CEDE1A5B8      16459          1          1          1 KGLHD            000000007C956FB8        560 recr             80 00



SYS@book> @ sharepool/shp4 a31kd5tkdvvmm 0

TEXT           KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09

-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------

子游標控制程式碼地址 000000007C7F8B90 000000007C956FE8 SELECT DEPTNO FROM DEPT WHERE DEPTNO=10           0          0          0 000000007C4468D8 000000007C88E3D8       4528       8088       3072     15688      15688 1692266099 a31kd5tkdvvmm          0

父遊標控制程式碼地址 000000007C956FE8 000000007C956FE8 SELECT DEPTNO FROM DEPT WHERE DEPTNO=10           0          0          0 000000007CA7BFE8 00                     4720          0          0      4720       4720 1692266099 a31kd5tkdvvmm      65535

--//可以發現MUTEX_ADDR='000000007C957128'的地址與父遊標控制程式碼的地址比較接近.實際上從KSMCHCOM=KGLHD也可以確定,


SYS@book> @ fcha 000000007C956FE8

Find in which heap (UGA, PGA or Shared Pool) the memory address 000000007C956FE8 resides...


WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention

in systems under load and with large shared pool. This may even completely hang

your instance until the query has finished! You probably do not want to run this in production!


Press ENTER to continue, CTRL+C to cancel...


LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR

--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------

SGA 000000007C956FB8          1          1 KGLHD                   560 recr             80 00


SELECT * FROM x$ksmsp WHERE TO_NUMBER ('000000007C956FE8', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ-1;

ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR

---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------

00007F3CEDDFBC00      12611          1          1          1 KGLHD            000000007C956FB8        560 recr             80 00


--//library cache mutex結構體在父遊標控制程式碼地址裡面.

--//0x7C957128=2090168616 0x7C956FB8=2090168248 2090168616-2090168248 = 368,在父遊標控制程式碼地址偏移368位元組處.


3.知道地址就可以模擬問題產生:

--//session 1:

SCOTT@book> @ spid


       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50

---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------

        32         35 10708                    DEDICATED 10709       26         84 alter system kill session '32,35' immediate;

--//32=0x20 


--//session 2:

SYS@book> oradebug setmypid

Statement processed.


SYS@book> oradebug peek 0x000000007C957128 8

[07C957128, 07C957130) = 00000000 00000000


SYS@book> oradebug poke 0x000000007C957128 8 0x0000004400000020

BEFORE: [07C957128, 07C957130) = 00000000 00000000

AFTER:  [07C957128, 07C957130) = 00000020 00000044


--//session 1:

SCOTT@book> @ m2.txt 1 c1 0

1 row created.

Commit complete.

--//掛起!!注第一次執行會掛起,第2次就不會了.


--//session 2:

SYS@book> @ wait

P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                  STATUS   STATE   WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS

---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------- -------- ------- --------------- --------------- -----------

0000000064DDEE73 0000004400000020 0000000000000039 1692266099 2.9206E+11         57         32         33         38 library cache: mutex X ACTIVE   WAITING        35878972              36 Concurrency

--//出現library cache: mutex X 等待.


SYS@book> oradebug poke 0x000000007C957128 8 0x0

BEFORE: [07C957128, 07C957130) = 00000020 00000044

AFTER:  [07C957128, 07C957130) = 00000000 00000000


--//再次執行,因為該游標已經cache. PL/sql語句中的sql語句與sqlplus執行的sql語句方式不同.


SYS@book> select * from v$open_cursor where sql_id='a31kd5tkdvvmm';

SADDR             SID USER_NAME ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE

---------------- ---- --------- ---------------- ---------- ------------- --------------------------------------- ------------------- ----------- --------------------

000000008635DE10   32 SCOTT     000000007C956FE8 1692266099 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10                                 PL/SQL CURSOR CACHED

--//第一次執行就cache了.第2次執行不會在library cache上出現阻塞.


SYS@book> oradebug poke 0x000000007C957128 8 0x0000004400000020

BEFORE: [07C957128, 07C957130) = 00000000 00000000

AFTER:  [07C957128, 07C957130) = 00000020 00000044


SCOTT@book> @ m2.txt 1 c1 0

1 row created.

Commit complete.

PL/SQL procedure successfully completed.

11 rows updated.

Commit complete.


--//可以正常執行.但是退出會出現問題.

SCOTT@book> quit


--//掛起!!

SYS@book> @ wait

P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                  STATUS   STATE   WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS

---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------- -------- ------- --------------- --------------- -----------

0000000064DDEE73 0000004400000020 0000000000000055 1692266099 2.9206E+11         85         32         35         57 library cache: mutex X ACTIVE   WAITING        17103974              17 Concurrency


--//還有1個情況是阻塞無法查詢x$mutex_sleep_history檢視,

SYS@book> @ mutexy 6 a31kd5tkdvvmm

--//掛起!!取消後正常!!

SYS@book> oradebug poke 0x000000007C957128 8 0x00

BEFORE: [07C957128, 07C957130) = 00000020 00000044

AFTER:  [07C957128, 07C957130) = 00000000 00000000

----


      HASH SUM_SLEEPS   SUM_GETS LOCATION                       MUTEX_TYPE           MUTEX_ADDR       SQLID         KGLNAOWN C100

---------- ---------- ---------- ------------------------------ -------------------- ---------------- ------------- -------- ---------------------------------------

1692266099  161187946  600188278 kksfbc [KKSCHLPIN1]            Cursor Pin           000000007C88E330 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099    9215322 1.8927E+11 kksLockDelete [KKSCHLPIN6]     Cursor Pin           000000007C88E330 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099    5019588 1.0546E+11 kksfbc [KKSCHLFSP2]            Cursor Pin           000000007C88E330 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099    1351526   63672414 kgllkc1   57                   Library Cache        000000007C957128 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099     499588    9089718 kkslce [KKSCHLPIN2]            Cursor Pin           000000007C957128 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099     212212    3838112 kglGetHandleReference 124      Library Cache        000000007C957128 a31kd5tkdvvmm          SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

6 rows selected.


--//感覺在11g下出現Library Cache的情況性很小.library cache主要出現在sql語句登入第1-3次執行時需要透過雜湊定位檢查父遊標是

--//否存在,以及是否要硬解析的情況,如果cache就不再需要了,不同會話相同語句同時執行的可能性在這裡發生阻塞的可能性很小,更容

--//易出現cursor: pin S.(我的測試也說明這個問題),加上session_cached_cursors作用,更難在這裡出現阻塞.

--//當然一些應用開始執行的語句可能都是一樣的,可能密集的登入時偶爾出現不會成為主要矛盾.

--//不像10g,採用是latch caceh latch,而且還有數量限制.而11g是採用mutex,library cache mutex的結構體在生成的父遊標控制程式碼裡面,

--//等於打散到共享池的許多地方,出現碰撞的機率降低了.


4.附上相關指令碼:

$ cat tpt/cha

channels2.sql  channels3.sql  channels.sql

[oracle@gxqyydg4 IP=100.78 ~/sqllaji ] $ cat tpt/fcha.sql

--------------------------------------------------------------------------------

--

-- File name:   fcha.sql (Find CHunk Address) v0.2

-- Purpose:     Find in which heap (UGA, PGA or Shared Pool) a memory address resides

--

-- Author:      Tanel Poder

-- Copyright:   (c) http://blog.tanelpoder.com | @tanelpoder

--

-- Usage:       @fcha <addr_hex>

--              @fcha F6A14448

--

-- Other:       This would only report an UGA/PGA chunk address if it belongs

--              to *your* process/session (x$ksmup and x$ksmpp do not see other

--              session/process memory)

--

--------------------------------------------------------------------------------


prompt Find in which heap (UGA, PGA or Shared Pool) the memory address &1 resides...

prompt

prompt WARNING!!! This script will query X$KSMSP, which will cause heavy shared pool latch contention

prompt in systems under load and with large shared pool. This may even completely hang

prompt your instance until the query has finished! You probably do not want to run this in production!

prompt

pause  Press ENTER to continue, CTRL+C to cancel...



select

    'SGA' LOC,

    KSMCHPTR,

    KSMCHIDX,

    KSMCHDUR,

    KSMCHCOM,

    KSMCHSIZ,

    KSMCHCLS,

    KSMCHTYP,

    KSMCHPAR

from

    x$ksmsp

where

    to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')

    between

        to_number(ksmchptr,'XXXXXXXXXXXXXXXX')

    and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1

union all

select

    'UGA',

    KSMCHPTR,

    null,

    null,

    KSMCHCOM,

    KSMCHSIZ,

    KSMCHCLS,

    KSMCHTYP,

    KSMCHPAR

from

    x$ksmup

where

    to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')

    between

        to_number(ksmchptr,'XXXXXXXXXXXXXXXX')

    and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1

union all

select

    'PGA',

    KSMCHPTR,

    null,

    null,

    KSMCHCOM,

    KSMCHSIZ,

    KSMCHCLS,

    KSMCHTYP,

    KSMCHPAR

from

    x$ksmpp

where

    to_number(substr('&1', instr(lower('&1'), 'x')+1) ,'XXXXXXXXXXXXXXXX')

    between

        to_number(ksmchptr,'XXXXXXXXXXXXXXXX')

    and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1

/


$ cat mutexy.sql

column kglnaown format a20

column MUTEX_TYPE format a20

column kglnaobj format a100

column LOCATION format a30

  select * from (

  SELECT kglnahsh hash

        ,SUM (sleeps) sum_sleeps

        ,SUM (gets) sum_gets

        ,location

        ,mutex_type

                ,MUTEX_ADDR

        ,kglobt03 sqlid

        ,kglnaown

        ,replace(kglnaobj,chr(13)) c100

    --,SUBSTR (kglnaobj, 1, 140) object

    FROM x$kglob, x$mutex_sleep_history

   WHERE kglnahsh = mutex_identifier

         and kglobt03= decode('&&2','',kglobt03,lower('&&2'))

GROUP BY kglnaobj

        ,kglobt03

        ,kglnaown

        ,kglnahsh

        ,location

        ,mutex_type

                ,MUTEX_ADDR

ORDER BY sum_sleeps DESC ) where rownum<= &1;



$ cat sharepool/shp4.sql

column N0_6_16 format 99999999

SELECT DECODE (kglhdadr,

               kglhdpar, '父遊標控制程式碼地址',

               '子游標控制程式碼地址')

          text,

       kglhdadr,

       kglhdpar,

       substr(kglnaobj,1,40) c40,

           KGLHDLMD,

           KGLHDPMD,

           kglhdivc,

       kglobhd0,

       kglobhd6,

       kglobhs0,kglobhs6,kglobt16,

       kglobhs0+kglobhs6+kglobt16 N0_6_16,

           kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,

           kglnahsh,

           kglobt03 ,

           kglobt09

  FROM x$kglob

 WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2640133/,如需轉載,請註明出處,否則將追究法律責任。

相關文章