[20220413]shared pool latch与使用sga heap的疑问3.txt

--//接着前面的测试,验证注解为KGLHD,KGLDA的情况.
--//首先注解为KGLHD,KGLDA,一定分配在特定sga heap(N,0) 与sga heap(N+,0)之间.,前面的测试已经验证.
--//本文验证KGLDA的情况.

--//首先x$kglob的父/子游标的kglobhd0,kglobhd6 等于x$ksmsp的ksmchpar.

SYS@127.0.0.1:9014/ywdb> select KSMCHIDX,count(*) from xx2 where KSMCHCOM='KGLDA' group by  cube(KSMCHIDX) order by 1;
  KSMCHIDX   COUNT(*)
---------- ----------
         1       9780
         2       9709
         3       7753
         4       9139
         5       8945
         6      10124
         7       7712
                63162

8 rows selected.

create table xx4 as
with tt1 as (select /*+ MATERIALIZE */ distinct ksmchpar ksmchparx,
(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
 from xx1,xx2 where  (xx1.kglobhd0 = xx2.ksmchpar  or xx1.kglobhd6 = xx2.ksmchpar ) and xx2.ksmchpar<>'00')
select  * from xx2,tt1 where KSMCHCOM='KGLDA'
and  
to_number( tt1.ksmchparx,'XXXXXXXXXXXXXXXX') between to_number(xx2.ksmchptr,'XXXXXXXXXXXXXXXX') and to_number(xx2.ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1;

--//先建立表XX4，因为查询很慢，便于反复查询。

SYS@127.0.0.1:9014/ywdb> select count(*) from xx4;
  COUNT(*)
----------
     62972

SYS@127.0.0.1:9014/ywdb> select count(*) from xx4 where n10=ksmchidx;
  COUNT(*)
----------
     62972

SYS@127.0.0.1:9014/ywdb> select count(*) from xx4 where n10<>ksmchidx;
  COUNT(*)
----------
         0

--//仅仅少量63162-62972 = 190不存在。
--//这样经过前面的测试，基本可以肯定使用那个shared pool latch ，其chunk在对应的sga heap 中分配。