Sorguların gerçek zamanlı izlenmesi

Oracle 11g R1 sürümünden itibaren uzun süren sorgular ya da /* MONITOR*/ şeklindeki hint ile çalıştırdığımız sorgular otomatik olarak izlenmektedir. İzleme sırasında her saniye SQL execution ile ilgili istatistikler toplanmaktadır.

İzlemenin olması için CONTROL_MANAGEMENT_PACK_ACCESS parametresinin değeri ‘DIAGNOSTIC+TUNING’ , STATISTICS_LEVEL parametresinin değeri ALL ya da TYPICAL olması gerekmektedir.

Aşağıdaki durumlar olduğunda izleme otomatik başlayacaktır.

• Sorgu 5 saniyeden fazla CPU zamanı tükettiyse
• Sorgu 5 saniyeden fazla I/O zamanı tükettiyse
• Sorgu parallel olarak çalıştırıldıysa
• Sorgu /*+ MONITOR */ hint’i verilerek çalıştırıldıysa

Bir sorgunun izlenmesini istiyorsak /*+ MONITOR */ ile izlenmemesini istiyorsak /*+ NO_MONITOR */ hint’i ile çalıştırabiliriz.

İzlenen sorgular ile ilgili bilgiler V$SQL_MONITOR ve V$SQL_PLAN_MONITOR görüntülerinde bulunmaktadır.

V$SQL_MONITOR : Oracle tarafından çalıştırılan (veya izlenen) SQL ifadelerini görüntüler. Gözlenlenen sogru her çalıştığında burada bir kayıt oluşur.
V$SQL_PLAN_MONITOR : V$SQL_MONITOR’de bulunan her SQL ifadesi için plan düzeyinde izleme istatistiklerini görüntüler

İzleme ile ilgili bazı gizli parametreler bulunmaktadır :
_sqlmon_max_plan – İzlenebilecek maksimum plan girişi sayısı. Varsayılan değer CPU başına 20 şeklindedir.
_sqlmon_max_planlines – Bir planın izlenemediği plan çizgisi sayısı Varsayılan değeri 300 şeklindedir.

İzleme sonuçlarını görüntülemek için bu gizli parametrelerin değerlerini arttırmak faydalı olacaktır.

Aşağıdaki gibi oturum bazlı parametre değerlerini değiştirilip, sorgulama yapılabilir.

ALTER SESSION SET "_SQLMON_MAX_PLAN"=4020;
ALTER SESSION SET "_SQLMON_MAX_PLANLINES"=4000;

SELECT 
  sql_exec_id,
  sql_plan_hash_value,
  mon.process_name,
  mon.status, 
  mon.sid,
  mon.px_qcsid,
  round((LAST_REFRESH_TIME - FIRST_REFRESH_TIME)*24*60) AS elapsed_min,
  FIRST_REFRESH_TIME,
  LAST_REFRESH_TIME,
  px_maxdop,
  px_servers_requested,
  mon.sql_text,
  mon.* 
FROM v$SQL_MONITOR mon WHERE sql_id = '2kspy7v98wv35'
ORDER BY 
mon.sql_exec_id,
mon.sql_plan_hash_value,
mon.process_name;

İzlenen veriler ile , V$SQL_MONITOR de bulunan bir sorguya ait rapor oluşturmak için dbms_sqltune.report_sql_monitor fonksiyonu kullanılabilir. Bu fonksiyonu belirli bir sorgu için sql id bilgisi ile aşağıdaki gibi çalıştırabiliriz.

SQL Monitoring Report

SQL Text
------------------------------
DELETE FROM AKTARIM_KUYRUK WHERE ID = :B1

Global Information
------------------------------
 Status                                 :  DONE                
 Instance ID                            :  3                   
 Session                                :  ADURUOZ (7492:36208) 
 SQL ID                                 :  2kspy7v98wv35       
 SQL Execution ID                       :  56701488            
 Execution Started                      :  11/27/2018 12:11:28 
 First Refresh Time                     :  11/27/2018 12:11:31 
 Last Refresh Time                      :  11/27/2018 12:11:31 
 Duration                               :  3s                  
 Module/Action                          :  aktarim.exe/-    
 Service                                :  ORCL                 
 Program                                :  aktarim.exe      
 PLSQL Entry Ids (Object/Subprogram)    :  233559,1            
 PLSQL Current Ids (Object/Subprogram)  :  233559,1            

Binds
========================================================================================================================
| Name | Position |     Type     |                                        Value                                        |
========================================================================================================================
| :B1  |        1 | VARCHAR2(32) | 5L0DQRO028H2WGPO                                                                    |
========================================================================================================================

Global Stats
===================================================================
| Elapsed |   Cpu   |    IO    | Cluster  | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes |
===================================================================
|    2.96 |    0.76 |     2.13 |     0.07 |   194K | 6914 |  54MB |
===================================================================

SQL Plan Monitoring Details (Plan Hash Value=2154625852)
===================================================================================================================================================================================
| Id |       Operation        |           Name           |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |           Activity Detail           |
|    |                        |                          | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |             (# samples)             |
===================================================================================================================================================================================
|  0 | DELETE STATEMENT       |                          |         |      |         1 |     +3 |     1 |        0 |      |       |          |                                     |
|  1 |   DELETE               | AKTARIM_KUYRUK    	 |         |      |         1 |     +3 |     1 |        0 | 1172 |   9MB |    33.33 | cell single block physical read (1) |
|  2 |    PARTITION RANGE ALL |                          |      36 |   91 |         1 |     +3 |     1 |    13204 |      |       |          |                                     |
|  3 |     INDEX RANGE SCAN   | IX_AKTARIM_KUYRUK 	 |      36 |   91 |         3 |     +1 |    30 |    13204 | 5742 |  45MB |    66.67 | cell single block physical read (2) |
===================================================================================================================================================================================