一、概述
Oracle在称为共享池的SGA内存段中维护共享SQL和PL/SQL区域。当用户进程发出SQL语句或PL/SQL块时，Oracle会处理该请求，并将执行信息存储在共享的SQL或PL/SSQL区域中。当下一条要处理的语句出现时，Oracle会检查是否已经处理了相同的语句。如果是，则不会分析当前语句，因为执行信息已经存在。这使Oracle可以处理其他请求。随着用户进程开始争夺Oracle资源，共享SQL和PL/SQL区域可能会因最近最少使用（LRU）机制而从共享池中老化。这种竞争降低了共享区域保留足够长时间以供需要它们的流程使用的机会。由于共享区域过早老化，Oracle资源被浪费在重新分析语句上，性能很快就会下降。此外，由于反复加载和卸载不同大小的PL/SQL对象，也会导致内存碎片，从而减少了找到足够的连续内存来加载大型PL/SSQL对象的机会。DBMS_SHARED_POOL可用于管理LRU机制对共享池的影响。它可以用来固定PL/SQL对象、SQL游标、触发器和序列。如果使用得当，DBMS_SHARED_POOL可以提高应用程序性能，并有助于高效使用Oracle资源。应该使用DBMS_SHARED_POOL将大型且频繁执行的SQL语句和存储程序保存在共享池中。锁定对象的最佳时间是在数据库启动后立即执行。这增加了有足够的连续内存可用于存储对象的可能性。用户SYS拥有的以下Oracle核心包应固定在共享PL/SQL区域：DUTIL STANDARD DIANA DBMS_SYS_SQL DBMS_SQL DB MS_UTILITY DBMS_DESCRIBE DBMS_JOB DBMS-STANDARD DBMS_OUTPUTPIDL如果正在使用Oracle的“对称复制”选项，还应固定以下包：DBMS_DEFER DBMS_REPUTIL DBMS_SNAPSHOT DBMS_REFRESHDBMS_DEFER_INTERNAL_SYSREP$WHAT_AM_I此外，应固定为复制模式中的表创建的特定于应用程序的包，即带有“$RP”和“$RR”的包，特别是当表上有大量DML活动时。其他包（如DBMS_ALERT、DBMS_LOCK和大型用户编写的包）也可能需要保存在共享池中。
二、创建该包
SQL> @?/rdbms/admin/dbmspool.sql
Session altered.
Package created.
Grant succeeded.
Session altered.
SQL> @?/rdbms/admin/prvtpool.plb
Session altered.
View created.
Synonym created.
PL/SQL procedure successfully completed.
Synonym created.
Package body created.
Session altered.
三、ABORTED_REQUEST_THRESHOLD过程
为共享池设置中止请求阈值，设置会话级别阈值，在共享池中找不到内存时，将生成ORA-4031。如果空间不可用，只有发出内存请求的会话才会看到ORA-4031。
参数：threshold_size -请求的大小（以字节为单位），该请求不会尝试释放共享池中未固定（不是“未保留”）的内存。threshold_size 的范围是 5000 到~2 GB（含）。通常，如果在空闲列表中无法满足请求，RDBMS会尝试通过从空闲列表中释放对象来回收内存。LRU列表并定期检查以查看是否可以满足请求。完成此步骤后，RDBMS 执行了几乎相当于“alter system flushshared_pool”的操作。由于这会影响系统上的所有用户，因此此过程“本地化”对无法找到大小大于 thresh_hold 大小的共享池内存的进程的影响。该用户将在
不尝试搜索 LRU 列表的情况下收到内存不足错误。
SQL> begin
2 DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD (threshold_size =>0);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-20000: threshold_size not in valid range: (5000 - 2147483647)
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 167
ORA-06512: at line 2
set serveroutput on;
SQL> begin
2 DBMS_SHARED_POOL.ABORTED_REQUEST_THRESHOLD (threshold_size =>10000);
3 end;
4 /
PL/SQL procedure successfully completed.
四、KEEP过程
DBMS_SHARED_POOL.KEEP (
name VARCHAR2,
flag CHAR DEFAULT 'P');
DBMS_SHARED_POOL.KEEP (
schema VARCHAR2,
objname VARCHAR2,
namespace NUMBER,
heaps NUMBER,
edition_name VARCHAR2 DEFAULT NULL);
DBMS_SHARED_POOL.KEEP (
hash VARCHAR2,
namespace NUMBER,
heaps NUMBER);
4.1、固定游标
SQL> begin
2 dbms_shared_pool.keep('00000000618158C8,2763161912','c');
3 end;
4 /
PL/SQL procedure successfully completed.
4.2、过程、函数或包
SQL> begin
2 dbms_shared_pool.keep('LOAD_UNDO_STAT', 'p');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_shared_pool.keep('sys.dbms_shared_pool');
3 end;
4 /
PL/SQL procedure successfully completed.
4.3、触发器
SQL> begin
2 dbms_shared_pool.keep('DELETE_ENTRIES','r');
3 end;
4 /
PL/SQL procedure successfully completed.
4.4、序列
SQL> begin
2 dbms_shared_pool.keep('RGROUPSEQ','q');
3 end;
4 /
PL/SQL procedure successfully completed.
五、unkeep过程
要将固定的共享 SQL 或 PL/SQL 区域放回 LRU 列表，只需使用与 KEEP 相同的参数执行 UNKEEP 过程。
SQL> begin
2 dbms_shared_pool.unkeep('RGROUPSEQ','q');
3 end;
4 /
PL/SQL procedure successfully completed.
六、MARKHOT过程
将库缓存对象标记为热对象
DBMS_SHARED_POOL.MARKHOT (
schema VARCHAR2,
objname VARCHAR2,
namespace NUMBER DEFAULT 1,
global BOOLEAN DEFAULT TRUE,
edition_name VARCHAR2 DEFAULT NULL);
DBMS_SHARED_POOL.MARKHOT (
hash VARCHAR2,
namespace NUMBER DEFAULT 1,
global BOOLEAN DEFAULT TRUE);
6.1、查询内存中对象
SQL> select NAME,NAMESPACE,EXECUTIONS,STATUS,FULL_HASH_VALUE,HASH_VALUE from v$db_object_cache where name like '%select count(*) from t%';
NAME NAMESPACE EXECUTIONS STATUS FULL_HASH_VALUE HASH_VALUE
------------------------------------------------------------ ---------- ---------- ---------- ----------------------------------- ----------
select count(*) from t SQL AREA 6 VALID 10bef657c8a7ea0dcf7ff45fa4b28138 2763161912
select count(*) from t SQL AREA 6 VALID 10bef657c8a7ea0dcf7ff45fa4b28138 2763161912
6.2、标记为sql
SQL> begin
2 dbms_shared_pool.markhot(hash => '10bef657c8a7ea0dcf7ff45fa4b28138', namespace => 1, global => true);
3 end;
4 /
PL/SQL procedure successfully completed.
6.3、查询
SQL> select NAME,NAMESPACE,EXECUTIONS,STATUS,FULL_HASH_VALUE,HASH_VALUE,PROPERTY from v$db_object_cache where name like '%select count(*) from t%';
NAME NAMESPACE EXECUTIONS STATUS FULL_HASH_VALUE HASH_VALUE PROPERTY
------------------------------------------------------------ ---------- ---------- ---------- ----------------------------------- ---------- ----------
select count(*) from t SQL AREA 6 VALID 10bef657c8a7ea0dcf7ff45fa4b28138 2763161912
select count(*) from t SQL AREA 6 VALID 10bef657c8a7ea0dcf7ff45fa4b28138 2763161912
select count(*) from t SQL AREA 4 VALID 10bef657c8a7ea0dcf7ff45fa4b28138 2763161912 HOT
select count(*) from t SQL AREA 4 VALID 10bef657c8a7ea0dcf7ff45fa4b28138 2763161912 HOT
PROPERTY字段可能的值:
- HOTCOPYnnn- 具有整数标识符“nnn”的热拷贝对象。例如，HOTCOPY5、HOTCOPY94和HOTCOPY125。
- HOTCOPY- 如上所述，但标识符未知
- HOT- 已标记为热的“根”kgl 对象
- NULL- 一个普通的对象
SQL> select SQL_ID,OPEN_VERSIONS,EXECUTIONS,LOADS,PARSE_CALLS,HASH_VALUE,ADDRESS,PLAN_HASH_VALUE from v$sqlarea where sql_text='select count(*) from t';
SQL_ID OPEN_VERSIONS EXECUTIONS LOADS PARSE_CALLS HASH_VALUE ADDRESS PLAN_HASH_VALUE
-------------------------- ------------- ---------- ---------- ----------- ---------- ---------------- ---------------
cyzznbykb509s 1 20 2 20 2763161912 00000000618158C8 2966233522
SQL> select SQL_ID,CHILD_ADDRESS,CHILD_NUMBER from v$sql where sql_text='select count(*) from t';
SQL_ID CHILD_ADDRESS CHILD_NUMBER
-------------------------- ---------------- ------------
cyzznbykb509s 000000006BCD9808 0
cyzznbykb509s 0000000062ACD0B0 0
bug很多,谨慎使用
七、UNMARKHOT过程
SQL> begin
2 dbms_shared_pool.unmarkhot(hash => '10bef657c8a7ea0dcf7ff45fa4b28138', namespace => 1, global => true);
3 end;
4 /
PL/SQL procedure successfully completed.
select NAME,NAMESPACE,EXECUTIONS,STATUS,FULL_HASH_VALUE,HASH_VALUE,PROPERTY from v$db_object_cache where name like '%select count(*) from t%';
select count(*) from t SQL AREA 6 VALID 10bef657c8a7ea0dcf7ff45fa4b28138 2763161912
select count(*) from t SQL AREA 6 VALID 10bef657c8a7ea0dcf7ff45fa4b28138 2763161912
select count(*) from t SQL AREA 4 VALID 10bef657c8a7ea0dcf7ff45fa4b28138 2763161912 HOT
select count(*) from t SQL AREA 4 VALID 10bef657c8a7ea0dcf7ff45fa4b28138 2763161912 HOT
八、PURGE程序
DBMS_SHARED_POOL.PURGE (
name VARCHAR2,
flag CHAR DEFAULT 'P',
heaps NUMBER DEFAULT 1);
DBMS_SHARED_POOL.PURGE (
schema VARCHAR2,
objname VARCHAR2,
namespace NUMBER,
heaps NUMBER,
edition_name VARCHAR2 DEFAULT NULL);
DBMS_SHARED_POOL.PURGE (
hash VARCHAR2,
namespace NUMBER,
heaps NUMBER);
8.1、清理游标
SQL> select address, hash_value, executions, invalidations, parse_calls,sql_text,sql_id from v$sql where sql_text like '%select count(*) from t%';
ADDRESS HASH_VALUE EXECUTIONS INVALIDATIONS PARSE_CALLS SQL_TEXT SQL_ID
---------------- ---------- ---------- ------------- ----------- ------------------------------ --------------------------
00000000618158C8 2763161912 0 0 0 select count(*) from t cyzznbykb509s
SQL> begin
2 dbms_shared_pool.purge ('00000000618158C8,2763161912','C');
3 end;
4 /
begin
*
ERROR at line 1:
ORA-06596: object cannot be purged, object is permanently kept in shared pool
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 51
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 120
ORA-06512: at line 2
SQL> begin
2 dbms_shared_pool.unkeep('00000000618158C8,2763161912','c');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_shared_pool.purge ('00000000618158C8,2763161912','C');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select address, hash_value, executions, invalidations, parse_calls,sql_text,sql_id from v$sql where sql_id='cyzznbykb509s';
no rows selected
SQL> select ADDRESS,HASH_VALUE,SQL_TEXT from v$sqltext where sql_id='cyzznbykb509s';
ADDRESS HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------
00000000618158C8 2763161912 select count(*) from t;
v$sql已经删除,v$sqltext依然存在。因为DBMS_SHARED_POOL.PURGE清除库缓存中的对象堆，而不是对象句柄。如果将来再次执行查询，则不会清除对象句柄以避免完全硬解析。由于视图（如v$sqltext）基于对象句柄获取值，因此在执行DBMS_SHARED_POOL之后，它们仍然可以显示信息。
九、SIZES过程
此过程显示shared_pool大于指定大小的对象。还给出了对象的名称，它可以用作KEEPorUNKEEP调用的参数
SQL> set serveroutput on size 2000
SQL> begin
2 dbms_shared_pool.sizes(0);
3 end;
4 /
SIZE(K) KEPT NAME
------- ------ ---------------------------------------------------------------
7412 MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_cdn(ST)
OPT_PARAM('_parallel_syspls_obey_force' 'fals
e') */ INTO STATS_TARGET$ ST USING (SELECT CASE
WHEN DBMS_STATS_INTERNAL.BITCLR(AFLAGS, :B23 + :B28 ) = :B
10 THEN DECODE(TYPE#, 19, :B35 , :B34 ) ELSE STALENESS END
STALENESS, OSIZE, OBJ#, TYPE#, AFLAGS, STATUS, SID, SER
(0000000070371DD8,38503625(CURSOR)
7352 .MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_cdn(ST)
OPT_PARAM('_parallel_syspls_obey_force' 'fal
se') */ INTO STATS_TARGET$ ST USING (SELECT CAS
E WHEN DBMS_STATS_INTERNAL.BITCLR(AFLAGS, :B23 + :B28 ) = :
B10 THEN DECODE(TYPE#, 19, :B35 , :B34 ) ELSE STALENESS END
STALENESS, OSIZE, OBJ#, TYPE#, AFLAGS, STATUS, SID, SERIAL
#, PART#, BO# FROM ( SELECT /*+ no_expand dynamic_sampling(
4) dynamic_sampling_est_cdn */ DECODE(BITAND(T.FLAGS,16), 1
6, ROUND( LOG(0.01, NVL( LEAST( 100, GREATEST( 0.01, (DECOD
E(BITAND(M.FLAGS, :B22 ), :B22 , GREATEST(T.ROWCNT, M.INSER
TS), LEAST((M.INSERTS + M.DELETES + M.UPDATES), GREATEST(T.
ROWCNT, (T.ROWCNT + M.INSERTS - M.DELETES)))) / (T.ROWCNT +
0.01)))), 0.01)), 1), -100.0) STALENESS, CASE WHEN BITAND(
T.PROPERTY, 32) = 32 OR T.FILE# = 0 THEN DBMS_STATS_INTERNA
L.GET_TABLE_BLOCK_COUNT(U.NAME, O.NAME, NULL, NULL, '(-376,
-942)') WHEN S.TYPE# = 5 THEN DBMS_STATS_INTERNAL.SEGMENT_
NUMBER_BLOCKS(T.TS#, T.FILE#, T.BLOCK#, S.TYPE#, S.CA(CURSO
R)
begin
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 201
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 201
ORA-06512: at line 2
这个报错是serveroutput设置过小导致，放大即可。
十、参考文档
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SHARED_POOL.html#GUID-52AF86BF-AFF4-4366-8B5D-12C32E47B7B1
Using the Oracle DBMS_SHARED_POOL Package (Doc ID 61760.1)
How To Pin Objects in the Shared Pool (Doc ID 1012047.6)
Is Cursor Purging using DBMS_SHARED_POOL.PURGE Not Working? (Doc ID 2655262.1)
最后修改时间：2022-10-20 17:19:14
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
