[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.