关于PLSQL性能调整的一点技巧关于dbms_shared_pool缓存对象的详细用法

今天处理一个程序是意外遇到dbms_shared_pool执行报错，就研究了下这个包，

这个包主要是处理shared pool池的工具，能够将对象缓存入shared pool，或者

清除shared pool中的对象，对于pkg包、序列还有函数等常用对象，可以考虑将其

通过dbms_shared_pool手动加入shared pool，从而避免加载等动作，提高效率，

具体如下：

一，共享池

1):触发器与shared pool

将经常性使用或者及时性高的对象如出发其等放入shared pool，在放入前先评估对象的大小

如下：确定code的大小

select source_size,parsed_size,code_size,type
  from
dba_object_size
where lower(name)='dbms_output';

主要看code_size

SOURCE_SIZE PARSED_SIZE  CODE_SIZE TYPE
----------- ----------- ---------- -------------
       7795        1685        928 PACKAGE
       1618           0       3762 PACKAGE BODY

2，看uga使用

select name,value
  from v$statname n,v$sesstat v
where n.statistic#=v.statistic# and
      n.name like '%session uga%' and
      sid=userenv('sid');

NAME                           VALUE
------------------------------ ----------
session uga memory                 287208
session uga memory max             3791692

3，将经常使用的对象pin到shared pool中

就是通过dbms_shared_pool包，可以运行dbmspool.sql来创建。该包位置在?/C:/oracle/product/10.2.0/db_3/RDBMS/ADMIN

对sql的pin

step1：

select address||','||hash_value sql_id
  from v$sql
where sql_text like '%select * from scott.emp%' and
sql_text not like '%v$sql%';

step2：

exec dbms_shared_pool.keep('6B8E6760,52404428','C');

 

在psoug上的dbms_shared_pool的详细使用示例

Oracle DBMS_SHARED_POOL
Version 11.1
 
General
Purpose     Pin and unpin objects from memory
Source     {ORACLE_HOME}/rdbms/admin/dbmspool.sql
First Available     10.1
Dependencies    
DBMS_OUTPUT     GV
DBMS_STANDARD     GV
DBMS_UTILITY     X
Security Model     EXECUTE is granted to the EXECUTE_CATALOG_ROLE role
GRANT EXECUTE ON dbms_shared_pool TO uwclass;
 
ABORTED_REQUEST_THRESHOLD
Sets the aborted request threshold for the shared pool     dbms_shared_pool.aborted_request_threshold(threshold_size IN NUMBER);

The range of threshold_size is 5000 to ~2 GB inclusive.
exec dbms_shared_pool.aborted_request_threshold(100000000);
 
KEEP

Pin A Cursor In Memory     dbms_shared_pool.keep(name IN VARCHAR2, flag IN CHAR DEFAULT 'P');
 
Flag Values     Description
C     cursor
JC     java class
JD     java shared data
JR     java resource
JS     java source
P     procedure
Q     sequence
R     trigger
T     type
conn / as sysdba

GRANT select ON gv_ TO uwclass;

conn uwclass/uwclass

-- SQL statement to load cursor into the shared pool
VARIABLE x REFCURSOR

BEGIN
   OPEN :x for
   SELECT *
   FROM all_tables;
END;
/

--Determine address and hash value of the SQL statement
SELECT address, hash_value
FROM gv
WHERE sql_text LIKE '%ALL_TABLES%';

-- substitute your query results for mine, below.

exec sys.dbms_shared_pool.keep('1C5B28DC, 3958201300', 'C');

conn / as sysdba

SELECT owner, name, type
FROM gv
WHERE kept = 'YES'
AND TYPE = 'CURSOR';

Pin A Package, Procedure Or Function In Memory (this is the default)
SELECT owner, name, type
FROM gv
WHERE kept = 'YES'
AND owner = 'UWCLASS';

CREATE OR REPLACE PROCEDURE testproc IS
BEGIN
  NULL;
END testproc;
/

exec sys.dbms_shared_pool.keep('testproc', 'P');

conn / as sysdba

col owner format a30

SELECT owner, name, type
FROM gv
WHERE kept = 'YES'
AND owner = 'UWCLASS';

Pin A Sequence In Memory
conn / as sysdba
SELECT owner, name, type
FROM gv
WHERE kept = 'YES'
AND owner = 'UWCLASS';

conn uwclass/uwclass

CREATE SEQUENCE seq_test;

exec sys.dbms_shared_pool.keep('seq_test', 'Q');

conn / as sysdba

SELECT owner, name, type
FROM gv
WHERE kept = 'YES'
AND owner = 'UWCLASS';

Pin A Trigger In Memory
conn uwclass/uwclass

SELECT owner, name, type
FROM gv
WHERE kept = 'YES'
AND owner = 'UWCLASS';

CREATE TABLE t (
testcol VARCHAR2(20));

CREATE OR REPLACE TRIGGER testtrig
BEFORE UPDATE
ON t
BEGIN
   NULL;
END testtrig;
/

exec sys.dbms_shared_pool.keep('testtrig', 'R');

conn / as sysdba

SELECT owner, name, type
FROM gv
WHERE kept = 'YES'
AND owner = 'UWCLASS';
 
PURGE

Purge the named object or particular heap(s) of the object     dbms_shared_purge(
name  IN VARCHAR2,
flag  IN CHAR   DEFAULT 'P',
heaps IN NUMBER DEFAULT 1);

Note: heaps to purge. e.g if heap 0 and heap 6 are to be purged.
1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.
Default is 1 i.e heap 0 which means the whole object will be purged.
conn uwclass/uwclass

CREATE OR REPLACE PROCEDURE testproc IS
BEGIN
  NULL;
END testproc;
/

conn / as sysdba

exec dbms_shared_pool.purge('UWCLASS.TESTPROC');
 
SIZES
Shows what is in the Shared Pool larger than a specified size in bytes     dbms_shared_pool.sizes(minsize IN NUMBER);
set serveroutput on

exec dbms_shared_pool.sizes(500);
 
UNKEEP
Unpin an object from memory     dbms_shared_pool.unkeep(name IN VARCHAR2, flag IN CHAR DEFAULT 'P');
exec dbms_shared_pool.unkeep('UWCLASS.TESTPROC', 'P');
 
Query
To find information on pinned cursors     SELECT address, hash_value
FROM gv
WHERE sql_text LIKE '%<name_from_v%';