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