Açık kursör hatalarının çözümü hakkında bir inceleme

Eğer çok sık “Maksimum Open Cursors exceeded error” hata mesajı ile karşılaşılıyorsa, ilk yapılması gereken şeylerden birisi open_cursors başlangıç parametresinin değerinin kontrol edilmesidir. Aşağıdaki gibi açık kursörlerin mevcut değeri görüntülenebilir.

SQL> show parameter open_cursors

NAME         TYPE        VALUE

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

open_cursors integer     300

OPEN_CURSORS parametresi bir oturumun bir seferde maksimum sayıda açabileceği kursör sayısını ayarlar ve açık kursör sayısını kontrol etmek için kullanılmaktadır. Bu değerin çok düşük seviyede ayarlanması ORA-01000 hatasının alınması ile sonuçlanmaktadır. OPEN_CURSORS parametresi için çok büyük bir değerin belirlenmesinin bir zararı yoktur (pek mümkün olmasada tüm oturumların eşzamanlı olarak maksimum kürsör dışarı vereceği beklenmediği durumlar dışında), böylece bu parametreyi daha yüksek bir değere artırarak genellikle kursör tabanlı hatalar kolayca çözülebilir. Ancak, bu değerin arttırlması her zaman bu sorunu çözmeyebilir. Böyle durumlarda bu açık kursörleri kullanan proseslerin hangileri olduğunun bulunması gerekmektedir.  

SQL> select a.value, s.username,s.sid,s.serial#,s.program,s.inst_id

from gv$sesstat a,gv$statname b,gv$session s

where a.statistic# = b.statistic# and s.sid=a.sid

and b.name='opened cursors current';

GV$OPEN_CURSOR (veya V$OPEN_CURSOR) görünümleri her bir kullanıcı oturumunun o an açtığı, çözümlediği(parse) ve önbelleğe aldığı tüm kursörleri göstermektedir. Aşağıdaki sorgu kullanılarak yüksek sayıda açık ve çözümlenmiş veya önbelleğe almış oturumlar belirlenebilmektedir.

SQL> select saddr, sid, user_name, address,hash_value,sql_id, sql_text

from gv$open_cursor

where sid in

(select sid from v$open_cursor

group by sid having count(*) > &esik_degeri);

Bu sorgu tanımlanan eşik değerinden daha yüksek sayıdaki açık kursörler için bütün oturumları listelemektedir. Bu şekilde, sorgu çıktısı sınırlandırılabilmekte ve açık, çözümlenmiş veya yüksek sayıda  kursörleri önbellekleyen oturumlar üzerine odaklanılabilmektedir.

Yukardaki sorgu sonucunda sorunlu oturum için gerçek SQL kodu ve açık kursör sayısı aşağıdaki gibi elde edilebilmektedir:

SQL> select sql_id,substr(sql_text,1,60) sql_text, count(*)

from gv$open_cursor where sid=&sid

group by sql_id,substr(sql_text,1,60)

order by sql_id;

Yukardaki sorgu tanımlanacak olan SID için tüm açık kursörlerin SQL komutlarını göstermektedir. Böylece, yüksek sayıda açık kursör tutan SQL komutlarının neden bu kadar yüksek sayıda açık kursör tuttuğu incelenebilmektedir.

Eğer kullanılan uygulama açık kursörleri kapamıyorsa, OPEN_CURSORS parametresinin daha yüksek bir değere arttırılmasının bir yararı olmayacaktır.  Sorun geçici olarak çözülebilecektir, ancak aynı sorun ile pek yakında tekrar karşılaşılacaktır.  Eğer uygulama katmanı PL/SQL kod ile oluşturulan ref kursörleri hiçbir zaman kapamazsa, kullanılan kursörler sunucu kaynaklarında askıda kalacaktır.  Bu durumda mantıksal uygulama katmanının açık kursörleri kapatması için yendien düzenlenmesi gerekecektir ve sorunun çözümü veritabanı tarafında değildir.

Eğer bir uygulama sunucusunda Oracle WebLogic Server veya IBM WebSphere gibi bir java uygulaması mevcut ise, JDBC bağlantı havuzları uygulama için açık veritabanı bağlantılarını desteklemelidir. Bu bağlantılarda herhangi bir şekilde hazırlanmış bir SQL komutu bir kursör kullanacaktır. Çoklu uygulama sunucu instance ları ve çoklu JDBC bağlantı havuzları, veritabanının tüm kursörleri destekleme ihtiyacı anlamına gelecektir. Eğer birçok talep aynı oturum ID sini paylaşırsa, üstü kapalı(implicit) kursörler sebebiyle açık kursör sorunu oluşabilmektedir. Bunun çözümü, o zaman herbir talep sonrasında bağlantının kapatılması şeklinde olmaktadır.

“Kursör sızıntısı”, veritabanı bir kursörü açtıktan sonra işi bittiğinde kapamadığı zaman olmaktadır.  10046 izlemesi ile oturumun açıp kapamadığı kursörler bulunabilmektedir.

SQL> alter session set events '10046 trace name context forever, level 12';

Eğer aynı SQL komutunun farklı kursörler ile ilişkili olduğu anlaşılırsa, bu demektirki uygulama kendi kursörünü kapatmamaktadır.  Eğer uygulama kursörü açtıktan sonra kapamıyorsa, bu uygulamanın çalıştırdığı bir sonraki SQL komutu için farklı kursör sayısı atamaktadır.  Eğer kursör kapanmışsa, Oracle bir sonra atanan kursör için aynı kursör sayısını kullanacaktır.  Böylece, PARSING IN CURSOR #nnnn parçasının 10046 izlemesi çıktısında devamlı artış gösterdiği görülürse, uygulamanın kursörleri kapamadığı anlaşılmaktadır.

Eğer bu 10046 izlemesi sonunda oturum tarafından çok sayıda alt kursör(child cursor) ün açık olarak tutulduğundan şüphelenilirse, bunların en son ne zaman aktif olduğu aşağıdaki sorgu ile bulunabilmektedir.

SQL> SELECT

      CHILD_NUMBER CN,

      PARSING_SCHEMA_NAME,

      OPTIMIZER_ENV_HASH_VALUE OPTIMIZER_ENV,

      INVALIDATIONS,

      PARSE_CALLS,

      IS_OBSOLETE,

      FIRST_LOAD_TIME,

      TO_CHAR(LAST_ACTIVE_TIME,'YYYY-MM-DD/HH24:MI:SS') LAST_ACTIVE_TIME

FROM V$SQL

WHERE SQL_ID=&sql_id;

Burada OPTIMIZER_ENV_HASH_VALUE kolon değeri açık alt kursörler için farklı değerlerdeyse bu farklı olan alt imleçlerin hash değerlerinin V$SQL_OPTIMIZER_ENV görünümünden aşağıdaki gibi incelenmesi gerekmektedir. Aşağıdaki örnekte “6thgzsfstg5ugh” SQL ID sinin 3 farklı hash_value değerine sahip alt kursörlerinin (0,3 ve 6) analiz sorgusu yer almaktadır.

SQL>  SELECT CHILD_NUMBER CN,NAME,VALUE, ISDEFAULT DEF

FROM V$SQL_OPTIMIZER_ENV

WHERE SQL_ID='6thgzsfstg5ugh'

      AND CHILD_NUMBER in (0,3,6)

ORDER BY NAME,CHILD_NUMBER; 

SESSION_CACHED_CURSORS başlangıç parametresi, her bir oturum için maksimum sayıdaki önbelleklenmiş kapalı kursör sayısını ayarlamak için kullanılmaktadır ve varsayılan değeri 50’dir. Bu parametre ile bir oturumun aşırı sayıda kursör açmasının önüne geçilmektedir, böylece library bellek doldurulur veya aşırı “hard parse” işleminin önüne geçilir. Bir SQL sorgusu için tekrarlanmış çözümleme(parse) çağrıları Oracle’ın o komutun oturum kursörünü oturum kursör önbelleğine taşımasınada önderlik etmektedir.  Veritabanı, daha sonraki çözümleme çağrılarını karşılamak için kursörü yeniden açmak yerine, önbelleklenmiş kursörleri kullanmaktadır.

Bir SQL komutu yeniden çalıştırılırken, Oracle ilk önce bu komutun paylaşımlı havuz içerisindeki çözümlenmiş versiyonunu bulmaya çalışmaktadır, eğer paylaşımlı havuzda çözümlenmiş versiyon bulunursa “soft parse” meydana gelir. Bir komutun paylaşımlı havuzda çözümlenmiş versiyonunu bulunamazsa, Oracle çok daha pahalı “hard parse”  işlemine girilmesi noktasında zorlanmaktadır. “Soft parse”, hard parse” işlemine nazaran çok daha az maliyetli iken,  çok fazla sayıda “soft parse” işlemi CPU kullanımı gereksinimi ve library önbellek mandalları(latches) sebebiyle performansı etkileyebilecektir. “Soft parse” sayısını azaltmak için Oracle, mantıksal oturum içinde her bir oturumun en son kapanan kursörlerini önbellekler, böylece kursör önbelleğini dolduracak olan her bir tekil oturum kursör önbelleklenmesi işlemininde önüne geçilir ve aşırı “soft parse “işleminin önüne geçilir.

SESSION_CACHED_CURSORS başlangıç parametresinin varsayılan değeri olan 50 pek çok veritabanı için küçük olabilmektedir. Aşağıdaki sorgu kullanılarak veritabanının oturum önbellekli kursör kullanımında maksimum sınırda olup olmadığı anlaşılabilmektedir:

SQL> select max(value) from v$sesstat

where statistic# in (select statistic# from v$statname

where name = 'session cursor cache count');

MAX(VALUE)

----------

47

Yukardaki sorgu ile geçmişte önbelleklenen maksimum sayıdaki oturum kursör sayısı görülebilmektedir. Buradaki sayı eğer SESSION_CACHED_CURSORS parametresinde belirtilen değere çok yakınsa,  bu durumda bu sayıyı biraz artırmak gerekmektedir. Oturum kursör önbelleklemesi paylaşımlı havuzu kullanmaktadır. Eğer otomatik bellek yönetimi kullanılıyorsa, SESSION_CACHED_CURSORS parametresinin yeniden ayarlanmasından sonra paylaşımlı havuzun büyüklüüğünü değiştirmek için bir işlem yapılmasına gerek yoktur, otomatik bellek yönetimi ile Oracle paylaşımlı havuzu -eğer gerekliyse- genişletecektir. 
Bunun yanında SESSION_CACHED_CURSORS parametre değerinin iyileştirme amacıyla izlenmesi hakkında arşivdeki yazımı okumanızı tavsiye ederim. Aşağıdaki sorgu ile her bir oturumun kendi oturum önbelleği içinde ne kadar sayıda kursör tuttuğu görülebilmektedir.

SQL> 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';