CPU üzerinde “çözümleme zamanı” sorunu

Bir komutun çözümlemesi, bir PARSE çağrısının parçası olarak gerçekleştirilmesi için zorunlu olarak gerekmemekte, ancak başlangıç çözümlemesinden itibaren imlecin geçersiz olduğu durumlarda EXEC çağrısı vasıyasıyla da tetiklenebilmektedir. Bu demektirki, izleme dosyasında pek çok PARSE çağrısı görülmemesine rağmen “çözümleme” ile ilişkili performans sorunlarınız olabilmektedir. TKPROF aracı üzerinden görülebilen bununla ilgili istatistikler, çözümleme esnasında library cache içindeki kayıplar(misses) olarak ve çalıştırma esnasında ise library cache içindeki kayıplar(misses) olarak görülmektedir.  

Burada bunların görülme sayısı, komutun optimizasyon aşamasıda dahil ne kadar sayıda “full parse-tam çözümleme” ihtiyacını duyduğunu temsil ederken, halbuki TKPROF çıktısındaki çözümleme istatistiklerinde görülebilen “hafif çözümleme-soft parse” çağrıları daha düşük kaynak tüketim hassasiyetindedir.

İdeal olarak, bir uygulama imleci bir sefer çözümleyerek hem “hafif”,  hemde “sert” çözümleme çağrılarının üstesinden gelebilir ve aynı imlecin daha sonraki çalıştırma işlemleri için gerekli olan en uzun sürede bu imleci açık olarak tutmaktadır.  İmlecin geçersiz(invalidated) olmasının önlemi alındığında, imleç bir sefer çözümlenecek ancak pek çok kez çalıştırılabilecektir.

Eğer başlıca çözümle zamanı CPU üzerinde gerçekleşiyor ve/veya izleme dosyası analizi neticesinde geniş miktarda sürekli getirmeler(consistent gets) çözümleme adımında gerçekleşiyorsa, aşağıdaki sebeplerden birisine göz atılmasında fayda vardır; 

·         SQL komutu pek çok kez çözümlenmektedir. Gerek “hafif” gerekse “sert” olan bir komutun aşırı çözümlemesi CPU kullanımını artıracaktır ve potensiyel olarak paylaşımlı havuz içinde çekişmeye yol açacaktır. Aşağıdaki örnekteki TKPROF çıktısı komutun her bir çalıştırma esnasında çözümlenme gerektirdiğini göstermesine rağmen (çalıştırma esnasında library cache kayıpları: 2527 - Misses in library cache during execute), PL/SQL uygulaması komutu sadece bir sefer çözümlemeye çalışmış ve daha sonraki çalıştırmalar için imleci açık bırakmıştır (PARSE sayısı = 1):

SELECT *

FROM MVJDT.FINSTJ

call   count cpu    elapsed disk query current rows

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

Parse      1   0.03    0.03     0     0      0       0

Execute 2527 451.42  882.42     0     0      0       0

Fetch   2527   2.03    2.51     0  8734      0  242140

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

total   5054 453.48  884.96     0  8734      0  242140

Misses in library cache during parse: 1

Misses in library cache during execute: 2527

Optimizer mode: ALL_ROWS

Parsing user id: 46 (recursive depth: 1)

Rows Row Source Operation

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

0 TABLE ACCESS FULL FINSTJ (cr=0 pr=0 pw=0 time=5 us)

Elapsed times include waiting on following events:

Event waited on          Times Waited Max. Wait Total Waited

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

latch: row cache objects 7684         0.06        6.65

cursor: pin S            178          0.02        0.54

cursor: pin S wait on X   298         0.03        4.16

latch: shared pool        264         0.02        0.21

latch: library cache      517         0.12        2.59

latch: library cache lock 120         0.03        0.14

latch free                 53         0.00        0.03

kksfbc child completion     4         0.06        0.21

·         Dinamik örnekleme kullanılmıştır, muhtemelen yüksek seviye ile ayarlanarak nesnenin büyük bölümü örneklenmeye çalışılmıştır. DBMS_XPLAN paketi kontrol edilerek bu anlaşılabilir. Genişletilmiş SQL izleme dosyasından, /* OPT_DYN_SAMP */ hinti içeren SQL komutları kontrol edilerek, Oracle tarafından çalıştırılan tekrarlamalı(recursive) sorguların izi sürülebilir. Eğer çözümleme zamanının çoğundan dinamik örnekleme sorumluysa, daha az veri analiz etmek için daha düşük dinamik örnekleme seviyesinin yeterli olup olmayacağı kontrol edilmeli, veya saklı taslak veya SQL profilleri gibi çalıştırma planını kilitlemede kullanılabilecek alternatiflere bakılmalıdır. Aşağıdaki TKPROF çıktısı, SQL komutunun zamanın çoğunu PARSE çağrısında harcadığını göstermektedir. Asıl çalıştırma, sadece çözümleme zamanının bir bölümüne gerek duymaktadır.

select /*+ dynamic_sampling(t 10) */count(*)

from MVJDT.FINSTJ t

where val1 = 34

call   count cpu    elapsed disk  query current rows

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

Parse      1    0.62    1.84 10005 11340       0      0

Execute    1    0.00    0.00     0     0       0      0

Fetch      2    0.00    0.00     0     5       0      1

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

total      4    0.62    1.84 10005 11345       0      1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 46

Rows Row Source Operation

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

1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=264 us)

200 INDEX RANGE SCAN FINSTJ04_IDX (cr=3 pr=0 pw=0 time=823 us)

(object id 64158)

Elapsed times include waiting on following events:

Event waited on          Times Waited Max. Wait Total Waited

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

db file sequential read             2        0.00         0.00

SQL*Net message to client           2        0.00         0.00

SQL*Net message from client         2        0.04         0.04

·         Sorgu pek çok IN liste parametresiveya OR takısı kullanmaktadır. Bazı Oracle sürümlerinde bu parametreler aşırı çözümleme zamanına sebebiyet verebilmektedir. Sorgu optimizerın birleştirme(concatenation) yeniden yazmalarının önüne geçmek için NO_EXPAND hinti kullanılabilmektedir. Daha önceki Oracle sürümlerinde bu hint yerine IN-LIST ITERATOR operasyonu ile birleştirme yeniden yazmalarının önüne geçilmekteydi.  Bu operasyon, çalıştırma açısından hala verimsiz olabilmesine karşılık belirgin şekilde düşük çözümleme zamanına ihtiyaç duymaktadır.  Bu konu hakkında Metalink Doc ID 744664.1—“High Parse Time and Memory Usage for Query with Complex OR Predicates or IN-Lists After Upgrade to 10.2 or 11.1” bu performans sorunun çözümü  hakkında etkin bir bug un çözümünü işaret etmektedir.

·         Sorguda kullanılan tablolardan en azından birisi çok büyük sayıda partition kullanmaktadır (örneğin;1,000/10,000 partitionlardan daha büyüğü). Genel olarak, bu çözümleme süresini arttıracaktır çünkü optimizer pek çok partitionun partition seviyesinde istatistiğini işleyecektir.  Tabi, bunun yanında bu durumlarda ilk olarak bununla ilgili bug olup olmadığını anlamak için MOS’a başvurulmalı ve kritik yamaların sisteme yüklü olunduğundan emin olunması gerekmektedir.