[20190329]探究sql語句相關mutexes補充2.txt

--//昨天測試sql語句相關mutexes,看看如果出現多個子游標的情況.

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_caching= &v_a;

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

--//這樣建立3個子游標.

2.測試:

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

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

SYS@book> @ mutexy 6

HASH SUM_SLEEPS SUM_GETS LOCATION MUTEX_TYPE MUTEX_ADDR SQLID KGLNAOWN C100

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

1692266099 14495552 3.6572E+11 kksLockDelete [KKSCHLPIN6] Cursor Pin 000000007BD3EC98 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 7893620 2.0592E+11 kksfbc [KKSCHLFSP2] Cursor Pin 000000007BD3EC98 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 4307308 8.8088E+10 kksLockDelete [KKSCHLPIN6] Cursor Pin 000000007BD3F260 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 1997852 4.6240E+10 kksfbc [KKSCHLFSP2] Cursor Pin 000000007BD3F260 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 1345020 1.7428E+10 kksfbc [KKSCHLFSP2] Cursor Pin 000000007C08D440 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 1273888 1.9013E+10 kksLockDelete [KKSCHLPIN6] Cursor Pin 000000007C08D440 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

6 rows selected.

SYS@book> @ fcha 000000007BD3EC98

LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR

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

SGA 000000007BD3E5D0 1 1 KGLH0^64ddee73 4096 recr 4095 000000007CA31368

SYS@book> @ fcha 000000007BD3F260

LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR

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

SGA 000000007BD3E5D0 1 1 KGLH0^64ddee73 4096 recr 4095 000000007CA31368

SYS@book> @ fcha 000000007C08D440

LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR

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

SGA 000000007C08D168 1 1 KGLH0^64ddee73 4096 freeabl 0 000000007CA31368

--//3個子游標的KSMCHPAR=000000007CA31368.不過位置不一樣,1個在KSMCHCLS=recr.另外1個在KSMCHCLS=freeabl.

3.對比共享池的情況看看.

select a.* from x$ksmsp a where a.ksmchpar=hextoraw('000000007CA31368')

ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR

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

00007FDD4B32BE98 11124 1 1 1 KGLH0^64ddee73 000000007C08D168 4096 freeabl 0 000000007CA31368

00007FDD4B44A178 11921 1 1 1 KGLH0^64ddee73 000000007BD3E5D0 4096 recr 4095 000000007CA31368

--//可以發現如果子游標很多,會大量消耗父遊標堆0的空間,也就是父遊標堆0會變大.

--//參考連結:http://blog.itpub.net/267265/viewspace-2212504/

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

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

子游標控制程式碼地址 000000007BD26060 000000007CD3E1C0 SELECT DEPTNO FROM DEPT WHERE DEPTNO=10 0 0 0 000000007BF848C0 000000007BD3ED40 4528 8088 6080 18696 18696 1692266099 a31kd5tkdvvmm 0

子游標控制程式碼地址 000000007CE19EE0 000000007CD3E1C0 SELECT DEPTNO FROM DEPT WHERE DEPTNO=10 0 0 0 000000007C4BE340 000000007BD3F308 4528 8088 6080 18696 18696 1692266099 a31kd5tkdvvmm 1

子游標控制程式碼地址 000000007CD3B6D0 000000007CD3E1C0 SELECT DEPTNO FROM DEPT WHERE DEPTNO=10 0 0 0 000000007CBF7AC8 000000007C08D4E8 4544 8088 6080 18712 18712 1692266099 a31kd5tkdvvmm 2

父遊標控制程式碼地址 000000007CD3E1C0 000000007CD3E1C0 SELECT DEPTNO FROM DEPT WHERE DEPTNO=10 0 0 0 000000007CA31368 00 8792 0 0 8792 8792 1692266099 a31kd5tkdvvmm 65535

--//KGLOBHD0=000000007CA31368.

--//也就是metux的結構體在父遊標的堆0中,為什麼放在父遊標的堆0中,而不是各個子游標的堆0中.

--//集中管理嗎?不知道....

SCOTT@book> select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by to_number(substr(method,4)),3;

METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)

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

g2_150 150 19804 2970658

f2_150 150 19871 2980723

d2_150 150 20575 3086315

--//這樣因為引數不同,爭用相對減少,反而快一點點.

4.如果指令碼修改如下:

$ 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

--//這樣產生150個子游標.看看情況如何?重啟資料庫測試看看.

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

--//看到等待事件主要有:

cursor: mutex X

cursor: pin S

latch: shared pool

library cache lock

library cache: mutex X

--//主要是library cache lock

SYS@book> @ mutexy 20 a31kd5tkdvvmm

old 14: and kglobt03= decode('&&2','',kglobt03,lower('&&2'))

new 14: and kglobt03= decode('a31kd5tkdvvmm','',kglobt03,lower('a31kd5tkdvvmm'))

old 22: ORDER BY sum_sleeps DESC ) where rownum<= &1

new 22: ORDER BY sum_sleeps DESC ) where rownum<= 20

HASH SUM_SLEEPS SUM_GETS LOCATION MUTEX_TYPE MUTEX_ADDR SQLID KGLNAOWN C100

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

1692266099 86674 76708 kkslce [KKSCHLPIN2] Cursor Pin 000000007D93F0C8 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 44998 1963 kkslce [KKSCHLPIN2] Cursor Pin 000000007DCC25D0 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 21442 3322 kkslce [KKSCHLPIN2] Cursor Pin 000000007DD04320 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 21291 10570 kkslce [KKSCHLPIN2] Cursor Pin 000000007D87DCA0 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 10268 21744 kkslce [KKSCHLPIN2] Cursor Pin 000000007DD039A0 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 10268 18062620 kkscsPruneChild [KKSPRTLOC35] Cursor Parent 000000007D176240 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 6493 837144 kkslce [KKSCHLPIN2] Cursor Pin 000000007C506250 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 4228 21744 kkslce [KKSCHLPIN2] Cursor Pin 000000007D20C438 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 4077 15704 kkslce [KKSCHLPIN2] Cursor Pin 000000007C11AD58 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 4077 453 kkslce [KKSCHLPIN2] Cursor Pin 000000007D20CE38 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 3775 259720 kkslce [KKSCHLPIN2] Cursor Pin 000000007CB89DE0 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 3322 388674 kkslce [KKSCHLPIN2] Cursor Pin 000000007CA2F9D0 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 3322 151 kkslce [KKSCHLPIN2] Cursor Pin 000000007D1D6A10 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 3322 56625 kkslce [KKSCHLPIN2] Cursor Pin 000000007DCC2A90 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 3020 57078 kkslce [KKSCHLPIN2] Cursor Pin 000000007E248738 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 3020 29143 kkslce [KKSCHLPIN2] Cursor Pin 000000007C11A3D8 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 2567 10268 kkslce [KKSCHLPIN2] Cursor Pin 000000007DCC2F50 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 2416 8154 kkslce [KKSCHLPIN2] Cursor Pin 000000007D93F588 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 1963 92412 kkslce [KKSCHLPIN2] Cursor Pin 000000007CE74E10 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

1692266099 1963 850885 kgllkdl1 85 Library Cache 000000007D1701D0 a31kd5tkdvvmm SELECT DEPTNO FROM DEPT WHERE DEPTNO=10

20 rows selected.

SCOTT@book> select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by to_number(substr(method,4)),3;

METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)

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

h2_150 150 17556 2633437

g2_150 150 19804 2970658

f2_150 150 19871 2980723

C2_150 150 20481 3072117

d2_150 150 20575 3086315

e2_150 150 20589 3088339

6 rows selected.

--//執行更快一些.

--//另外我看到僅僅是cursor: pin S,還有許多與mutex有關的等待事件.自己還不熟悉.

SYS@book> @ ev_name cursor

old 1: select * from v$event_name where lower(name) like lower('%&&1%')

new 1: select * from v$event_name where lower(name) like lower('%cursor%')

EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS

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

278 877525844 cursor: mutex X idn value where 3875070507 4 Concurrency

279 1575214430 cursor: mutex S idn value where 3875070507 4 Concurrency

280 3085815766 cursor: pin X idn value where 3875070507 4 Concurrency

281 352301881 cursor: pin S idn value where 3875070507 4 Concurrency

282 1729366244 cursor: pin S wait on X idn value where 3875070507 4 Concurrency

SYS@book> @ ev_name mutex

old 1: select * from v$event_name where lower(name) like lower('%&&1%')

new 1: select * from v$event_name where lower(name) like lower('%mutex%')

EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS

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

272 39333034 SecureFile mutex 3875070507 4 Concurrency

278 877525844 cursor: mutex X idn value where 3875070507 4 Concurrency

279 1575214430 cursor: mutex S idn value where 3875070507 4 Concurrency

289 1646780882 library cache: mutex X idn value where 3875070507 4 Concurrency

290 2446268751 library cache: mutex S idn value where 3875070507 4 Concurrency

5.附上相關指令碼:

$ 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;

$ 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,

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,

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;

[20190329]探究sql語句相關mutexes補充2.txt

相關文章