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.
