Bir tabloda sürekli olarak belirli satırları sorguluyor ya da değiştiriyorsanız, her seferinde diske gitmeniz mantıklı olmaz. Çünkü I/O maliyetli bir işlemdir. Bu yüzden bellek performans için can simididir. Sıkça okuduğunuz ya da sıkça değiştirdiğiniz şeyleri her seferinde diske yazmak ya da diskten okumak yerine bunu bellekte kotarabilirseniz, performansı arttırabilirsiniz. Bellekte çalışmak performans konusunda artış sağladığından Oracle da böyle yapar; diskte çalışmak yerine mümkün olduğunca belleği kullanır. İşte bu yüzden bir sorguyu ikinci defa çalıştırdığınızda, sonuçlar daha hızlı gelir. Çünkü sorguda kullanılan block’lar hâlâ ‘sıcaktır’ ve diske hiç gitmeden, direkt bellekten sonuç döner. Bellekte yer kalmadığında, daha sıcak (kullanım sıklığı daha fazla) olan block’lar, soğuk (kullanım sıklığı daha az) block’ların yerini alır. Oracle kapalı bir yazılım olduğundan arka plânda ne olduğunu elbette bilemiyoruz. Ama zekice yazılmış bir Least Recently Used – LRU algoritmasıyla bu işin yapıldığını tahmin edebiliriz.
İşlemlerin bellekte gerçekleştirilmesi güzel bir olay. Ama bazı durumlarda (performans problemleriyle karşı karşıya kaldığımızda ya da testlerin daha tutarlı olması gerektiğinde) Oracle’a bazı komutlarla müdahele etmemiz gerekir.

1. BUFFER CACHE

Oracle’da birçok şey sadece bellekte tamamlanıyor. Örneğin bir DML işlemi (insert, update, delete) yaptığınızda gidip veritabanı dosyalarına yazması gerekmez. Ya da bir sorgu çalıştırdığınızda, sorgu biter bitmez, veri saklayan block’ların bellekten atılması gerekmez. Block’lar sorgulara göre bellekte tutulabilir. Hatta çalıştırdığınız sorgular, diske hiç erişmeden sonuç dönüyor olabilir. Bununla ilgili bir örnek üzerinden gidelim. Önce aşağıdaki gibi tabloyu yaratıyoruz:

create table test as select * from dba_objects;

Yarattığımız tabloda toplamda 96.760 kayıt var ve tablonun bulunduğu tablespace’te her block 8K’dan oluşuyor. DBA_SEGMENTS’ten baktığımda tablonun 11.534.336 byte (11MB) kapladığını ve 1408 block’tan oluştuğunu görüyorum. Tablo istatistiklerini topladığımdaysa, 1387 block görünüyor. (Block boyutunu 1K’ya kadar indirip, PCTFREE değerini düşürebilseydik; bu ikisi arasındaki fark daha da ufak olabilirdi.)
Sırada basit bir sorgu yazmak var:

SELECT COUNT(*)FROM test WHERE OBJECT_ID >1000;

Şimdi de sorgunun ne kadar disk okuması yaptığına bakalım.

SELECT a.SQL_ID,A.DISK_READS,A.EXECUTIONS,A.BUFFER_GETS  FROM V$SQL a 
WHERE a.SQL_TEXT LIKE'%SELECT COUNT(*) FROM TEST WHERE OBJECT_ID > 1000%'
SQL_ID        DISK_READS EXECUTIONS BUFFER_GETS
------------- ---------- ---------- -----------
77mjdqdj82paz    1395          1           1450

Buradaki DISK_READS, okunan block sayısı; EXECUTIONS ifadenin kaç kere çalıştığı; BUFFER_GETS ise SQL ifadesi nedeniyle bellekten ne kadar blockokunduğunu gösteriyor. Bunları daha iyi görebilmek için aynı sorguyu defalarca çalıştırıyorum:
Sorguyu defalarca çalıştırdıktan sonra, EXECUTIONS sayısı arttığı hâlde DISK_READS artmıyor, fakat BUFFER_GETS ciddi bir artış gösteriyor. Yani SQL’in diske gitmeden sürekli bellekten sonuç döndüğünü görebiliyoruz. Bu güzel bir özellik ama eğer bir SQL’i test ediyor olsaydım, sonuçları sürekli bellekten okumak beni yanıltırdı. Bu gibi durumlar için bellekteki block’ları boşaltmak daha doğru sonuçlar elde etmemi sağlar. Burada aşağıdaki komutla manüel olarak buffer cache’in içini boşaltıyoruz.

ALTER SYSTEM FLUSH BUFFER_CACHE;

Özetle FLUSH BUFFER_CAHCE yaparsanız;
• Bellekte tutulan block’lar atılır.
• Dirty Buffers (değişime uğramış ama diske yazılmamış, bellekte tutulan block’lar) diske yazılır
• SQL Plan bozulmaz
• SQL ile ilgili istatistikler silinmez

2.SHARED POOL

İlk kez çalışan SQL ifadeleri hard parse yapılır. SQL ifadesi hash’lenerek ona özel bir kimlik (SQL_ID) verilir; ihtimaller hesaplanarak en uygun (cost’u en düşük) plan belirlenir. Bu masraflı bir iştir; CPU’ya maliyeti vardır. Bu yüzden plan tekrar tekrar hesaplanmaz. Aynı ifade söz konusu ise aynı plan üzerinden gidilir. Bu güzel bir özellik ama bazı zamanlar operasyonumuz sonrası SQL’imiz için yeni bir execution plan oluştursuz isteriz. Bu gibi durumlardan sakınmak için shared pool’u boşaltmak bir çözümdür;

ALTER SYSTEM FLUSH SHARED_POOL;

Yukarıda anlattıklarım zaten bilinen şeyler. Fakat göstermek istediğim bir durum var. Aşağıdaki sorguyu çalıştırırsanız artık sonuç boş gelecektir –ki bu beklediğimiz bir şey. Çünkü SQL ID ve buna bağlı her şey gidiyor.

SELECT a.SQL_ID,A.DISK_READS,A.EXECUTIONS,A.BUFFER_GETS  FROM V$SQL a 
WHERE a.SQL_TEXT LIKE'%SELECT COUNT(*) FROM TEST WHERE OBJECT_ID > 1000%'
SQL_ID        DISK_READS EXECUTIONS BUFFER_GETS
------------- ---------- ---------- -----------
no rows selected.

Şimdi ilk sorgumuzu tekrar çalıştırıp, disk okumalarına bakalım:

SELECT a.SQL_ID,A.DISK_READS,A.EXECUTIONS,A.BUFFER_GETS  FROM V$SQL a 
WHERE a.SQL_TEXT LIKE'%SELECT COUNT(*) FROM TEST WHERE OBJECT_ID > 1000%'
SQL_ID        DISK_READS EXECUTIONS BUFFER_GETS
------------- ---------- ---------- -----------
77mjdqdj82paz    0          1           1450

İlk defa çalıştırıldığı ve sql plan ilk defa hazırlandığı hâlde, hiç disk okuması yapılmıyor.
Özetle FLUSH SHARED_POOL yaparsanız;
• Bütün SQL planları ve bellekte tutulan SQL bilgilerini atarsınız.
• Ancak bu buffer cache’te tutulan data block’larını etkilemez. Daha önce eriştiğiniz block’lar hâlâ bellektedir.
NOT: Shared Pool’u boşaltmak gerekli olmadıkça yapılmamalı. Shared Pool’u boşalttıktan sonra her sql için yeniden plan hesaplanacağından performans sorunu doğabilir.

2.1.DBMS_SHARED_POOL.PURGE

Veritabanındaki bütün sql leri değilde tek bir SQL’i shared pool’dan çıkartmak istersek DBMS_SHARED_POOL packegine’nin PURGE prosedürünü kullanabiliriz. Örneğin;

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';
ADDRESS   HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

no rows selected

Alternatif olarak Carlos Sierra’nın scriptinide kullanabiliriz.
• flush_from_cursor.sql

REM Flushes one cursor out of the shared pool. Works on 11g+
REM To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script.
REM The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs.
REM These scripts are not run by as part of standard database creation.
SPO flush_cursor_&&sql_id..txt;
PRO *** before flush ***
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;
BEGIN
 FOR i IN (SELECT address, hash_value
 FROM gv$sqlarea WHERE sql_id = '&&sql_id.')
 LOOP
 SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');
 END LOOP;
END;
/
PRO *** after flush ***
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;
UNDEF sql_id;
SPO OFF;

• output

SQL> @flush_from_cursor.sql
Enter value for sql_id: 0k81dk25hwcx8
*** before flush ***
old   2: FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1
new   2: FROM gv$sqlarea WHERE sql_id = '0k81dk25hwcx8' ORDER BY 1
 
   INST_ID LOADED_VERSIONS INVALIDATIONS ADDRESS          HASH_VALUE
---------- --------------- ------------- ---------------- ----------
         1               1             1 00000024FC21E608 2332963752
         2               1             1 00000027BA545408 2332963752
 
old   2: FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2
new   2: FROM gv$sql WHERE sql_id = '0k81dk25hwcx8' ORDER BY 1, 2
 
   INST_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I
---------- ------------ --------------- ---------- -
         1            0      1169678410        344 Y
         2            0      1169678410        344 Y
 
old   3:  FROM gv$sqlarea WHERE sql_id = '&&sql_id.')
new   3:  FROM gv$sqlarea WHERE sql_id = '0k81dk25hwcx8')
 
PL/SQL procedure successfully completed.
 
*** after flush ***
old   2: FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1
new   2: FROM gv$sqlarea WHERE sql_id = '0k81dk25hwcx8' ORDER BY 1
 
   INST_ID LOADED_VERSIONS INVALIDATIONS ADDRESS          HASH_VALUE
---------- --------------- ------------- ---------------- ----------
         1               1             2 00000024FC21E608 2332963752
         2               1             1 00000027BA545408 2332963752
 
old   2: FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2
new   2: FROM gv$sql WHERE sql_id = '0k81dk25hwcx8' ORDER BY 1, 2
 
   INST_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I
---------- ------------ --------------- ---------- -
         1            0      1169678410        344 Y
         2            0      1169678410        344 Y

http://www.cagataycebi.com/oracle/20_memory_management.pdf
http://technopark02.blogspot.com/2010/05/oracle-rdbms-flushing-single-sql.html

Mustafa Bektaş Tepe
İyi Çalışmalar

Loading