[20220413]shared pool latch與使用sga heap的疑問3.txt

[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 中分配。