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

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

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

编辑推荐：

返回列表

[20241012]dbms_shared_pool.keep减少硬分析吗.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推