[20260808]测试21c cursor_sharing=force下的library cache lock pin的情况.txt

--//补充测试21c下cursor_sharing=force下的library cache lock/library cache pin的情况.

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试：
SCOTT@book01p> @ spid
==============================
SID                           : 123
SERIAL#                       : 39288
PROCESS                       : 3100
SERVER                        : DEDICATED
SPID                          : 3102
PID                           : 49
P_SERIAL#                     : 4
KILL_COMMAND                  : alter system kill session '123,39288' immediate;
PL/SQL procedure successfully completed.

SCOTT@book01p> @ curs force
alter session set cursor_sharing=force
Session altered.

--//测试前执行Select * from dept where deptno=21多次，注意开头S大写，避免一些递归sql语句。

# bpftrace /home/oracle/sqllaji/bt/lkpn21x.bt 3102
Attached 4 probes
Tracing Oracle kgllkal/kglpnal... Target PID: 3102
Hit Ctrl-C to stop.

--//select * from dept where deptno= 21;
--//第1次执行：
[10:00:45] kgllkal #01 pid=3102 handle=00000000629bb2c0 mode=1 obj=select * from dept where deptno= :"SYS_B_0"
[10:00:45] kglpnal #01 pid=3102 handle=00000000629bb2c0 mode=2 obj=select * from dept where deptno= :"SYS_B_0"
[10:00:45] kgllkal #02 pid=3102 handle=000000007279f830 mode=2 obj=bookSYSCDB$ROOT
[10:00:45] kgllkal #03 pid=3102 handle=000000006ddc26b0 mode=2 obj=1847639831SYSCDB$ROOT
[10:00:45] kgllkal #04 pid=3102 handle=00000000629bad90 mode=2 obj=90c81d3b599d9a2e7bfc35d9acb552f3$BUILD$BOOK01P
[10:00:45] kgllkal #05 pid=3102 handle=00000000629bab60 mode=1 obj=
[10:00:45] kglpnal #02 pid=3102 handle=00000000629bab60 mode=3 obj=
[10:00:45] kgllkal #06 pid=3102 handle=000000007279f830 mode=2 obj=bookSYSCDB$ROOT
[10:00:45] kgllkal #07 pid=3102 handle=000000006ddc26b0 mode=2 obj=1847639831SYSCDB$ROOT
[10:00:45] kgllkal #08 pid=3102 handle=00000000629ba730 mode=1 obj=90c81d3b599d9a2e7bfc35d9acb552f3Child:0BOOK01P
[10:00:45] kglpnal #03 pid=3102 handle=00000000629ba730 mode=3 obj=90c81d3b599d9a2e7bfc35d9acb552f3Child:0BOOK01P
[10:00:45] kgllkal #09 pid=3102 handle=00000000671cd8c0 mode=1 obj=SCOTTBOOK01P
[10:00:45] kgllkal #10 pid=3102 handle=000000007279f830 mode=2 obj=bookSYSCDB$ROOT
[10:00:45] kgllkal #11 pid=3102 handle=000000006ddc26b0 mode=2 obj=1847639831SYSCDB$ROOT
[10:00:45] kgllkal #12 pid=3102 handle=0000000062bcd5a0 mode=2 obj=DEPTSCOTTBOOK01Pb
[10:00:45] kglpnal #04 pid=3102 handle=0000000062bcd5a0 mode=2 obj=DEPTSCOTTBOOK01Pb
[10:00:45] kgllkal #13 pid=3102 handle=000000007279f830 mode=2 obj=bookSYSCDB$ROOT
[10:00:45] kgllkal #14 pid=3102 handle=000000006ddc26b0 mode=2 obj=1847639831SYSCDB$ROOT
[10:00:45] kgllkal #15 pid=3102 handle=0000000062bc31f0 mode=2 obj=356430430180546253BOOK01P
[10:00:45] kglpnal #05 pid=3102 handle=0000000062bc31f0 mode=2 obj=356430430180546253BOOK01P
[10:00:45] kgllkal #16 pid=3102 handle=0000000062bbc380 mode=2 obj=7574277674127661070BOOK01P
[10:00:45] kglpnal #06 pid=3102 handle=0000000062bbc380 mode=2 obj=7574277674127661070BOOK01P
[10:00:45] kgllkal #17 pid=3102 handle=0000000062bb29f0 mode=2 obj=4204170303842060717BOOK01P
[10:00:45] kglpnal #07 pid=3102 handle=0000000062bb29f0 mode=2 obj=4204170303842060717BOOK01P
[10:00:45] kgllkal #18 pid=3102 handle=0000000062bb15c0 mode=2 obj=5467474030038125289BOOK01P
[10:00:45] kglpnal #08 pid=3102 handle=0000000062bb15c0 mode=2 obj=5467474030038125289BOOK01P
[10:00:45] kgllkal #19 pid=3102 handle=0000000062bbc380 mode=2 obj=7574277674127661070BOOK01P
[10:00:45] kglpnal #09 pid=3102 handle=0000000062bbc380 mode=2 obj=7574277674127661070BOOK01P

--//第2次执行：
[10:00:51] kgllkal #20 pid=3102 handle=00000000629bb2c0 mode=1 obj=select * from dept where deptno= :"SYS_B_0"
[10:00:51] kgllkal #21 pid=3102 handle=00000000629bab60 mode=1 obj=
[10:00:51] kgllkal #22 pid=3102 handle=0000000062bcd5a0 mode=2 obj=DEPTSCOTTBOOK01Pb
[10:00:51] kglpnal #10 pid=3102 handle=0000000062bcd5a0 mode=2 obj=DEPTSCOTTBOOK01Pb

--//第3次执行：
[10:00:56] kgllkal #23 pid=3102 handle=00000000629bb2c0 mode=1 obj=select * from dept where deptno= :"SYS_B_0"
[10:00:56] kgllkal #24 pid=3102 handle=00000000629bab60 mode=1 obj=

--//第4次执行：
--//没有显示输出。

3.继续：
--//sql语句换一个文字变量执行看看。

# bpftrace /home/oracle/sqllaji/bt/lkpn21x.bt 3102
Attached 4 probes
Tracing Oracle kgllkal/kglpnal... Target PID: 3102
Hit Ctrl-C to stop.

--//第1次执行：select * from dept where deptno= 22;
--//没有显示输出。

--//第2次执行：select * from dept where deptno= 23;
--//没有显示输出。
--//说明以后的会话类似的语句oracle都是软软解析。

SYS@book> select kglnaobj,kglnahsv,KGLOBT03,KGLNAHSH,kglobt09 from x$kglob where kglnaobj like 'select * from dept w%';
KGLNAOBJ                                                     KGLNAHSV                         KGLOBT03        KGLNAHSH   KGLOBT09
------------------------------------------------------------ -------------------------------- ------------- ---------- ----------
select * from dept where deptno= :"SYS_B_0"                  90c81d3b599d9a2e7bfc35d9acb552f3 7rz1pv6qbanrm 2897564403          0
select * from dept where deptno= :"SYS_B_0"                  90c81d3b599d9a2e7bfc35d9acb552f3 7rz1pv6qbanrm 2897564403      65535
--//并没有11g下看到的其他chunk。

4.如果文字变量的sql语句先执行的情况下情况如何呢。
--//打开新会话，执行如下sql语句多次，注意cursor_sharing=exact
SCOTT@book01p> select * from dept where deptno= 24;
no rows selected
--//以上语句执行3次。

--//继续在原会话执行：

# bpftrace /home/oracle/sqllaji/bt/lkpn21x.bt 3102
Attached 4 probes
Tracing Oracle kgllkal/kglpnal... Target PID: 3102
Hit Ctrl-C to stop.

--//第1次执行：select * from dept where deptno= 24;
[10:11:27] kgllkal #01 pid=3102 handle=000000006294cbb0 mode=1 obj=select * from dept where deptno= 24
[10:11:27] kgllkal #02 pid=3102 handle=000000006b39bbb0 mode=1 obj=
--//找到原始语句的光标，选择该执行计划，软解析执行。

--//第2次执行：select * from dept where deptno= 24;
--//没有显示输出。
--//说明oracle的执行是软软解析。

5.小结：
--//可以看出在cursor_sharing=force的情况如果原始sql语句的父子光标不存在，21c下直接转换而绑定变量的格式执行。
--//如果原始sql语句的父子光标存在的情况，情况类似cursor_sharing=exact,即使设置会话cursor_sharing=force，oracle还是选择文
--//字变量的方式执行。

--//可以发现与我前几天在11g下的完全情况不同，说明oracle 新版本一直在改进其算法，选择更加合理的执行方式，消耗共享内存更小。

6.测试使用代码：
# cat  /home/oracle/sqllaji/bt/lkpn21x.bt
#!/usr/bin/env bpftrace

BEGIN
{
    printf("Tracing Oracle kgllkal/kglpnal... Target PID: %d\n", $1);
    printf("Hit Ctrl-C to stop.\n\n");
}

uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kgllkal
/ $1 == 0 || pid == $1 /
{
    @lk++;
    printf("[%s] kgllkal #%02d pid=%d handle=%016lx mode=%d obj=%s\n",
        strftime("%H:%M:%S", nsecs),
        @lk, pid, arg2, arg3,
        str(arg2 + 0x1c8, 256));
}

uprobe:/u01/app/oracle/product/21.0.0/dbhome_1/bin/oracle:kglpnal
/ $1 == 0 || pid == $1 /
{
    @pn++;
    printf("[%s] kglpnal #%02d pid=%d handle=%016lx mode=%d obj=%s\n",
        strftime("%H:%M:%S", nsecs),
        @pn, pid, arg2, arg3,
        str(arg2 + 0x1c8, 256));
}

END
{
    printf("\nTotal kgllkal: %d, Total kglpnal: %d\n", @lk, @pn);
    delete(@lk);
    delete(@pn);
}