Açık ve önbelleklenmiş imleçlerin izlenmesi

Açık imleçler(cursors) library cache içinde yer tutmaktadırlar. Pekçok oturumunun, library cache içini tepeleme doldurmasının önüne geçmek için veya CPU’nun milyonlarca çözümleme(parse) çağrıları ile tıkanmasını önlemek için, OPEN_CURSORS parametresi ayarlanır.

OPEN_CURSORS parametresi, her bir oturumun oturum başına açabileceği maksimum imleç sayısını belirtir. Mesela, eğer OPEN_CURSORS parametresi değeri 1,000 olarak ayarlanırsa, bu durumda her bir oturum bir seferde en çok 1,000 imleci açabilir. Eğer tek bir oturum OPEN_CURSORS parametresince belirlenen değeri aşarsa, bu açacağı imleçler esnasında ORA-01000 hata mesajını alacaktır.

OPEN_CURSORS parametresinin varsayılan değeri 50’dir, ancak  pek çok uygulama için bu sayının en azından 500, hatta pek çok yeni nesil web uygulamalarında yüzlerce kullanıcı oturumunun paylaşımlı havuzları sık şekilde kullanmasından dolayı bu sayının 1,500 olması tavsiye edilir.

Eğer SESSION_CACHED_CURSORS parametresi ayarlanmazsa, değeri 0 olarak ayarlanır ve oturum için hiç bir imleç önbelleklenmez. Tabii, imleçler paylaşımlı havuzda öncelleklenecek, ancak bunları bulmanız gerekecektir ve bu durum en iyimser yaklaşımla bolca yumuşak çözümleme(soft parse) olayına sebep verecektir. Eğer bu parametre ayarlanırsa Oracle, library cache içine bakacak ve bu SQL komutu için 3 seferden fazla çözümleme(parse) çağrısı yapılıp yapılmadığına bakacaktır. Eğer yapılmışsa, Oracle bu komut ile ilişkili oturum imlecini oturum imleç önbelleği içine taşıyacaktır.  Bu oturum tarafından ilgili SQL komutu için yapılan daha sonraki çözümleme çağrıları oturum imleç önbelleğinden doldurulacak, böylece yumuşak çözümleme(soft parse) olayından bile kaçınılmış olunacaktır. Bu noktada çözümleme işleminden kaçınmak mümkün değildir ve “daha yumuşak” çözümleme olayları, daha az CPU tüketimi ve çok daha hızlı çözümleme anlamına gelecektir.

Oturumlarca imleçlerin önbelleklenmesinin başlıca avantajı, daha az çözümleme zamanı ve böylece daha hızlı ayrıntılı çalıştırma zamanı anlamını taşımaktadır. Bu durum genellikle bir formdan, diğer bir forma geçiş yaparken ilk formun açık oturum imleçlerini kapatmak zorunda bırakan tipte form uygulamalarında oldukça faydalı olmaktadır. Tekrar ilk forma geçiş yaparken özdeş imleçler yeniden açılır. Böylece oturum tarafından imleçleri önbelleklemek, yeniden çözümlemeyi azaltır.

Elbette başka avantajlarda söz konusudur. Bir oturum daha önceden çözümlenmiş SQL için library cache içine bakma zorunda kalmadığı andan itibaren, oturum tarafından önbelleklenmiş imleçler library cache ve paylaşımlı havuz mandallarını(shared pool latches) daha seyrek kullanacaktır. Bu noktalar meşgul OLTP sistemlerinde çakışmaların ana noktalarıdır. Mandalı azaltmak, mandal bekleme olaylarınıda azaltır, böylece sadece hız anlamında değil aynı zamanda ölçeklenebilirlik üzerindede artış meydana getirir.

Aşağıdaki sorgu, oturum tarafından mevcut açık imleç sayısını verecektir;

select a.value, s.username, s.sid, s.serial#
   from v$sesstat a, v$statname b, v$session s
   where a.statistic# = b.statistic#  and s.sid=a.sid
      and b.name = 'opened cursors current';

Eğer, bir çok web sunucusu üzerinde N-sıralı uygulamalar çalışıyorsa, bu durumda kullanıcı adı ve makine tarafından açık imleçleri gözlemlemekte aşağıdaki sorgu faydalı olacaktır.

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine
  from v$sesstat a, v$statname b, v$session s
  where a.statistic# = b.statistic#  and s.sid=a.sid
    and b.name = 'opened cursors current'
  group by s.username, s.machine
  order by 1 desc;

OPEN_CURSORS parametresini iyileştirmenin en iyi yolu oturumların en yüksek sayıda açtığı imleçleri bulup, bu değerin üstünde bir sayıyı OPEN_CURSORS paremetre değeri olarak atamaktır. Her oturum OPEN_CURSORS parametresindeki değer kadar imleç açacak diye bir kural yoktur, imleçler ihtiyaç duyulduğunda ihtiyaç sayısı kadar açıldığından bu değerin yüksek olmasının sistem üzerinde negative bir etkisi yoktur.  

OPEN_CURSORS değerinin yeterince yüksek olduğunu anlamak için V$SESSTAT görünümü izlenebilir ve eğer oturumlar limite yakın seyediyorsa bu değer arttırılmalıdır. Aşağıdaki sorguda oturumların maksimum seviyede açtığı imleç sayısı OPEN_CURSORS değeri listelenmektedir.

 select max(a.value) as highest_open_cur, p.value as max_open_cur
  from v$sesstat a, v$statname b, v$parameter p
  where a.statistic# = b.statistic#
    and b.name = 'opened cursors current'
    and p.name= 'open_cursors'
  group by p.value;

HIGHEST_OPEN_CUR    MAX_OPEN_CUR
----------------    ------------
            1953            2500

Oturum imleç önbelleğinin izlenmesi
V$SESTAT  görünümü ayrıca ilgili oturumun imleç belleği içindeki oturum imleç sayısını her bir oturum bazında listelemektedir.

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'session cursor cache count' ;

Bunun yanında, V$OPEN_CURSOR görünümü sorgulanarak oturum imleç önbelleği içinde ne olduğuda direkt olarak görülebilmektedir. V$OPEN_CURSOR görünümü, oturum önbelleklenmiş imleçlerini SID değeri ile listeler ve SQL komutunun ilk karakterlerini ve SQL ID değerlerinide sonuca ekler, böylece hangi imlecin kime ait olduğu net olarak gözlemlenebilir.

select c.user_name, c.sid, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id
and c.sid=&sid;

SESSION_CACHED_CURSORS değerinin iyileştirilmesi
Eğer sürekli imleç kapatan ve yeniden açan uygulamalar iyileştirilmek isteniyorsa, SESSION_CACHED_CURSORS başlangıç parametresi ayarlanmalı ve V$SESSTAT görünümünden alınacak iki ayrı istatistik ile bunun verimliliği izlenmelidir. "session cursor cache hits" istatistiği, oturum tarafından bir komut için gönderilmiş yeniden çözümleme çağrısının ne kadar sayıda oturum imleç önbelleğinde bulunduğunu gösterir, kısacası yeniden çözümleme gerektirmeme ve bir oturumun bunun için library cache üzerinden arama yapmasına gerek olmaması demektir. Bu değer, "parse count (total)" istatistiği ile kıyaslanır. "session cursor cache hits" değerinin "parse count (total)" değerinden çıkarılması ile gerçekte oluşan çözümleme sayısı elde edilmektedir.

select cach.value cache_hits, prs.value all_parses, prs.value-cach.value sess_cur_cache_not_used
   from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
   where cach.statistic# = nm1.statistic# 
     and nm1.name = 'session cursor cache hits'
     and prs.statistic#=nm2.statistic#
     and nm2.name= 'parse count (total)'
     and cach.sid= &sid and prs.sid= cach.sid ;

Enter value for sid: 134
old   8: and cach.sid= &sid and prs.sid= cach.sid
new   8: and cach.sid= 134 and prs.sid= cach.sid

CACHE_HITS   ALL_PARSES   SESS_CUR_CACHE_NOT_USED
----------   ----------   -----------------------
       106          210                       104

Verilecek olan SID ile maksimum “session_cached_cursors” değeri ile oturum önbelleklenmiş imleçlerini kıyaslayarak kesişimin izlenmesi ise aşağıdaki sorgu kullanılabilir;

select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial#
  from v$sesstat a, v$statname b, v$session s, v$parameter2 p
  where a.statistic# = b.statistic#  and s.sid=a.sid and a.sid=&sid
    and p.name='session_cached_cursors'
    and b.name = 'session cursor cache count' 
   order by 1 desc, 2 desc ;



CURR_CACHED MAX_CACHED USERNAME          SID    SERIAL#
----------- ---------- ---------- ---------- ----------
        98  100        MDBUSR            232      36002
        98  100        MDBUSR            219      25177
        97  100        MDBUSR            208      59860
...

Yukardaki sorgu sonucunda 232, 219 ve 208 numaralı SID lerin oldukça maksimum cache sayısına ulaştığı görülmektedir.

Bunun sonucunda "session cursor cache" içinde bulunan toplam parse çağrılarının yüzdesini bulmak için aşağıdaki sorgu kullanılabilir. Sorgu sonucundan da görüleceği üzere yukardaki sorgudan maks. cache sayısına ulaştığını tespit etmiş olduğumuz SID lerin toplam parse çağrısı, "session cursor cache" oranına göre oldukça düşüktür(% found in cache kolonundaki yüzdesel oran). Bu sonuç bize SESSION_CURSOR_CACHE değerinin yükseltilmesi gerektiğini işaret etmektedir.


SELECT cach.value cache_hits, prs.value all_parses,round((cach.value/prs.value)*100,2) as "% found in cache"
FROM v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
WHERE cach.statistic# = nm1.statistic#
AND nm1.name = 'session cursor cache hits'
AND prs.statistic#=nm2.statistic#
AND nm2.name= 'parse count (total)'
AND cach.sid in (232,219,208) 
AND prs.sid= cach.sid;

CACHE_HITS ALL_PARSES % found in cache
---------- ---------- ----------------
       141        864            16,32
       155        562            27,35
       423       1638            25,82


3 rows selected.