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