Keep Procedure di Shared Pool Memori Oracle

Oleh : Achmad Faisol

Di pembahasan "Bagaimana Database Oracle Memandang Perintah SQL?" telah kita lihat bagaimana cara kerja Oracle dalam mengeksekusi sebuah perintah SQL. Perintah-perintah tersebut ditaruh oleh Oracle di Shared Pool, setelah itu dijalankan. Bagaimana bila Shared Pool penuh?

Bila Shared Pool penuh oleh sekian banyak SQL Statement, maka ada perintah yang dibuang/dihilangkan. Prinsip dasar yang digunakan adalah MRU (Most Recently Use) artinya yang sering digunakan tidak akan dibuang. Timbul pertanyaan, "Bagaimana bila kita menginginkan agar sebuah perintah tertentu ada terus di memori dan tidak dibuang ketika Shared Pool penuh?"

Kita bisa membuat procedure atau package (kumpulan procedure) untuk di-keep di Shared Pool. Dengan demikian tidak akan dibuang walaupun shared pool penuh. Berikut ini akan penulis paparkan cara membuat package dan bagaimana meng-keep di memori. Package yang dibuat berisikan procedure-procedure untuk mencari, menambah, merubah dan menghapus data di tabel scott.dept.

1. Buka SQL window I. Login sebagai scott dan pastikan scott mempunyai hak untuk membuat procedure.

SQL> connect scott/tiger
SQL> select * from session_privs;
PRIVILEGE
------------------------
. . .
. . .
CREATE PROCEDURE
CREATE TRIGGER

Bila scott belum punya hak (privilege) membuat procedure, cara termudah yaitu grant-lah role resource untuk scott.

SQL> connect system/inix2009
SQL> grant resource to scott;

2. Login lagi sebagai scott dan buatlah package dengan nama dept_actions :

SQL> create or replace package dept_actions as
procedure tambah_dept (
no_dept number,
nama_dept varchar2,
lokasi varchar2);

procedure rubah_dept (
no_dept number,
nama_dept varchar2,
lokasi varchar2);

procedure hapus_dept (no_dept number);

procedure cari_dept (no_dept number);

end dept_actions;


SQL> create or replace PACKAGE BODY dept_actions as
procedure tambah_dept (
no_dept number,
nama_dept varchar2,
lokasi varchar2)
is
begin
insert into scott.dept
values(no_dept,nama_dept,lokasi);
end tambah_dept;

procedure rubah_dept (
no_dept number,
nama_dept varchar2,
lokasi varchar2)
is
begin
update scott.dept set dname=nama_dept,
loc=lokasi where deptno=no_dept;
end rubah_dept;

procedure hapus_dept (no_dept number) is
begin
delete from scott.dept where deptno=no_dept;
end hapus_dept;

procedure cari_dept (no_dept number) is
v_nama dept.dname%type;
v_lokasi dept.loc%type;
begin
select dname, loc into v_nama,
v_lokasi from scott.dept where deptno=no_dept;
dbms_output.put_line ('nama departemen : '
v_nama);
dbms_output.put_line ('lokasi departemen : '
v_lokasi);
end cari_dept;

end dept_actions;

Package body berisi apa saja (langkah-langkah detail) yang dilakukan oleh procedure-procedure yang dideklarasikan sebelumnya.

3. Lihat data awal scott.dept lalu grant package tersebut ke public agar bisa dieksekusi oleh semua user.

SQL> connect scott/tiger
SQL> select * from scott.dept;

DEPTNO     DNAME          LOC
---------- -------------- ------------
10         ACCOUNTING     NEW YORK
20         RESEARCH       DALLAS
30         SALES          CHICAGO
40         OPERATIONS     BOSTON

SQL> grant execute on dept_actions to public;

4. Cara menjalankan package dept_actions. Buka SQL window II

a. Procedure tambah_dept

SQL> connect hr/hr
SQL> execute scott.dept_actions.tambah_dept(50,'Edu','Inix');
SQL> execute scott.dept_actions.tambah_dept(60,'Mkt','Inix');
SQL> commit;SQL> select * from scott.dept;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK
20         RESEARCH       DALLAS
30         SALES          CHICAGO
40         OPERATIONS     BOSTON
50         Edu            Inix
60         Mkt            Inix

b. Procedure rubah_dept

SQL> execute scott.dept_actions.rubah_dept(60,'Mktg','Sby');
SQL> select * from scott.dept;

DEPTNO     DNAME          LOC
---------- -------------- ---------
. . .
50         Edu            Inix
60         Mktg           Sby

c. Procedure hapus_dept

SQL> execute scott.dept_actions.hapus_dept(60);
SQL> select * from scott.dept;

DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK
20         RESEARCH       DALLAS
30         SALES          CHICAGO
40         OPERATIONS     BOSTON
50         Edu            Inix

d. Procedure cari_dept

SQL> set serveroutput on
SQL> execute scott.dept_actions.cari_dept(50);

nama departemen : Edu
lokasi departemen : Inix

PL/SQL procedure successfully completed.

5. Login sebagai sys, jalankan script dbmspool.sql untuk membuat package dbms_shared_pool. Package ini dibutuhkan untuk meng-keep procedure/package kita di memori. Script tersebut terletak di $ORACLE_HOME/rdbms/admin.

SQL> connect sys/inix2009 as sysdba
SQL> @C:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN\dbmspool.sql

6. Keep package scott.dept_actions di memori dan verifikasi dengan melihat isi memori.

SQL> connect sys/inix2009 as sysdba
SQL> execute dbms_shared_pool.keep('scott.dept_actions');

SQL> column owner format a10
SQL> column name format a20
SQL> column type format a20
SQL> SELECT owner, name, type FROM v$db_object_cache
WHERE (type='PACKAGE' OR type='PACKAGE BODY' OR
type='FUNCTION' OR type='PROCEDURE')
AND kept='YES';

OWNER      NAME                 TYPE
---------- -------------------- --------------------
SCOTT      DEPT_ACTIONS         PACKAGE BODY
SCOTT      DEPT_ACTIONS         PACKAGE

Terlihat bahwa package dan package body scott.dept_actions telah di-keep di memori. Hal ini menunjukkan bahwa package tersebut tidak akan dibuang dari memori walaupun Shared Pool penuh. Bila ingin un-keep, lakukan :

SQL> execute dbms_shared_pool.unkeep('scott.dept_actions');
SQL> SELECT owner, name, type FROM v$db_object_cache
WHERE (type='PACKAGE' OR type='PACKAGE BODY' OR
type='FUNCTION' OR type='PROCEDURE') z
AND kept='YES';

no rows selected

Jika ingin melihat procedure/package apa saja yang ada di memori tapi statusnya tidak di-keep, gantilah klausul kept='YES' menjadi kept='NO'.