[20190402]Library Cache mutex.txt
[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;
