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

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