ORACLE PERFORMANCE TUNING -1 (AWR)

Oracle Veritabanı için en çok DBA desteğine kurumların ihtiyaç duyduğu bölüm Performans Tuning konularıdır, performans tuning gerçekten Oracle DBA ler açısından en zorlu konuları içerdiğini düşünmekteyim. Oracle Veritabanı performansını değerlendirebilmek için DBA’ in zaten bir çok noktada bilgi ve beceri sahibi olması gerekmektedir. Veritabanı Mimarisi, SQL, PL/SQL, RAC kullanıyor ise RAC mimarisi, ASM mimarisi vs. bir çok konu var bunları ve detaylarını biliyor olmak gerekiyor ki AWR- ADDM ve ASH raporlarını yorumlayarak darboğazları veya performans problemlerini tespit etmeyi ve bu problemlere çözüm üretilebilsin. Yani buradan çıkaracağımız sonuç şudur ki Oracle Performans konusunda başlamadan önce bu kısımlarda eksik varsa mutlaka giderilmelidir.

Oracle performans bilgileri ile ilgili olarak arka plan proseslerimiz MMON ve MMNL prosesleridir. MMON varsayılan olarak 60 sn de bir istatistik bilgilerini yakalar ve ilgili performans viewleri ile erişime sunar. Bu bilgiler Disk üzerinde snapshoot olarak kaydedilir. Bu snapshootlar aracılığı ile AWR raporları oluşturulur, bu AWR ların yorumlanmasıyla da ADDM raporları oluşturulur, bu raporlar aracılığı ile veri tabanının o snapshoot zaman aralıklarındaki durumu ile ilgili detaylı bilgiler edinebiliriz.

AWR MMON arka plan prosesi tarafından toplanan istatistik bilgilerinin snapshoot olarak kaydedildiğini, ve bu snapshootlar aracılığı ile de bize veri tabanı durumu ile ilgili çok geniş bilgiler veren bir rapor olşturulduğunu yukarıda açıklamıştık, AWR raporları Cloud Control Enterprise Manager üzerinden alınabildiği gibi, Oracle kurulumu sırasında gelen awrrpt.sql scripti ilede AWR raporları oluşturabiliriz.

Ben html ve 1 günlük snapshotların içerisinden AWR oluşturmak istiyorum aşağıdaki seçenekte buna göre seçimler yapıyor olacağım; (koyu renkli olanlar benim seçimlerimdir)

SQL> @ORACLE_HOME/rdbms/admin/awrrpt.sql

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type:

Enter value for num_days:1

Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

frkdb1       FRKDB               60  13 Oct 2021 00:00    1
                                 61  13 Oct 2021 01:00    1
                                 62  13 Oct 2021 02:00    1
                                 63  13 Oct 2021 03:00    1
                                 64  13 Oct 2021 04:00    1
                                 65  13 Oct 2021 05:00    1
                                 66  13 Oct 2021 06:00    1
                                 67  13 Oct 2021 07:00    1
                                 68  13 Oct 2021 08:00    1
                                 69  13 Oct 2021 09:00    1
                                 70  13 Oct 2021 10:00    1
                                 71  13 Oct 2021 11:00    1
                                 72  13 Oct 2021 12:00    1
                                 73  13 Oct 2021 13:00    1
                                 74  13 Oct 2021 14:00    1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 67

Begin Snapshot Id specified: 67

Enter value for end_snap: 68

End   Snapshot Id specified: 68


Enter value for report_name: testawr.html

testawr.html awr raporumuz oluştu $ORACLE_HOME/rdbms/admin altından bu raporu alabiliriz artık.

AWR oluşturma seçeneği veritabanı ilk oluşturulduğunda açık olarak gelir, STATISTIC_LEVEL parametresini defaultta TYPICAL dır eğer BASIC yaparsak artık AWR raporu oluşturamaz hale geliriz.

SQL> show parameter STATISTICS_LEVEL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_statistics_level              string      TYPICAL
statistics_level                     string      TYPICAL

Veri tabanımızda hangi aralıklarla snapshoot alınır ve bunu nasıl ayaralayabiliriz?

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL       RETENTION
+00000 01:00:00.0  +00008 00:00:00.0

Varsayılan olarak her saat başı snapshoot alınıyor ve 7 gün saklanıyor, peki bunu değiştirmek istersek; 45 dk da bir snapshoot alınsın ve 15 gün saklansın istiyorsam;

SQL> execute dbms_workload_repository.modify_snapshot_settings(interval=>45,retention=>21600);

eğer istersek aşağıdaki gibi manuel de snapshoot alabiliriz;

SQL> execute dbms_workload_repository.create_snapshot;

Snapshot oluşturmak bunları kullanarak AWR ve ADDM raporları oluşturmak elbette oldukça kolay, asıl mesele bunları doğru yorumlayabilmektedir.

Veritabanındaki değişimler performanstaki değişimleri anlamlandırabilmek için oldukça önemlidir, hayat kurtarır :). Bu nedenle veritabanın düzgün çalıştığından emin olduğumuz ve veritabanı üzerinde yük olan bir anı Baseline olarak kaydetmek oldukça işimize yarayacaktır. Bu şekilde ileride bir zamanda veritabanında bir problem olduğunda referans alabileceğimiz bir değerler kümesinin raporu elimizde olacaktır. Baseline oluşturabilmek için 2 tane snapshot seçmemiz gerekmektedir, snapshotların listesini dba_hist_snaphot viewinden aşağıdaki sorguyla elde edebiliriz.

SQL> select SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot;

Sorgunun çıktısındaki bize uygun zaman aralığı için aşağıdaki gibi bir baseline oluşturabiliriz.

SQL> execute dbms_workload_repository.create_baseline(start_snap_id=>46, end_snap_id=>47,baseline_name=>'healty_base_db');

Baseline larımızı dbs_hist_baseline view ından görebiliriz.

Baseline ile işimiz bittiğinde drop edebiliriz

execute dbms_workload_repository.drop_baseline(baseline_name=>'healty_base_db');

AWr raporu almayı baseline oluşturmayı ve bunların ne olduğunu kabaca tarif ettik peki AWR raporlarını alırken nelere dikkat etmeliyiz, birde buna değinelim.

• Birden fazla AWR alınmalı; Kıyaslama yapabilmek önemlidir, Performans durumunu bildiğimiz bir ann AWR ı ile problemli durumun AWR ı karşılaştırmak bize bir çok ipucu sunacaktır.
• Zaman aralığının doğru seçilmesi; AWR seçilen snapshot zaman aralıklarındaki ortalama değerleri verecektir, dolayısıyla doğru zaman aralığının alınması doğru tespite götürecektir.
• Küçük zaman aralıklarının AWR larına bakmak daha faydalı olacaktır; 3 saatlik bir AWR yerine 1 saatlik 3 AWR almak daha mantıklı olacaktr.
• RAC için her instance dan ayrı AWR alınmalı
• AWR yanında ASH rapolarınada başvurulmalı
• Retention süresi ayarlanmalı; Performans sorunlarımız varsa karşılaştırabileceğimiz ne kadar fazla zaman aralığı varsa o kadar iyidir.

AWR Raporu Yorumlamak;

AWR Raporları çok geniş ve kapsamlı raporlardır, burada sadece belli başlıkların belli bölümleri üzerinde yorum yapmaya çalışacağız. Aslında AWR raporu alındığında bu AWR raporunun tamamını inceleyen bir babayiğit olduğunu da düşünmüyorum, odaklanmamız gereken konu, darboğaz nerede veritabanını ne bekletiyor bunu tespit etmek asıl önceliğimiz olmalı, bu konuda bize en fazla yarıdımı dokunacak olan kısım ise Top wait eventler kısmıdır, burada ipuçları yakalayabilirsek ondan sonrasını çekerek getirmemiz gerekiyor.

Yukarıdaki wait eventlere baktığımızda burada, direct path read çok yüksek, Sql Tuning yapılması gerektiğini anlıyoruz, Hemen sql istatistiklerine gidelim,

Burada problemli görünen 2 adet sql var bunların Tune edilmesi gerekmektedir. En üstteki 2 sql için tune yapılmalı yüksek sevide diskten okuma yapmakta, darboğazın kaynağı burası görünmektedir.

Yukarıda AWR dan Tune a giden yola bir örnek verildi, fakat AWR Raporundaki önemli kısımlara ve kısaca açıklamalarına bir göz atalım;

HEADER : Veritabanımıza ait bilgiler vardır.

Burada bazı tanımları anlamakta fayda var

DB Time: Sessionların CPU da geçirdikleri zaman ve bekleme zamanlarının toplamına denir.

DB Time= CPU Time + Waits

Elapsed Time : Sessionın ilk andan son ana kadar tüm süresine denir yani session ın toplam süresidir.

Elapsed time =   cpu time +    user i/o wait time +    application_wait_time +   concurrency_wait_time +
   cluster_wait_time +   plsql_exec_time +   java_exec_time + …….

LOAD PROFILE Buradaki değerlerin veritabanımızın türüne göre ve yoğunluğuna göre tutarlı olmasını bekleriz (DSS, OLTP vs..)

Burada dikkat edilmesi gereken fiziksel okuma yazma oranları, hard parse ile executes(SQL) oranlarına dikkat etmek gerekir.

• Redo Size (Bytes): Yüksek Redo miktarı, fazla sayıda DML işleminin veritabanında gerçekleştiği bir aralıkta bu AWR ın alındığını anlatmaktadır.
• Logical Read(block) : Oracle kendine gelen bir istek için, ilk önce bufferdan getirmek isteyecektir. Eğer aradığı veri buffer da yoksa Logical Read 1 artar Logical Read=Consistent get+ db block gets
• User calls Veritabanına gelen tüm soorguların sayısıdır.
• Parses(SQL) : Hard ve Soft Tüm pars işlemlerinin sayısıdır
• Hard parses (SQL): CPU sayısı ile ilgili olarak düşük veya yüksek olarak ifade edilebilir, 11 in altı iyidir, ancak 100 tane CPU çekirdeğiniz var ve sisteminizin Hard Pars yapması gerekiyorsa buda olabilir.
• Phsical read (block) : Oracle aradığı dataları buffer cache de bulamaz ise fiziksel okuma artacaktır, bu durumda bu okuma işlemlerini yapan sql lerin araştırılması gerekmektedir. Yukarıdaki örnekte bu net bir şekilde görünmektedir.
• Executes(SQL) : Eğer executes oranları çok yüksek ise ilk olarak bunu hard parse sayısı ile bir karşılaştırmalıyız.
• Logons; Her yeni bağlantı (connection) veritabanı açısından oldukça maliyetlidir, Eğer bu değer çok yüksek ise Time Model Statistics alanında Connection Management Elapsed Time değeri kontrol edilmelidir.

INSTANCE EFFICIENCY PERCENTAGE

Buradaki alanların %100 e yakın olması istenir.

• Buffer Nowait Buffera ulaşılma yüzdesidir, oran düşük ise RAM yetmezliği ve memory parametrelerini kontrol etmek gerekir.
• Library Hit % SQL ve PlSQL lerin Shared poolda bulunma oranlarını gösterir%95 altında ise Shared Pool size kontrol edilmelidir;
  • SHARED_POOL_SIZE parametresi kontrol edilir
  • CURSOR_SHARING force olarak ayarlanır
  • Bind değişkenlerin kullanımı kontrol edilir.
• Latch Hit %: %99 dan küçün ise Latch problemi değerlendirilebilir.
• Redo NoWait % : Redo Log buffer ın yeterli boyutta olup olmadığını gösterir.

TOP 10 FOREGROUND EVENTS BY TOTAL WAIT TIME

AWR raporundaki belki ilk baklacak en önemli ksımlar Wait time lardır. buradaki bilgiler bize veritabanının nerede dar boğaz yaşadığı hakkında önemli bilgiler verecektir.

Bu bölümde tüm wait eventler yoktur özellikle bakılmak istenen Wait eventler için Wait Events Statistics kısmına bakılabilir. %DB Time ın büyük bir kısmını DB CPU tutnması bizim için iyi bir işarettir ve veritabanında I/O veya Memory bazlı bir dar boğaz yaşanmadığını düşünebiliriz. Burada karşılaşabileceğimiz bazı eventler hakkında konuşalım,

• db file sequential read Eğer bu değerde normalin üzerinde bir artış söz konusu ise , buffer cache üzerinden ardışık okuma (sequentiol read) yapıldığı sırada bir fiziksel I/O yapmaya çalışması ve bu işlemin buffer da bekletilmesi sonucu bu değerde artış olur. Olası sebepleri ise
  • Yanlış Index kullanımlar
  • Indexlerin Fragmente Olması
  • İstatistik Bilgilerinin Güncellenmemesi olabilir.
• log file sync Bu event başlatılan bir Tansactionın commit veya rollback yaptığında LGWR prosesinin bekleme yapması ile oluşan bir wait eventtir. Bu eventi sık gözlemliyorsak
  • LGWR I/O performansı kötüdür veya
  • Çok sık commit veya Rollback geliyor şeklinde yorumlanır. Çözüm ise;
  • Redo Logların hızlı disklere taşınması
  • Redo logların olduğu disk üzerindeki aktivitekleri azaltmak
  • Uygulamadan gelen commit ve rollback sayılarını azaltmak.
• db file scattered reads: Dağınık (Scattered) okuma bir Indexin “Fast Full Scan” yapması veya Full Table Scan (FTS) olduğu zaman artacaktır. Elbette bir veritabanında sequential ve scattered read olacaktır ancak dengeli olmalıdır.
• enq TX Row Lock Contention : Lock olan satırın bekleme durumunu gösterir, eğer yüksek ise Lock olan SQL lerin tespiti ve bunlara neyin sebep olduğunun araştırılmasına geçilmelidir.
•  enq: TM – contention : Genellikle bir DML ifadeden gelen eksik veya kısıtlı bir Foreign Key durumundan kaynaklanan Lock wait eventidir.
• direc path read :bu eventi görüyorsanız parallel çalşan sorgularınızda, FTS yapar ve bufferda SGA alanını bypass ederek doğrudan PGA alanda işlem yapcaktır. TEMP etkinliği etkilenecektir. Sıralama yapan bir SQL için index kullanmadan paralel bir şekilde doğrudan PGA alanda işlem yapılması olarak ifade edebiliriz. İlgili SQL ifadenin incelenmesi gerekmektedir. Indexlerimiz tam ise Optimizer farklı bir plan çalışıyordur, ilgili planın değiştirilip fixlenmesi gerekir.
• Wait Class column : Wait class genel olarak bize olayın uygulama veya sistemsel olup olmadığını sınıflandırmamızda yardımcı olur, aşağıdaki şekilde sınıflandırılır
  • Administrative
  • Application
  • Concurrency
  • User IO
  • System IO
  • Cluster
  • Commit
  • Configuration
  • Idle
  • Network
• Buffer Busy Wait : Belirli bir veri bloğuna aynı anda birden fazla erişim isteğinden kaynaklanır, genellikle aynı sql in arka arkaya çok fazla çalıştırılması veya sql in yüksek paralellikte çalıştırılması gibi nedenlerden bu wait event ortaya çıkar.

MEMORY STATISTICS

• Memory Usage % Shared Pool kullanım oranını gösterir; Eğer %80 lerin altında ise Shared pool büyük, %90 ların üzerinde ise Shared Pool alanı küçüktür anlamına gelir.
• %SQL with executions>1 : Sql lerin birden fazla kez kullanıldığı anlamına gelirki bu değerin %100 yakın olmasını isteriz, Eğer %60 lalrın altında ise, Bind variable kullanımına dikkat etmek gerekir.
• Memory for SQL w/exec>1: Cursor lar için memoriden tahsis edilen alanda cusorların birden fazla kez kullanım oranını gösterir.

TIME MODEL STATISTICS

Burada en çok zamanın nerede harcandığına dair fikir elde edebiliriz.

SQL ORDERED BY ELAPSED TIME

En fazla Veritabanı seviyesinde en fazla zaman harcayan SQL listesidir. Problem anında buradaki SQL ler hemen analiz edilmeli ve Tuning ihtiyacı var mı? bakılmalıdır. Yukarıdaki listeye bakıldığında ilk iki sıradaki SQL ler bize SORUN VAR diye bağırmaktadır.

SQL ORDERED BY CPU TIME

SQL lerin CPU da geçirği sürelere göre sıralanmasıdır. Yine buradaki Top Sqllerimiz Tuning açısından incelenmelidir.

SQL ORDERED BY READS

En yüksek Fiziksel I/O yapan SQL listesi TUNE açısından çok önemli bir liste. 1. Sıraya bakıldığında 591 kez çalıştırılan ve çok yüksek fiziksel I/O yapan bir SQL var.