Blog ' da Ara

Loading

30 Nisan 2012

Enqueue Waits & Locks Nedir Ve Nasıl Tune Edilir?


            Enqueue bir oracle shared memory (paylaşımlı memory) birimidir.enqueue ile database kaynaklarına gelen erişimler serileşitirlir.Bir proses Kaynağa erişirken, yapacağı işlemi bitirinceye kadar bu kaynağı lock konumunda tutar. Yani başka proseslerin erişmesine izin vermez. Gelen diğer proseslerde  bu lock durumunun bitmesini bekler.Lock oluşturan prosesin yaptığımı işleme görede lock tipi şekillenir. Kaynak locklı durumda iken gelen prosesler bir kuyruğa girerler. İşte bu tip locklara enqueue lock denir. Genel olarak gösterimi “enq: .. contention” gibidir.Biz bu yazımızda en yaygın görülen aşağıdaki enqueue tipleri detaylı inceleyeceğiz ve diğer enque tiplerini de tanımlayacağız.
            TX       à Transaction Locks
            TM      à DML Locks
            ST        à Space Management Enqueue

            Bekleme Parametreleri
            P1:Enqueue adı ve bekleyen prosesin beklediği mode.Aşağıdaki sql ile v$session_wait view den enque tipini öğrenebilirsiniz.
                SELECT sid,
              CHR (BITAND (p1, -16777216) / 16777215)
              || CHR (BITAND (p1, 16711680) / 65535)
                 "Name",
              (BITAND (p1, 65535)) "Mode"
             FROM v$session_wait
 WHERE event = 'enqueue';
            P2:Lock Kaynağını belirtir. V$lock tablosundaki ID1 kolonu ile aynıdır.v$lock.id1
            P3:Lock Kaynağını belirtir. V$lock tablosundaki ID2 kolonu ile aynıdır.v$lock.id2


            V$lock Tablosundaki ID1 ve ID2 yorumlanması enqueue tipine gore değişir.Aşağıdaki sql, sql tipine gore p1 ve p2 parametrelerinin yorumlaması kkonusunda ipucu verecektir.
SELECT *
  FROM v$event_name
 WHERE name LIKE '%enq: %'

            Tablo1 de bazı sık görülen enueue tiplerinin p1 ve p2 parametreleri için detaylı açıklama mevcuttu.
           

Lock type
TX
ID1
ID1 rollback segment ve  the slot numarasını belirtir. Değerler  V$TRANSACTION viewında XIDUSN ve  XIDSLOT kolonlarında görülebilir.
ID2
Rollback segment wrap veya  sequence numarasıdır. Değerler  V$TRANSACTION viewında XIDSQN kolonunda görülebilir.
Lock type
TM
ID1
ID1 , tablonun object ID sini belirtir, DBA_OBJECTS.OBJECT_ID kolonu ile aynı değerdir.
ID2
Daima 0 dır.
Lock type
TS
ID1
Tablespace numarasıdır. TS$.TS# kolonu ile aynı değeri gösterir.
ID2
Relatif Database Block Address (DBA) i gösterir.
Lock type
JQ
ID1
Daima 0 dır.
ID2
Job number (Job numarasını) gösterir.
Lock type
MR
ID1
Tamp fileler dahil Data file ID dir.
ID2
Daima  0 dir.
Lock type
RT
ID1
Redo thread numarasıdır.
ID2
Always 0.
Tablo 1: Bazı enqueue tipleri ve parametrelerin yorumlanması
                Eğer uygulama Paralel DML işlemlerini çok yoğun kullanıyorsa ORA-00052: "maximum number of enqueue resources exceeded" hatasını alabilirsiniz ve bu durumda bu resource  değerini yükseltmeniz gerekebilir.Ancak Default değerler genelde yeterlidir. Aşağıdaki sql ile enqueue ile alakalı resourceları ve değerlerini gözlemleyebilirsiniz.
                SELECT *
  FROM v$resource_limit
 WHERE resource_name IN
          ('enqueue_resources',
           'enqueue_locks',
           'dml_locks',
           'processes',
           'sessions');
                Enqueue Type ve Mode Belirleme
                Enqueue tipini ve modunu v$session_wait view P1 kolonundan analayabiliriz.Bunun için aşağıdaki gibi bir sql kullanmamız gerekir.
SELECT sid,
       event,
       p1,
       p1raw,
       CHR (BITAND (P1, -16777216) / 16777215)
       || CHR (BITAND (P1, 16711680) / 65535)
          TYPE,
       MOD (P1, 16) "MODE"
  FROM v$session_wait
 WHERE event = 'enqueue';
                Eğer bir kaynak paylaşımlı olarak birden çok proses tarafından aynı anda kullanılıyorsa oracle bu kullanımı lock mode belirleyerek sağlar.Aşağıdaki tablo 1 de lock mode ile ilgili bilgileri inceleyebilirsiniz. Tablo 2 de ise sql query lere göre uygunluk gösterir.
Mode
Açıklama
0
None
1
Null (N)
2
Row-Share (RS), ayrıca Subshare lock (SS) olarakda bilinir.
3
Row-Exclusive (RX), ayrıca Subexclusive lock (SX) olarakda bilinir.
4
Share (S)
5
Share Row Exclusive (SRX), yada Share-Subexclusive lock (SSX)
6
Exclusive (X)
                          Tablo 2 : Lock Mode Bilgileri
               
Sql Query
Mode
N
RS
RX
S
SRX
X
SELECT
N
Yes
Yes
Yes
Yes
Yes
Yes
SELECT … FOR UPDATE
RS
Yes
Yes*
Yes*
Yes*
Yes*
No
lock table in row share mode
RS
Yes
Yes
Yes
Yes
Yes
No
INSERT
RX
Yes
Yes
Yes
No
No
No
UPDATE
RX
Yes
Yes*
Yes*
No
No
No
DELETE
RX
Yes
Yes*
Yes*
No
No
No
lock table in row exclusive mode
RX
Yes
Yes
Yes
No
No
No
lock table in share mode
S
Yes
Yes
No
Yes
No
No
lock table in share row exclusive mode
SRX
Yes
Yes
No
No
No
No
lock table in exclusive mode
X
Yes
No
No
No
No
No
* Yes burada eğer paylaşılan kaynak başka bir row lock tarafından tutulmuyorsa paylaşımın mümkün olduğunu gösterir.

                Genel Enqueue Tipleri ve Çözüm Yöntemleri
                Enqueue beklemelerinin sebebi , enqueue tipine göre değişiklik gösterir.Aşağıdaki sql ile v$enqueue_stat viewina bakıp hangi enqueue tipinde sistem çok bekleme oluşturmuş görebiliriz.
  SELECT *
    FROM v$enqueue_stat
   WHERE cum_wait_time > 0
ORDER BY inst_id , cum_wait_time desc;
                Aşağıda en yoğun karşılaşılan enqueue tipleri ile ilgili oluşabilecek sorunlar ve çözüm yöntemleri belirtilmiştir.
                TX Enqueue -Mode 6 (TX row lock Contention) : En genel TX enqueue  tipidir. TX-Row lock contention olarakda bilinir. İsminden de anlaşılacağı üzere row bazında bir contentiondur. Bir transaction bir row u update etmeye çalışırken aynı row başka bir transaction tarafından tutuluyorsa oluşur. Bu bir uygulama problemidir. Burada olması gereken söz konusu row u tutan transactionın commit yada rollback yaparak row ‘u bırakmasıdır. Ancak row’u tutan transaction kill edilirse rollback edilmiş olur.
                Mode 6 TX enqueue , v$lock view’ında aşağıdaki gibi gözükür.

ADDR     KADDR    SID TY    ID1    ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ------ ----- ------- ----- -----
A3950688 A395069C  10 TM 188154      0     3       0     3     0

A304E2A0 A304E2B0  10 TX  65585 147836     0       6     3     0

01AD23D4 01AD24A4  20 TX  65585 147836     6       0    10     1
                Databasede TX lock gördüğünüzde ilk yapmanız gerekn blocker sessionu bulmak ve bu sessionun alive olup olmadığını kontrol etmektir.Ayrıce bu session alive olduğu halde parent session dead olmuş olabilir. Bunada dikkat etmeniz gerekir.Sonrasında Uygulama tarafında sessionın commit yada rollback yapılması gerektiğini belirtirsiniz. Session canlı değilse zaten kill edilebilir ancak canlı ise uygulama tarafında onay alınmadan kill edilmesi uygulama açısından doğru olmaz.
                TX enque için bloke eden session aşağıdaki sql ile bulabilirsiniz.
SELECT    /*+ ordered */
        a.sid blocker_sid,
         a.username blocker_username,
         a.serial#,
         a.logon_time,
         b.TYPE,
         b.lmode mode_held,
         b.ctime time_held,
         c.sid waiter_sid,
         c.request request_mode,
         c.ctime time_waited
    FROM v$lock b, v$enqueue_lock c, v$session a
   WHERE a.sid = b.sid
     AND b.id1 = c.id1(+)
     AND b.id2 = c.id2(+)
     AND c.TYPE(+) = 'TX'
     AND b.TYPE = 'TX'
     AND b.block = 1
ORDER BY time_held, time_waited;

                Ayrıca TX lock hangi obje üzerinde gerçekleşiyor aşağıdaki sql ile bulabilirsiniz.
SELECT c.sid waiter_sid, a.object_name, a.object_type
  FROM dba_objects a, v$session b, v$session_wait c
 WHERE (a.object_id = b.row_wait_obj# OR a.data_object_id = b.row_wait_obj#)
   AND b.sid = c.sid
   AND CHR (BITAND (c.P1, -16777216) / 16777215)
       || CHR (BITAND (c.P1, 16711680) / 65535) = 'TX'
   AND c.event = 'enqueue';
TX Enqueue – Mode 4: Mode 4 deki TX enqueue 3 şekilde incelenebilir.Bunlar;
·         ITL (Interested Transaction List) Shortage
·         Unique Key Enforcement
·         Bitmap Index Entry
Mode 4 enqueue v$lock viewinda aşağıdaki gibi görüntülenebilir.
ADDR     KADDR    SID TY    ID1    ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ------ ----- ------- ----- -----
8A2B6400 8A2B6414   8 TM   3172      0     3       0   248     0

89EF3A0C 89EF3A1C   8 TX 131147     13     0       4   248     0

                ITL Shortage (enq: TX – Allocate ITL entry):ITL , data blokları içindeki transaction slotları dır.Bir ITL slot’un ilk değeri INITRANS ile belirlenirken alabileceği en yüksek değer ise MAXTRANS ile belirlenir.Default olarak tablolar da ITL 1 indexlerde ise  dir.Her bir ITL default olarak 24 byte uzunluğundadır.USN.SLOT#.WRAP# formatında olup Transaction ID bilgisini gösterir.Herbir  DML transaction data yı değiştirmeden önce IT block içerisindeki ITL alanına ihtiyaç duyar.İşte Contention denilen yoğunluğa bağlı kaynak uyuşmazlığı, PCTFREE alanından ITL için yeterli boş alan bulunamamsından kaynaklanır.Bundan dolayı yeni transactionlar için ITL denilen gerekli alan mevcut transactionlar işini bitirmedikçe ortaya çıkmaz ve bekleme durumu ortaya çıkar.
                Aşağıda enq: TX-Allocate ITL entry enqueue contention oluştuğunda, file# ve block# bulabileceğiniz bir sql mevcuttur.
               
SELECT s.sid,
       s.serial#,
       s.username,
       s.sql_id,
       S.EVENT,
       O.OBJECT_NAME,
       O.OBJECT_TYPE,
       O.OWNER,
       S.ROW_WAIT_BLOCK# block#,
       S.ROW_WAIT_FILE# file#,
       D.FILE_NAME file_name,
       D.TABLESPACE_NAME
  FROM v$session s, dba_objects o, dba_data_files d
 WHERE S.ROW_WAIT_OBJ# = O.OBJECT_ID
   AND S.ROW_WAIT_FILE# = D.FILE_ID
   AND S.ROW_WAIT_OBJ# <> -1
   and S.EVENT='enq: TX - allocate ITL entry'
Sonrasında alter system dump datafile <file#> block <block#>; komutu ile problemli objenin dumpını alıp amnaliz edebiliriz.Analiz sonrasında aşağıdaki satırları dump dosyasında görebileceksiniz.
-- The ITL portion of a block dump
 Itl         Xid                                           Uba                        Flag  Lck Scn/Fsc
0x01 0x000a.051.0001fcf2 0x07ca2145.4e11.18 --U-    0  scn 0x070e.03df2f08
0x02 0x0005.049.00022d46 0x090618b7.5967.1c C---   0  scn 0x070e.03df2f6a
0x03 0x0012.008.0001244b 0x0580a510.26ac.0c --U-    0  scn 0x070e.03df2f7b
0x04 0x0014.00d.00012593 0x090d4f93.28d3.1e C---    0  scn 0x070e.03e08919
. . .
Bu şekilde blok’un Dump dosyasını almış olduk ve dump  flag kolonu --U-    şeklinde gördüğünüz ITL ler active ITL ler dir.Bu şekilde Aktif ITL lerin sayısına gorebilir ve Objeyi daha yüksek INITRANS değeri ile create ederek sorunu çözebilirsiniz.Ayrıca PCTFREE değerini artırmakda benzer bir çözüm sağlayacaktır.Aşağıdaki sql ilede en Fazla hangi objeler  üzerinde ITL beklemeleri yaşandığını gözlemleyebilirsiniz.
Unique Key Enforcement: Unique Key Enforcement, birden fazla sessionun anda unique key yada primary key bulunan kolona insert etmeye çalışması ile oluşur.Bu durum ‘enq: TX—row lock contention’ olarak da bilinir. Unique Key den dolayı oluşan TX enqueue bir uygulama problemidir ve uygulamanın transaction yönetimi ile çözülebilir.
ST – Enqueue:   Bir veritabanında sadece bir tane ST lock bulunur.St contention temporary managed tablespacelerde , birden Fazla sessionun aynı anda dinamik olarak alan almaya çalışmasından kaynaklanır.St Contentionun’I azaltmak için aşağıdaki işlemleri yapabilirsiniz.
·         Locally managed Tablespace kullanın.
·         Tüm temporary tablespceleri ‘CREATE TEMPORARY TABLESPACE TEMPFILE’ komutu ile tekrar oluşturun.
·         Dictionary managed tablespacelerde extend next extend sizesini artırmakda contention çözmesinize yardımcı olabilir.
TM – Enqueue: Indexlenmemiş  foreign key kolonları TM –Enqueue  lerin en buyuk sebebidir. Ayrıca bir tablo share modda yada daha üst seviye bir modda aynı anda çalışan DML işlemleri tarafından doğrudan lock edilmişse de TM enqueue görüntülenebilir. Çözüm için lock oluşturan dml işleminin commit yada rollback yapmasını ve uygulama tarafından kullanılan third party vendor code ‘larının incelenmesi gerekir. Aşağıdaki sql ifade ile TM Lock olup olmadığını ve bu lock’I oluşturan sql ifadeyi ve session bilgilerini bulabilirsiziniz.
SELECT S.SID,
       S.SERIAL#,
       S.SQL_ID,
       S.USERNAME,
       S.OSUSER,
       O.OWNER,
       O.OBJECT_NAME,
       O.OBJECT_TYPE,
       l.TYPE,
       L.LMODE,
       P.SPID
  FROM gv$lock l,
       dba_objects o,
       gv$session s,
       gv$process p
 WHERE l.id1 = O.OBJECT_ID
   AND request > 0
   AND L.SID = S.SID
   AND S.PADDR = P.ADDR

                                                                                                                                Özcan YILDIRIM

0 YORUM:

Yorum Gönder

"Sorularınız ve Eleştirileriniz Değerlidir"