[20240824]测试21c _column_tracking_level=17.txt

--//测试看看_column_tracking_level=17是否减少chunk的建立.

1.环境:
SYS@book> @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.

SYS@book> @ hide _column_tracking_level
SYS@book> @pr
==============================
NAME                          : _column_tracking_level
DESCRIPTION                   : column usage tracking
DEFAULT_VALUE                 : TRUE
SESSION_VALUE                 : 53
SYSTEM_VALUE                  : 53
ISSES_MODIFIABLE              : TRUE
ISSYS_MODIFIABLE              : IMMEDIATE
PL/SQL procedure successfully completed.
--//ISSYS_MODIFIABLE=IMMEDIATE,该参数会话级别能修改,立即生效.

2.测试脚本:
$ cat lkpn.gdb
set pagination off
set logging file /tmp/lkpn.log
set logging overwrite on
set logging on
set $lk  = 0
set $pn  = 0

#break kgllkal if $rcx==3
break kgllkal
commands
 silent
 printf "kgllkal count %02d -- handle address: %016x, mode: %d ", ++$lk ,$rdx ,$rcx
 echo kglnaobj address:
 x/s $rdx+0x1c8
 c
 end

#break kglpnal if $rcx==3
break kglpnal
commands
 silent
 printf "kglpnal count %02d -- handle address: %016x, mode: %d ", ++$pn ,$rdx ,$rcx
 echo kglnaobj address:
 x/s $rdx+0x1c8
 c
 end

3.测试:
--//"_column_tracking_level"=53的情况,以前的测试,直接贴上来.
kgllkal count 93 -- handle address: 0000000065b0d0d0, mode: 1 kglnaobj address:0x65b0d298:      "Select * from dept where deptno==10"
kglpnal count 35 -- handle address: 0000000065b0d0d0, mode: 2 kglnaobj address:0x65b0d298:      "Select * from dept where deptno==10"
kgllkal count 94 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:      "bookSYSCDB$ROOT"
kgllkal count 95 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 96 -- handle address: 000000006e261978, mode: 2 kglnaobj address:0x6e261b40:      "85d6f5c0bce7df033db8e86ed0624d44$BUILD$BOOK01P"
kgllkal count 97 -- handle address: 000000006f9af9a8, mode: 1 kglnaobj address:0x6f9afb70:      ""
kglpnal count 36 -- handle address: 000000006f9af9a8, mode: 3 kglnaobj address:0x6f9afb70:      ""
kgllkal count 98 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:      "bookSYSCDB$ROOT"
kgllkal count 99 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 100 -- handle address: 0000000063d4de10, mode: 1 kglnaobj address:0x63d4dfd8:     "85d6f5c0bce7df033db8e86ed0624d44Child:0BOOK01P"
kglpnal count 37 -- handle address: 0000000063d4de10, mode: 3 kglnaobj address:0x63d4dfd8:      "85d6f5c0bce7df033db8e86ed0624d44Child:0BOOK01P"
kgllkal count 101 -- handle address: 0000000066bf5db0, mode: 1 kglnaobj address:0x66bf5f78:     "SCOTTBOOK01P"
kgllkal count 102 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:     "bookSYSCDB$ROOT"
kgllkal count 103 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:     "1073777561SYSCDB$ROOT"
kgllkal count 104 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:     "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 38 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:      "DEPTSCOTTBOOK01Pp~\027k"
kgllkal count 105 -- handle address: 0000000067504158, mode: 2 kglnaobj address:0x67504320:     "5358706841214419813BOOK01P"
kglpnal count 39 -- handle address: 0000000067504158, mode: 2 kglnaobj address:0x67504320:      "5358706841214419813BOOK01P"
kgllkal count 106 -- handle address: 0000000069ef2ff8, mode: 2 kglnaobj address:0x69ef31c0:     "1256087081022357994BOOK01P"
kglpnal count 40 -- handle address: 0000000069ef2ff8, mode: 2 kglnaobj address:0x69ef31c0:      "1256087081022357994BOOK01P"
kgllkal count 107 -- handle address: 0000000068e857f0, mode: 2 kglnaobj address:0x68e859b8:     "13547376130454050250BOOK01P"
kglpnal count 41 -- handle address: 0000000068e857f0, mode: 2 kglnaobj address:0x68e859b8:      "13547376130454050250BOOK01P"
kgllkal count 108 -- handle address: 000000006f394f70, mode: 2 kglnaobj address:0x6f395138:     "4448762010415191240BOOK01P"
kglpnal count 42 -- handle address: 000000006f394f70, mode: 2 kglnaobj address:0x6f395138:      "4448762010415191240BOOK01P"
kgllkal count 109 -- handle address: 0000000069ef2ff8, mode: 2 kglnaobj address:0x69ef31c0:     "1256087081022357994BOOK01P"
kglpnal count 43 -- handle address: 0000000069ef2ff8, mode: 2 kglnaobj address:0x69ef31c0:      "1256087081022357994BOOK01P"
--//第1次执行,注意看后面一堆数字,也就是建立许多chunk.

kgllkal count 110 -- handle address: 0000000065b0d0d0, mode: 1 kglnaobj address:0x65b0d298:     "Select * from dept where deptno==10"
kgllkal count 111 -- handle address: 000000006f9af9a8, mode: 1 kglnaobj address:0x6f9afb70:     ""
kgllkal count 112 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:     "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 44 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:      "DEPTSCOTTBOOK01Pp~\027k"
--//第2次执行

kgllkal count 113 -- handle address: 0000000065b0d0d0, mode: 1 kglnaobj address:0x65b0d298:     "Select * from dept where deptno==10"
kgllkal count 114 -- handle address: 000000006f9af9a8, mode: 1 kglnaobj address:0x6f9afb70:     ""
--//第3次执行

--//== 实际上一个=

4.修改隐含参数_column_tracking_level=17的测试:

SCOTT@book01p> alter session set "_column_tracking_level"=17;
Session altered.

SCOTT@book01p> Select * from dept where deptno=51;
no rows selected

--//gdb跟踪结果.
$ rlgdb -f -p  6216 -x lkpn.gdb
...

Breakpoint 1 at 0x15367e90
Breakpoint 2 at 0x1536c020
(gdb) c
Continuing.
kgllkal count 01 -- handle address: 000000006cb57310, mode: 1 kglnaobj address:0x6cb574d8:      "Select * from dept where deptno=51"
kglpnal count 01 -- handle address: 000000006cb57310, mode: 2 kglnaobj address:0x6cb574d8:      "Select * from dept where deptno=51"
kgllkal count 02 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:      "bookSYSCDB$ROOT"
kgllkal count 03 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 04 -- handle address: 000000006abcfca0, mode: 2 kglnaobj address:0x6abcfe68:      "7a559592244c331b97553705371057c7$BUILD$BOOK01P"
kgllkal count 05 -- handle address: 0000000064a154c0, mode: 1 kglnaobj address:0x64a15688:      ""
kglpnal count 02 -- handle address: 0000000064a154c0, mode: 3 kglnaobj address:0x64a15688:      ""
kgllkal count 06 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:      "bookSYSCDB$ROOT"
kgllkal count 07 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 08 -- handle address: 000000006699ba48, mode: 1 kglnaobj address:0x6699bc10:      "7a559592244c331b97553705371057c7Child:0BOOK01P"
kglpnal count 03 -- handle address: 000000006699ba48, mode: 3 kglnaobj address:0x6699bc10:      "7a559592244c331b97553705371057c7Child:0BOOK01P"
kgllkal count 09 -- handle address: 0000000066bf5db0, mode: 1 kglnaobj address:0x66bf5f78:      "SCOTTBOOK01P"
kgllkal count 10 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:      "bookSYSCDB$ROOT"
kgllkal count 11 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 12 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:      "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 04 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:      "DEPTSCOTTBOOK01Pp~\027k"
kgllkal count 13 -- handle address: 0000000067504158, mode: 2 kglnaobj address:0x67504320:      "5358706841214419813BOOK01P"
kglpnal count 05 -- handle address: 0000000067504158, mode: 2 kglnaobj address:0x67504320:      "5358706841214419813BOOK01P"
--//第1次执行
--//共18出, kgllkal=13,kglpnal=5.

kgllkal count 14 -- handle address: 000000006cb57310, mode: 1 kglnaobj address:0x6cb574d8:      "Select * from dept where deptno=51"
kgllkal count 15 -- handle address: 0000000064a154c0, mode: 1 kglnaobj address:0x64a15688:      ""
kgllkal count 16 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:      "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 06 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:      "DEPTSCOTTBOOK01Pp~\027k"
--//第2次执行

kgllkal count 17 -- handle address: 000000006cb57310, mode: 1 kglnaobj address:0x6cb574d8:      "Select * from dept where deptno=51"
kgllkal count 18 -- handle address: 0000000064a154c0, mode: 1 kglnaobj address:0x64a15688:      ""
--//第3次执行

--//可以看出明显减少了chunk的建立.如果你字段很多,会建立许多chunk的.
--//scott.dept的表字段不多,你可以尝试表emp看看.

5.修改隐含参数_column_tracking_level=1的测试:

SCOTT@book01p> alter session set "_column_tracking_level"=1;
Session altered.

SCOTT@book01p> Select * from dept where deptno=52;
no rows selected

--//gdb跟踪结果.
kgllkal count 22 -- handle address: 0000000064970660, mode: 1 kglnaobj address:0x64970828:      "Select * from dept where deptno=52"
kglpnal count 08 -- handle address: 0000000064970660, mode: 2 kglnaobj address:0x64970828:      "Select * from dept where deptno=52"
kgllkal count 23 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:      "bookSYSCDB$ROOT"
kgllkal count 24 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 25 -- handle address: 0000000065e8b1c8, mode: 2 kglnaobj address:0x65e8b390:      "d43828613b12b3f5524b106ff35244f5$BUILD$BOOK01P"
kgllkal count 26 -- handle address: 0000000064972c30, mode: 1 kglnaobj address:0x64972df8:      ""
kglpnal count 09 -- handle address: 0000000064972c30, mode: 3 kglnaobj address:0x64972df8:      ""
kgllkal count 27 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:      "bookSYSCDB$ROOT"
kgllkal count 28 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 29 -- handle address: 0000000067daa6a0, mode: 1 kglnaobj address:0x67daa868:      "d43828613b12b3f5524b106ff35244f5Child:0BOOK01P"
kglpnal count 10 -- handle address: 0000000067daa6a0, mode: 3 kglnaobj address:0x67daa868:      "d43828613b12b3f5524b106ff35244f5Child:0BOOK01P"
kgllkal count 30 -- handle address: 0000000066bf5db0, mode: 1 kglnaobj address:0x66bf5f78:      "SCOTTBOOK01P"
kgllkal count 31 -- handle address: 00000000701df138, mode: 2 kglnaobj address:0x701df300:      "bookSYSCDB$ROOT"
kgllkal count 32 -- handle address: 000000006b97c9f8, mode: 2 kglnaobj address:0x6b97cbc0:      "1073777561SYSCDB$ROOT"
kgllkal count 33 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:      "DEPTSCOTTBOOK01Pp~\027k"
kglpnal count 11 -- handle address: 0000000064a29230, mode: 2 kglnaobj address:0x64a293f8:      "DEPTSCOTTBOOK01Pp~\027k"
--//共16出, kgllkal=12,kglpnal=4.