Time Spent Waiting Due to Locking
- Kilitleme sorunları yüzünden oturumlar tarafından harcanan toplam süreyi belirlemek istiyorsunuz.
- Bir tablonun satırlarının kilitlenmesinin neden olduğu beklemeleri tanımlamak (ve ölçmek) için aşağıdaki sorguyu kullanabilirsiniz.
Sorgu, bekleme olaylarını bekleme zamanına göre sıraladığı için instance daki bekleme durumlarının çoğunu hangi bekleme olayları türleri oluşturduğunu hızla görebilirsiniz.
SQL> select wait_class, event, time_waited / 100 time_secs
from v$system_event e
where e.wait_class <> 'Idle' AND time_waited > 0
union
select 'Time Model', stat_name NAME,
round ((value / 1000000), 2) time_secs
from v$sys_time_model
where stat_name NOT IN ('background elapsed time', 'background cpu time')
order by 3 desc;
WAIT_CLASS EVENT TIME_SECS
------------ --------------------------- -----------
System I/O log file parallel write 45066.32
System I/O control file sequential read 23254.41
Time Model DB time 11083.91
Time Model sql execute elapsed time 7660.04
Concurrency latch: shared pool 5928.73
Application enq: TX - row lock contention 3182.06
…
- Bu örnekte bekleme olayı enq: TX - row lock contention, row lock enqueue wait events olaylarından dolayı toplam süreyi ortaya koymaktadır.
Shared pool latch events, Concurrency wait class ın altında sınıflandırılır , enqueue TX - row lock contention event olayı Application class wait event olarak sınıflandırılır.
Nasıl Çalışır:
- Yukarıdaki sorgu, çeşitli bekleme olaylarından dolayı beklenen toplam süreyi göstermek için V$SYSTEM_EVENT ve V$SYS_TIME_MODEL viewleri joinlenir. Bizim durumumuzda,"enqueue locking" nedeniyle beklenen toplam süreyle ilgileniyoruz.
Belirli bir oturumun beklediği toplam süreyle ilgileniyorsanız, oturumların bekleme durumunun ne kadar uzun olduğunu öğrenmek için birkaç farklı V$ view kullanabilirsiniz, ancak V$SESSION viewini kullanmanızı öneririz, çünkü blocking ve blocked oturumların çeşitli yararlı niteliklerini gösterir. Bir oturumun başka bir oturum tarafından ne kadar süreyle engellendiğini öğrenmenin bir yolu da aşağıdaki sorgu:
SQL>select sid, username, event, blocking_session,
seconds_in_wait, wait_time
from v$session where state in ('WAITING');
- Sorgu, bekleme durumundaki SID 81 olan session hakkında aşağıdakileri ortaya çıkarır:
SID : 81 (this is the blocked session)
username: SH (user who's being blocked right now)
event: TX - row lock contention (shows the exact type of lock contention)
blocking session: 68 (this is the "blocker")
seconds_in_wait: 3692 (how long the blocked session is in this state)
- Sorgu, SID 81 olan user SH'nin yaklaşık bir saat (3,692 saniye) engellendiğini ortaya koymaktadır.
Kullanıcı SH şu anda oturum 68 tarafından kilitlenmiş bir tabloda bir kilidi beklemede olarak gösterilir.
V$SESSION viewini, blocking ve blocked sessionları belirlemek için son derece yararlı olsa da,
kullanıcı oturumunda yer alan tablonun engellenmesine sebep olan SQL deyimini size söyleyemez.
Çoğu zaman, bir blocking durumuna dahil olan SQL deyimini tanımlamak, ifadenin kilitleme davranışına tam olarak neden olduğunu bulmakta yardımcı olur. Gerçek SQL deyimini öğrenmek için, burada gösterildiği gibi V$SESSION ve V$SQL viewlerinin joinlenmesi gerekir.
SQL> select sid, sql_text
from v$session s, v$sql q
where sid in (68,81)
and (
q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id)
SID SQL_TEXT
------------- -----------------------------------------------------
68 select * from test for update
81 update hr.test set name='nalapati' where user_id=1111
-Sorgunun çıktısı, SELECT 68 ... FOR UPDATE deyimini kullanarak oturum 68 tarafından kilitlenmiş bir tablodaki bir satırı güncelleştirmeye çalıştığı için oturum 81 engellendiğini gösterir. Bu gibi durumlarda, başka bir oturum tarafından engellenen kullanıcı oturumlarının uzun bir sırasını bulursanız, diğer oturumların çalışmalarını işleyebilmesi için blocking sessionu sonlandırmanız gerekir. Bu durumlarda veritabanında yüksek active session sayısı da görürsünüz.
Blocking sessionun kesilmesi, "enqueue locks" un neden olduğu çekişmeyi çözmek için size derhal bir çözüm sunar.
Daha sonra, bu durumları önlemek için blockların neden oluştuğunu araştırabilirsiniz.
- Herhangi bir oturum için, aşağıdaki sorguyu vererek, her wait class için bir oturum tarafından beklenen toplam süreyi belirleyebilirsiniz:
SQL> select wait_class_id, wait_class,
total_waits, time_waited
from v$session_wait_class
where sid = <SID>;
- Örneğin, bu oturumun, application wait classda (bu sınıf için wait class ID 4217450380 ) çok fazla sayıda bekleme yaptığını bulursanız, V$SYSTEM_EVENT viewini kullanarak aşağıdaki sorguyu verebilirsiniz;
SQL> select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and e.wait_class_id = 4217450380;
EVENT TOTAL_WAITS TIME_WAITED
-------------------- ------------ ------------
enq: TM - contention 82 475
- Örneğimizde, application classdaki (ID 4217450380) beklemeler, bekleme olayı enq:TM - contention dan kaynaklanmaktadır.
V$EVENT_HISTOGRAM viewini, instance ı başlattığınızdan beri belirli bir bekleme olayı için oturumların kaç kez beklediğini öğrenmek için de kullanabilirsiniz.
Kapatılan enqueue lock waits de bekleme süresi modelini bulmak için aşağıdaki sorguyu kullanabilirsiniz:
SQL> select wait_time_milli bucket, wait_count
from v$event_histogram
where event = 'enq: TX - row lock contention';
- Kilitleme davranışından ötürü yüksek miktarda "enqueue waits" genellikle hatalı uygulama tasarımından kaynaklanmaktadır.
- Bu dört DML locking contention a neden olabilir: INSERT, UPDATE, DELETE ve SELECT FOR UPDATE. INSERT deyimleri bir kilit beklemektedir, çünkü başka bir oturum aynı değeri olan bir satır eklemeye çalışıyor. Bu, genellikle, key üreten uygulamanın primary keyi veya unique constrainti olan bir tablonuz olduğunda ortaya çıkar. Bu tür kilitleme durumlarından kaçınmak için key değerlerini oluşturmak için onun yerine bir Oracle sequence kullanın. Kilitler nedeniyle oturum engellemeyi ortadan kaldırmak için NOWAIT seçeneğini bir SELECT FOR UPDATE deyimi ile belirtebilirsiniz.
Sessionlar bir UPDATE veya DELETE deyimi verdiklerinde, oturumlar tarafından kilitler için beklemekten kaçınmak için SELECT FOR UPDATE NOWAIT deyimini de kullanabilirsiniz. SELECT FOR UPDATE NOWAIT deyimi satırı beklemeden kilitler.