DBMS_SHARED_POOL을 이용한 KEEP
Shared Poll에 크기가 큰 프로그램을 KEEP하기 위해서는 아래에 있는 것처럼 DBMS_SHARED_POOL Package를 이용 할 수 있습니다.
SQL> @C:\oracle\ora92\rdbms\admin\dbmspool.sql
패키지가 생성되었습니다.
권한이 부여되었습니다.
뷰가 생성되었습니다.
패키지 본문이 생성되었습니다.
SQL> @C:\oracle\ora92\rdbms\admin\prvtpool.plb
뷰가 생성되었습니다.
패키지 본문이 생성되었습니다.
SQL> grant execute on dbms_shared_pool to scott;
권한이 부여되었습니다.
Object를 KEEP하는 방법은 다음과 같습니다.
Procedure,Function,Package : exec dbms_shared_pool.keep(‘pname’,’p’)
Trigger : exec dbms_shared_pool.keep(‘tr_emp’,’r’)
Sequence : exec dbms_shared_pool.keep(‘seq_empno,’q’)
SQL문은 아래와 같은 방법으로 KEEP 합니다.
예를들어 select empno, ename, sal from emp where deptno = ‘20’ 라는 SQL문장을 Library Cache안의 Shared Cursor 부분에 KEEP하기 위해서는 아래처럼 하면 됩니다…
SQL> conn scott/tiger
연결되었습니다.
SQL> select empno, ename, sal from emp where deptno = 20;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000
SQL> conn / as sysdba
연결되었습니다.
SQL> select address, hash_value from v$sqlarea
2 where sql_text = 'select empno, ename, sal from emp where deptno = 20';
ADDRESS HASH_VALUE
-------- ----------
7856AC4C 1137127237 <- 원하는 SQL문장에 대한 주소와 해시 값
아래 명령으로 KEEP 합니다.
SQL> exec dbms_shared_pool.keep('7856AC4C, 1137127237','c');
PL/SQL 처리가 정상적으로 완료되었습니다.
Object의 KEEP 상태는 다음으로 체크 가능 합니다.
SQL> select distinct name, sharable_mem, loads
2 from v$db_object_cache
3 where name like '%emp%'
4 and kept = 'YES';
NAME SHARABLE_MEM LOADS
------------ ----------------------------------------------
select empno, ename, sal from emp where deptno = 20 1469 1
또는 exec dbms_shared_pool.sizes(0)로 확인 가능 합니다. 이 sizes라는 procedure는 제한된 사이크 이상의 keep된 Object를 나타내 줍니다.
SQL> set serveroutput on size 2000
SQL> exec dbms_shared_pool.sizes(0) -> buffer overflow가 나더라도 pin시킬(KEEP할) SQL문장을 찾을 수는 있습니다.
각 Object를 Shared Pool에 유지하던 것을 해제 할 때는 아래의 unkeep 프로시저를 이용 합니다.
SQL> exec dbms_shared_pool.unkeep('7856AC4C, 1137127237','c');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select distinct name, sharable_mem, loads
2 from v$db_object_cache
3 where name like '%emp%'
4 and kept = 'YES';
결과가 없겠죠…
아래는 DBMS_SHARED_POOL.keep procedure 명셉니다… 메타링크 자료니 참고 하세요…
PROCEDURE:DBMS_SHARED_POOL.KEEP
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Version/s: 7.1+, 8.0, 8.1
See Also: [NOTE:67537.1] The Purpose of DB Package Reference Articles
[NOTE:67569.1] DBMS_SHARED_POOL Package Description
Specification:
procedure keep(name varchar2, flag char DEFAULT 'P');
Description:
Keep an object in the shared pool. Once an object has been keeped in
the shared pool, it is not subject to aging out of the pool. This
may be useful for certain semi-frequently used large objects since
when large objects are brought into the shared pool, a larger
number of other objects (much more than the size of the object
being brought in, may need to be aged out in order to create a
contiguous area large enough.
WARNING: This procedure may not be supported in the future when
and if automatic mechanisms are implemented to make this
unnecessary.
Input arguments:
name
The name of the object to keep. There are two kinds of objects:
PL/SQL objects, triggers, sequences which are specified by name,
and SQL cursor objects which are specified by a two-part number
(indicating a location in the shared pool). For example:
dbms_shared_pool.keep('scott.hispackage')
will keep package HISPACKAGE, owned by SCOTT. The names for
PL/SQL objects follows SQL rules for naming objects (i.e.,
delimited identifiers, multi-byte names, etc. are allowed).
A cursor can be keeped by
dbms_shared_pool.keep('0034CDFF, 20348871')
The complete hexadecimal address must be in the first 8 characters.
The value for this identifier is the concatonation of the
'address' and 'hash_value' columns from the v$sqlarea view. This
is displayed by the 'sizes' call above.
Currently 'TABLE' and 'VIEW' objects may not be keeped.
flag
This is an optional parameter. If the parameter is not specified,
the package assumes that the first parameter is the name of a
package/procedure/function and will resolve the name. It can also
be set to 'P' or 'p' to fully specify that the input is the name
of a package/procedure/function.
Other FLAG options are:
'T' or 't' to specify that the input is the name of a type.
'R' or 'r' to specify that the input is the name of a trigger
'Q' or 'q' to specify that the input is the name of a sequence.
other In case the first argument is a cursor address and
hash-value, the parameter should be set to any
character except 'P','p','Q','q','R','r','T' or 't'.
Exceptions:
An exception will raised if the named object cannot be found.