SQL incelemelerini yapmak için SQL'lerin nasıl işlendiğini bilmek gerekir. SQL işlenmesi 4 ana kısımdan oluşur.
SQL çalıştırılınca ilk aşamada parse edilir. Syntax kontrolü ve yetki ve mantık kontrolü yapılır. Sorgu doğru yazılmış mı, sorgu doğru yazıldıysa sorgu da yer alan kaynaklara erişilebilinir mi, sorgu çalışabilir durumdaysa anlamlı bir ifade mi oluşturmuştur gibi sorular sorulabilinir. Bundan sonra olay sorgu içerisindeki kaynakların Shared Pool'da olup olmadığının aranmasıdır. Sorgunun arayacağı datalar shared pool'da varsa soft parse edilir. Daha önceden yoksa hard parse edilir. Execution plan'ı çıkarılır. Sorgu eskiden bire bir şekilde çalıştırıldıysa direk cache'den çekilir. SQL sorgularımızda bind değişkenleri kullanırsak hard parse'lara gerek kalmaz.
Shared Pool içerisinde yer alan library cache ve sqlarea PL/SQL ve SQL ifadelerini saklamak içindir. Bir sorgu çalıştırıldığında ilk önce bu cache ortamı aranır.
Bind aşamasına gelindiğinde sorgu içerisinde bind değişkenleri aranır. Bu bind değişkenleri varsa ifadeye değerler atanır. Bu şekilde çalıştırılında sorgu soft parse edilir. Böylece kaynaklar daha az kullanılmış olur.
Execute ile sorgu için gerekli işlemler yapılmaya başlanır. Tablolar sorgulanır.
Fetch ile sonuçlar geri döndürülür. Düzenlenmesi gerekiyorsa düzenlenir.
Execution Plan Nedir?
Execution plan Oracle Optimizer tarafından belirlenen adımlar serisidir. SQL'ler çalıştırılırken işlemler sıralanır. Sorguda sıralama varsa Sort işlemi, başka bir tabloyla birleştirildiyse join işlemi bu plana eklenir. Bu planlardan sorunlar tespit edilebilinir. Eğer index yerine full table scan yapılıyorsa, hint kullanılmıyorsa doğal olarak kaynaklar fazla kullanılıyor demektir.
Execution planına göre çeşitli parametre değişiklikleri yapmamız gerekebilir. İndex yaratıp silmemiz gerekebilir. İstatistik toplamamız gerekip gerekmediğini görebiliriz.
Execution Plan'lerin Görüntülenmesi:
-"Explain Plan" komutu ile optimizer'ın kullanmayı seçtiği yolu görebiliriz. Explain Plan komutunda sorgu çalıştırılmaz; sadece optimizer seçebileceği yol gösterilir.
-V$SQL_PLAN view'ı ile çalıştırılan SQL komutlarının planlarını görebiliriz. Bir durum örneği vermemiz gerekirse ilk önce istenen SQL'in id'si bulunur. Oradaki SQL id V$SQL_PLAN içerisinde aranır ve orada seçilen operasyonlar görülür.
select prev_sql_id from v$session where sid =392;
select * from v$sql_plan where sql_id=(select prev_sql_id from v$session where sid =392);
V$SQL_PLAN'de gerçekten çalıştırılmış SQL gösterilir. EXPLAIN PLAN'de ise teorik olarak belirlenmiş bir SQL bulunur.
V$SQL_PLAN dışında incelenebilecek 2 tane daha önemli tablo bulunur. Bunlar V$SQL_PLAN_STATISTICS ve v$SQL_PLAN_STATISTICS_ALL'dur. V$SQL_PLAN_STATISTICS_ALL view'ı V$SQL_PLAN,V$SQLPLA_STATISTICS ve V$SQL_WORKAREA tablolarının bileşiminden oluşur.
Problemli SQL İncelemesi:
Problemli SQL'leri incelerken önce bazı konulara dikkat etmemiz gerekir. Bunlara hızlıca bakmak iyi olur. Çünkü bunlar temel konulardır. SQL index'inin incelenmesi, SQL'lerde hint kullanılması, SQL sorgularının
biçimlendirilmesi gibi konular için inceleme yapmamız gerekir.
Problemli SQL'ler genelde çok kaynak tüketirler. Uzun parse sürelerine sahiptirler. CPU tüketimi fazladır. Fazladan wait'lere neden oluyordur. Çok fazla I/O yapıyorlardır. Kaynaklar bakımından sorgular AWR raporlarında listelenirler. Buradan hangi sorgularda değişiklik yapılabilineceği araştırılabilinir.
Sql'leri nasıl inceleyebiliriz? Eğer "Toad" programını kullanıyorsak, üst bardaki ambulans görüntülü tuşu incelemek istediğimiz SQL'i seçtikten sonra tıklayabiliriz. Sqlplus'ta çalıştırıyorsak "set autotrace=on" şeklide sorgumuzu çalıştırmadan önce ifademizi çalıştırmamız gerekir.
Bu konuları eğer sıralarsak:
-Tablolar ve index'ler analiz edildi mi?
Index ve tabloların en son analiz edildiği zaman
-SQL Hint'leri kullanılmış mı?
SQL Hint'leri
-Cartesian Product kullanılmış mı?
Kartezyen çarpım bir sorgunun sırasında kullanıldıysa bu sorgu çok fazla kaynak harcamaktadır. Bu sorgunun tekrar düzenlenmesi gerekebilir. O yüzden buna dikkat edilmelidir.
-Full Table Scan mi kullanıyor?
Full Table Scan yapılması gereken zamanlarda sorgularımızı iyice incelememiz gerekir. Çünkü full table scan bayağı maliyetli olabilir. Eğer tablomuz çok küçükse, tablonun büyük bir kısmını sonuç olarak getiriyorsa full table scan iyi bir sorgulama yöntemiyken, tablo çok büyükse ve çok az bir sonuç için full table scan yapılıyorsa kötü bir sorgulama yöntemine dönüşür.
-Sorgu içerisinde kaç tablo join edilmiş?
Sorgu içerisinde join edilen tablolar arttıkça "Cost Based Optimizer" 'ın aralarından seçeceği yöntem sayısı artıyor. Seçenek artınca kullanılan zaman artıyor. Bu sorunu sorgumuzu "Explain Plan" ile test edip, sonuç olarak A-B-C-E-D şeklinde sıralama yaptıysa tespit edebiliriz. A-B-C-D-E şeklinde olsaydı kullanım daha başarılı olurdu. Bu sorunu hint kullanımı ile aşabiliriz ancak hint kullanımı önceden de belirttiğimiz gibi çok tercih edilmemektedir.
-Sorgu planı içerisinde "Remote" sözcüğü var mı?
Bunun anlamı sorgunun sonuçlarının çekilmesi için uzak bir veritabanı bağlantısı yapılmakta; yani
dblink kullanılıyor olabilmektedir. Bu durum doğal olarak sonuçların gelmesini geciktirebilir.
-Trigger bulunan bir tabloya DML ifadelerimi uygulanmakta?
Bu soru sorgunun performansını çok ilgilendirmektedir; çünkü her bir DML ifadesinde(Insert,Update,Delete) sorgu her çalıştığında trigger'ı da tetikleyeceği için zaman kaybına neden olacaktır.
SELECT *
FROM dba_triggers
WHERE table_name = 'tablo_adı';
-INSERT/UPDATE/DELETE yavaşlığı:
Bu durum ise ancak sorguların birbirini lock'lamasıyla oluşabilir. Eskiden çok kısa süren DML işlemleri şimdi çok uzun sürüyorsa eğer sistemdeki "lock"'lar araştırılmalıdır. Aşağıdaki 2 sorgu bu konuda bize yardımcı olabilir.
Bu sorgu bize sistemdeki kilitli durumları verir:
Bu sorguda işletim sistemi kullanıcısını parametre olarak vermek yeterlidir. Client PID veya Server PID'de sorup bu sorguyu daha detaylı hale getirebiliriz.
SELECT s.osuser
, s.process
, p.spid
, s.username
, decode (s.status,'INACTIVE','I'
, 'ACTIVE' ,'A'
, 'KILLED','K'
, s.status) status
, w.event
, w.wait_time
, t.sql_text
FROM sys.v_$session s
JOIN sys.v_$process p ON (p.addr = s.paddr)
LEFT OUTER JOIN
sys.v_$session_wait w ON (w.sid = s.sid)
LEFT OUTER JOIN
sys.v_$sqltext t
ON (
t.address = s.sql_address
AND t.hash_value = s.sql_hash_value
)
WHERE p.background IS NULL
AND s.audsid != userenv('SESSIONID')
AND (
upper(s.osuser) like upper('&response')
OR s.process = '&response'
)
AND (
p.username = 'oracle'
OR p.username = s.osuser
)
UNION ALL
SELECT s.osuser
, s.process
, p.spid
, s.username
, decode (s.status,'INACTIVE','I'
, 'ACTIVE' ,'A'
, 'KILLED','K'
, s.status) status
, w.event
, w.wait_time
, t.sql_text
FROM sys.v_$process p
JOIN sys.v_$session s ON (p.addr = s.paddr)
LEFT OUTER JOIN
sys.v_$session_wait w ON (w.sid = s.sid)
LEFT OUTER JOIN
sys.v_$sqltext t
ON (
t.address = s.sql_address
AND t.hash_value = s.sql_hash_value
)
WHERE p.background IS NULL
AND s.audsid != userenv('SESSIONID')
AND p.spid = '&response'
AND (
p.username = 'oracle'
OR p.username = s.osuser
)
ORDER BY 1,2,3
/
Buradaki sorgu ise bekleyen ve bekleten kullanıları ve bekleme tiplerini listelemektedir.
SELECT waitsess.osuser || '(' || waiter.sid || ')' waiter,
waiter.type,
case waiter.request
when 0 then null
when 1 then null
when 2 then 'row-S'
when 3 then 'row-X'
when 4 then 'share'
when 5 then 'sRowX'
when 6 then 'excl'
end req,
holdsess.osuser || '(' || holder.sid || ')' holder,
case holder.lmode
when 0 then null
when 1 then null
when 2 then 'row-S'
when 3 then 'row-X'
when 4 then 'share'
when 5 then 'sRowX'
when 6 then 'excl'
end held,
nvl(obj.object_name, waiter.id1 || ',' || waiter.id2) object
FROM sys.v_$lock holder
JOIN sys.v_$lock waiter
ON (
holder.id1 = waiter.id1
AND holder.id2 = waiter.id2
AND holder.type = waiter.type
)
JOIN sys.v_$session holdsess
ON (holdsess.sid = holder.sid)
JOIN sys.v_$session waitsess
ON (waitsess.sid = waiter.sid)
LEFT OUTER JOIN sys.v_$lock tmhold
ON (tmhold.type = 'TM' and tmhold.sid = holder.sid)
LEFT OUTER JOIN sys.v_$lock tmwait
ON (tmwait.type = 'TM'
AND tmwait.sid = waiter.sid)
LEFT OUTER JOIN dba_objects obj
ON (obj.object_id = tmwait.id1)
WHERE holder.request = 0
AND holder.block = 1
AND waiter.request > 0
AND nvl(tmhold.id1,0) = nvl(tmwait.id1, 0)
/
