Kimi zaman database’ deki performans problemlerini tespit etmek için aşağıdaki ratio oranlarını kontrol ederiz. Bu oranlar bize temelde database’ in doğru konfigure edilip edilmediğini veya performans problemi olduğu aralıkda çalışan bazı transactionların bir nedenden dolayı farklı davranış gösterdiğini gösterip göstermediğini tespit etmemize yarayan oranlardır.
Öncelikle bu oranların neler olduğundan bahsedelim sonrasında bu oranları nasıl sorgulayacağımızdan ve sonrasında neler yapabileceğimizden konuşacağız.
• Database Buffer Cache Hit Ratio : En çok bilinen orandır. Database’ den yapılan okumaların ne kadarının tablo veya index’ den değilde cache’ den yapıldığını gösteren orandır. Dönen sonuç 1’ e ne kadar yakınsa cache kullanımı o kadar yoğun demektir.
• Table Acces Ratio : Database’ de küçük tablolar üzerinde okuma yaparken full table scan yöntemi çoğu zaman performance artırıcı bir etkiye sahiptir. Ancak bu durum büyük tablolarda gerçekleştiğinde database performance’ ında çok ciddi düşüşlere yol açabilmektedir. Okumalardaki en hızlı yöntemin küçük tablolar üzerinde Rowid kullanılarak yapılan yöntem olduğunu söyleyebiliriz. Bu oran database’ de yapılan full table scan’ lerin dağılımını vermektedir.
• Index Use Ratio : Tabloya yapılan erişimlerin ne kadarının index üzerinden yapıldığını gösteren ratio’ dur.
• Library Cache Hit Ratio : Shared pool içerisindeki, library cache parsing aktivitelerinin oranını gösteren ratio’ dur.
• Disk Sort Ratio : Disk üzerinde yapılan sort işlemlerinin ne kadar kullanıldığını gösteren ratio’ dur.
• Chained rows ratio : Tablo içerisine insert edilen datalar tek bir bloğun içerisine sığmadığı durumlarda bir diğer bloğa taşarak yazma işlemi devam etmektedir. Bu tarz durumlarda bu datalar üzerinde yapılan işlemlerde yavaşlıklar olabilmektedir. Bu oran bu durumu göstermektedir.
• Parse Ratio : Database içerisinde yapılan parsing işlemlerinin dağılımını gösteren ratio’ dur.
• Latch Hit Ratio : Memory’ de yaşanan kilitlenmeleri gösteren ratio’ dur.
Tanımlamaya çalıştığımız bu oranların sırayla üzerinden geçmeye çalışalım.
Database Buffer Cache Hit Ratio
Database buffer cache hit ratio oranı ne kadar yüksekse buffer cache içerisinde datanın cachelenme oranıda o kadar yüksek demektir. Buffer cache oranı aşağıdaki gibi hesaplanmaktadır;
DATABASE buffer CACHE hit ratio = 1 – (PHYSICAL READS / (db BLOCK gets + CONSISTENT gets))
Aşağıdaki sql yardımıyla değerini sorgulayabiliriz;
SELECT ‘Database Buffer Cache Hit Ratio ‘ “Ratio”,
ROUND ( ( 1 – ( (SELECT SUM (VALUE) FROM V$SYSSTAT WHERE name = ‘physical reads’) /
( (SELECT SUM (VALUE) FROM V$SYSSTAT WHERE name = ‘db block gets’) + (SELECT SUM (VALUE)
FROM V$SYSSTAT
WHERE name = ‘consistent gets’)))) * 100) || ‘%’ “Percentage”
FROM DUAL;
Örneğin kendi test ortamımda bu sorguyu çalıştırdığımda bu değer %92 dönüyor. Yani üzerinde çalışılan datanın yaklaşık %92’ I memoryden çekilebiliyor demektir. Ancak burada beklenen, bu değerin olabildiğince yüksek olmasıdır. Genelde %99 civarı çok iyi olarak yorumlanmaktadır.
Ratio Percentage
——————————– —————————————–
Database Buffer Cache Hit Ratio 92%
1 row selected.
Database buffer cache hit ratio oranının düşük çıkması demek fiziksel disk okumalarının fazla olduğunun yani database’ de bir I/O yoğunluğunun olduğuna işaret olacaktır.
Buffer cache’ in yapısına baktığımız da 3 alt bölümden oluştuğunu söyleyebiliriz. Bunlar default, keep ve recyclebin dir. Toplam db_cache_size değerinin, %5 olacak şekilde db_keep_cache_size, %25’ i olacak şekildede db_recycle_cache_size değeri set edilebilir. Bu işlemi aşağıdaki şekilde yapabilirsiniz;
ALTER SYSTEM SET db_keep_cache_size = 1G;
ALTER SYSTEM SET db_recycle_cache_size = 5G;
Bir tabloyu buffer cache içerisinde bir havuzda saklamak isterseniz eğer;
ALTER TABLE owner.table_name STORAGE(BUFFER_POOL KEEP);
ALTER TABLE owner.index_name STORAGE(BUFFER_POOL KEEP);
ALTER TABLE owner.table_name STORAGE(BUFFER_POOL DEFAULT);
ALTER TABLE owner.index_name STORAGE(BUFFER_POOL DEFAULT);
ALTER TABLE owner.table_name STORAGE(BUFFER_POOL RECYCLE);
ALTER TABLE owner.index_name STORAGE(BUFFER_POOL RECYCLE);
Komutlarından faydalanabilirsiniz. Normal şartlar altında database’ de select edilen tabloları oracle cacheleyerek bir sonraki kullanımda burdan kullandırmaya çalışmaktadır. Ancak doğal olarak burası da sınırlı bir kaynak olduğundan daha sık kullanılan bir tablo geldiğinde ve buradada yer kalmadığında daha az kullanılanlardan başlayarak üzerine yazmak suretiyle buradaki alanı kullanmaya devam edecektir. Eğer siz her ne durumda olursa olsun belirli bazı tablolarınızın sürekli olarak buffer cache de kalmasını istiyorsanız eğer o zaman buffer cache alanındaki keep alanını kullanacaksınız demektir. Öyle bir tablonuz varki çok nadir olarak kullanılıyor olsun dolayısıyla zaten ayda yılda bir kullanılıyor o zamanda cache’ den değil zaten diskden okuma yapılacağı için ayrıca birde cache tutulmasına gerek yok diyorsanız o zamanda buffer cache havuzunun Recyle havuzundan faydalanabilirsiniz. Default havuzuda adında anlaşılacağı gibi oracle tarafından manage edilmesini istediklerinizde burada yer alabilir.
Buffer cache’ inizi keep ve recyclebin olarak alanlarınızı siz yönetmek için set ettikden sonra zaman zaman bu alanlarınızdaki ratio oranlarınzı kontrol etmenizde fayda olacaktır. Buffer pool istatisitkleri size bu alanları set ettikden sonraki kullanım oranlarını verdiğinden dolayı eksik veya fazla vermiş olduğunuz size’ lar olabileceğinden bunları artırıp azaltabilmeniz açısından size fikir verecektir. Böylelikle elinizde kısıtlı bir şekilde bulunan memory’ nizi etkin bir şekilde kullanmış olursunuz.
SELECT a.name “Pool”, a.physical_reads, a.db_block_gets
, a.consistent_gets
,(SELECT ROUND((1-(physical_reads / (db_block_gets + consistent_gets)))*100)
FROM v$buffer_pool_statistics
WHERE db_block_gets+consistent_gets != 0
AND name = a.name) “Ratio”
FROM v$buffer_pool_statistics a;
Pool PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS Ratio
——————– ————– ————- ————— ———-
KEEP 2270 0 5935 62
DEFAULT 82282 1912603 6133701 99
RECYCLE 0 0 30 100
3 rows selected.
Yukarıdaki sorgu sonucuna bakıldığında keep pool’ un size’ nın yetersiz kladığını ve buraya kaynak aktarılmasının faydalı olacağını söyleyebiliriz.
Genellikle küçük tablolar kullanılacağı zaman optimizer, full table scan yapacak şekilde karar verir. Bu yüzden sık kullanılan (örneğin tanım tablolarının) küçük tabloların keep alanında store edilmesi faydalı olacaktır.
Buffer cache alanındaki bu bahsetmiş olduğumuz 3 alt havuzun içerisinde neler olduğunu aşağıdaki sorgu yardımıyla bulabilirsiniz. (Bir süre hangi tablo veya index buffer pool içerisinde nerede tutuluyor veya tutuluyormu diye düşünebilirsiniz)
SELECT table_name AS “Table”, NULL, buffer_pool, cache
FROM dba_tables
WHERE buffer_pool != ‘DEFAULT’ OR TRIM(cache)=’Y’
UNION
SELECT table_name, index_name, NULL, buffer_pool
FROM dba_indexes
WHERE buffer_pool != ‘DEFAULT’
ORDER BY 1, 2 NULLS FIRST;
Table NULL BUFFER_POOL CACHE
D1 RECYCLE Y
D2 KEEP Y
E1 KEEP Y
Q1 KEEP Y
R1 KEEP N
U1 KEEP N
X1 KEEP Y
Y1 KEEP N
Table Acces Ratio :
Database içerisindeki küçük tablolar üzerinde çoğunlukla index üzerinden gitmek yerine full table scan yapılması daha performanslı olabilmektedir. Ancak bu durum büyük tablolarda tam tersine işlemektedir. Aşağıdaki sorgu ile database içerisinde yapılan full table scan’ lerin dağılımı görebilirsiniz ;
SELECT value, name FROM V$SYSSTAT WHERE name IN
(‘table fetch by rowid’, ‘table scans (short tables)’
, ‘table scans (long tables)’);
VALUE NAME
———- —————————————————————-
26729 table scans (short tables)
8 table scans (long tables)
382919 table fetch by rowid
3 rows selected.
Yapılan bu full table scan’lerin oransal dağılımını görmek içinse;
SELECT ‘Short to Long Full Table Scans’ “Ratio”, ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = ‘table scans (short tables)’) / (SELECT SUM(value)
FROM V$SYSSTAT WHERE name IN (‘table scans (short tables)’, ‘table scans (longtables)’)) * 100, 2)||’%’ “Percentage” FROM DUAL
UNION
SELECT ‘Short Table Scans ‘ “Ratio” , ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = ‘table scans (short tables)’)/ (SELECT SUM(value)
FROM V$SYSSTAT WHERE name IN (‘table scans (short tables)’, ‘table scans (longtables)’ , ‘table fetch by rowid’))* 100, 2)||’%’ “Percentage” FROM DUAL
UNION
SELECT ‘Long Table Scans ‘ “Ratio” , ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = ‘table scans (long tables)’) / (SELECT SUM(value)
FROM V$SYSSTAT WHERE name IN (‘table scans (short tables)’, ‘table scans (longtables)’ , ‘table fetch by rowid’))* 100, 2)||’%’ “Percentage” FROM DUAL
UNION
SELECT ‘Table by Index ‘ “Ratio” , ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = ‘table fetch by rowid’)/ (SELECT SUM(value)
FROM V$SYSSTAT WHERE name IN (‘table scans (short tables)’, ‘table scans (longtables)’, ‘table fetch by rowid’))* 100, 2)||’%’ “Percentage” FROM DUAL
UNION
SELECT ‘Efficient Table Access ‘ “Ratio” , ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name IN (‘table scans (short tables)’,’table fetch by rowid’)) / (SELECT SUM(value)
FROM V$SYSSTAT WHERE name IN (‘table scans (short tables)’, ‘table scans (longtables)’ , ‘table fetch by rowid’))* 100, 2)||’%’ “Percentage”
FROM DUAL;
Ratio Percentage
—————————— —————————————–
Efficient Table Access 100%
Long Table Scans 0%
Short Table Scans 6.3%
Short to Long Full Table Scans 100%
Table by Index 93.7%
5 rows selected.
Yukarıdaki sql’ den faydalanabilirsiniz.
Index Use Ratio
Tablolara yapılan erişimlerin ne kadarı indexler üzerinden sağlanarak yapılıp yapılmadığını gösteren faydalı bir ratio’ dur. Kesin olmamakla beraber aşağıdaki sorgu size bir fikir verecektir ;
SELECT value, name FROM V$SYSSTAT
WHERE name IN(‘table fetch by rowid’, ‘table scans (short tables)’, ‘table scans (long tables)’)
OR name LIKE ‘index fast full%’
OR name = ‘index fetch by key’;
VALUE NAME
———- —————————————————————-
28582 table scans (short tables)
29 table scans (long tables)
421055 table fetch by rowid
1071 index fast full scans (full)
0 index fast full scans (rowid ranges)
0 index fast full scans (direct read)
598386 index fetch by key
7 rows selected.
Index kullanım oranını ise aşağıdaki sorgu ile bulabilirsiniz ;
SELECT ‘Index to Table Ratio ‘ “Ratio” , ROUND(
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name LIKE ‘index fast full%’
OR name = ‘index fetch by key’
OR name = ‘table fetch by rowid’)
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
(‘table scans (short tables)’, ‘table scans (long tables)’)
),0)||’:1′ “Result”
FROM DUAL;
Ratio Result
——————— ——————————————
Index to Table Ratio 36:1
1 row selected.
Çıkan bu sonucun iyi veya kötü yorumlanması aslında biraz kullanılan uygulamaya bağlıdır. Ancak tek başına bu oran çoğu zaman yanıltıcı olabilmektedir. Dolayısıyla performans problemlerinin tespitinde bu ratio oranlarını destekleyen bir takım bulgular varsa onlarla destekliyor olmanızdır. (örneğin ciddi i/o’ ya sebeb olan full table scan yapan sorgular gibi)
Dictionary Cache Hit Ratio
Bu oran database’ in metadata sına ulaşmak için yapılan erişimlerin ne kadarı cache’ den ne kadarı fiziksel okuma üzerinden yapılıp yapılmadığını gösterir.
SELECT ‘Dictionary Cache Hit Ratio ‘ “Ratio” ,ROUND((1 – (SUM(GETMISSES) / SUM(GETS))) * 100,2)||’%’ “Percentage”
FROM V$ROWCACHE;
Ratio Percentage
————————— —————————————–
Dictionary Cache Hit Ratio 97.43%
1 row selected.
Bu değerin 1’e olabildiğince yakın olması beklenen ve önerilen orandır.
Library Cache Hit Ratio
Bu oran library cache içerisinde yapılan preparsing ve reloading activitelerinin oranını verir ;
SELECT ‘Library Lock Requests’ “Ratio” , ROUND(AVG(gethitratio) * 100, 2) ||’%’ “Percentage”
FROM V$LIBRARYCACHE
UNION
SELECT ‘Library Pin Requests’ “Ratio”, ROUND(AVG(pinhitratio) * 100, 2) ||’%’ “Percentage”
FROM V$LIBRARYCACHE
UNION
SELECT ‘Library I/O Reloads’ “Ratio”, ROUND((SUM(reloads) / SUM(pins)) * 100, 2)||’%’ “Percentage”
FROM V$LIBRARYCACHE
UNION
SELECT ‘Library Reparses’ “Ratio”, ROUND((SUM(reloads) / SUM(pins)) * 100, 2)||’%’ “Percentage”
FROM V$LIBRARYCACHE;
Ratio Percentage
——————— —————————————–
Library I/O Reloads .55%
Library Lock Requests 69.53%
Library Pin Requests 75.77%
Library Reparses .55%
4 rows selected.
Disk Sort Ratio
Disk üzerinde sort işlemlerinin oranını belirten reatio’ dur. Disk üzerinde yapılan sort işlemlerini azaltmak için SORT_AREA_SIZE parametresi size’ i artırılabilir. Bu parametrenin default değeri db_block_size*8 dir.
Yani db_block_size’ ınız 8 kb ise sort_area_size değeriniz defaultta 65536 (8*8192) olacaktır. Burada önemli bir nokta, bu parametreyi kullanabilmeniz için workarea_size_policy parametresinin MANUAL olması gerekmektedir. Bu parametrenin AUTO olması durumunda bu parametre kullanılmayacaktır. Set etmek isterseniz;
alter session set workarea_size_policy=manual;
alter session set sort_area_size=5000;
komutlarını kullanabilirsiniz.
Database’ inizdeki disk
SELECT ‘Sorts in Memory ‘ “Ratio”, ROUND ( (SELECT SUM (VALUE)
FROM V$SYSSTAT
WHERE name = ‘sorts (memory)’) / (SELECT SUM (VALUE)
FROM V$SYSSTAT
WHERE name IN (‘sorts (memory)’, ‘sorts (disk)’)) * 100,2) || ‘%’ “Percentage”
FROM DUAL;
Ratio Percentage
—————- —————————————–
Sorts in Memory 96.58%
1 row selected.
Memory’ den yapılan sort işlemlerinin diskden yapılan sort işlemlerine oranının %100’ den daha az çıkmasıda bir problem olduğu anlamına gelmez.
Chained Rows Ratio
Chaining’ den bahsetmeden önce bu konu ile ilgili olan row migration’ dan da bashetmek istiyorum.
Öncesinde aşağıdaki semayı oracle’ ın yapısını anlamak adına sizinle paylaşmak istedim.
Şekil1 : Oracle Database Yapısı
Hazır yeri gelmişken burada row migration ve row chaining’ in ne olduğundan ve aralarındaki farkdan da kısaca bahsedelim. Row migration, data üzerinde yapılan bir update işlemi sonrasında oluşan bir durumdur, yapılan update sonrasında ilgili satırın diğer bloğa taşma durumuna row migration denilmektedir.
Şekil2 : Row migration
Row chaining ise, daha çok insert aşamasında oluşan bir durumdur. Insert edilmeye çalışılan row’ un eğer çok büyükse ve içinde bulunduğu bloğa sığamıyorsa takip eden bir diğer bloğa taşması sonucunda oluşan durumdur.
Şekil3 : Row chaining
Konuyu daha iyi anlamak adına row migration ve row chaining’ in ne olduğundan çok özetle bahsetmiş olduk. Bir sonraki yazımda row chaining ve row migration’ ları nasıl önleyebiliriz varsa database içerisindeki row chaining’ leri ve migrationları nasıl elimine edebiliriz bunlardan bahsediyor olacağım.
Database içerisindeki row chaining oranına bakmak için aşağıdaki scriptten faydalanabilirsiniz ;
SELECT ‘Chained Rows ‘ “Ratio” , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = ‘table fetch continued row’) / (SELECT SUM(value)
FROM V$SYSSTAT
WHERE name IN (‘table scan rows gotten’, ‘table fetch by rowid’)) * 100, 3)||’%’ “Percentage”
FROM DUAL;
Ratio Percentage
————- —————————————–
Chained Rows .025%
1 row selected.
Parse Ratios
Database ‘de tüm sql sorguları parse edilmektedir. Çalışan tüm sorgular ve execution planlarına ait tüm bilgilerde shared pool içerisinde saklanmaktadır. Sql’ in çalışması sırasındaki en maliyetli bölüm parsing işlemlerinin yapıldığı kısımdır. Pars işlemleri 2 çeşittir. Hard parsing ve soft parsing, soft parse işlemi shared pool içerisinde yapılan ve çok daha hızlı bir parsing yöntemidir. Hard parsing ise sorguya ait tüm aşamaların en baştan hesaplandığı ve response süresini ciddi oranda artıran bir parsing yöntemidir. Sorgularda ne kadar hard parsing yapılmazsa is o kadar performanslı olacaktır.
Parsing işlemi shared pool içerisinde yapıldığı için zaman zaman shared pool size’ ını artırıyor olmak çözüm olabilmektedir. Database’ deki parsing oranlarını aşağıdaki sorgu yardımıyla bulabiliriz ;
SELECT ‘Soft Parses ‘ “Ratio” , ROUND(
((SELECT SUM(value) FROM V$SYSSTAT WHERE name = ‘parse count
(total)’)
– (SELECT SUM(value) FROM V$SYSSTAT WHERE name = ‘parse count
(hard)’))
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = ‘execute
count’)
* 100, 2)||’%’ “Percentage”
FROM DUAL
UNION
SELECT ‘Hard Parses ‘ “Ratio”
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT WHERE name = ‘parse count
(hard)’)
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = ‘execute
count’) * 100, 2)||’%’ “Percentage”
FROM DUAL
UNION
SELECT ‘Parse Failures ‘ “Ratio”
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT
WHERE name = ‘parse count (failures)’)
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = ‘parse count
(total)’)
* 100, 2)||’%’ “Percentage”
FROM DUAL;
Ratio Percentage
————— —————————————–
Hard Parses .01%
Parse Failures 0%
Soft Parses 56.52%
3 rows selected.
Latch Hit Ratio
Latche’ ler, memory bufferlarını eşzamanlı kullanımlara karşı koruyan mekanizmalardır. Latch ratio oranı %99 civarında olması database’ deki durumun sağlıklı olduğunu gösterir.
SELECT ‘Latch Hit Ratio ‘ “Ratio” , ROUND((SELECT SUM(gets) – SUM(misses) FROM V$LATCH)
/ (SELECT SUM(gets) FROM V$LATCH) * 100, 2)||’%’ “Percentage”
FROM DUAL;
Ratio Percentage
—————- —————————————–
Latch Hit Ratio 99.96%
1 row selected.
Database içerisinde olası performans problemlerinde database’ de mutlaka bakılması gereken ratio’ lardan bahsettik. Performans problemlerinde bunların dışında da sayısız nedenleri olabilir. Bu saymış olduklarımız ilk akla gelen ve ilk kontrol edilmesi gereken kısımlar olarak düşünebilirsiniz. Umarım faydalı olmuştur.
Referans ;
http://www.amazon.com/Oracle-Performance-Tuning-10gR2-Second/dp/1555583458
(Oracle Performance Tuning for 10gR2 – Gavin Powell )
http://www.akadia.com/services/ora_chained_rows.html
Eline sağlık Kamil
Teşekkürler Güneş