Oracle 10g için PGA ve Buffer Cache bellek dağıtımlarında tavsiyecilerin kullanımı

IO bekleme olayları sıklıkla bizi doğru bellek iyileştirme yönüne yönlendirmektedir. Örneğin tüm bekleme olayları tampona alınmış IO için ise, PGA yı artırmak muhtemelen yardımcı olmayacaktır. Ancak, hem geçici segment beklemeleri hemde tampona alınmış IO beklemeleri önemli  ise, en iyi deneme bile belirgin değişme yapmayabilir.

V$PGA_TARGET_ADVICE ve V$DB_CACHE_ADVICE görünümleri  artan veya azalan bellek veya PGA boyutunun muhtemel etkisini belirlemek için tavsiyeler sunmaya yardımcı olmaktadır.  Bu görünümleri kullanarak PGA ve SGA arasında nasıl en iyi bellek tahsisini belirleyebileceğimizi görebiliriz.  

Oracle 10g’de, bu prosedür oldukça komplike hale getirilmiştir, çünkü PGA tavsiyecisi geçen süre ile ilgili tahminler içermemektedir.  Oracle 10g de ideal prosedür aşağıdaki gibidir:

1.       Geçici segment direkt IO işlemleri için ortalama süre ve blok sayısı belirlenmektedir.

2.       Bu ortalamalar kullanılarak V$PGA_TARGET_ADVICE  içindeki byte miktarı, geçen süreye dönüştürülür.

3.       V$DB_CACHE_ADVICE  içindeki bu PGA tamamlanma  süreleri, tampon önbellek tavsiyeci tamamlanma süresi ile birleştirilerek, hangi hedef birleşmelerinin tüm tamamlanma süreleri içinde azami artış gösterdiğinin belirlenmesi sağlanır.  

Oracle 11g için yukardaki üç adımın ilk ikisi gereksizdir, çünkü tamamlanma süresi beklentileri V$PGA_TARGET_ADVICE  görünümüne dahil edilmiştir.

Oracle 10g için PGA tavsiyecisi tamamlanma süresinin hesaplanması

Aşağıdaki perl kodu SQL izleme dosyası içindeki geçici segment IO işlemlerini analiz etmeye yaramaktadır. Varsayılan olarak 8kb blok boyutu seçilidir:

$blocksize=8192;

while (<>) {

if ($_=~/WAIT #(.*) nam='direct

path(.*)temp'(.*)ela=(.*)file(.*)cnt=(.*)obj#=(.*)/)

{

$count++;

$ela+=$4;

$blocks+=$6;

}

}

printf("%-20s %10d\n","Total temp IO waits",$count);

printf("%-20s %10d\n","Elapsed Microseconds",$ela);

printf("%-20s %10d\n","Total blocks",$blocks);

printf("%-20s %10d\n","Average blocks",$blocks/$count);

printf("%-20s %10d\n","Microseconds/block",$ela/$blocks);

printf("%-20s %10.4f\n","Microseconds/byteRW",$ela/$blocks/$blocksize);

print "\nNB: assuming blocksize of $blocksize\n";

>perl trc_tempio_stat.pl <orcl_ora_3210.trc

Total temp IO waits 7593

Elapsed Microseconds 115996220

Total blocks 111979

Average blocks 14

Microseconds/block 1035

Microseconds/byteRW 0.1279

NB: assuming blocksize of 8192

Oracle 10g de yukardaki perl komutu çıktısındaki “Microseconds/byteRW” değeri  V$PGA_TARGET_ADVICE  görünümü içerisinde sonraki hesaplamalarda kullanılacaktır.  Bu değer, geçici dosya IO larının her bir byte değeri için ortalama süre maliyetini temsil etmektedir. Eğer bu değeri  V$PGA_TARGET_ADVICE görünümü içine eklersek, PGA hedefinde çeşitli değişiklikler için ilişkisel değişiklikleri IO süresi olarak tahmin edebiliriz.

SQL> SELECT current_size / 1048576 “Current MB”,

pga_target_for_estimate / 1048576 “PGA Target MB”,

(estd_extra_bytes_rw - current_extra_bytes_rw)

* 0.1279 / 1000000 AS "Estimated Time Delta(s)",

estd_extra_bytes_rw / 1048576 "Estimated extra MB"

FROM v$pga_target_advice,

(SELECT pga_target_for_estimate current_size,

estd_extra_bytes_rw current_extra_bytes_rw

FROM v$pga_target_advice

WHERE pga_target_factor = 1);

Current   PGA Target  Estimated         Estimated

MB        MB          time delta (s)    extra MB

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

500       63             75,206.67       867,971

500       125             8,230.72       368,571

500       250               972.50       314,451

500       375               721.38       312,578

500       500                  .00       307,199

500       600                -1.10       307,191

500       700                -1.10       307,191

500       800                -1.10       307,191

IO lar için zaman maliyetini V$DB_CACHE_ADVICE içinde hesaplamak oldukça basittir, çünkü  V$DB_CACHE_ADVICE  görünümü zaten zaman tahminlerini sağlamaktadır;

SQL> SELECT current_size, size_for_estimate,

(estd_physical_read_time - current_time)

estd_io_seconds_delta,

estd_physical_reads - current_reads

physical_reads_delta

FROM v$db_cache_advice,

(SELECT size_for_estimate current_size,

estd_physical_read_time current_time,

estd_physical_reads current_reads

FROM v$db_cache_advice

WHERE size_factor = 1 AND name = 'DEFAULT'

AND block_size = 8192)

WHERE name = 'DEFAULT' AND block_size = 8192;

Current   Estimate        Est IO          Phys Reads

MB        MB              Time Delta(s)   Delta

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

404        40             1,202,966       76,149,733

404        80               999,489       63,269,307

… … …

404       280               228,083       14,438,021

404       320               127,328        8,060,085

404       360                50,139        3,173,861

404       400                 3,318          210,066

404       404                     0                0

404       440               -29,661       -1,877,597

404       480               -36,936       -2,338,118

404       520               -38,058       -2,409,108

… … …

404       760               -41,040       -2,597,909

404       800               -41,043       -2,598,103

Oracle 10g de PGA ve önbellek tavsiyelerini birleştirmek

Eğer her iki tavsiyecide tek SQL cümlesi içinde birleştirilmek istenirse, önbellek ve PGA bellek birleşimleriniden hangisinin şu an kullanılan birleşimden daha verimli olduğunun görülmesi sağlanmalıdır. Aşağıdaki sorguda PGA ve önbellek kombinasyon çeşitlemeleri ile muhtemel bellek dağıtım performans gelişmeleri  arasındaki delta değişimler görülmektedir.

SQL> WITH db_cache_times AS

(SELECT current_size current_cache_mb,

size_for_estimate target_cache_mb,

(estd_physical_read_time - current_time)

cache_secs_delta

FROM v$db_cache_advice,

(SELECT size_for_estimate current_size,

estd_physical_read_time current_time

FROM v$db_cache_advice

WHERE size_factor = 1

AND name = 'DEFAULT' AND block_size = 8192)

WHERE name = 'DEFAULT' AND block_size = 8192),

pga_times AS

(SELECT current_size / 1048576 current_pga_mb,

pga_target_for_estimate / 1048576 target_pga_mb,

ROUND((estd_extra_bytes_rw - current_extra_bytes_rw)

* 0.1279 / 1000000,2) pga_secs_delta

FROM v$pga_target_advice,

(SELECT pga_target_for_estimate current_size,

estd_extra_bytes_rw current_extra_bytes_rw

FROM v$pga_target_advice

WHERE pga_target_factor = 1))

SELECT current_cache_mb||'MB->'||target_cache_mb||'MB'

Buffer_cache,

current_pga_mb||'->'||target_pga_mb||'MB' PGA,

pga_secs_delta,cache_secs_delta,

(pga_secs_delta+cache_secs_delta) total_secs_delta

FROM db_cache_times d,pga_times p

WHERE (target_pga_mb+target_cache_mb)

<=(current_pga_mb+current_cache_mb)

AND (pga_secs_delta+cache_secs_delta) <0

ORDER BY (pga_secs_delta+cache_secs_delta);

Buffer Cache PGA          Pga Time Cache Time  Total Time

Change      Change        Delta (s) Delta (s)  Delta (s)

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

444MB->880MB 702->175.5MB  3,565     -40,303     -36,738

444MB->880MB 702->87.75MB  3,565     -40,303     -36,738

444MB->836MB 702->175.5MB  3,565     -36,066     -32,501

444MB->836MB 702->87.75MB  3,565     -36,066     -32,501

444MB->792MB 702->175.5MB  3,565     -31,828     -28,263

444MB->792MB 702->87.75MB  3,565     -31,828     -28,263

444MB->792MB 702->351MB    3,565     -31,828     -28,263

Yukarıdaki sorgu genellikle bu amaçla kullanılan tekli sorgulardan daha uzun gibi gözüksede, aslında kullanışlılık açısından oldukça basit ve yararlıdır, şöyleki:

·         Kırmızı kısım ile tanımlı kod satırında çeşitli önbellek boyutları için tamamlanma sürelerindeki değişiklikleri V$DB_CACHE_ADVICE  görünümünü tanımlamaktadır.

·         Mavi kısım ile tanımlanan kod satırı V$PGA_TARGET_ADVICE ile aynı işleve sahiptir: “PGA Aggregate Target” içindeki değişiklikler için tamamlanma sürelerindeki etkiyi raporlayan bir in-line görünümdür(daha önceden hesapladığımız beher  0.1279 byte değerini kullanmaktadır).

·         Siyah kısım ile tanımlı kod satırında her iki görünümün kartesyen birleşmesi gerçekleşmektedir. V$DB_CACHE_ADVICE içindeki her bir satır, V$PGA_TARGET _ADVICE görünümünün her biri satırı ile birleşmektedir. Ancak,  tamamlanma süresi veya toplam tahsis edilen bellek miktarı, mevcut bellek dağılımdan daha büyük olan satırlar bu sorguya dahil edilmez (AND (pga_secs_delta+cache_secs_delta) <0).

Bu analiz ile eğer PGA dan buffer cache kısmına bellek kaydırması olursa, IO zamanında genel bir azalma  meydana gelecektir. “PGA Aggregate Target”  miktarında 702 Mb dan 175MB a olan bir azalma IO zamanında 3.565 saniyelik bir artış meydana getirsede,  buffer cache içinde 444 Mb den 880 MB a yükselen bellek takviyesi sonucunda IO süresinde 40,303 saniyelik bir azalma meydana gelecektir, sonuçta bu önemli bir genel iyileştirmedir.