[20241013]记录刷新共享池遇到的问题.txt

--//昨天的测试遇到的问题，做一个记录：

1.环境：
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.测试：
--//session 1:
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-13 11:07:43    16777216

--//session 2,cdb下:
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   0000000063E53140 000000006767E4D8 select * from dept                                0          0          0 0000000063E6E630 0000000067656C40       8080      12128       3283     23491      23491 3599690174 3154rqzb8xudy          0
parent handle address  000000006767E4D8 000000006767E4D8 select * from dept                                0          0          0 0000000063DA41E0 00                     4064          0          0      4064       4064 3599690174 3154rqzb8xudy      65535

--//session 3,pdb下:
SYS@book01p> @ 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   00000000683719E0 000000006306DBC0 select * from dept                                0          0          0 00000000682CD368 00000000682329F0       8080      12128       3283     23491      23491 3599690174 3154rqzb8xudy          0
parent handle address  000000006306DBC0 000000006306DBC0 select * from dept                                0          0          0 000000006A71F988 00                     4064          0          0      4064       4064 3599690174 3154rqzb8xudy      65535

SYS@book01p> @ keep_sql 3154rqzb8xudy
SQL_ID        EXECUTIONS PLAN_HASH_VALUE ADDRESS          HASH_VALUE NAME                           C80
------------- ---------- --------------- ---------------- ---------- ------------------------------ --------------------------------------------------------------------------------
3154rqzb8xudy          1      3383998547 000000006306DBC0 3599690174 000000006306DBC0,3599690174    exec dbms_shared_pool.keep('000000006306DBC0,3599690174),'C');

exec dbms_shared_pool.keep('000000006306DBC0,3599690174','C');

SYS@book01p> exec dbms_shared_pool.keep('000000006306DBC0,3599690174','C');
PL/SQL procedure successfully completed.

SYS@book01p> @ 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   00000000683719E0 000000006306DBC0 select * from dept                                0          0          0 00000000682CD368 00000000682329F0       8080      12128       3283     23491      23491 3599690174 3154rqzb8xudy          0
parent handle address  000000006306DBC0 000000006306DBC0 select * from dept                                0          0          0 000000006A71F988 00                     4064          0          0      4064       4064 3599690174 3154rqzb8xudy      65535

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                         00000000683719E0
select * from dept                      1 VALID                          YES CURSOR                         000000006306DBC0

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
--//在pdb下查询已经看不见记录。

--//session 2,cdb下:
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   00000000683719E0 000000006306DBC0 select * from dept                                0          0          1 00               00                        0          0       3283      3283       3283 3599690174 3154rqzb8xudy          0
parent handle address  000000006306DBC0 000000006306DBC0 select * from dept                                0          0          1 000000006A71F988 00                     4064          0          0      4064       4064 3599690174 3154rqzb8xudy      65535

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                         00000000683719E0
select * from dept                      1 VALID                          YES CURSOR                         000000006306DBC0
--//实际上在cdb下依旧可以看到相关记录。换一句话将在pdb下刷新共享池，许多相关chunk并没有刷出共享池。
SYS@book> select con_id,name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;
    CON_ID NAME                           EXECUTIONS STATUS                         KEP TYPE                           ADDR
---------- ------------------------------ ---------- ------------------------------ --- ------------------------------ ----------------
         1 select * from dept                      1 UNKNOWN                        NO  CURSOR                         00000000683719E0
         1 select * from dept                      1 VALID                          YES CURSOR                         000000006306DBC0
--//con_id=1.

--//session 1,再次执行：
SCOTT@book01p> select * from dept;
    DEPTNO DNAME                          LOC
---------- ------------------------------ -------------
        10 ACCOUNTING                     NEW YORK
        20 RESEARCH                       DALLAS
        30 SALES                          CHICAGO
        40 OPERATIONS                     BOSTON

--//session 2,cdb下:
SYS@book> select con_id,name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;
    CON_ID NAME                           EXECUTIONS STATUS                         KEP TYPE                           ADDR
---------- ------------------------------ ---------- ------------------------------ --- ------------------------------ ----------------
         3 select * from dept                      1 VALID                          NO  CURSOR                         00000000683719E0
         1 select * from dept                      2 VALID                          YES CURSOR                         000000006306DBC0

--//session 3,pdb下:
SYS@book01p> select con_id,name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;
    CON_ID NAME                           EXECUTIONS STATUS                         KEP TYPE                           ADDR
---------- ------------------------------ ---------- ------------------------------ --- ------------------------------ ----------------
         3 select * from dept                      1 VALID                          NO  CURSOR                         00000000683719E0
         3 select * from dept                      2 VALID                          YES CURSOR                         000000006306DBC0
--//看到con_id不同。

--//session 2,cdb下:
SYS@book> alter system flush shared_pool;
System altered.

SYS@book> select con_id,name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;
    CON_ID NAME                           EXECUTIONS STATUS                         KEP TYPE                           ADDR
---------- ------------------------------ ---------- ------------------------------ --- ------------------------------ ----------------
         1 select * from dept                      1 UNKNOWN                        NO  CURSOR                         00000000683719E0
         1 select * from dept                      2 VALID                          YES CURSOR                         000000006306DBC0

--//session 3,pdb下:
SYS@book01p> select con_id,name, executions, status, kept, type,addr from v$db_object_cache where hash_value=3599690174;
no rows selected

--//做一个记录，细节先不解析。oracle pdb搞得也太复杂了。
--//父游标句柄以及父游标堆0还在。子游标句柄也在，但是堆0，堆6都清除了，这样基本没用。
--//当然我手工刷新alter system flush shared_pool;，如果不刷新，也许保存在共享池更长一些。
--//我更倾向于这样设置没用。
--//我以前使用sql profile设置稳定执行计划，仅仅减少了少量的分析时间。
--//不过有空可以尝试看看，是否起作用。