Aşırı CPU tüketen UNIX Proses ID sinden ilgili SQL komutlarının tespit edilmesi

Sistemin yavaşladığı zamanlarda bakılması gereken noktalardan biriside UNIX/Linux proses kullanımında aşırı kaynak tüketen prosesin ID sini bulmak, bunun Oracle SQL prosesi olup olmadığının tespit edilmesi, eğer SQL prosesi ise bunun hangi SQL komutu olduğunun bulunarak çalıştırma planında ters giden noktaların analiz edilmesidir.

Unix ve Linux ortamlarda, kaynak kullanan proseslerin ID’si(PID) tespit edilince bu PID nin Oracle veritabanı prosesi ile ilişkili olup olmadığıda anlaşılabilmektedir. Bu süreç en baştan sona doğru aşağıdaki gibidir;

1.    Unix/Linux sistem komutu ile kaynak kullanan prosesler ve ilişkili ID leri belirlenmelidir.

2.    Veritabanı ile ile ilişkili prosesler belirlenmelidir.

3.    Bu proses hakkındaki detaylar veritabanı data dictionay görünümlerinden ortaya çıkarılmalıdır.

4.    Eğer bu proses bir SQL komutu ise, bunun detayları alınmalıdır.

5.    İlgili SQL komutu için çalıştırma planı oluşturulmalıdır.

Bununla ilgili bir örnek göstermek gerekirse, ps komutu ile işletim seviyesinde en çok CPU tüketen sorguları bulmak istediğimizi varsayarak:

$ ps -e -o pcpu,pid,user,tty,args|grep -i oracle|sort -n -k 1 -r|head

Aşağıda bu komut sonucu Oracle ile ilişkili prosesler, CPU kullanım yüzdesi bazında ayıklanmış olarak yer almaktadır.

18.2 10634 oracle ? oracleMVXLIVE (DESCRIPTION=(LOCAL=YES) (ADDRESS=(PROTOCOL=beq)))

0.2 4362    oracle ? oracleMVXFINETR (LOCAL=NO)

0.6 2743    oracle ? ora_dia0_MVXSTKHLD

0.5 8726    oracle ? ora_dia0_MVXSEM

0.5 4162    oracle ? ora_dia0_MVXSTK

0.4 5354    oracle ? ora_dia0_RSDYRD

0.3 3221    oracle ? ora_mmnl_MVXRD

...

Yukardaki sonuçtan ilk sıradaki işletim sistemi prosesinin aşırı oranda CPU tükettiği görülmektedir(%18.2). Bu prosesin ID’si 10634 ve ismi oracleMVXLIVE’dir.Bu proses isminden bunun MVXLIVE veritabanı ile ilişkili olduğu anlaşılmaktadır.

Bunun ne tür bir Oracle prosesi olduğu data dictionary görünümlerinden bir sorgu ile aşağıdaki gibi elde edilebilecektir:

SELECT

'USERNAME : ' || s.username || CHR(10) ||

'SCHEMA : ' || s.schemaname || CHR(10) ||

'OSUSER : ' || s.osuser || CHR(10) ||

'PROGRAM : ' || s.program || CHR(10) ||

'SPID : ' || p.spid || CHR(10) ||

'SID : ' || s.sid || CHR(10) ||

'SERIAL# : ' || s.serial# || CHR(10) ||

'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) ||

'MACHINE : ' || s.machine || CHR(10) ||

'TYPE : ' || s.type || CHR(10) ||

'TERMINAL : ' || s.terminal || CHR(10) ||

'SQL ID : ' || q.sql_id || CHR(10) ||

'SQL TEXT : ' || q.sql_text

FROM v$session s

,v$process p

,v$sql q

WHERE s.paddr = p.addr

AND p.spid = 10634

AND s.sql_id = q.sql_id(+);

Bu sorgu sonucunda ilgili işletim sistemi proses ID’sine bağlı tüm detaylar aşağıdaki gibi raporlanmaktadır.

USERNAME    : MVXFIN

SCHEMA      : MVXFIN

OSUSER      : oracle

PROGRAM     : sqlplus@mvxlive (TNS V1-V3)

SPID        : 10634

SID         : 367

SERIAL#     : 30653

KILL STRING : '367,30653'

MACHINE     : mvxdb02

TYPE        : USER

TERMINAL    : TM162

SQL ID      : yd8s9265fmnug

SQL TEXT    : SELECT "FS1"."PLANTATION_ID","FS1"."PRODUCT_VARIETY_ID"

,"A1"."LOT_ID","A1"."LOT_ENTRY_TL_ADDRESS","FS1"...

Yukardaki sonuç bu prosesin 367 veritabanı SID sine sahip 30653 seri numaralı bir SQL*Plus prosesi olduğunu işaret etmektedir.

Bu bilgi, ALTER SYSTEM KILL SESSION  komutu ile bu prosesi veritabanında sonlandırmak için yeterli olabileceği gibi, bu prosesin çalışan bir SQL komutu olmasından dolayı, bu SQL komutuna ait çalıştırma planı oluştururak daha ileri detayların tespit edilmesinde de kullanılabilmektedir. Aşağıda bu SQL ID sinden çalıştırma planı bilgileri alınmaktadır.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('yd8s9265fmnug')));

Bu komut ile ilgili SQL ID sinin çalıştırma planından örnek bir bölüm aşağıda yer almaktadır.

SQL_ID yd8s9265fmnug, child number 0

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

SELECT "FS1"."PLANTATION_ID","FS1"."PRODUCT_VARIETY_ID"

,"A1"."LOT_ID","A1"."LOT_ETY_CROP_NAME","FS1"."REGISTRATION_STATUS","FS1"."GRP_ID","FS1"."GRW_VARIANCE","FS2"."CROP_FLG","FS2"."LOT_INSTC_ID","FS2"."TAG_PLT

Plan hash value: 2367289305

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

| Id|Operation |Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0|SELECT STATEMENT    | | | | 72536 (100)| |

| 1| NESTED LOOPS       | | | | | |

| 2| NESTED LOOPS       | | 1 |688 | 72536 (5)| 00:16:00 |

| 3| NESTED LOOPS OUTER | | 1 | 651 | 64975 (5)| 00:16:00 |

| 4| NESTED LOOPS OUTER | | 1 | 643 | 64973 (5)| 00:16:00 |

| 5| NESTED LOOPS       | | 1 | 509 | 72531 (5)| 00:16:00 |

| 6| NESTED LOOPS OUTER | | 1 | 440 | 64971 (5)| 00:16:00 |

| 7| NESTED LOOPS       | | 1 | 367  | 72528 (5)| 00:16:00 |

| 8| NESTED LOOPS OUTER | | 1 | 277 | 64967 (5)| 00:16:00 |

| 9| NESTED LOOPS       | | 1 | 269 | 72525 (5)| 00:16:00 |

|*10| TABLE ACCESS FULL |VARIETY_ENTR | 1 | 166 | 72523 (5)| 00:16:00 |

Bu çalıştırma planı sonucu ile SQL komutunda nerelerde düzeltme yapılması gerekeceğiı ve CPU tüketiminin böylece nasıl kabul edilir seviyeye çekileceği ile ilgili yararlı bilgilere erişilmiş olacaktır.