Melihat Isi Buffer Cache Oracle

Oleh : Achmad Faisol

Di pembahasan "Bagaimana Database Oracle Memandang Perintah SQL?" telah kita lihat bahwa sintaks SQL yang berbeda dianggap berbeda oleh Oracle walaupun hasil yang diinginkan sama, yaitu melihat seluruh isi tabel scott.dept (deptno, dname, loc). Variasi perintah yang dianggap beda oleh Oracle, misalnya :

SQL> connect scott/tiger
=>login sebagai scott
SQL> select * from dept;
SQL> select deptno, dname, loc from dept;
SQL> select * from scott.dept;
SQL> select * from Scott.Dept;
SQL> select * from SCOTT.dept;
SQL> select * from SCOTT.DEPT;


Pertanyaan selanjutnya adalah, "Apakah masing-masing perintah menghasilkan output sendiri-sendiri di memori? Apakah setiap perintah me-load isi tabel dept ke memori (buffer cache) sehingga di memori ada banyak isi tabel dept walaupun sama? Bila masing-masing perintah me-load isi tabel dept, bukankah memori menjadi cepat penuh (boros)? Ataukah hanya perintah pertama yang me-load isi tabel dept ke memori sedangkan perintah-perintah selanjutnya menggunakan hasil yang sudah ada?"

Langkah-langkah berikut ini akan menjawab pertanyaan tersebut.

1. Buka SQL window I. Login sebagai scott, berikan object privilege agar semua user bisa melihat tabel dept dan emp.

SQL> connect scott/tiger
SQL> set verify off
SQL> grant select on &nama_tabel to public;
Enter value for nama_tabel: dept

Grant succeeded.
SQL> /
Enter value for nama_tabel: emp

Grant succeeded.

Login sebagai sys lalu jalankan script untuk membuat view v$cache, yaitu $ORACLE_HOME/rdbms/admin/catclust.sql.

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

Lihat berapa block yang digunakan oleh table dept dan emp yang dimiliki scott (tanpa segment primary key).

SQL> column owner format a10
SQL> column segment_name format a15
SQL> column extents format 999
SQL> column bytes format 99999
SQL> column blocks format 999

SQL> select owner, segment_name, extents, bytes, blocks
        from dba_segments
        where owner='SCOTT' and
        segment_name in ('DEPT','EMP');

OWNER      SEGMENT_NAME    EXTENTS  BYTES BLOCKS
---------- --------------- ------- ------ ------
SCOTT      DEPT                  1  65536      8
SCOTT      EMP                   1  65536      8

Terlihat bahwa masing-masing table telah mengggunakan 1 buah extents (8 block atau 64 KB). Ukuran 1 block dalam KB sesuai setting saat instalasi Oracle atau saat membuat tablespace, dalam kasus ini 1 block = 8 KB.

Perlu diingat lagi bahwa pada saat membuat segment, misalnya tabel, Oracle akan memesan minimal 1 extents. Bila 1 extents ini penuh oleh data (record), pemesanan berikutnya juga 1 extents, bukan 1 block. Dari hasil query di atas, bisa jadi isi (record) tabel dept sebenarnya belum mencapai 8 block (banyak block kosong karena belum diisi).

Selanjutnya, shutdown dan startup Oracle untuk memastikan memori (SGA) belum pernah digunakan.

SQL> shutdown immediate
SQL> startup

Lihat isi buffer cache, apakah pernah ada yang me-load (select) tabel dept atau emp.

SQL> column owner format a10
SQL> column name format a15
SQL> column blocks format 999

SQL> select u.username owner, c.name, count(c.block#) blocks
from v$cache c, dba_users u
where (lower(c.name) = 'dept'
or lower (c.name) = 'emp')
and c.owner# = u.user_id
group by u.username, c.name;

no rows selected (belum ada yang pernah select).

2. Buka SQL window II. Login sebagai scott, select tabel dept.

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

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

3. Klik SQL window I (user sys) lalu select v$cache.

SQL> select u.username owner, c.name, count(c.block#) blocks
from v$cache c, dba_users u
where (lower(c.name) = 'dept'
or lower (c.name) = 'emp')
and c.owner# = u.user_id
group by u.username, c.name;

OWNER      NAME            BLOCKS
---------- --------------- ------
SCOTT      DEPT                 6
 

4. Buka SQL window III, login sebagai hr, lalu select tabel scott.dept

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

5. Klik SQL window I (user sys) lalu select v$cache.

SQL> select u.username owner, c.name, count(c.block#) blocks
from v$cache c, dba_users u
where (lower(c.name) = 'dept'
or lower (c.name) = 'emp')
and c.owner# = u.user_id
group by u.username, c.name;

OWNER      NAME            BLOCKS
---------- --------------- ------
SCOTT      DEPT                 6

6. Buka SQL window IV, login sebagai system, lalu select tabel SCOTT.dept

SQL> connect system/inix2009
SQL> select * from SCOTT.dept;

7. Klik SQL window I (user sys) lalu select v$cache.

SQL> select u.username owner, c.name, count(c.block#) blocks
from v$cache c, dba_users u
where (lower(c.name) = 'dept'
or lower (c.name) = 'emp')
and c.owner# = u.user_id
group by u.username, c.name;

OWNER      NAME            BLOCKS
---------- --------------- ------
SCOTT      DEPT                 6

Sebagai sys, select tabel SCOTT.DEPT. Setelah itu lihat v$cache.

SQL> select * from SCOTT.DEPT;
SQL> select u.username owner, c.name, count(c.block#) blocks
from v$cache c, dba_users u
where (lower(c.name) = 'dept'
or lower (c.name) = 'emp')
and c.owner# = u.user_id
group by u.username, c.name;

OWNER      NAME            BLOCKS
---------- --------------- ------
SCOTT      DEPT                 6

Ternyata, walaupun sekian banyak user melakukan perintah SQL dengan sintaks yang berbeda, tidak ada penambahan block di v$chace. Untuk lebih meyakinkan lagi, lakukan perintah di bawah ini :

8. Klik SQL window III (user hr).

SQL> select deptno, dname, loc from scott.dept;
SQL> select deptno, dname, loc from Scott.Dept;
SQL> select deptno, dname, loc from SCOTT.dept;
SQL> select deptno, dname, loc from SCOTT.DEPT;
SQL> select Deptno, Dname, Loc from scott.dept;
SQL> select DEPTNO, DNAME, LOC from ScOtT.dePt;

9. Klik SQL window I (user sys) lalu select v$cache.

SQL> select u.username owner, c.name, count(c.block#) blocks
from v$cache c, dba_users u
where (lower(c.name) = 'dept'
or lower (c.name) = 'emp')
and c.owner# = u.user_id
group by u.username, c.name;

OWNER      NAME            BLOCKS
---------- --------------- ------
SCOTT      DEPT                 6

Telah terbukti bahwa hanya perintah SQL pertamalah yang melakukan loading isi tabel dept ke memori (buffer cache). Adapun perintah-perintah selanjutnya yang mengakses table scott.dept menggunakan hasil yang sudah ada. Dengan demikian, pemakaian buffer cache efisien.

10. Klik SQL window II (user scott), lalu select tabel emp.

SQL> select * from emp;

11. Klik SQL window I (user sys) lalu select v$cache.

SQL> select u.username owner, c.name, count(c.block#) blocks
from v$cache c, dba_users u
where (lower(c.name) = 'dept'
or lower (c.name) = 'emp')
and c.owner# = u.user_id
group by u.username, c.name;

OWNER      NAME            BLOCKS
---------- --------------- ------
SCOTT      EMP                  6
SCOTT      DEPT                 6