[20241012]dbms_shared_pool.keep减少硬分析吗.txt

--//以前遇到的情况 [20240409]为什么一条sql语句在实例2执行要慢的分析.txt
--//别人建议使用dbms_shared_pool.keep 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> select * from dept;
DEPTNO DNAME      LOC
------ ---------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH   DALLAS
    30 SALES      CHICAGO
    40 OPERATIONS BOSTON

SCOTT@book01p> @ hash

HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
3599690174 3154rqzb8xudy            0      59838      3383998547  d68ee9be  2024-10-12 09:58:33    16777221

SYS@book> select name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;
NAME                           EXECUTIONS STATUS KEP TYPE   ADDR
------------------------------ ---------- ------ --- ------ ----------------
select * from dept                      1 VALID  NO  CURSOR 0000000065EB1FA8
select * from dept                      1 VALID  NO  CURSOR 0000000065EB3508 --//父游标

SYS@book> @ sharepool/shp4 3154rqzb8xudy 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                 KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ------------------- -------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address   0000000065EB1FA8 0000000065EB3508 select * from dept         0          0          0 0000000065EB1DE8 0000000065EB2D30       8080      12128       3283     23491      23491 3599690174 3154rqzb8xudy          0
parent handle address  0000000065EB3508 0000000065EB3508 select * from dept         0          0          0 0000000065ECBD50 00                     4064          0          0      4064       4064 3599690174 3154rqzb8xudy      65535

SYS@book> exec dbms_shared_pool.keep('0000000065EB3508,3599690174','c');
PL/SQL procedure successfully completed.

SYS@book> select name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;
NAME                           EXECUTIONS STATUS KEP TYPE   ADDR
------------------------------ ---------- ------ --- ------ ----------------
select * from dept                      1 VALID  NO  CURSOR 0000000065EB1FA8
select * from dept                      1 VALID  NO  CURSOR 0000000065EB3508

SYS@book> exec dbms_shared_pool.keep('0000000065EB1FA8,3599690174','c');
PL/SQL procedure successfully completed.

SYS@book> select name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;
NAME                           EXECUTIONS STATUS KEP TYPE   ADDR
------------------------------ ---------- ------ --- ------ ----------------
select * from dept                      1 VALID  NO  CURSOR 0000000065EB1FA8
select * from dept                      1 VALID  NO  CURSOR 0000000065EB3508
--//在cdb下执行完全不起作用，在pdb下执行：

SYS@book01p> select name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;
NAME                           EXECUTIONS STATUS KEP TYPE   ADDR
------------------------------ ---------- ------ --- ------ ----------------
select * from dept                      1 VALID  NO  CURSOR 0000000065EB1FA8
select * from dept                      1 VALID  NO  CURSOR 0000000065EB3508

SYS@book01p> exec dbms_shared_pool.keep('0000000065EB3508,3599690174','c');
PL/SQL procedure successfully completed.

SYS@book01p> select name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;

NAME                           EXECUTIONS STATUS KEP TYPE   ADDR
------------------------------ ---------- ------ --- ------ ----------------
select * from dept                      1 VALID  YES CURSOR 0000000065EB1FA8
select * from dept                      1 VALID  YES CURSOR 0000000065EB3508

SYS@book01p> exec dbms_shared_pool.keep('0000000065EB1FA8,3599690174','c');
PL/SQL procedure successfully completed.
--//注：应该不需要再带入子光标的地址再执行1次，我仅仅想测试看看是否报错。上面2行kept=YES。

SYS@book01p> select name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;
NAME                           EXECUTIONS STATUS KEP TYPE   ADDR
------------------------------ ---------- ------ --- ------ ----------------
select * from dept                      1 VALID  YES CURSOR 0000000065EB1FA8
select * from dept                      1 VALID  YES CURSOR 0000000065EB3508

SYS@book01p> alter system flush shared_pool;
System altered.

SYS@book01p> select name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;
no rows selected

--//在cdb下以sys用户执行：
SYS@book> select name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;
NAME                           EXECUTIONS STATUS  KEP TYPE   ADDR
------------------------------ ---------- ------- --- ------ ----------------
select * from dept                      1 UNKNOWN NO  CURSOR 0000000065EB1FA8
select * from dept                      1 VALID   YES CURSOR 0000000065EB3508
--//可以发现子光标那行的KEPT=NO，也就是根本没有pin住。

SYS@book> @ sharepool/shp4 3154rqzb8xudy 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                  KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ------------------ ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address   0000000065EB1FA8 0000000065EB3508 select * from dept          0          0          1 00               00                        0          0       3283      3283       3283 3599690174 3154rqzb8xudy          0
parent handle address  0000000065EB3508 0000000065EB3508 select * from dept          0          0          1 0000000065ECBD50 00                     4064          0          0      4064       4064 3599690174 3154rqzb8xudy      65535
--//父游标句柄以及父游标堆0还在。子游标句柄也在，但是堆0，堆6都清除了，这样基本没用。
--//当然我手工刷新alter system flush shared_pool;，如果不刷新，也许保存在共享池更长一些。
--//我更倾向于这样设置没用。
--//我以前使用sql profile设置稳定执行计划，仅仅减少了少量的分析时间。
--//不过有空可以尝试看看，是否起作用。

3.附上shp4.sql:
$ cat sharepool/shp4.sql
column N0_6_16 format 99999999
column handle_type format a22

set term off
define vc=&&2
col 2 new_value 2
column text format a20
column hex_status format a10
SELECT text
      ,n10 "2"
      ,REPLACE (TO_CHAR (n10, LPAD ('x', 16, 'x')), ' ') c16
      ,hex_status
  FROM (SELECT '&&vc' text
--             ,DECODE ( hex_status ,'hex', TO_NUMBER ( SUBSTR ('&&vc', INSTR (LOWER ('&&vc'), 'x',-1) + 1) ,'xxxxxxxxxxxxxxxx') ,'dec', TO_NUMBER ('&&vc') ,0) n10
              ,DECODE ( hex_status ,'hex', TO_NUMBER ( replace (lower('&&vc'), 'x') ,'xxxxxxxxxxxxxxxx') ,'dec', TO_NUMBER ('&&vc') ,0) n10
              ,hex_status
          FROM (SELECT CASE
                          WHEN REGEXP_LIKE (LOWER ('&&vc'), '^[0-9]+$') THEN 'dec'
                          WHEN REGEXP_LIKE (LOWER ('&&vc') ,'^0?[xX]?[0-9a-f]+[xX]?$') THEN 'hex'
                          ELSE 'other'
                       END AS Hex_Status
                  FROM DUAL));
set term on

SELECT /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */
       DECODE (kglhdadr, kglhdpar, 'parent handle address', 'child handle address') handle_type,
       kglhdadr,
       kglhdpar,
       --//substr(kglnaobj,1,40) c40,
       substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaobj||'.'||kglnaown), '(name not found)'),chr(13),'') ,1,40)  c40,
           KGLHDLMD,
           KGLHDPMD,
           kglhdivc,
       kglobhd0,
       kglobhd6,
       kglobhs0,kglobhs6,kglobt16,
       kglobhs0+kglobhs6+kglobt16 N0_6_16,
           kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
           kglnahsh,
           kglobt03,
           kglobt09
  FROM x$kglob
 WHERE kglobt03 = lower('&1') or KGLNAHSH= &2;