Oracle 11g Query Result Cache ile sorgu sonuçlarının önbelleklenmesi

Result Cache özelliği nedir?

Cache(Önbellek): Oracle dünyasında pek çok önbellek çeşidi vardır: library cache, buffer cache, dictionary cache, database cache, keep cache, recycle cache, etc. Çok uzun zamandan beri Oracle mimarisindeki ana hedef, en iyi performansı yakalamak için verileri önbelleklemektir.

Bir sorgu ilk kez çalıştırıldığında kullanıcının prosesi bu veriyi “database buffer cache” içinde arar. Eğer bu veri buradaysa(başka birileri bu veriyi daha önce çağırdıysa) bunu kullanır. Aksi takdirde, bu veriyi önbelleğe almak için veri dosyasına I/O işlemi yapar ve bu veriden final sonuç seti oluşturulur.

Daha sonra, eğer başka bir sorgu aynı veri setine ihtiyaç duyarsa bu kullanıcı tarafından ihtiyaç duyulan sonuç setini inşa etmek için önbellekteki bu veri ilgili kullanıcı prosesince kullanılır. Eğer önbellek tampondaki veri tekrar kullanılabilmekteyse, o zaman nedir peki bu yeni gelen “Result Cache” özelliği? Basit kavramla Result Cache, önbellek içerisinde sonuç setlerini tutan bir önbellek alanı olarak adlandırılabilir - yani paylaşımlı havuz içinde çalıştırılmış olan sorgu sonuçlarını tutan bir alan-

Peki bu demektir? Gelin bunu bir örnek üzerinden netleştirelim. Aşağıdaki örnek Oracle Database 11g Release 2 veritabanında çalıştırılıyor:

SELECT OL_NUMBER, SUM (OL_AMOUNT), SUM (OL_QUANTITY)

FROM   ORDER_LINE OL, ORDERS ORD

WHERE  OL.OL_O_ID = ORD.O_ID AND

       OL.OL_W_ID = ORD.O_W_ID AND

       OL.OL_D_ID = ORD.O_D_ID

GROUP BY OL_NUMBER;

Trace dosyasının tkprof rapor çıktısı, sorgunun 347,000 satırdan daha fazlası üzerinden geçiş yaparak sonuç seti olan 300 satırlık veriyi içeren sonuç çıktısını sağladığını ortaya koymaktadır.

call     count       cpu    elapsed       disk      query    current        rows

-------       ------      --------      ----------      ----------      ----------      ----------       ----------

Parse        1      0.01       0.02          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch       21     33.04      96.12     346671     347172          0         300

-------       ------      --------      ----------      ----------      ----------      ----------       ----------

total       23     33.06      96.14     346671     347172          0         300

Başka bir kullanıcı aynı sorguyu çalıştırdığı zaman ne olmaktadır? Kullanıcı oturumu, önbellek tamponu içindeki tüm satırlar üzerinden geçiş yaparak, final sonucu olan 300 satıra erişecektir. Peki, bu 300 satırı ikinci seferde direkt getirecek bir metod olsaydı? Aslında, bu veriyi tutacak yeterli büyüklükte önbellek olursa bu mümkün olabilmektedir. İşte bu olay tam olarak “Result Cache” adını verdiğimiz özelliktir.

Yukardaki tkprof raporunda gösterildiği üzere bu 300 satır Result Cache özelliğini kullanarak paylaşımlı havuz içindeki Result Cache bölümüne taşınmaktadır. Ardından, aynı sorgu çalıştırıldığında 347,000 satıra üzerinden geçiş yapmaktan ziyade “Result Cache” içinden ilgili 300 satır döndürülür. Bu da sorgu sonuç performans iyileştirme çalışmalarında aslında pekçok sıkıntılı araştırma noktasında DBA lere inanılmaz yardımcı olacak bir özellik olmaktadır.

“Result Cache” hem istemci tarafında hemde sunucu tarafında yönetilebilmektedir. İstemci tarafında “Result cache” özelliğinin kullanılabilmesi için uygulamanın Oracle Call Interface(OCI)’yi kullanabilmesi gerekmektedir. Sunucu tarafı yapılandırması nispeten daha basittir. Bu yazıda istemci tarafından ziyade sunucu tarafında yapılandırmaya odaklanacağız.

Sunucu tarafında yapılandırmada aynı sorgunun “Result Cache” havuzundan çalıştırılabilmesi için sorgu cümlesinde /*+ RESULT CACHE */ hinti kullanılmalı veya  result_cache_mode parametresi AUTO olarak ayarlanmalıdır. Bu parametrenin AUTO olarak ayarlanması tüm sorgu sonuçlarını paylaşımlı havuz içerisindeki “Result Cache” bölümüne taşıyacaktır, yani yukardaki örnekteki sonuç satırları olan 300 satır, yeniden kullanılmak üzere bu alana taşınacaktır.

/*+ RESULT CACHE */ hinti ile bir sorgu çalıştırıldığında, veritabanı sonuçların önbellekte olup olmadığını belirlemek için paylaşımlı havuz içindeki “Result Cache” bölümüne bakar. Eğer buradaysa veritabanı bu sonuçları sorguyu çalıştırmadan kullanıcıya geri döndürür.

Bununla beraber, aşağıdaki sebeplerden dolayı sorgu sonucu önbelleğe alınmamış olabilir:

• Sorgu ilk defa çalıştırıldığı durumlarda.
• result_cache_max_result parametresi ile etkinleştirilen maksimum alan sınırlaması sonucunda, başka bir işlemin önbellekteki boş alanı tahsis etmesinden dolayı önbellek boşaltılmış olabilir(flush).
• Veritabanı yöneticisi “dbms_result_cache.flush procedure” prosedürünü çalıştırmış olabilir. Bu prosedür sonunda tüm önbellek boşaltılmış olur ve sorgunun tekrar çalıştırılması gerekmektedir. Ardından final seti “Result Cache” alanına taşınır ve burada sonuç seti saklanır.

NAME                             TYPE                VALUE

------------------               -----------         ---------

client_result_cache_lag          big integer         3000

client_result_cache_size         big integer         0

result_cache_max_result          integer              5

result_cache_max_size            big integer         251680K

result_cache_mode                string              MANUAL

result_cache_remote_expiration   integer             0

Yukarıda yeralan client_result_cache_lag ve client_result_cache_size parametreleri “Result Cache” özelliğinin istemci tarafında yapılandırılması için kullanılmaktadır.

Sunucu üzerinde Result Cache alanının büyüklüğü iki parametre ile belirlenmektedir: result_cache_max_result and result_cache_max_size. Varsayılan olarak result_cache_max_size parametresi memory_target parametresinin %0.25’i veya shared_pool parametresinin %1'i büyüklüğündedir. Bu parametreleri değiştirerek ne kadar veri setinin Result Cache alanında saklanacağı kontrol edilebilir. result_cache_max_result parametresi result_cache_max_size ile belirtilen maksimum önbellek alanının ne kadar yüzdesinin beher sonuç setine tahsis edilieceğini gösterir. Varsayılan değer %5’dir.Örneğin, 30 MB result_cache_max_size büyüklüğünde ve 55 result_cache_max_result değerinde, her bir sorgu seti Result Cache içinde en fazla 1.5MB lık alanı kullanabilir.

Her bir sorgu seti 90 karakter uzunluğunda bir CACHE_ID değeri kullanarak önbellek içinde tanımlanır. Bir sorgunun CACHE_ID değeri V$SQL içinde yeralan ve “library cache” içindeki sorguyu tanımlamak için kullanılan SQL_ID değeri ile eşleşmez. Aksine, SQL_ID değeri Oracle üzerinde çalıştırılan her bir SQL sorgusu ile üretilir, CACHE_ID değeri ise sorgu sonuçlarını saklayan paylaşımlı havuz içindeki “Result Cache” bölümü içindeki bir alanı temsil eder.

Eğer bu yazıda çalıştırdığımız sorgu bu sefer  /*+ RESULT_CACHE */ hinti ile çalıştırılırsa aşağıdaki plan üretilir. Result Cache’e atanan isim burada CACHE_ID değeridir(kırmızı kısım).

--------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                            |   150 |  4950 |       |   126K  (1)| 00:25:17 |
|   1 |  RESULT CACHE           | 8fbjhchhd9zwh7uhn4mv7dhvga |       |       |       |            |          |
|   2 |   HASH GROUP BY         |                            |   150 |  4950 |       |   126K  (1)| 00:25:17 |
|*  3 |    HASH JOIN            |                            |    20M|   649M|    45M|   125K  (1)| 00:25:09 |
|   4 |     INDEX FAST FULL SCAN| ORDERS_I2                  |  2063K|    21M|       |  2743   (1)| 00:00:33 |
|   5 |     INDEX FAST FULL SCAN| IORDL                      |    20M|   432M|       | 87415   (1)| 00:17:29 |
--------------------------------------------------------------------------------------------------------------

Result Cache ile ilişkili bilgileri izlemek için birkaç görünüm vardır. Result Cache ile ilişkili objeleri elde etmek için V$RESULT_CAHCE_OBJECTS görünümü kullanılmaktadır. Aşağıdaki sorgu yukardaki örnekte elde edilen 8fbjhchhd9zwh7uhn4mv7dhvga adlı CACHE_ID değerinin result Cache içindeki sonuç setini doğrulamaya yardımcı olur. Sonuçta; 300 satırın Result Cache içinde saklandığı ROW_COUNT kolonu içindeki değer ile doğrulanmaktadır.

SQL> SELECT ID, TYPE, CREATION_TIMESTAMP, BLOCK_COUNT, COLUMN_COUNT, PIN_COUNT, ROW_COUNT

FROM V$RESULT_CACHE_OBJECTS WHERE CACHE_ID='8fbjhchhd9zwh7uhn4mv7dhvga';

ID       TYPE         CREATION_  BLOCK_COUNT COLUMN_COUNT  PIN_COUNT  ROW_COUNT

----------    ----------            ---------       -----------       ------------      ----------      ----------

2        Result     19-JAN-10           1            3          0        300