"db file sequential read" bekleme olayı üzerine detaylı bir inceleme

db file sequential read bekleme olayı, indekslere,rollback(veya undo) segmentlerine, rowid yoluyla erişilen tablolara, kontrol dosyalarına ve veri dosyası başlıklarına karşı tek-blok okuma işlemi gerçekleştiren işlemlerin SQL komutlarınca(hem kullanıcı hemde tekrarlamalı) başlatılır. Bu bekleme olayı sistem çapında beklemelere göre genellikle top 5 bekleme olayları içinde yer almaktadır.

Bu objeler için fiziksel I/O istekleri gayet normaldir, yani db file sequential read bekleme olayının veritabanındaki varlığı, illada veritabanı veya uygulama ile ilgili birşeylerin yanlış olduğu anlamına gelmez. Bir oturumun bu olay üstünde uzun zaman harcaması da her zaman kötü bir şey olmayabilir. Buna karşılık, bir oturum eğer kuyruğa ekleme(enqueue) veya “latch free” olayları için çok fazla zaman harcıyorsa, işte bu durum iyiye işaret olmaz. İşte bu iki olay tekli blok okuma konusunu çetrefilli bir duruma sokar. Peki ne zaman “db file sequential read” olayı sorun haline gelir? Aşırılık nasıl tanımlanır? Ne zaman ve nereye bir çizgi çekmelisiniz?  Aslında bu sorulara Oracle uzmanlarınında şu ana kadar kesin cevapları yoktur, hala araştırılır ve standart cevaplar bulunmamıştır hala ne yazıkki… Mesela, db file sequential read beklemesi proses yanıt süresinin büyük bir bölümünü temsil ediyorsa o zaman “aşırılık” kelimesi bir anlam kazanır. Diğer bir yol ise, bilimsel olmayan bir avam yaklaşımı takınarak, kullanıcılar bu bekleme olayından sıkıntı yaşayıp çığlık atana kadar beklemek olacaktır. Tabii bu durum profesyonel bir yaklaşım olmaz, hele hele proaktif olarak performans geliştirme iddiasında iseniz.

“db file sequential read” bekleme olayının üç parametresi vardır: file#, first block# ve block count. Bu bekleme olayı “User I/O” bekleme sınıfı altında yer almaktadır. db file sequential read bekleme olayı ile karşılaşıldığında aşağıdaki anahtar noktalara dikkat edilmesi gerekir.

• Bir Oracle prosesi, SGA içinde olmayan bloğa ihtiyaç duymakta olup bu veritabanı bloğunun diskten SGA alanına okunmasını beklemektedir.
• Bireysel oturumlarda bakılması gereken en önemli iki değer, TIME_WAITED ve AVERAGE_WAIT kolonları olacaktır.
• Dikkate değer db file sequential read bekleme olayı sıklıkla uygulama katmanından kaynaklanır. 

Aslında, V$SESSION_EVENT görünümünden “db file sequential read” bekleme olayında hangi oturumların yüksek TIME_WAITED değerine sahip olduğunu kolayca keşfedebiliriz. TIME_WAITED değeri LOGON_TIME değeri ile birlikte değerlendirilmeli ve daha kesin bir analiz için bu oturuma ait diğer boşta olmayan olaylar ile kıyaslanmalıdır.  Günler ve hatta haftalar boyunca açık olan oturumlarda db file sequential read olayı süresi dolgunca miktarda birikir.  Bu durumda, TIME_WAITED değeri sorun olmayabilir. Ayrıca, TIME_WAITED diğer boşta olmayan olaylar ile birlikte bir perspektife konduğunda, bu durum bizleri gafil avlanmaktan korur. Daha büyük önem taşıyan başka bekleme olayları bulunabilir. Aşağıdaki örneğe dayalı olarak, SID # 31 dikkati çekmeli ve diğerlerine göre daha kısa zamandan beri oturum açık olmasına rağmen yüksek bekleme süresinin nedeni incelenmelidir.

select a.sid,
       a.event,
       a.time_waited,
       a.time_waited / c.sum_time_waited * 100 pct_wait_time,
       round((sysdate - b.logon_time) * 24) hours_connected
from   v$session_event a, v$session b,
       (select sid, sum(time_waited) sum_time_waited
        from   v$session_event
        where  event not in (
                    'Null event',
                    'client message',
                    'KXFX: Execution Message Dequeue - Slave',
                    'PX Deq: Execution Msg',
                    'KXFQ: kxfqdeq - normal deqeue',
                    'PX Deq: Table Q Normal',
                    'Wait for credit - send blocked',
                    'PX Deq Credit: send blkd',
                    'Wait for credit - need buffer to send',
                    'PX Deq Credit: need buffer',
                    'Wait for credit - free buffer',
                    'PX Deq Credit: free buffer',
                    'parallel query dequeue wait',
                    'PX Deque wait',
                    'Parallel Query Idle Wait - Slaves',
                    'PX Idle Wait',
                    'slave wait',
                    'dispatcher timer',
                    'virtual circuit status',
                    'pipe get',
                    'rdbms ipc message',
                    'rdbms ipc reply',
                    'pmon timer',
                    'smon timer',
                    'PL/SQL lock timer',
                    'SQL*Net message from client',
                    'WMON goes to sleep')
        having sum(time_waited) > 0 group by sid) c
where  a.sid         = b.sid
and    a.sid         = c.sid
and    a.time_waited > 0
and    a.event       = 'db file sequential read'
order by hours_connected desc, pct_wait_time;

 SID EVENT                        TIME_WAITED PCT_WAIT_TIME HOURS_CONNECTED
---- -----------------------      ----------    -----------  ---------------
 18  db file sequential read      64446         77.0267848   105
 28  db file sequential read      1458405       90.992838    105
 19  db file sequential read      1458708       91.0204316   105
 32  db file sequential read      1462557       91.1577045   105
 13  db file sequential read      211325        53.6281055    11
 45  db file sequential read      247236        56.0469755    11
 31  db file sequential read      263113        89.0193625     2

…

db file sequential read bekleme olayını azaltmak için yapılması gereken iki şey vardır:

• Fiziksel ve mantıksal okumalarının sayısını azaltarak pek çok bekleme olayını başlatan SQL komutunu en uygun hale getirmek.
• Ortalama bekleme süresini düşürmek. 

Bir oturumu “event 10046” veya sürekli çalışan olay verisi toplama aracı olmadan kümülatif bekleme olaylarına sebebiyet veren SQL komutlarını belirlemek zordur. Eğer hale benim gibi Oracle 9i çalışan sisteminizde varsa, aslında TOAD programı içindeki “SGA Trace” ve “Session Browser” araçları ile kümülatif bekleme olaylarına sebebiyet veren SQL komutlarını belirlemekte oldukça hızlı ve kesin sonuçlar döndürecektir.

Yukardaki SID #31 kaynaklı sıkıntıya sebep veren bekleme darboğazında dönersek; 263113 senti saniye bekleme olayı muhtemelen uzun çalışan bir SQL komutundan veya çok hızlı SQL komutlarından olmaktadır. Sonraki durum sorun olmayabilir. Bununla birlikte, halihazırda çalışan SQL komutuda bu bekleme olaylarına sebebiyet verebilir, ancak sebebiyet vermeyebilirde. İşte bu sebeple, geçmiş verisi olmadan interaktif teşhis sıklıkla verimsiz olmaktadır. Yüksek “ortalama DISK_READS” değerine sahip komutlar için V$SQL görünümüne sorgu çekilebilir, ancak bu durumda bunların ilgili oturuma sahip olduğunu bulabilecekmiyiz?  Bu kısıtlamalar yüzünden  kusurlu SQL komutların etrafında odaklanmak için, bu oturumu bir dahaki sefere izlemeye almak ve kusurlu SQL komutlarını belirlemek gerekmektedir. Bu kusurlu SQL komutları bulununca, bunları en uygun hale getirmek için fiziksel ve mantıksal okumaların sayısını azaltmak hedefimiz olmalıdır.  Bunun yanında V$SQL ve V$SQL_AREA görünümlerinde yer alan USER_IO_WAIT_TIME,  DIRECT_WRITES, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, PLSQL_EXEC_TIME ve  JAVA_EXEC_TIME kolonlarıda oldukça yararlı bilgiler vermektedir, bilhassa  USER_IO_WAIT_TIME kolonu ile en yüksek kümülatif veya ortalama bekleme olayına sebep veren SQL komutları tespit edilebilir.

db file sequential read bekleme olayının etkisini azaltmak için uygulanacak diğer bir yol ise AVERAGE_WAIT süresinin düşürülmesidir. Bu süre, diskten tek-blok getirme(fetch) işlemi için oturumun beklemek zorunda kaldığı ortalama süredir ve V$SESSION_WAIT görünümünden elde edilebilir. Yeni nesil depolama alt sistemleri mimarisinde, ortalama tek-blok okumaları 8 milisaniye veya 1 sentisaniye üzerinde olmamaktadır. Geniş önbelleği sebebiyle SAN depolama birimlerinde ise ortalama bekleme süresinin 4 milisaniye ile 8 milisaniye arasında olması beklenmektedir. Yüksek bekleme süresi maliyeti yüksek tek blok okuma işlemini temsil ettiğinden, tüm proses yanıt süresi zahmetli olacaktır.  Öteki taraftan, daha düşük ortalama bekleme süresine daha fazla tahammül edilir ve bu düşük ortalama bekleme süreleri pekçok tek-blok okuması gerçekleştiren proseslerin yanıt süreleri üzerinde daha az etkiye sahiptir. Burada, SQL optimizasyonunu engellemek için ortalama bekleme süresini geliştirmeye teşvik etmiyorum.  Eğer bir uygulama, aşırı miktarda tek blok okuması gerçekleştiren SQL komutlarına sahipse, öncelikli olarak bunlar kontrol edilmeli ve optimize edilmelidir.

Oturumu izlerken db file sequential read olayı ile karşılaşılırsa, bu oturumun P1 ve P2 parametrelerini temsil ettiği objeye bakmak gerekir. Objenin normalde indeks veya tablo olduğunu bulmak gerekir. Obje isim çözümlemelerinde sıklıkla DBA_EXTENTS görünümü kullanılır. Ancak, DBA_EXTENTS görünümü ilgili performans işlemi için, kompleks kalır ve kullanıcı dostu bir görünümde değildir aslında. Obje isim çözümlendirme işlemi, X$BH ve DBA_OBJECTS görünümleri kullanarak daha hızlı yapılabilir. Bu 2 görünüm için yapılması gereken bir uyarı; bloğun önbellek tamponu içine okunması için beklenmesi gerekeceğidir, aksi durumda X$BH görünümü P1 ve P2 parametreleri tarafından referans alınan tamponda bir bilgi tutmayacaktır. Ayrıca, DBA_OBJECTS görünümü, P1 ve P2 parametrelerinin referans alabileceği rollback veya undo segment objeleri içermez.

select b.sid,

       nvl(substr(a.object_name,1,30),

                  'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,

       a.subobject_name,

       a.object_type

from   dba_objects a, v$session_wait b, x$bh c

where  c.obj = a.object_id(+)

and    b.p1 = c.file#(+)

and    b.p2 = c.dbablk(+)

and    b.event = 'db file sequential read'

union

select b.sid,

       nvl(substr(a.object_name,1,30),

                  'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,

       a.subobject_name,

       a.object_type

from   dba_objects a, v$session_wait b, x$bh c

where  c.obj = a.data_object_id(+)

and    b.p1 = c.file#(+)

and    b.p2 = c.dbablk(+)

and    b.event = 'db file sequential read'

order  by 1;

SID   OBJECT_NAME          SUBOBJECT_NAME   OBJECT_TYPE

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

14    P1=18 P2=310801 P3=1

32       I_LINK1_PK                         INDEX

36          MPHED30_PK                      INDEX

İndekslere dayalı sıralı(sequential) okumalar

İndekslere dayalı sıralı okumalarda ana sorun indeks erişimi değil, aşırı ve yersiz indeks okumalarından kaynaklanan beklemelerdir. Önbelleği gereksiz işgal eden indeksleri görmek için alttaki sorgu kullanılabilir.

select distinct b.owner, b.segment_name
   from x$bh a, dba_extents b        
   where b.file_id=a.dbarfil
     and a.dbablk between b.block_id
     and b.block_id+blocks-1
     and segment_type='INDEX'
     and b.owner = upper('&OWNER')

Eğer db file sequential read olayı oturumun yanıt süresinin önemli bölümünü ortaya koyarsa, bunun hepsi uygulamanın pekçok indeks okuması yaptığını söyler ve bu da uygulama geliştirme sorununa işaret eder. Bu durumda indeksler üzerinden verilere erişen SQL komutlarının çalışma(execution) planlarını denetlemek gerekir. Bu durumda aşağıdaki sorulara cevap aramak gerekecektir.

1. SQL komutlarının verilere erişmek için indeks aramaları yapması uygunmudur?
2. Kullanılan uygulama OLTP mi, yoksa DSS mi?  
3. FTS(Full Table Scan) yapmak daha verimli olurmuydu?
4. Komutlar doğru sürücü(driving) tablo kullanıyormu? 
5. İndekslerde fragmantasyon varmı?
6. Doğru indeksler kullanılıyormu?

Tabii bu sorular daha uzar da gider… Bu noktada optimizasyon hedefi, mantıksal ve fiziksel I/O sayısını azaltmak olacaktır. Eğer çalışan uygulamanın koduna erişirseniz, uygulama mantığı incelenebilir.Bu noktada, tüm mantığa bakmak ve ne yapmaya çalıştığını anlamak en iyi yol olacaktır. Bu durumda daha iyi bir yaklaşım tavsiyesi noktasına gelinecektir.

İndeks okuma performansı, yavaş I/O alt sistemleri kullanımı ve/veya zayıf veritabanı dosya yapılandırması gibi daha yüksek ortalama bekleme süreleri ile sonuçlanan pekçok kriterden etkilenir. Buna rağmen, I/O iyileştirmesi pek çok DBA’in yaptığı gibi uygulama ve SQL iyileştirmesinden daha öncelikli duruma getirilmemelidir. I/O iyileştirmesi, SQL komutları optimize edilmeden ve fiziksel I/O talebinin hala yüksek seviyelerde devam etmesi durumunda bu sorunu çözmeyecektir. Genellikle, uygulama geliştiriciler SQL ve uygulama iyileştirmesi yapmak yerine işin kolayına kaçarak “karar vericileri” daha gelişmiş ve güçlü donanımlar satın almaya teşvik ederler. Bu durumda, uygulama geliştiricilere dişlerimizi göstermemiz gerekir(aynı olay geçen Aralık ayında benimde başıma gelmişti. Aşırı I/O okuma sorunu yaşanan bir veritabanında, uygulamayı geliştiren firmanın danışmanları “application tuning” yerine, daha kuvvetli depolama birimine geçmemiz gerektiği tavsiye etmişler, hatta CIO’yu ikna etmişler ancak son anda CIO’ya hazırladığım yaklaşık 20 sayfalık bir darboğaz raporu sonucunda geri adım atıp “application tuning” noktasında ikna olmuşlardı.) Eğer kullanılan uygulama üçüncü parti bir uygulama ise, yüksek beklemeye sebebiyet veren noktalarda yeni indeksler ekleyebilir, doğru sürücü tablo kullanımını optimize edebilir, mevcut anahtarları daha uygun anahtarlarla değiştirmeyi deneyebilir, tek tek SQL ad-hoc sorguları kullanmak yerine PL/SQL paketleri içine gömülü SQL ad-hoc sorguları kullanabilirsiniz.  

SQL iyileştirmesine ilave olarak, eğer çalışma planı “table access by index rowid” olayını çağırıyorsa bu durumda indeks kümeleme faktörünü(indeks clustering factor) kontrol etmekte zaman harcamaya değer bir çalışma olacaktır. Bir indeksin kümeleme faktörü, tablo içinde satırların nasıl sıralandığını belirtir ve bütün bir işlem için gereken I/O sayısını etkiler. Eğer indeksin DBA_INDEXES.CLUSTERING_FACTOR değeri tablo içindeki "toplam blok" sayısına yaklaşırsa, bu durumda tablo içindeki pek çok satır sıralanmış olacaktır. Ancak, eğer kümeleme faktörü tablo içindeki "toplam satır" sayısına yaklaşırsa, bu demek olur ki tablo içindeki satırlar rastgele sıralanmıştır. Bu durumda, aynı yaprak bloğu içindeki indeks girişleri için aynı veri bloğu içindeki satırları göstermek olası olmaz, böylece işlemi tamamlamak için daha fazla I/O’ya ihtiyaç duyulur.İndeksin kümeleme faktörü, önce tablonun yeniden inşası(veya "alter table ... shrink space compact" komutu ile tablonun sıkıştırılması), ve sonrasında kümeleme faktörü yüksek olan indeksin yeniden inşa edilmesi ile geliştirilebilir, böylece satırlar tekrardan indeks anahtarına bağlı olarak sıralanmaya başlar. 

Ayrıca aşağıdaki  sorguyu kullanarak, uygulamaya kısa zamanda yeni bir indeks işleme girişi olup olmadığı kontrol edilmelidir. Veritabanında yeni bir indeksin işleme girmesi optimizer’ın tabloya erişen SQL komutunda farklı bir çalışma planı seçmesine sabebiyet verebilir.

select owner,
       substr(object_name,1,30) object_name,
       object_type,
       created
from   dba_objects
where  object_type in ('INDEX','INDEX PARTITION')
order by created;

OPTIMIZER_INDEX_COST_ADJ ve OPTIMIZER_INDEX_CACHING başlangıç parametreleri, optimizerın “nested loop- içiçe döngü” işlemlerini kayırmasına yardım edebilir ve FTS üzerinden indeks erişim yolunu seçebilir.

OPTIMIZER_INDEX_COST_ADJ parametresinin varsayılan değeri 100’dür. Daha düşük bir değer optimizer’ın  indeks erişim yollarının daha ucuz olduğunu düşünmesine iter. Maliyet(cost) formulünde bu başlangıç parametresinin etkisini anlamak için nasıl çalıştığına bir bakalım.

1. İndeksin kök bloğuna erişir.
2. İlk anahtarları içeren yaprak bloklara yönelmek için branch bloklar üzerinden gidilir.
3. Her bir anahtarın arama kriterini karşılaması için, veri bloğunu referans eden rowid’ler ekstrakt edilir, ardından rowid’ce referans edilen veri bloklarına erişilir.

OPTIMIZER_INDEX_CACHING parametresi için varsayılan değer ise 0’dır. Daha yüksek bir değer, optimizer’a yüksek oranda indeks bloklarının halihazırda önbellek tamponda bulunduğunun ve “nested loop” işlemlerinin daha ucuz olduğu bilgisini verir. Bazı üçüncü parti uygulamaları bu metodu indeks kullanımını desteklemek için kullanır. Bu parametrelerin uygun olmayan şekilde kullanımı anlamlı I/O bekleme olayına sebebiyet verebilir.  Bu sebeple oturumların hangi değerler ile çalıştığını bulmak önemlidir.  Oracle 9i sürümünde bu bilgi, oturumların ancak birinci seviyede “trace 10053”  ile izlenmesiyle elde edilebilmekteydi, ancak Oracle 10g itibariyle V$SES_OPTIMIZER_ENV görünümünden kolayca elde edilebilir, ayrıca AWR raporlaroıda faydalıdır.

Tüm obje istatistikleri mevcut veriyi temsil ederken, hatalı istatistiklerin ise optimizer’ın o an kullanımda olmayan indeks okumalarını çağırarak zayıf çalışma planı oluşturmasına sebep olacağını bilmemiz gerekir. Tablolar veya indeksler düşük ESTIMATE değeri ile analiz edilirken, Oracle genellikle tek-blok okuması yapar ve bunu db file sequential read istatistiğine ilave eder (oturum için V$SESSION_EVENT görünümüne, instance için V$SYSTEM_EVENT görünümü içine kaydeder).

Tablolara dayalı sıralı okumalar

P1 ve P2 parametrelerinin indeks yerine bir tabloyu çözmeye çalıştığı durumda, db file sequential read bekleme olayı görülebilir.Bu olay, indekslerden elde edilen rowid’lerle tablolara erişen SQL komutlarında gayet normaldir. Aşağıda bu olay ile ilgili bir çalışma planı yer almaktadır. Oracle, rowid’lerle bir tablo okurken tek-blok I/O işlemi kullanır.

LVL   OPERATION                           OBJECT               
---- -----------------------------       ---------------------
  1   SELECT STATEMENT
  2         TABLE ACCESS BY INDEX ROWID   SRC_TBSN_NTP  
  3                  INDEX RANGE SCAN     SRC_TBSN_SNP_1IX

Bunun yanında kayıp zamanların bulunmasında ve bir olay için ortalama bekleme süresi bulunmasında  V$EVENT_HISTOGRAM görünümüde oldukça faydalı bilgiler vermektedir. Pek çok datanın tek bir numara içine tıkılması durumunda kritik bilgiler noktasında resmin genel bütünü gözönüne serilemeyebilir. İşte $EVENT_HISTOGRAM belirli zaman aralığında toplanan bekleme olaylarının devamlılığını gözler önüne serer, aşağıda Jonathan Lewis’in yazısından bir anektot yer almaktadır. Bu görünümden elde edilen bilgi, bekleme olayının adrese yazmaya ihtiyaç duyulan devamlı bir problem mi, yoksa tekil bir olaymı olduğunu belirlemeye yardımcı olur.

select

        *

from    v$event_histogram

where  event = 'db file sequential read'

order by

        wait_time_milli

;           

EVENT#     EVENT                     WAIT_TIME_MILLI WAIT_COUNT

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

       115 db file sequential read                 1     230721

       115 db file sequential read                 2     161702

       115 db file sequential read                 4    4254007

       115 db file sequential read                 8   14293447

       115 db file sequential read                16    4148671

       115 db file sequential read                32    1683639

       115 db file sequential read                64     571850

       115 db file sequential read               128      40053

       115 db file sequential read               256       1114

       115 db file sequential read               512         70

       115 db file sequential read              1024         23

       115 db file sequential read              2048         20           

      

Sonuçtan da görüldüğü üzere WAIT_TIME_MILI değeri katlanarak artmaktadır. 230,721 okuma 1 milisaniye altında olmasına rağmen, 161,702 okuma 1 ile 2 milisaniye arasında sürmektedir. Burada ilginç olan en son satırdaki 20 okumanın yaklaşık 2 saniye sürmesidir. Bunun muhtemel sebebi, verimsiz SQL komutlarının çok hızlı şekilde blokları yeniden kullanıma sokarak herkes için  Oracle önbelleğini bozması ama ikincil tampondan bu blokları yeterince hızlı şekilde kurtarmak için blokları tekrar ziyaret etmeyi yönetmesidir. Aynı şekilde V$SYSTEM_EVENT çalıştırılsaydı ortalama 16 milisaniye bekleme süresi görülecekti, ancak yukardaki histogramda okumaların büyük bir kesmi 16 milisaniyeden daha fazladır,hatta 2 saniyelik beklemede okumalarda azda olsa vardır(son 2 satır).

Sistem tabanlı teşhisler

V$SYSTEM_EVENT görünümü, sistem seviyesinde teşhis verilerini sağlar. I/O ilişkili olaylar için dikkat edilmesi gereken AVERAGE_WAIT ve  TIME_WAITED kolonlarıdır.

Instance başlangıcı ile birlikte TIME_WAITED değerini değerlendirmeye almak gerekir. Eski bir instance’ın daha yüksek db file sequential read bekleme zamanı göstermesi normaldir. Ayrıca, aşağıdaki örnekte görüldüğü gibi doğru bir TIME_WAITED bilgisi almak için  her zaman V$SYSTEM_EVENT görünümünün kullanılması gerekmektedir. Bu bize db file sequential read beklemesinin sistem içindeki diğer anlamlı bekleme olayları ile kıyaslama yapma imkanı verir. Eğer db file sequential read bekleme zamanı top 5 kategorisi içinde değilse, o zaman bu bekleme olayını pek dikkate almak gerekmez. db file sequential read bekleme olayı top 5 bekleme listesinde yer alsa bile, bu bize veritabanının pek çok tek-blok I/O çağrısı gördüğünü söyler. Daha öncedende bahsettiğim gibi bu durum kısa süreli çalışan OLTP uygulamasından veya uzun süre çalışan batch proseslerden veya her ikisinden kaynaklanır. Sistem seviyesinde kimin I/O çağrısı yaptığı, ne zaman bu çağrıların yapıldığı, hangi objelere erişildiği ve hangi SQL komutlarının bu çağrılar ile ilişkili olduğu gibi soruların cevapları net olarak bulunamaz.  Kısacası, instance seviyesinde teşhis kapasitesi oldukça sınırlıdır.

select a.event,
       a.total_waits,
       a.time_waited,
       a.time_waited/a.total_waits average_wait,
       sysdate – b.startup_time days_old
from   v$system_event a, v$instance b
order by a.time_waited;

AVERAGE_WAIT kolonu oldukça faydalıdır. Ortalama tek-blok okuma bekleme zamanı tahammül sınırlanızı aşıyorsa, I/O altsistemine ve disk üzerinde sıcak noktaların olup olmadığını kontrol etmek gerekir. Eğer veritabanı dosya sistemleri üzerine inşa edilmişse, veritabanı mount dosyalarının sadece Oracle dosyalarını içerdiğinden emin olmak gerekir. Veritabanı mount dosyalarını başka uygulamalar ile paylaşmayın ve mümkünse ilgili tüm I/O cihazlarını başka uygulamalarla paylaşmayın. Linux sistemlerde /etc/vfstab dizinine bakarak nelerin mount edildiğini kontrol edebilirsiniz.

Ayrıca, veri dosyalarının sıcak noktalar oluşmasını önlemek amacıyla farklı fiziksel disklere düzgünce serpiştirildiğinden emin olun. I/O aktivitesini iostat ve sar komutları ile izlemek gerekir.Disk kuyruk uzunluğu, disk hizmet süresi ve I/O çıktısına dikkat etmek gerekir. Eğer bir fiziksel cihaz oldukça meşgulsa, bu durumda bu cihaz üzerindeki bazı veri dosyalarını başka disklere serpiştirmek gerekir. Aşağıdaki sorgu sonucunda fiziksel dosyalardan yapılan fiziksel okuma ve fiziksel yazma sonuçları gelmektedir. Bunun sonucunda hangi veri dosyasının yeniden serpiştirilmeye ihtiyacı var teşhis edilebilir.

select d.name,s.PHYRDS,s.PHYWRTS

from v$datafile d, v$filestat s

where d.file#=s.file#

order by 1;

Benim geçen yılın sonunda yaşağıdım aşırı I/O bekleme süresi sorununda aldığım rapor sonucu aşağıdadır.

NAME                                PHYRDS      PHYWRTS

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

/u02/oradata/XTLLIVE/system01.dbf   42873922    1527643

/u08/oradata/XTLLIVE/tmvxsd02.dbf   430128921   4995429

/u03/oradata/XTLLIVE/perfstat01.dbf 6320646     1311381

/u03/oradata/XTLLIVE/undotbs01.dbf  2025353     72899148

/u09/oradata/XTLLIVE/tmvxarch01.dbf 40357       28876

/u04/oradata/XTLLIVE/tmvxld01.dbs   40402       28876

/u04/oradata/XTLLIVE/tmvxmi01.dbf   44549       29263

/u06/oradata/XTLLIVE/tmvxrunt01.dbf 1719652399  22578180

/u07/oradata/XTLLIVE/tmvxsi01.dbf   328681641   31554730

/u04/oradata/XTLLIVE/tmvxsi02.dbf   211186362   25967626

/u11/oradata/XTLLIVE/tmvxsi03.dbf   112669265   15674990

/u04/oradata/XTLLIVE/tmvxsi04.dbf   1935556     1130918

/u04/oradata/XTLLIVE/tmvxti01.dbf   121885      44619

/u05/oradata/XTLLIVE/drsys01.dbf    40407       28876

/u05/oradata/XTLLIVE/example01.dbf  40390       28876

/u05/oradata/XTLLIVE/tmvxli01.dbf   40386       28876

/u05/oradata/XTLLIVE/tmvxmd01.dbf   42838       29639

/u10/oradata/XTLLIVE/tmvxsd01.dbf   1917529473  20655289

/u05/oradata/XTLLIVE/tmvxtd01.dbf   43467       28876

/u05/oradata/XTLLIVE/tools01.dbf    40398       28876

/u08/oradata/XTLLIVE/users01.dbf    98295940    44697434

Yukardaki senaryoda yaptığımız I/O iyileştirmesi; sorunlu 2 veri dosyasının (tmvxrunt01.dbf ve tmvxsd01.dbf) ait olduğu tablespaceler’e ilave ikişer adet veri dosyasının yeni ayrı fiziksel disklerde oluşturulması(/u12, /u13,/u14 ve /u15 diskleri) ve bu mevcut iki veri dosyası içinde bulunan aşırı fiziksel okuma yapılan ilgili tablolaların, bu yeni oluşturulan veri dosyaları üzerinde eşit yükte yeniden inşası neticesinde I/O işlemleri istenen seviyeye geldi. Tabii, bu arada indeks kümeleme faktörünün yeniden optimal seviyelere gelmesi için - yanılmıyorsam 5 tabloyu- ilgili indeksleri ile yeniden inşa etmiştik(tmvxsd02.dbf veri dosyası içinde yer alan). Tabii fiziksel depolama alt sisteminde de bazı mount noktaları için ek kontroller devreye sokulmuştu(/u6 ve /10 diskleri için)

Solaris sistemlerde, kontroller ve cihaz I/O istatistik bilgileri   iostat –dxnC komutu ile elde edilebilir. Ancak, sıcak noktaların iyileştirilmesini söylemek uygulamaktan daha zor olmaktadır. Bu iyileştirme için uygulamanın I/O yu nasıl kullandığını bilmek gerekir. Dahada ötesinde, eğer uygulama daha gelişmemişse ve devamlı yeni fonsiyonellikler ekleniyorsa sıcak noktalar hedef harekat noktası olabilir. Genelde DBA’ler uygulamadaki yeni geliştirmelerden haberdar edilmediğinden, reaktif olarak devamlı bir keşif içinde olmalıdır. İşte bu sebeple I/O dengelemesi hiç bitmeyen bir hikaye olur. Oracle 10g itibariyle ASM,  I/O dengelemesinde alternatif bir yardımcı olmaktadır.  

Sistem çapında db file sequential read ortalama bekleme olayına ilave olarak V$SYSTEM_EVENT görünümü ile bmünden her bir veri dosyası için tek-blok okuma istatistiklerini sağlar. Dosya seviyesinde tek-blok ortalama bekleme süresi SINGLEBLKRDTIM değerinin SINGLEBLKRDS değeri ile bölünmesiyle hesaplanabilir. Böylece hangi dosyaların kabul edilemez ortalama bekleme süresine sahip olduğu kolayca keşfedilebilir ve mount noktaları veya cihazları araştırmaya başlayıp veritabanı için exclusive olduğundan emin olunabilir.

select a.file#,
       b.file_name,
       a.singleblkrds,
       a.singleblkrdtim,
       a.singleblkrdtim/a.singleblkrds average_wait
from   v$filestat a, dba_data_files b
where  a.file# = b.file_id  
and    a.singleblkrds > 0
order by average_wait;

FILE#  FILE_NAME                           SINGLEBLKRDS  SINGLEBLKRDTIM AVERAGE_WAIT

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

17     /u06/oradata/XTLLIVE/tmvxrunt01.dbf   1704565376    30274581   0,0808258929944448

2      /u03/oradata/XTLLIVE/undotbs01.dbf    1995969       298792     0,149697715746086

10     /u10/oradata/XTLLIVE/tmvxsd01.dbf     1233644990    194123462  0,157357638197031

20     /u08/oradata/XTLLIVE/tmvxsd02.dbf     297022693     47960426   0,161470578276657

15     /u04/oradata/XTLLIVE/tmvxti01.dbf     92943         20714      0,222867779176485

1      /u02/oradata/XTLLIVE/system01.dbf     28582149      6759115    0,236480294046469

6      /u08/oradata/XTLLIVE/users01.dbf      74297964      19660265   0,264613778649439

14     /u07/oradata/XTLLIVE/tmvxsi01.dbf     314775010     118162427  0,375386937482744

12     /u05/oradata/XTLLIVE/tmvxli01.dbf     11502         4686       0,407407407407407

13     /u04/oradata/XTLLIVE/tmvxmi01.dbf     15654         6414       0,409735530854734

11     /u05/oradata/XTLLIVE/tmvxtd01.dbf     14577         6130       0,420525485353639

18     /u04/oradata/XTLLIVE/tmvxsi02.dbf     203732487     88190747   0,432875229172459

19     /u11/oradata/XTLLIVE/tmvxsi03.dbf     108599871     47420985   0,436657839123953

5      /u03/oradata/XTLLIVE/perfstat01.dbf   6147231       2793057    0,45436018265785

21     /u04/oradata/XTLLIVE/tmvxsi04.dbf     1898848       864557     0,455306059252768

3      /u05/oradata/XTLLIVE/drsys01.dbf      11523         5474       0,475049900199601

9      /u05/oradata/XTLLIVE/tmvxmd01.dbf     13550         7047       0,520073800738007

4      /u05/oradata/XTLLIVE/example01.dbf    11505         5994       0,520990873533246

7      /u05/oradata/XTLLIVE/tools01.dbf      11513         6080       0,528098671067489

16     /u09/oradata/XTLLIVE/tmvxarch01.dbf   11472         6324       0,551255230125523

8      /u04/oradata/XTLLIVE/tmvxld01.dbs     11518         6599       0,572929328008335