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

来源：这里教程网时间：2026-03-03 20:42:05 作者：

[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设置稳定执行计划，仅仅减少了少量的分析时间。 --//不过有空可以尝试看看，是否起作用。

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推