Oracle Performans Troubleshooting 2 (Ash Reports)

ASH (Active Session History) aktif olan session bilgilerinin performans istatistiklerinin tutulduğu rapordur. Mevcut session bilgileri v$session ve v$session_wait_history eventlerinden gözlemlenebilir. Örneğin aşağıdaki sql veritabanında o an en fazla gözlemlenen eventleri gösterir.

   SELECT event, COUNT (*)

    FROM v$session_wait_history

GROUP BY event

ORDER BY 2 DESC

Ancak bu viewlar aktif sessionlara ait performans bilgilerini gözlemlemek, sorun tespit etmek ve çözüm geliştirmek için yeterli historic (geçmişe dönük) bilgiye sahip değildir. Sessionlar hakkındaki historic performans dataları için ASH raporları kullanılabilir. ASH aktif sessionlara ait saniyelik sample(örnek veri) elde ederek istatistiksel yorum yapılabililinmesini sağlayan very koleksiyonudur.  İşte bu yazımızda ASH raporlarını tanıtmaya çalışacak, ASH raporları ile neler yapılır ve ASH raporları nasıl yorumlanmalıdır anlatmaya çalışacağız.

Aktif Session Nedir?

ASH raporlarındaki aktif session ile v$session.state ‘ACTIVE’ değeri karıştırılmamalıdır. ASH raporlarında çıkan sessionlar Oracle RDBMS ‘e (Relational Database Management System) çağrı yollayan sessionlardır. Bu sessionlar hakkında toplanan datalar;

·         Parse, Execute ve Fetch Operasyonları

·         I/O işleminin tamamlanması için meydana gelen bekleme

·         Rac Sistemlerde remote instanceden mesaj yada buffer beklemeleri

·         Cpu üzerindeki beklemeler

·         Tekrarlanan sesionlar için beklememe

·         Paralel işlemler

·         Idle Wait (Boş bekleyen session) dışındaki digger tüm bekleme olayları’ dır.

ASH Raporu Nasıl Elde Edilir?

Ash raporu Enterprise Manager üzerinden yada $ORACLE_HOME/rdbms/admin/ashrpt.sql scripti ile elde edilebilir. Tabi Ash raporlarını toad gibi third party tool’lar ile elde etmekte mümkündür.

                ASH Verilerine Nasıl Ulaşılır?

                Ash verilerine, yukarıdada bahsettiğimiz gibi ash raporu oluşturarak, v$active_session_history wrh$_active_session_history yada dba_hist_active_sess_history viewlarına sql sorgu ile ulaşılabilir. Aşağaıdaki queryleri inceledikten sonra ASH raporu nasıl yorumlanır anlatmaya çalışacağız.

                Öncelikle ASH viewlarındaki en çok kullanılan kolonların ne anlam ifade ettiğini açıklayalım.

                V$ACTIVE_SESSION_HISTORY;

Kolon	Veritipi	Açıklama
SAMPLE_ID	NUMBER	Sample id
SAMPLE_TIME	TIMESTAMP(3)	Sample alındığı zaman
SESSION_ID	NUMBER	V$SESSION.SID
SESSION_SERIAL#	NUMBER	V$SESSION.SERIAL#
SESSION_TYPE	VARCHAR2(10)	Session tipi: FOREGROUND BACKGROUND
USER_ID	NUMBER	Oracle user identifier; maps to V$SESSION.USER#
SQL_ID	VARCHAR2(13)	Sample alındığı anda session tarafından çaılıştırılan sql
SQL_CHILD_NUMBER	NUMBER	Sql child numarası
SQL_OPCODE	NUMBER	SQl cümle çalışırken hangi operasyonda id? V$SESSION.COMMAND
SQL_PLAN_HASH_VALUE	NUMBER	SQl ‘ ait plan hash değeri
QC_INSTANCE_ID	NUMBER	Paralel çalışan sqller için hangi instanced çalıştığına dair bilgi. Eğer bu değer 0 ise tüm instance ler anlamına gelir.
QC_SESSION_ID	NUMBER	Paralel çalışan sqller için hangi instanced çalıştığına dair bilgi. Eğer bu değer 0 ise tüm instance ler anlamına gelir.
QC_SESSION_SERIAL#	NUMBER	Paralel çalışan sqller için session numarası. Eğer bu değer 0 ise tüm sessionlar anlamına gelir.
EVENT	VARCHAR2(64)	Eğer SESSION_STATE = WAITING, durumunda ise sessionun hangi event’I beklediğini gösterir. Eğer SESSION_STATE = ON CPU, ise bu kolon boş gelir.
P1TEXT	VARCHAR2(64)	Evente ait P1 değeri text ifadesi.
P1	NUMBER	Evente ait P1 değeri numeric ifade
P2TEXT	VARCHAR2(64)	Evente ait P2 değeri text ifadesi.
P2	NUMBER	Evente ait P2 değeri numeric ifade.
P3TEXT	VARCHAR2(64)	Evente ait P3 değeri text ifadesi.
P3	NUMBER	Evente ait P3 değeri numeric ifade.
WAIT_CLASS	VARCHAR2(64)	Beklenen event gurubu. V$SESSION.WAIT_CLASS.
WAIT_CLASS_ID	NUMBER	Beklenen event gurup numarası V$SESSION.WAIT_CLASS_ID.
WAIT_TIME	NUMBER	Toplam Bekleme Zamanı.Eğer CPU da bekleniyorsa 0 olarak gözükür. V$SESSION.WAIT_TIME.
SESSION_STATE	VARCHAR2(7)	Session durumu: WAITING ON CPU
TIME_WAITED	NUMBER	Eğer  SESSION_STATE = WAITING, durumunda ise eventi bekliyor demektir.
BLOCKING_SESSION_STATUS	VARCHAR2(11)	Bloke eden session durumu: VALID NO HOLDER GLOBAL NOT IN WAIT UNKNOWN
BLOCKING_SESSION	NUMBER	Eğer session başka bir session tarafında n tutuluyorsa ve tutan sessionda aynı instance üzerinde ise bloke eden yani tutan session id sini verir. V$SESSION.BLOCKING_SESSION.
BLOCKING_SESSION_SERIAL#	NUMBER	Bloke eden session serial number.
BLOCKING_INST_ID	NUMBER	Bloke eden session instance numarası.
BLOCKING_HANGCHAIN_INFO	VARCHAR2(1)	Bloke eden session hangmidir değilmidir bildirir. (Y) or not (N)
CURRENT_OBJ#	NUMBER	Eğer session waiting durumunda ise hangi obje üzerinde beklediğini gösterir. Ancak bu bazı event guruplar için var olan bir değerdir.Örneğin uygulama yada I/O gibi guruplar. V$SESSION.ROW_WAIT_OBJ#.
CURRENT_FILE#	NUMBER	Eğer session waiting durumunda ise hangi dosya üzerinde beklediğini gösterir. Ancak bu bazı event guruplar için var olan bir değerdir.Örneğin uygulama yada I/O gibi guruplar. V$SESSION.ROW_WAIT_FILE#.
CURRENT_BLOCK#	NUMBER	Eğer session waiting durumunda ise hangi block üzerinde beklediğini gösterir. Ancak bu bazı event guruplar için var olan bir değerdir.Örneğin uygulama yada I/O gibi guruplar. V$SESSION.ROW_WAIT_BLOCK#.
CURRENT_ROW#	NUMBER	Eğer session waiting durumunda ise hangi row üzerinde beklediğini gösterir. Ancak bu bazı event guruplar için var olan bir değerdir.Örneğin uygulama yada I/O gibi guruplar. V$SESSION.ROW_WAIT_ROW#.
CONSUMER_GROUP_ID	NUMBER	Consumer group numarası
XID	RAW(8)	Sessiona ait Transaction numarası
REMOTE_INSTANCE#	NUMBER	Session ‘ın beklediği blogun hangi instance tarafından tutulduğunu gösterir.Bu kolon sadece cluster eventler da geçerlidir.
TIME_MODEL	NUMBER	Time model bilgisi.
IN_PARSE	VARCHAR2(1)	Sample alındığında session parse ediliyor? (Y) or not (N)
IN_HARD_PARSE	VARCHAR2(1)	Indicates whether the session was hard parsing at the time of sampling (Y) or not (N)
IN_SQL_EXECUTION	VARCHAR2(1)	Sample alındığında session harde parse ediliyor? (Y) or not (N)
IN_PLSQL_EXECUTION	VARCHAR2(1)	Session pl/sql mi çalıştırıyor? (Y) or not (N)
IN_PLSQL_COMPILATION	VARCHAR2(1)	Session pl/sql compile işlemimi yapıyor? (Y) or not (N)
IN_JAVA_EXECUTION	VARCHAR2(1)	Session javamı çalıştırıyor? (Y) or not (N)
IN_BIND	VARCHAR2(1)	Session bid işlemimi yapıyor? (Y) or not (N)
PROGRAM	VARCHAR2(48)	Sessiona ait işletim sistemi program ismi
MODULE	VARCHAR2(48)	Sessiona’ a ait module bilgisi DBMS_APPLICATION_INFO.SET_MODULE procedure
CLIENT_ID	VARCHAR2(64)	Client identifier of the session; maps to V$SESSION.CLIENT_IDENTIFIER
MACHINE	VARCHAR2(64)	Client makina ismi
PORT	NUMBER	Client port numarası
PGA_ALLOCATED	NUMBER	Session tarafında işgal edilen PGA alanı (bytes)
TEMP_SPACE_ALLOCATED	NUMBER	Session tarafında işgal edilen TEMP alanı (bytes)

Aynı kolonlar DBA_HIST_ACTIVE_SESS_HISTORY ve WRH$_ACTIVE_SESSION_HISTORY viewları içinde kullanılabilir. Ancak v$active_session history de sadece active olan sessionların bilgisi bulunur ve hersaniye snap alınır.Geçimişe yönelik bilgi bulunmaz. WRH$_ACTIVE_SESSION_HISTORY ve DBA_HIST_ACTIVE_SESS_HISTORY viewları ise 10 saniyede bir refresh edilir ve geçmişe yönelik bilgi tutabilirler. Bu viewlar awr raporlarınında temeli niteliğindedir.

SELECT A.INST_ID,A.SAMPLE_ID,A.SAMPLE_TIME,(SELECT username FROM dba_users WHERE user_id = A.USER_ID) USERNAME,

            A.SESSION_ID SID,A.SESSION_SERIAL# SERIAL#,s.status,

           A.SQL_ID,A.SESSION_STATE,A.EVENT,A.TIME_WAITED,A.BLOCKING_SESSION,

            A.BLOCKING_SESSION_STATUS,A.CURRENT_BLOCK#,A.CURRENT_FILE#,

            D.OWNER Object_owner,D.OBJECT_NAME,D.OBJECT_TYPE

    FROM GV$ACTIVE_SESSION_HISTORY a, dba_objects d,gv$session s

   WHERE A.SESSION_TYPE = 'FOREGROUND' AND A.CURRENT_OBJ# = D.OBJECT_ID

   and a.SESSION_ID=s.sid

    -- and s.sid=100

    --and s.status='ACTIVE'

    --and username='ORCL'

    --and d.object_name='IDX_EMPLOYEE_PK'

    --a.sql_id='7u6pfhkqnp641'

 ORDER BY A.SAMPLE_TIME DESC

Bu sql aktif sessionların control edilebilimesi için gayet iyidir ayrıca sid,USERNAME, sql id bazında ash verilerine filtrede uygulanabilir. Aynı şekilde farklı versiyonunu da kullanmak geçmişe dönük veriler istenirken faydalı olabilir.

SELECT A.SAMPLE_ID,A.SAMPLE_TIME,S.INST_ID,A.SESSION_ID SID,A.SESSION_SERIAL#,s.status,     

(SELECT username FROM dba_users WHERE user_id = A.USER_ID) USERNAME,  A.SQL_ID,A.XID,A.SESSION_STATE,A.EVENT,A.TIME_WAITED,A.BLOCKING_SESSION,

            A.BLOCKING_SESSION_STATUS,A.CURRENT_BLOCK#,A.CURRENT_FILE#,

            D.OWNER Object_owner,D.OBJECT_NAME,D.OBJECT_TYPE

    FROM DBA_HIST_ACTIVE_SESS_HISTORY a, dba_objects d,gv$session s

   WHERE A.SESSION_TYPE = 'FOREGROUND' AND A.CURRENT_OBJ# = D.OBJECT_ID

   and a.SESSION_ID=s.sid --and s.status='ACTIVE'

    --and username='SSABATCH92'

    --and d.object_name='SEARCHSERVICES_SYNC_NSA'

    --a.sql_id='7u6pfhkqnp641'

 ORDER BY A.SAMPLE_TIME DESC

Bunlarla birlikte ash verilerine istatistiksel olarak ulaşmakda mümkündür.aşağıdaki sqllere göz atabilirsiniz.

--Son 1 dakika içindeki en çok active sqller

  SELECT sql_id,

         COUNT (*),

         ROUND (COUNT (*) / SUM (COUNT (*)) OVER (), 2) pctload

    FROM v$active_session_history

   WHERE sample_time > SYSDATE - 1 / 24 / 60 AND session_type <> 'BACKGROUND'

GROUP BY sql_id

ORDER BY COUNT (*) DESC;

--Son 1 dakikaiçindeki en çok active sqller

  SELECT sql_id,

         COUNT (*),

         ROUND (COUNT (*) / SUM (COUNT (*)) OVER (), 2) pctload

    FROM v$active_session_history

   WHERE sample_time > SYSDATE - 1 / 24 / 60 AND session_type <> 'BACKGROUND'

GROUP BY sql_id

ORDER BY COUNT (*) DESC;

 --Son 15 dakika içindeki en önemli bekleme olaylarýnýn tespit edilmesi;

 select event,

 sum(wait_time + time_waited) total_wait_time

 from v$active_session_history

 where sample_time between (sysdate - (30/2880)) and sysdate

 group by event order by total_wait_time desc;

--Son 15 dakika içinde hangi kullanýcýlarýn en çok bekleme olayýna sebebiyet verdiðinini tespit edilmesi;

 select s.sid, s.username,

 sum(a.wait_time + a.time_waited) total_wait_time

 from v$active_session_history a,

 v$session s

 where a.sample_time between sysdate - 30/2880 and sysdate

 and a.session_id=s.sid

 group by s.sid, s.username

 order by total_wait_time desc;

--Son 15 dakika içinde en fazla bekleme olayýna sebebiyet veren SQL komutlarýnýn tespit edilmesi;

 select a.user_id,u.username,s.sql_text,

 sum(a.wait_time + a.time_waited) total_wait_time

 from v$active_session_history a,

 v$sqlarea s, dba_users u

 where a.sample_time between sysdate - 30/2880 and sysdate

 and a.sql_id = s.sql_id

 and a.user_id = u.user_id

 group by a.user_id,s.sql_text, u.username;

--Hem cpu, hem IO hemde beklemeleri sql id bazýnda veriyor.

 select

 ash.SQL_ID ,

 sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",

 sum(decode(ash.session_state,'WAITING',1,0)) -

 sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,

 sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,

 sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"

 from v$active_session_history ash,

 v$event_name en

 where SQL_ID is not NULL and en.event#=ash.event#

 group by sql_id

 order by sum(decode(session_state,'ON CPU',1,1))

Şimdi bir Ash raporunda bakılması öncelikle bölümler nerelerdir  ve performans tuning açısından bir ash raporu inceleyelim.Şekil 1 de ash raporunun yapısı şematize edilmiştir.

Şekil 1 : Ash Rapor yapısı

Şekil 2 : Ash Rapor Görünümü

Şekil 2 de örnek bir ash rapor bulunmaktadır. Linkler deki kısımlar ise ash raporda bulunabileecek bilgileri içeririr. Şimdi bu kısımlarda ne şekilde bilgiler bulunur inceleyelim.

Top Events: Sesionlara ait en fazla bekleme olaylarını raporlar.Kullanıcı eventleri , background eventler ve eventlere ait parametre değerleri bu bölümde incelenebilir. Raporda ki en önemli ksısım denebilir.Eğer bu kısımda beklenmedik bir wait event 1. Yada 2. Sırada görüntüleniyorsa tuning işlemine bu wait event ten başlanması en doğru yöntemdir.

Load Profile: Session bazında yük dağılımını ulabileceğiniz kısımdır.Bu kısımda Servis bazında veya sql tipi olarak en fazla yükse sebep olan işlemleri gözlemleyebilir ve tuning işlemi için başlangıç noktası belirleyebilirsiniz.

Top Sql, PL/SQL, Object,FileSession:En fazla yüse sahip sql PL/SQL vs. işlemleri yada sessionları gözlemleyebileceğimiz kısımlardır.Örneğin top session2s kısımında activity % değeri en yüksek olan session db ye en fazla yük oluşturan yada db yi en fazla meşgul eden session dır denebilir. Aynı kıısmda bu sessiona ait event bölümü nden ipucu alınarak bu session tune edilebilir.Mesela bu kısımda faza activity gösteren session buffer busy wait event ile karşımıza çıkıyorsa bu durumda bu session memoriden diske yazarken dirty buffer bekliyor demektir.Böylece tune ederken ğere specific objeler bu event e takılıyorsa obje parametreleri ile tuning yapılabilir.Yada Buffer cache advisor ile memory durumu sorgulanabilir. Daha detaylı çözüm yöntemleri için buffer busy wait event nedir ve nasıl tune edilir? adlı yazımıza göz atabilirsiniz.

Activity Over Time:Ash raporu alınan zaman dilimi için belli periyotlarda en fazla karşılaşılan 3 adet bekleme olayı hakkında bilgi sunulur. Activity over time kısmında toplam ash zaman aralığı 10 eşit periyoda bölünür.Bu periyotlarda slot count ve event count kısımları önemlidir.Slot count active session sayısı ile lişkilidir. Event count ise eventin gözülme sıklığını gözterir.Bu değerlerdeki ani yükselişler performans probleminin temel sebebi olarak bilinir.

Bir sonraki performans Troubleshooting yazımız ADDM reports da görüşmek üzere.Ayırdığınız vakit karşılığında okuduklarınızın faydalı olmasını dileriz.

                                                                                                                Özcan YILDIRIM