[20220412]shared pool latch與使用sga heap的疑問2.txt

[20220412]shared pool latch與使用sga heap的疑問2.txt

--//接著前面的測試,驗證註解為KGLHD,KGLDA的情況.
--//首先註解為KGLHD,KGLDA,一定分配在特定sga heap(N,0) 與sga heap(N+,0)之間,前面的測試已經驗證.
--//先驗證KGLHD的情況.

--//首先如果過濾包含KGLHD行,就可以知道chunk的開始地址.開始地址+48(0x30),就是對應父/子游標控制程式碼的地址(注:至少對於11g是這樣
--//的情況).

SYS@127.0.0.1:9014/ywdb> select KSMCHIDX,count(*) from xx2 where KSMCHCOM='KGLHD' group by  cube(KSMCHIDX) order by 1;
  KSMCHIDX   COUNT(*)
---------- ----------
         1      28897
         2      31911
         3      20928
         4      28428
         5      29036
         6      31497
         7      26083
               196780

8 rows selected.

with t_xx2 as
( select KSMCHIDX,hextoraw(to_char((to_number(rawtohex(ksmchptr),'xxxxxxxxxxxxxxxx')+48), 'FM0xxxxxxxxxxxxxxx')) ksmchptr2 from xx2 where KSMCHCOM='KGLHD')
SELECT CASE WHEN (KGLHDNSD='SQL AREA STATS' and KGLOBTYD='CURSOR STATS' ) then
mod(mod(TO_NUMBER(substr(kglnaobj,-8),'xxxxxxxxxxxxxxxx'),131072),7)+1
else
mod(mod(kglnahsh, 131072),7)+1
end N10,t_xx2.KSMCHIDX
from xx1,t_xx2
where
(CASE WHEN (KGLHDNSD='SQL AREA STATS' and KGLOBTYD='CURSOR STATS' ) then
mod(mod(TO_NUMBER(substr(kglnaobj,-8),'xxxxxxxxxxxxxxxx'),131072),7)+1
else
mod(mod(kglnahsh, 131072),7)+1
end) = t_xx2.KSMCHIDX and t_xx2.ksmchptr2=xx1.KGLHDADR;

--//輸出太長，結果不貼出了。共計194,313條。

with t_xx2 as
( select KSMCHIDX,hextoraw(to_char((to_number(rawtohex(ksmchptr),'xxxxxxxxxxxxxxxx')+48), 'FM0xxxxxxxxxxxxxxx')) ksmchptr2 from xx2 where KSMCHCOM='KGLHD')
SELECT CASE WHEN (KGLHDNSD='SQL AREA STATS' and KGLOBTYD='CURSOR STATS' ) then
mod(mod(TO_NUMBER(substr(kglnaobj,-8),'xxxxxxxxxxxxxxxx'),131072),7)+1
else
mod(mod(kglnahsh, 131072),7)+1
end N10,t_xx2.KSMCHIDX,xx1.KGLHDADR,xx1.KGLNAOBJ
from xx1,t_xx2
where
(CASE WHEN (KGLHDNSD='SQL AREA STATS' and KGLOBTYD='CURSOR STATS' ) then
mod(mod(TO_NUMBER(substr(kglnaobj,-8),'xxxxxxxxxxxxxxxx'),131072),7)+1
else
mod(mod(kglnahsh, 131072),7)+1
end) <> t_xx2.KSMCHIDX and t_xx2.ksmchptr2=xx1.KGLHDADR;

no rows selected
--//都是符合的記錄。

select KSMCHIDX,count(*) from
(with t_xx2 as
( select KSMCHIDX,hextoraw(to_char((to_number(rawtohex(ksmchptr),'xxxxxxxxxxxxxxxx')+48), 'FM0xxxxxxxxxxxxxxx')) ksmchptr2 from xx2 where KSMCHCOM='KGLHD')
SELECT CASE WHEN (KGLHDNSD='SQL AREA STATS' and KGLOBTYD='CURSOR STATS' ) then
mod(mod(TO_NUMBER(substr(kglnaobj,-8),'xxxxxxxxxxxxxxxx'),131072),7)+1
else
mod(mod(kglnahsh, 131072),7)+1
end N10,t_xx2.KSMCHIDX
from xx1,t_xx2
where
(CASE WHEN (KGLHDNSD='SQL AREA STATS' and KGLOBTYD='CURSOR STATS' ) then
mod(mod(TO_NUMBER(substr(kglnaobj,-8),'xxxxxxxxxxxxxxxx'),131072),7)+1
else
mod(mod(kglnahsh, 131072),7)+1
end) = t_xx2.KSMCHIDX and t_xx2.ksmchptr2=xx1.KGLHDADR）
group by cube(KSMCHIDX) order by 1 ;

  KSMCHIDX   COUNT(*)
---------- ----------
         1      28711
         2      31659
         3      20352
         4      28042
         5      28709
         6      31286
         7      25554
               194313
8 rows selected.

--//僅僅存在少量的記錄在表xx1不存在。

select * from xx2 where KSMCHIDX=1 and not exists (select 1 from xx1 where xx1.KGLHDADR = hextoraw(to_char((to_number(rawtohex(xx2.ksmchptr),'xxxxxxxxxxxxxxxx')+48), 'FM0xxxxxxxxxxxxxxx')))
and  KSMCHCOM='KGLHD';

--//共計186條。28897-28711 = 186

select ksmchidx,count(*) from xx2 where  not exists (select 1 from xx1 where xx1.KGLHDADR = hextoraw(to_char((to_number(rawtohex(xx2.ksmchptr),'xxxxxxxxxxxxxxxx')+48), 'FM0xxxxxxxxxxxxxxx')))
and  KSMCHCOM='KGLHD'
group by cube(ksmchidx)
order by 1;

  KSMCHIDX   COUNT(*)
---------- ----------
         1        186
         2        252
         3        576
         4        386
         5        327
         6        211
         7        529
                 2467
8 rows selected.

--//196780 - 194313 = 2467.