SQL komutlarının ayrıştırılması safhası hakkında

SQL ile ilgili bir performans sorunu oluştuğunda,sorunu anlamak ve ardından bir çözüm bulabilmek için ilk olarak SQL komutlarının veritabanında nasıl işlendiğinin anlaşılması gerekmektedir. Uygulama, veritabanı instance’ına her bağlandığında, eninde sonunda veritabanı sunucusunda yaslı olan bir sunucu prosesine atanır. Bu sunucu prosesi, veritabanı ve son kullanıcı uygulaması arasındaki arayüzü desteklemektedir. Bu proses ayrıca SQL komutu sentaksını kontrol etmek, SQL komutunun nasıl yürütüldüğünün ve sonuçların son kullanıcı uygulamasına nasıl döndüğünün belirlenmesinden sorumludur. SQL komutu, sunucu prosesi tarafından alındığında, “hafif ayrıştırma-soft parse” meydana gelmektedir. Bu safhada komut sentaksı doğruluk için kontrol edilmekte, komut içinde referans verilen objelere kullanıcının erişim yetkisinin olduğu doğrulanmakta ve SQL “karma” değeri(SHV-SQL hash value) komut metninde hesaplanmaktadır. SHV, veritabanı içinde komutu kolayca belirlemek için kullanılan numerik bir değerdir. Ardından, SGA içindeki “library cache” de SHV in halen mevcut olup olmadığının belirlenmesi için araştırılır. Aynı yada diğer bir sunucu prosesi, benzer bir SQL komutunu halihazırda çalıştırdığında bu olay meydana gelir.  Eğer SHV bulunursa, sunucu prosesi komut hakkında “library cache” içinde saklanan bilgiyi getirir. Bu, yürütme planı olarak bilinen, sorgu içinde çeşitli nesnelere erişim için kullanılan algoritmaları içermektedir.  Komut alındıktan sonra, eğer bağlaç değişkenler(bind variables) komut içinde mevcutsa, aslına uygun değerler yerine geçer ve yürütme planı tarafından belirtilen işlemler sunucu prosesi tarafından yapılır. SQL komutlarını yürütürken bütünsel olarak en düşük kaynak maliyetini ürettiği ve en hızlı yanıt süresini verdiğinden dolayı bu arzu edilen bir olaylar dizisi olmaktadır.

Eğer SQL komutuna karşılık gelen SHV, “soft parse” esnasında “library cache” içinde bulunmazsa, sunucu prosesi komut üzerinde “hard parse-katı ayrıştırma” işlemi gerçekleştirmek zorundadır. Bu operasyon esnasında, komut için yürütme planı belirlenmeli ve sonuç “library cache” içinde saklanmalıdır. Bu genellikle pahalı bir hesaplama adımıdır ve bazı durumlar için oldukça yüksek zaman tüketebilir. Komutun kompleksliğine bağlı olarak, optimal yürütme planı keşfinden önce yüzlerce hatta binlerce farklı permutasyon denenebilir. Varsayılan olarak, geliştirici 80,000 gibi pekçok farklı yürütme planını test ederek bunların içinden en düşük kaynak maliyetinde  bir yürütme planını bulacaktır. Yürütme planını saklamak için, belleğin “library cache” içinden tahsis edilmesi gerekir. Bu işlem esnasında, bir dizi mandal(latches) sonradan kazanılmalıdır ve sunucu prosesi tarafından tutulmalıdır. Mandallar, basit olarak, library, dictionary veya tampon bellek gibi belirli bir bellek objesine aynı anda sadece tek bir prosesin yazmasını güvenceye almak için, veritabanı içinde kullanılan semaforlar veya bayraklardır. Eğer pekçok oturum eşzamanlı olarak “hard parse” gerçekleştirirse, bu mandallar için çekişmeler meydana gelebilir. Bu da sorgu için yanıt süresini azaltacaktır. Eğer birçok oturum tarafından pekçok basit ve benzersiz SQL komutları yürütülürse, paylaşımlı havuz oldukça yüksek miktarda parçalanacaktır(fragmentation) ki, bu durumda ilerde mandal çekişmelerine sebebiyet verebilecektir.  

Aşırı durumlarda, bu tip mandal çekişmeleri “library cache”e yazmak için gerekli mandalları elde etmek için zamanının %50 sinden fazlasını beklemeyle tüketen oturumlar için oldukça iyi olabilir, Sorgular için yanıt süresi ve çıktı muhtemelen kabul edilemez olacaktır.

Sorunun tespiti

Aşırı ayrıştırma problemlerinin tespiti için birçok metot kullanılabilir. Aşağıdaki sorgu aşırı “hard parse” yapan sorunların tespiti için oldukça yararlıdır.  

SELECT /*+ RULE */ s.program, COUNT(*) users,

SUM(t.value) parses, SUM(t.value)/COUNT(*) parses_per_session,

SUM(t.value)/(SUM(sysdate-s.logon_time)*24) parses_per_hour

FROM v$session s, v$sesstat t

WHERE t.statistic# = 153

AND s.sid = t.sid

GROUP BY s.program HAVING SUM(t.value)/COUNT(*) > 2.0

ORDER BY parses_per_hour DESC;

Bu sorgu program ismi tarafından toplanan bir takım ayrıştırma metrikleri üretmektedir. Parses kolonu toplam “hard parse” sayısını göstermekte, parses_per_session kolonu programı çalıştıran tüm oturumlar için ortalama ayrıştırma sayısı olurken, parses_per_hour kolonu ise programı çalıştıran tüm oturumlar için ortalama ayrıştırma sayı olmaktadır.  Bu noktada parses_per_hour kolonundaki yüksek rakamların sebeplerine bakılmalıdır. OLTP programlar için 10 altındaki değerler makul değerlerdir. Batch programlar için daha yüksek değerler kabul edilebilir.  10 üzerindeki değerlere sahip programlar ise araştırılmalıdır. Eğer, bir batch program bağlaç değişkenleri düzgün şekilde kullanmıyorsa, binlerce değer olacaktır.

Şüpheli programlar için aşağıdaki sorgu kullanılarak, çalıştırılan bu tarz SQL komutlarını belirlemek için “library cache” sorgulanabilir. Aşağıdaki bu belirleyici sorguda, şüphelenilen SQL komutunun SQL id isi filtreleme için kullanılacaktır.

SELECT /*+ RULE */ t.sql_text

FROM v$sql t, v$session s

WHERE s.sql_address = t.address

AND s.sql_hash_value = t.hash_value

AND s.sid = <SQL sorgusunun SID si>;

Makül bir örnek alana kadar bu sorgu defalarca çalıştırılabilir ve komutların bağlaç değişkeni kullanıp kullanmadığının belirlenmesi için bu komutlarda incelenebilir. Bağlaç değişkenleri, “library cache” içinde bulunan sorgularda :b1,:b2 gibi tanımlanmaktadır. Yukardaki sorgu sonucu dönen değer aşağıdaki gibi gözükecektir:

SELECT employee_id, first_name, last_name, address

FROM hr.employees WHERE employee_id=:b1;

Eğer bu employee_id değeri aşağıdaki gibi bir değer almışsa, bu durumda bağlaç değişkeni kullanılmamış demektir.

SELECT employee_id, first_name, last_name, address

FROM hr.employees WHERE employee_id=102;

Bunun yanında bağlaç kullanılmayan SQL komutlarının hızlı yoldan ve kolayca tespiti için aşağıdaki sorguda kullanılabilir.

SELECT substr(sql_text,1,30) "SQL",

count(*) ,

sum(executions) "TotExecs"

FROM v$sqlarea

WHERE executions < 5

GROUP BY substr(sql_text,1,30)

HAVING count(*) > 30

ORDER BY 2

Hoşa gitmeyen programlar ve SQL komutları belirlendiğinde, bunların tüm veritbanına  etkisi değerlendirmeye alınmalıdır. Eğer bu hoşa gitmeyen kod belirgin şekilde veritabanında negatif bir etki yapıyorsa, program katmanında düzeltme yapılana kadar, veritabanında kısmi bir iyileşme sağlanması için birtakım konfigürasyon parametre değerleri genişletilmelidir.

Daha öncede belirtildiği gibi aşırı ayrıştırma, optimal CPU tüketiminden daha yüksek bir CPU tüketimi ile sonuçlanacaktır. Ancak, daha büyük etki muhtemelen paylaşımlı havuz içindeki kaynaklar için çekişme esnasında olacaktır. Eğer pekçok küçük komut katı şekilde ayrıştırılırsa(hard parse) bu durumda paylaşımlı havuz muhtemelen  fragmente olacak  ve paylaşımlı havuz fragmente oldukçada katı ayrıştırma için gereken zaman artacaktır. Pekçok benzersiz komutun yürütülmesi prosesi sürdükçe, kaynak çekişmesi kötüleşecektir. Bu kritik kaynaklar  muhtemelen “library cache” içindeki bellek ile paylaşımlı havuzla ilişkili çeşitli mandallar olacaktır. Çekişmenin tespiti için oldukça kolay metotlar vardır. Aşağıdaki sorgu, devam etmeden once tamamlanmak için bekleyen oturumlardaki olayların listesini göstermektedir. v$session_wait görünümü her bir oturum için tek bir satır içerdiğinden, bu sorgu her bir olay için bekleyen oturumların toplam sayısını döndürecektir. Görünüm eş-zamanlı veri içerdiğinden, muhtemel sorunların tespiti için tekrar tekrar çalıştırılmalıdır.

SELECT /*+ RULE */ SUBSTR(event,1,30) event, COUNT(*)

FROM v$session_wait

WHERE wait_time = 0

GROUP BY SUBSTR (event,1,30), state;