Bu gerçekten kapsamlı bir DBA aracı — RAC ortamında lock/wait analizi + plan tuning önerileri. Tek dosyada, menü tabanlı, cx_Oracle kullanan Python 3.6 uyumlu bir script.
Kısa özet — script neler yapıyor:
Bağlantı: Çalıştırdığınızda host/port/service_name(veya SID)/kullanıcı/şifre/SYSDBA sorar, cx_Oracle (yoksa oracledb thin mode) ile bağlanır. Python 3.6 uyumlu, f-string kullanılmadı.
Menü:
- Aktif sessionların wait event özet dağılımı (instance bazında,
gv$session) - Blocking Tree — kim kimi lock’lamış, RAC’ta cross-instance dahil,
blocking_session/blocking_instanceüzerinden recursive ağaç kurup ekrana basıyor - Bekleyen session’lar listesi — bir SID seçince o session’ın wait event’ini açıklıyor (neden bekliyor, olası nedenler, önerilen aksiyon)
- Belirli bir SID için tam detay: kilitlenen obje, blocker zinciri, kill komutu, çalıştırdığı SQL metni,
gv$sqlistatistikleri,DBMS_XPLAN.DISPLAY_CURSOR(...,'ALLSTATS LAST')ile execution plan (başarısız olursagv$sql_plan_statistics_allfallback’i), ve plan üzerinde otomatik tuning analizi (full scan, nested loop patlaması, sort/hash spill, yüksek maliyetli hash join vs.) - Hepsini tek raporda birleştiren tam rapor
Wait event bilgi tabanı: enq: TX, enq: TM, gc buffer busy, gc cr request, latch: cache buffers chains, buffer busy waits, log file sync, db file sequential/scattered read, direct path read(temp), cursor: pin S wait on X, library cache, row cache lock gibi en yaygın event’ler için açıklama + olası nedenler + önerilen aksiyon listesi var; tanınmayan event’ler için genel bir fallback mesajı veriyor.
Birkaç not:
cx_Oracle/oracledbkurulu olmalı (pip install cx_Oracle), Instant Client gerekebilir (thick mode için).- Kill komutlarını üretiyor ama otomatik kill yapmıyor — bilinçli olarak sadece öneriyor, siz karar veriyorsunuz.
- Plan analizindeki kurallar heuristic; kesin doğru/yanlış demiyor, dikkat çekmesi gereken noktaları işaretliyor.
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
oracle_lock_wait_analyzer.py
=============================================================================
Oracle 12c ve sonrasi (12c, 18c, 19c, 21c, 23ai) icin, RAC cluster'lara uygun
Session / Lock / Wait Event / SQL Tuning analiz araci.
Ozellikler:
1) Hangi session hangi session'i lock'lamis (blocking tree - RAC capraz instance)
2) Hangi session bir lock yuzunden bekliyor (enq: TX, enq: TM, vb.)
3) Hangi session hangi wait event yuzunden bekliyor (gc buffer busy, latch,
buffer busy waits, log file sync, direct path read, vb.) + aciklama + oneri
4) Bekleyen/blocklayan sessionlarin calistirdigi SQL, execution plan
(dbms_xplan.display_cursor / gv$sql_plan_statistics_all) ve plan uzerinde
tuning onerileri (Full Table Scan, cardinality misestimate, missing index
ipuclari, nested loops patlamasi, vb.)
Python: 3.6+ uyumlu (f-string, typing kullanilmadi -> eski surumlerle de calisir)
Driver: cx_Oracle (yoksa oracledb thin mode denenir)
Kullanim:
python3 oracle_lock_wait_analyzer.py
Script calisinca sizden DB baglanti bilgilerini (host, port, service_name/sid,
kullanici, sifre, sysdba?) soracak, sonra interaktif bir menu sunacaktir.
=============================================================================
"""
import sys
import getpass
import re
from collections import defaultdict, namedtuple
# --------------------------------------------------------------------------
# DB Driver import (cx_Oracle oncelikli, yoksa python-oracledb thin mode)
# --------------------------------------------------------------------------
DB_DRIVER = None
try:
import cx_Oracle as oradriver
DB_DRIVER = "cx_Oracle"
except ImportError:
try:
import oracledb as oradriver
DB_DRIVER = "oracledb"
# thin mode icin ayrica bir client kutuphanesine gerek yok
except ImportError:
print("HATA: 'cx_Oracle' veya 'oracledb' modulu bulunamadi.")
print("Kurulum icin: pip install cx_Oracle (veya) pip install oracledb")
sys.exit(1)
# ==========================================================================
# 0) YARDIMCI FONKSIYONLAR
# ==========================================================================
def hr(char="-", length=100):
print(char * length)
def print_table(headers, rows, max_col_width=40):
"""Basit, bagimliliksiz text tablo yazdirici."""
if not rows:
print(" (kayit yok)")
return
str_rows = [[("" if c is None else str(c)) for c in row] for row in rows]
widths = []
for i, h in enumerate(headers):
col_vals = [len(r[i]) for r in str_rows] + [len(str(h))]
widths.append(min(max(col_vals), max_col_width))
def fmt_row(row):
cells = []
for i, val in enumerate(row):
val = val[:widths[i]]
cells.append(val.ljust(widths[i]))
return " | ".join(cells)
print(fmt_row(headers))
print("-+-".join("-" * w for w in widths))
for r in str_rows:
print(fmt_row(r))
print(" (%d satir)" % len(rows))
def ask(prompt, default=None, required=True):
while True:
suffix = " [%s]" % default if default else ""
val = input("%s%s: " % (prompt, suffix)).strip()
if not val and default is not None:
return default
if not val and not required:
return ""
if val:
return val
print(" -> Bu alan zorunlu, tekrar girin.")
# ==========================================================================
# 1) BAGLANTI
# ==========================================================================
def get_connection():
print("=" * 100)
print(" ORACLE RAC / SESSION - LOCK - WAIT - TUNING ANALYZER")
print(" Driver: %s" % DB_DRIVER)
print("=" * 100)
print("Baglanti bilgilerini giriniz (RAC ise SCAN adresi + service_name onerilir).\n")
host = ask("Host / SCAN adresi", default="localhost")
port = ask("Port", default="1521")
conn_type = ask("Baglanti tipi - (1) service_name (2) SID", default="1")
if conn_type == "2":
sid = ask("SID")
dsn = oradriver.makedsn(host, port, sid=sid)
else:
service_name = ask("Service Name")
dsn = oradriver.makedsn(host, port, service_name=service_name)
username = ask("Kullanici adi (ornek: c##dba_user veya sistem yetkili user)")
password = getpass.getpass("Sifre: ")
sysdba_choice = ask("SYSDBA olarak baglanilsin mi? (e/h)", default="h")
mode = oradriver.SYSDBA if sysdba_choice.lower().startswith("e") else 0
try:
if mode:
conn = oradriver.connect(username, password, dsn, mode=mode)
else:
conn = oradriver.connect(username, password, dsn)
print("\n[OK] Baglanti basarili. (%s)\n" % dsn)
return conn
except Exception as ex:
print("\n[HATA] Baglanti kurulamadi: %s" % ex)
sys.exit(1)
def run_query(conn, sql, binds=None):
cur = conn.cursor()
try:
cur.execute(sql, binds or {})
cols = [d[0] for d in cur.description]
rows = cur.fetchall()
return cols, rows
finally:
cur.close()
# ==========================================================================
# 2) WAIT EVENT BILGI TABANI
# -> event adi (regex/prefix) : (aciklama, olasi_nedenler, onerilen_aksiyon)
# ==========================================================================
WaitInfo = namedtuple("WaitInfo", ["desc", "causes", "actions"])
WAIT_EVENT_KB = [
(re.compile(r"^enq: TX.*row lock contention", re.I), WaitInfo(
desc="Satir seviyesinde (row-level) lock bekleniyor. Baska bir session ayni satiri "
"commit/rollback etmeden once degistirmis (INSERT/UPDATE/DELETE) durumda.",
causes=[
"Blocklayan session commit/rollback yapmadan islemi acik birakmis.",
"Ayni PK/unique kolona iki oturumun ayni anda INSERT denemesi (ORA-00001 bekleyisi).",
"Uygulama tarafinda uzun suren transaction / kullanici etkilesimi (think time).",
],
actions=[
"Blocklayan session'i (final_blocking_session) tespit edip is mantigini kontrol edin.",
"Uygulamada commit/rollback'in transaction sonunda hemen yapildigindan emin olun.",
"Gerekliyse blocklayan oturumu 'ALTER SYSTEM KILL SESSION' ile sonlandirin (dikkatli).",
"Uzun transaction'lari kucuk batch'lere bolun (chunking).",
]
)),
(re.compile(r"^enq: TX.*index contention", re.I), WaitInfo(
desc="Index block'unda ITL (Interested Transaction List) veya index split kaynakli "
"TX enqueue beklemesi.",
causes=["Yogun concurrent INSERT ile index leaf block split.",
"INITRANS/ ITL slotlari yetersiz."],
actions=["Index/tabloda INITRANS degerini artirin.",
"Reverse key index veya hash partitioning dusunulebilir (monotonic artan key'lerde).",
"PCTFREE degerini gozden gecirin."]
)),
(re.compile(r"^enq: TM", re.I), WaitInfo(
desc="Table/DML lock (TM) beklemesi. Genelde foreign key kolonunda index olmamasi "
"veya DDL (drop/truncate/alter) ile DML'in cakismasi sonucu olusur.",
causes=[
"Child tabloda foreign key kolonu uzerinde index YOK -> parent DML sirasinda full "
"tablo kilidi (TM enqueue) alinir.",
"Ayni tabloya DDL (ALTER TABLE, TRUNCATE, index rebuild) ile DML ayni anda calisiyor.",
],
actions=[
"En sik neden: FK kolonlarina index ekleyin -> "
"'SELECT * FROM user_constraints WHERE constraint_type=''R''' ile kontrol edin.",
"DDL islemlerini bakim penceresinde / dusuk trafikte yapin.",
]
)),
(re.compile(r"^enq: UL", re.I), WaitInfo(
desc="Kullanici tanimli lock (DBMS_LOCK ile) beklemesi.",
causes=["Uygulama seviyesinde manuel lock (DBMS_LOCK.REQUEST) kullanimi."],
actions=["Uygulama kodunu inceleyip lock'un ne zaman serbest birakildigini kontrol edin."]
)),
(re.compile(r"^enq: HW", re.I), WaitInfo(
desc="High Water Mark (segment genisleme) enqueue'u. Concurrent INSERT sirasinda "
"segment buyurken olusur.",
causes=["Kucuk extent'li / yogun paralel insert alan tablolar."],
actions=["ASSM kullanildigindan emin olun, gerekiyorsa tabloyu daha buyuk INITIAL/UNIFORM "
"extent ile yeniden olusturun."]
)),
(re.compile(r"^gc buffer busy", re.I), WaitInfo(
desc="RAC ortaminda, istenen block baska bir instance'ta baska bir session tarafindan "
"aktif olarak degistiriliyor/okunuyor (Global Cache buffer busy).",
causes=[
"Cok node'lu 'hot block' erisimi (ornegin sequence ile beslenen index'in sag ucu, "
"sayac tablosu, vb.).",
"Yetersiz interconnect bant genisligi / yuksek gc gecikmesi.",
],
actions=[
"Hot block'u v$hotblock ve dba_hist_active_sess_history uzerinden tespit edin.",
"Reverse key index / hash partitioning ile 'right-growing index' sorununu azaltin.",
"Sequence CACHE degerini ve NOORDER secenegini kontrol edin.",
"Uygulamayi mumkunse ayni instance'a affinity ile yonlendirin (services).",
]
)),
(re.compile(r"^gc cr (block )?(request|2-way|3-way)", re.I), WaitInfo(
desc="Global Cache Consistent Read block istegi - block, cluster interconnect uzerinden "
"baska instance'tan getiriliyor.",
causes=["Node'lar arasi yuksek veri paylasimi (ayni tablo/index'e RAC genelinde erisim).",
"Interconnect gecikmesi/ congestion, yetersiz private network bant genisligi."],
actions=[
"gv$instance_cache_transfer ile hangi objelerin cross-instance trafik urettigini bulun.",
"Interconnect (private network) latency/throughput izleyin (OS seviyesinde).",
"Mumkunse uygulama/servisleri belirli node'lara yonlendirerek (RAC service affinity) "
"cross-instance block transferini azaltin.",
]
)),
(re.compile(r"^gc current (block|grant)", re.I), WaitInfo(
desc="Global Cache current mode block transferi bekleniyor (genelde DML/blok guncelleme "
"amacli).",
causes=["Node'lar arasi yogun yazma cakismasi (hot block)."],
actions=["Yukaridaki 'gc buffer busy' onerileriyle ayni: hot block/partitioning/sequence "
"cache ayarlarini gozden gecirin."]
)),
(re.compile(r"^latch: cache buffers chains", re.I), WaitInfo(
desc="Buffer cache'te hash zinciri (chain) korumasi icin latch beklemesi - 'hot block' "
"yogun okuma/yazma nedeniyle olusur.",
causes=["Ayni bloga (kucuk lookup tablosu, index kok blogu vb.) asiri yogun erisim.",
"Kotu SQL (buffer get sayisi cok yuksek, tekrar tekrar ayni blogu okuyor)."],
actions=[
"En cok buffer get yapan SQL'i (bu scriptteki SQL/Plan analiz menusu ile) bulun.",
"Full scan yerine index kullanimini saglayin, gereksiz tekrarli erisimi (loop icinde "
"sorgu) azaltin.",
"Cok sik guncellenen kucuk tablolar icin 'result cache' veya PL/SQL cache dusunulebilir.",
]
)),
(re.compile(r"^latch free", re.I), WaitInfo(
desc="Genel latch contention (spesifik latch adi p2 ile belirlenir).",
causes=["CPU starvation, asiri parse/hard parse, kotu yazilmis / literal degerli SQL'ler."],
actions=["Bind variable kullanimini kontrol edin (cursor_sharing), hard parse orani "
"yuksekse SQL'leri parametrize edin."]
)),
(re.compile(r"^buffer busy waits", re.I), WaitInfo(
desc="Ayni instance icinde ayni blogu okumak/yazmak isteyen birden fazla session var "
"(single-instance versiyonu, RAC'ta 'gc buffer busy' olur).",
causes=["Hot block (index sag ucu, freelist/ITL yetersizligi, segment header contention)."],
actions=["ASSM/segment yapisini, ITL/INITRANS degerlerini kontrol edin.",
"Sequence CACHE artirilabilir, index reverse key dusunulebilir."]
)),
(re.compile(r"^log file sync", re.I), WaitInfo(
desc="COMMIT/ROLLBACK sonrasi LGWR'in redo'yu diske (online redo log) yazmasi bekleniyor.",
causes=["Yavas redo log disk I/O.",
"Asiri sik commit (row-by-row commit) yapan uygulama.",
"LGWR'in CPU'ya yeterince erisememesi."],
actions=[
"Redo log dosyalarini hizli storage'a (mumkunse ayri, dusuk latency disk/NVMe) alin.",
"Uygulamada commit sikligini azaltin (batch commit).",
"'commit write batch/nowait' secenegi (dikkatli, veri kaybi riskini degerlendirin) "
"veya redo log grup/boyutunu artirmayi degerlendirin.",
]
)),
(re.compile(r"^log file (parallel )?write", re.I), WaitInfo(
desc="LGWR redo bloklarini online redo log dosyalarina yaziyor; yavas I/O gostergesi.",
causes=["Yavas/yogun kullanilan disk altyapisi."],
actions=["Redo log I/O gecikmesini (AWR/ASH) inceleyin, storage katmanini kontrol edin."]
)),
(re.compile(r"^db file sequential read", re.I), WaitInfo(
desc="Tekil blok okuma (genelde index ile ROWID uzerinden tabloya erisim) - I/O beklemesi.",
causes=["Index kullanan sorgularda cok sayida random I/O (secici olmayan index, "
"istatistik guncel degil, yanlis plan)."],
actions=[
"Execution plan'da INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID adimlarina bakin.",
"Istatistikleri (DBMS_STATS) guncelleyin, gerekiyorsa daha secici bir index olusturun.",
"Buffer cache boyutunu ve storage IOPS/latency degerlerini gozden gecirin.",
]
)),
(re.compile(r"^db file scattered read", re.I), WaitInfo(
desc="Cok bloklu okuma (genelde FULL TABLE SCAN veya INDEX FAST FULL SCAN).",
causes=["Full table scan yapan SQL, eksik/kullanilmayan index, guncel olmayan istatistik, "
"yanlis optimizer secimi."],
actions=[
"Execution plan'da 'TABLE ACCESS FULL' adimlarini tespit edin.",
"WHERE kosuluna uygun index olusturmayi degerlendirin.",
"Eger full scan bilerek yapiliyorsa (raporlama) parallel query / partition pruning "
"kullanimini degerlendirin.",
]
)),
(re.compile(r"^direct path read", re.I), WaitInfo(
desc="PGA'ya dogrudan (buffer cache'i bypass ederek) okuma - genelde full scan / parallel "
"query / smart scan (Exadata) esnasinda olur.",
causes=["Buyuk tablo/partition full scan, sort/hash join icin temp'e yazip okuma."],
actions=["Full scan'in gercekten gerekli olup olmadigini kontrol edin (index/partition "
"pruning). PGA_AGGREGATE_TARGET / work area boyutunu gozden gecirin."]
)),
(re.compile(r"^direct path (read|write) temp", re.I), WaitInfo(
desc="Sort/Hash Join/Group By islemleri PGA'ya sigmayip TEMP tablespace'e tasiyor "
"(disk sort/hash spill).",
causes=["PGA_AGGREGATE_TARGET yetersiz.", "Kotu cardinality tahmini nedeniyle hash join "
"icin ayrilan bellek yetersiz kaliyor.", "ORDER BY / GROUP BY buyuk veri seti "
"uzerinde calisiyor."],
actions=[
"PGA_AGGREGATE_TARGET / PGA_AGGREGATE_LIMIT degerlerini artirmayi degerlendirin.",
"Execution plan'da 'TempSpc' kolonunu kontrol edin (dbms_xplan ciktisinda gorunur).",
"Sorguyu daha az veri donduracek sekilde (filtre/erken agregasyon) yeniden yazin.",
]
)),
(re.compile(r"^cursor: pin S wait on X", re.I), WaitInfo(
desc="Ayni SQL cursor'unun baska bir session tarafinda hard parse / degistirilmesi "
"bekleniyor (mutex).",
causes=["Asiri hard parse (bind variable eksikligi, literal SQL), sik DDL/invalidations."],
actions=["Bind variable kullanimini saglayin, CURSOR_SHARING=FORCE degerlendirin "
"(gecici cozum), gereksiz DDL/istatistik toplama sikligini azaltin."]
)),
(re.compile(r"^library cache", re.I), WaitInfo(
desc="Library cache lock/pin beklemesi - genelde DDL (ALTER/COMPILE) ile aktif SQL "
"kullanimi cakisiyor.",
causes=["Prod saatlerinde DDL/paket derleme yapilmasi."],
actions=["DDL islemlerini bakim penceresine alin, invalid object'leri bakim saatinde "
"recompile edin."]
)),
(re.compile(r"^row cache lock", re.I), WaitInfo(
desc="Data dictionary cache (row cache) lock beklemesi - genelde DDL/sequence "
"cache miss kaynakli.",
causes=["Yogun DDL, sik sequence NEXTVAL cagrisi ile dictionary guncellemesi."],
actions=["Sequence CACHE degerini artirin, DDL'leri az yogun saatlere kaydirin."]
)),
]
DEFAULT_WAIT_INFO = WaitInfo(
desc="Bu event icin ozel bir bilgi tabani kaydi bulunamadi.",
causes=["Event detayini Oracle dokumantasyonundan (Reference Guide - Wait Events) "
"kontrol edin."],
actions=["p1/p2/p3 degerlerini event'in resmi tanimina gore yorumlayin.",
"ASH/AWR raporunda bu event'in gecmis trendini inceleyin."]
)
def lookup_wait_info(event_name):
if not event_name:
return DEFAULT_WAIT_INFO
for pattern, info in WAIT_EVENT_KB:
if pattern.search(event_name):
return info
return DEFAULT_WAIT_INFO
# --------------------------------------------------------------------------
# p1/p2/p3 decode yardimcisi (en yaygin event tipleri icin)
# --------------------------------------------------------------------------
LOCK_TYPE_MAP = {
"TX": "Transaction (row) lock",
"TM": "DML / Table lock",
"UL": "User-defined lock",
"HW": "High-water mark segment extension",
"US": "Undo segment",
"SQ": "Sequence cache",
}
LOCK_MODE_MAP = {
0: "none", 1: "null (NULL)", 2: "row-share (SS)", 3: "row-exclusive (SX)",
4: "share (S)", 5: "share row-exclusive (SSX)", 6: "exclusive (X)",
}
def decode_enqueue_p1p2p3(p1, p2, p3):
"""enq: XX turu event'lerde p1 icinde lock type + mode encode edilidir."""
try:
p1 = int(p1)
except (TypeError, ValueError):
return "p1/p2/p3 decode edilemedi (numeric degil)."
lock_type_code = (p1 >> 16) & 0xFFFF
lock_mode = p1 & 0xFFFF
try:
type_chars = chr((lock_type_code >> 8) & 0xFF) + chr(lock_type_code & 0xFF)
except ValueError:
type_chars = "?"
type_desc = LOCK_TYPE_MAP.get(type_chars.strip(), type_chars)
mode_desc = LOCK_MODE_MAP.get(lock_mode, "mode=%s" % lock_mode)
return "Lock Type=%s (%s), Mode=%s | p2(id1)=%s p3(id2)=%s" % (
type_chars, type_desc, mode_desc, p2, p3)
def decode_wait_params(event, p1, p2, p3):
if event and event.startswith("enq:"):
return decode_enqueue_p1p2p3(p1, p2, p3)
if event and event.startswith("gc "):
return ("RAC Global Cache - p1=file#(%s) p2=block#(%s) p3=misc(%s). "
"Hangi objeye ait oldugunu bulmak icin p1/p2 (file/block) ile "
"DBA_EXTENTS uzerinden segmenti tespit edebilirsiniz." % (p1, p2, p3))
if event in ("db file sequential read", "db file scattered read", "direct path read"):
return "p1=file#(%s) p2=block#(%s) p3=blocks(%s) - okunan datafile/blok bilgisi." % (p1, p2, p3)
if event and event.startswith("latch"):
return "p1=latch address(%s) p2=latch number(%s) p3=tries/mode(%s)" % (p1, p2, p3)
return "p1=%s p2=%s p3=%s" % (p1, p2, p3)
# ==========================================================================
# 3) SESSION / WAIT / LOCK SORGULARI (RAC uyumlu - gv$ view'lari)
# ==========================================================================
SQL_ACTIVE_SESS_SUMMARY = """
SELECT inst_id, NVL(event,'(cpu / on cpu)') AS event, COUNT(*) AS cnt
FROM gv$session
WHERE status = 'ACTIVE' AND type <> 'BACKGROUND'
GROUP BY inst_id, NVL(event,'(cpu / on cpu)')
ORDER BY inst_id, cnt DESC
"""
# 12c+ : blocking bilgisi dogrudan gv$session icinde (blocking_session,
# blocking_instance, final_blocking_session, final_blocking_instance)
SQL_WAITING_SESSIONS = """
SELECT s.inst_id, s.sid, s.serial#, s.username, s.osuser, s.machine, s.program,
s.module, s.sql_id, s.sql_child_number, s.event, s.state, s.seconds_in_wait,
s.p1, s.p2, s.p3,
s.blocking_instance, s.blocking_session,
s.final_blocking_instance, s.final_blocking_session,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#,
s.last_call_et
FROM gv$session s
WHERE s.status = 'ACTIVE'
AND s.type <> 'BACKGROUND'
AND (s.blocking_session IS NOT NULL OR s.event NOT LIKE '%SQL*Net%')
ORDER BY s.inst_id, s.seconds_in_wait DESC
"""
SQL_BLOCKERS_ONLY = """
SELECT DISTINCT blocking_instance, blocking_session
FROM gv$session
WHERE blocking_session IS NOT NULL
"""
SQL_SESSION_DETAIL = """
SELECT s.inst_id, s.sid, s.serial#, s.username, s.osuser, s.machine, s.program,
s.module, s.status, s.event, s.seconds_in_wait, s.sql_id, s.sql_child_number,
s.blocking_instance, s.blocking_session,
s.final_blocking_instance, s.final_blocking_session,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#,
s.last_call_et, s.p1, s.p2, s.p3,
'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || ''' IMMEDIATE;' AS kill_cmd
FROM gv$session s
WHERE s.sid = :sid AND s.inst_id = :inst_id
"""
SQL_LOCKED_OBJECT = """
SELECT o.owner, o.object_name, o.object_type
FROM dba_objects o
WHERE o.object_id = :objn
"""
SQL_SQL_TEXT = """
SELECT sql_fulltext
FROM gv$sqlarea
WHERE sql_id = :sql_id AND inst_id = :inst_id AND ROWNUM = 1
"""
SQL_SQL_STATS = """
SELECT inst_id, child_number, plan_hash_value, executions, buffer_gets,
ROUND(buffer_gets / GREATEST(executions,1), 2) AS gets_per_exec,
disk_reads, elapsed_time/1000 AS elapsed_ms, cpu_time/1000 AS cpu_ms,
first_load_time, last_load_time
FROM gv$sql
WHERE sql_id = :sql_id
ORDER BY inst_id, child_number
"""
SQL_PLAN_STATS = """
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => :sql_id,
cursor_child_no => :child_no,
format => 'ALLSTATS LAST +PEEKED_BINDS +NOTE'
)
)
"""
# gv$sql_plan_statistics_all uzerinden manuel filtre (kullanicinin verdigi ornege
# benzer, dbms_xplan.display_cursor calismazsa fallback olarak kullanilabilir)
SQL_PLAN_STATS_FALLBACK = """
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY(
'gv$sql_plan_statistics_all', NULL, 'ALLSTATS LAST',
'inst_id = ' || :inst_id || ' AND sql_id = ''' || :sql_id ||
''' AND child_number = ' || :child_no
)
)
"""
SQL_PLAN_RAW_ROWS = """
SELECT operation, options, object_owner, object_name, object_type,
cardinality, bytes, cost, cpu_cost, io_cost, access_predicates,
filter_predicates, id, parent_id, depth
FROM gv$sql_plan
WHERE sql_id = :sql_id AND inst_id = :inst_id AND child_number = :child_no
ORDER BY id
"""
# ==========================================================================
# 4) PLAN ANALIZ / TUNING ONERI MOTORU
# ==========================================================================
def analyze_plan_rows(plan_rows, plan_cols):
"""
gv$sql_plan satirlarini basit heuristic kurallarla tarayip
tuning onerileri uretir. plan_rows: SQL_PLAN_RAW_ROWS sonucudur.
"""
idx = {c: i for i, c in enumerate(plan_cols)}
findings = []
for row in plan_rows:
operation = (row[idx["OPERATION"]] or "").upper()
options = (row[idx["OPTIONS"]] or "").upper()
obj_name = row[idx["OBJECT_NAME"]]
cost = row[idx["COST"]]
cardinality = row[idx["CARDINALITY"]]
if operation == "TABLE ACCESS" and options == "FULL":
findings.append(
"[FULL TABLE SCAN] '%s' tablosunda FULL SCAN yapiliyor (cost=%s, "
"tahmini satir=%s). Eger WHERE kosulu secici (selective) ise, uygun "
"kolonlarda index olusturmayi veya partition pruning'i degerlendirin. "
"Eger kasitli (raporlama/agregasyon) ise PARALLEL hint/derece "
"degerlendirilebilir." % (obj_name, cost, cardinality)
)
if operation == "INDEX" and options in ("FULL SCAN", "FAST FULL SCAN"):
findings.append(
"[INDEX %s] '%s' index'inde tam tarama yapiliyor (cost=%s). Sorgunun "
"sadece indexten karsilanan (covering index) bir islem oldugunu "
"dogrulayin; degilse aslinda tabloya donuluyor olabilir." % (options, obj_name, cost)
)
if "NESTED LOOPS" in operation:
if cardinality is not None and cardinality > 100000:
findings.append(
"[NESTED LOOPS] Tahmini satir sayisi yuksek (%s). Buyuk veri "
"kumeleri uzerinde NESTED LOOPS join maliyetli olabilir; HASH JOIN "
"daha uygun olabilir (istatistiklerin guncel oldugundan emin olun)." % cardinality
)
if operation == "SORT" and options in ("ORDER BY", "GROUP BY", "AGGREGATE"):
findings.append(
"[SORT %s] Bellekte/TEMP'te siralama yapiliyor. Eger 'TempSpc' "
"kolonunda deger varsa (asagida gorunur) PGA_AGGREGATE_TARGET yetersiz "
"kalmis ve disk'e spill olmus demektir." % options
)
if operation == "HASH JOIN" and cost is not None and cost > 100000:
findings.append(
"[HASH JOIN] Cok yuksek maliyetli (cost=%s) bir HASH JOIN var. Join "
"kolonlarindaki istatistikleri, olasi cardinality misestimate'i ve "
"join sirasini (ordered hint / leading hint) gozden gecirin." % cost
)
if not findings:
findings.append("Plan uzerinde belirgin bir anti-pattern tespit edilmedi "
"(bu, planin optimal oldugu anlamina gelmez; E-Rows/A-Rows "
"karsilastirmasini asagidaki ALLSTATS ciktisindan kontrol edin).")
return findings
def print_plan_general_advice():
print("\n >>> GENEL PLAN OKUMA REHBERI <<<")
print(" - 'Starts' ile 'A-Rows': bir adim beklenenden COK fazla calisiyorsa (Starts yuksek)")
print(" genelde NESTED LOOPS ic dongude patlama var demektir -> HASH JOIN'e gecirmeyi deneyin.")
print(" - 'E-Rows' (tahmini) ile 'A-Rows' (gercek) arasinda 10x+ fark varsa: cardinality")
print(" misestimate var -> istatistikleri (histogram dahil) guncelleyin veya SQL Plan")
print(" Baseline / SQL Profile / dynamic sampling dusunun.")
print(" - 'Buffer Gets' yuksekse: index eksikligi veya kotu filtre secicilik sorunu olabilir.")
print(" - 'TempSpc' sutunu doluysa: PGA yetersiz, disk sort/hash spill var demektir.")
print(" - Predicate Information (access/filter) bolumunde 'implicit conversion' varsa")
print(" (ör. TO_NUMBER/ TO_CHAR) index kullanimi engelleniyor olabilir.\n")
# ==========================================================================
# 5) MENU FONKSIYONLARI
# ==========================================================================
def menu_active_summary(conn):
hr("=")
print("AKTIF SESSIONLARIN WAIT EVENT DAGILIMI (Instance bazinda)")
hr("=")
cols, rows = run_query(conn, SQL_ACTIVE_SESS_SUMMARY)
print_table(cols, rows)
def build_blocking_chains(conn):
"""
gv$session uzerinden (inst,sid) -> (blocking_inst, blocking_sid) haritasini
cikarip zincirleri (kim kimi blokluyor -> en son root blocker kim) kurar.
"""
cols, rows = run_query(conn, SQL_WAITING_SESSIONS)
idx = {c: i for i, c in enumerate(cols)}
sessions = {}
for r in rows:
key = (r[idx["INST_ID"]], r[idx["SID"]])
sessions[key] = r
return cols, idx, sessions
def menu_blocking_tree(conn):
hr("=")
print("BLOCKING TREE (Kim kimi lock'lamis - RAC capraz instance dahil)")
hr("=")
cols, idx, sessions = build_blocking_chains(conn)
if not sessions:
print("Su an lock/wait iceren aktif (background disi) session bulunamadi.")
return
# root blocker'lari bul: kendisi bloklaniyor olmayan ama baskalarini blocklayan
blocked_by = defaultdict(list) # (blk_inst,blk_sid) -> [ (inst,sid), ... ]
for key, r in sessions.items():
b_inst = r[idx["BLOCKING_INSTANCE"]]
b_sid = r[idx["BLOCKING_SESSION"]]
if b_inst is not None and b_sid is not None:
blocked_by[(b_inst, b_sid)].append(key)
root_blockers = [k for k in blocked_by if k not in sessions or
sessions[k][idx["BLOCKING_SESSION"]] is None]
if not root_blockers:
print("Aktif bir blocking zinciri tespit edilmedi (kimse kimseyi bloklamiyor).")
return
def describe(key):
r = sessions.get(key)
if r is None:
return "Inst=%s Sid=%s (gv$session'da bulunamadi -> muhtemelen sadece kisa " \
"sureli bir lock tutuyor, DBA_BLOCKERS ile de kontrol edin)" % key
return ("Inst=%s Sid=%s Ser#=%s User=%s Machine=%s Event='%s' "
"seconds_in_wait=%s SQL_ID=%s" % (
r[idx["INST_ID"]], r[idx["SID"]], r[idx["SERIAL#"]], r[idx["USERNAME"]],
r[idx["MACHINE"]], r[idx["EVENT"]], r[idx["SECONDS_IN_WAIT"]], r[idx["SQL_ID"]]))
def print_chain(key, level=0):
print(" " * level + ("|- " if level else "* ROOT BLOCKER -> ") + describe(key))
for waiter in blocked_by.get(key, []):
print_chain(waiter, level + 1)
for rb in root_blockers:
print_chain(rb)
print()
print("Not: 'ROOT BLOCKER' altinda listelenen session'lar dogrudan veya dolayli olarak "
"bu session tarafindan bekletiliyor. Detay/SQL/plan icin ana menuden "
"'Session Detay & Tuning' secenegini kullanip Inst/Sid girin.")
def menu_waiting_sessions(conn):
hr("=")
print("BEKLEYEN (ACTIVE) SESSIONLAR VE WAIT EVENT ACIKLAMALARI")
hr("=")
cols, rows = run_query(conn, SQL_WAITING_SESSIONS)
idx = {c: i for i, c in enumerate(cols)}
if not rows:
print("Su an aktif/bekleyen session bulunamadi.")
return
summary_rows = []
for r in rows:
summary_rows.append([
r[idx["INST_ID"]], r[idx["SID"]], r[idx["SERIAL#"]], r[idx["USERNAME"]],
r[idx["EVENT"]], r[idx["SECONDS_IN_WAIT"]],
r[idx["BLOCKING_INSTANCE"]], r[idx["BLOCKING_SESSION"]], r[idx["SQL_ID"]]
])
print_table(["INST", "SID", "SERIAL#", "USER", "EVENT", "SEC_WAIT",
"BLK_INST", "BLK_SID", "SQL_ID"], summary_rows)
print("\nDetayli aciklama icin bir SID secin (bos birakip Enter -> menuye don):")
sel = ask("SID", required=False)
if not sel:
return
for r in rows:
if str(r[idx["SID"]]) == sel:
explain_wait(r, idx)
return
print("Bu SID listede bulunamadi.")
def explain_wait(r, idx):
event = r[idx["EVENT"]]
p1, p2, p3 = r[idx["P1"]], r[idx["P2"]], r[idx["P3"]]
info = lookup_wait_info(event)
hr("-")
print("SID=%s Inst=%s User=%s Event='%s' seconds_in_wait=%s" % (
r[idx["SID"]], r[idx["INST_ID"]], r[idx["USERNAME"]], event, r[idx["SECONDS_IN_WAIT"]]))
print("Parametre yorumu: %s" % decode_wait_params(event, p1, p2, p3))
print("\nACIKLAMA:\n %s" % info.desc)
print("\nOLASI NEDENLER:")
for c in info.causes:
print(" - %s" % c)
print("\nONERILEN AKSIYONLAR:")
for a in info.actions:
print(" - %s" % a)
if r[idx["BLOCKING_SESSION"]] is not None:
print("\n>>> Bu session baska bir session tarafindan bloklaniyor: "
"Inst=%s Sid=%s (Detay icin ana menuden Session Detay'a girin)" %
(r[idx["BLOCKING_INSTANCE"]], r[idx["BLOCKING_SESSION"]]))
if r[idx["ROW_WAIT_OBJ#"]] is not None and r[idx["ROW_WAIT_OBJ#"]] != -1:
print("\nLock'lanan / erisilen obje: object_id=%s (file#=%s block#=%s row#=%s)" % (
r[idx["ROW_WAIT_OBJ#"]], r[idx["ROW_WAIT_FILE#"]],
r[idx["ROW_WAIT_BLOCK#"]], r[idx["ROW_WAIT_ROW#"]]))
hr("-")
def menu_session_detail(conn):
hr("=")
print("SESSION DETAY, SQL, EXECUTION PLAN VE TUNING ONERILERI")
hr("=")
sid = ask("SID")
inst_id = ask("INST_ID", default="1")
cols, rows = run_query(conn, SQL_SESSION_DETAIL, {"sid": int(sid), "inst_id": int(inst_id)})
if not rows:
print("Bu SID/INST_ID kombinasyonu ile aktif bir session bulunamadi.")
return
idx = {c: i for i, c in enumerate(cols)}
r = rows[0]
print("Kullanici : %s (%s@%s)" % (r[idx["USERNAME"]], r[idx["OSUSER"]], r[idx["MACHINE"]])
)
print("Program/Module : %s / %s" % (r[idx["PROGRAM"]], r[idx["MODULE"]]))
print("Status/Event : %s / %s (seconds_in_wait=%s)" % (
r[idx["STATUS"]], r[idx["EVENT"]], r[idx["SECONDS_IN_WAIT"]]))
print("SQL_ID : %s (child#=%s)" % (r[idx["SQL_ID"]], r[idx["SQL_CHILD_NUMBER"]]))
print("Blocking Session: Inst=%s Sid=%s | Final blocker: Inst=%s Sid=%s" % (
r[idx["BLOCKING_INSTANCE"]], r[idx["BLOCKING_SESSION"]],
r[idx["FINAL_BLOCKING_INSTANCE"]], r[idx["FINAL_BLOCKING_SESSION"]]))
print("Kill komutu : %s" % r[idx["KILL_CMD"]])
if r[idx["ROW_WAIT_OBJ#"]] is not None and r[idx["ROW_WAIT_OBJ#"]] != -1:
try:
_, obj_rows = run_query(conn, SQL_LOCKED_OBJECT, {"objn": r[idx["ROW_WAIT_OBJ#"]]})
if obj_rows:
print("Lock'lanan obje : %s.%s (%s)" % obj_rows[0])
except Exception as ex:
print("(Obje bilgisi alinamadi: %s)" % ex)
explain_wait(r, idx)
if r[idx["SQL_ID"]]:
show_sql_and_plan(conn, r[idx["SQL_ID"]], r[idx["SQL_CHILD_NUMBER"]] or 0, r[idx["INST_ID"]])
else:
print("\nBu session'a ait aktif bir SQL_ID bulunamadi (CPU'da degil / idle olabilir).")
def show_sql_and_plan(conn, sql_id, child_no, inst_id):
hr("-")
print("SQL METNI (sql_id=%s)" % sql_id)
hr("-")
try:
_, txt_rows = run_query(conn, SQL_SQL_TEXT, {"sql_id": sql_id, "inst_id": inst_id})
if txt_rows:
full_text = txt_rows[0][0]
if hasattr(full_text, "read"):
full_text = full_text.read()
print(full_text)
else:
print("(gv$sqlarea icinde bu sql_id icin metin bulunamadi - cursor tahliye edilmis olabilir)")
except Exception as ex:
print("SQL metni alinamadi: %s" % ex)
hr("-")
print("SQL CALISMA ISTATISTIKLERI (gv$sql, tum child cursor'lar / instance'lar)")
hr("-")
try:
cols, rows = run_query(conn, SQL_SQL_STATS, {"sql_id": sql_id})
print_table(cols, rows)
except Exception as ex:
print("SQL istatistikleri alinamadi: %s" % ex)
hr("-")
print("EXECUTION PLAN (DBMS_XPLAN.DISPLAY_CURSOR - ALLSTATS LAST)")
hr("-")
plan_lines = []
try:
cur = conn.cursor()
cur.execute(SQL_PLAN_STATS, {"sql_id": sql_id, "child_no": int(child_no)})
for row in cur:
line = row[0]
plan_lines.append(line)
print(line)
cur.close()
except Exception as ex:
print("DISPLAY_CURSOR basarisiz oldu (%s), fallback deneniyor "
"(gv$sql_plan_statistics_all uzerinden)..." % ex)
try:
cur = conn.cursor()
cur.execute(SQL_PLAN_STATS_FALLBACK,
{"sql_id": sql_id, "child_no": int(child_no), "inst_id": int(inst_id)})
for row in cur:
print(row[0])
cur.close()
except Exception as ex2:
print("Fallback da basarisiz: %s" % ex2)
print("Not: Plan cursor bellekten dusmus olabilir (AWR'de ayrica aranmali: "
"DBMS_XPLAN.DISPLAY_AWR('%s'))." % sql_id)
hr("-")
print("PLAN UZERINDE OTOMATIK TUNING ANALIZI")
hr("-")
try:
cols, rows = run_query(conn, SQL_PLAN_RAW_ROWS,
{"sql_id": sql_id, "inst_id": inst_id, "child_no": int(child_no)})
if rows:
findings = analyze_plan_rows(rows, cols)
for f in findings:
print(" - %s" % f)
else:
print(" gv$sql_plan icinde bu sql_id/child icin satir bulunamadi.")
except Exception as ex:
print(" Plan analizi yapilamadi: %s" % ex)
print_plan_general_advice()
def menu_full_report(conn):
hr("=")
print("TAM RAPOR: OZET + BLOCKING TREE + BEKLEYEN SESSIONLAR (ACIKLAMALI)")
hr("=")
menu_active_summary(conn)
print()
menu_blocking_tree(conn)
print()
cols, rows = run_query(conn, SQL_WAITING_SESSIONS)
idx = {c: i for i, c in enumerate(cols)}
if not rows:
print("Bekleyen session yok.")
return
print("\nHer bekleyen session icin otomatik aciklama:\n")
for r in rows:
explain_wait(r, idx)
print()
# ==========================================================================
# 6) ANA MENU
# ==========================================================================
MENU_TEXT = """
================= ORACLE RAC SESSION / LOCK / WAIT / TUNING ANALYZER =================
1) Aktif sessionlarin wait event ozet dagilimi (instance bazinda)
2) Blocking Tree - kim kimi lock'lamis (RAC capraz instance dahil)
3) Bekleyen sessionlar + wait event aciklamasi (secip detaya inebilirsiniz)
4) Belirli bir SID icin: detay + lock bilgisi + SQL + execution plan + tuning onerisi
5) TAM RAPOR (1+2+3 hepsi bir arada)
0) Cikis
========================================================================================
"""
def main():
conn = get_connection()
try:
while True:
print(MENU_TEXT)
choice = ask("Seciminiz", default="5")
try:
if choice == "1":
menu_active_summary(conn)
elif choice == "2":
menu_blocking_tree(conn)
elif choice == "3":
menu_waiting_sessions(conn)
elif choice == "4":
menu_session_detail(conn)
elif choice == "5":
menu_full_report(conn)
elif choice == "0":
break
else:
print("Gecersiz secim.")
except Exception as ex:
print("\n[HATA] Islem sirasinda bir sorun olustu: %s" % ex)
print()
finally:
try:
conn.close()
except Exception:
pass
print("Baglanti kapatildi. Gorusmek uzere.")
if __name__ == "__main__":
main()SQL BASH Komutu:
-- =============================================================================
-- oracle_lock_wait_analyzer.sql
-- SQL*Plus script - Oracle 12c+ (RAC uyumlu)
-- Kullanim: SQL> @oracle_lock_wait_analyzer.sql
-- =============================================================================
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 1000
SET LINESIZE 200
SET LONG 10000
SET LONGCHUNKSIZE 10000
COLUMN event FORMAT a30
COLUMN username FORMAT a15
COLUMN machine FORMAT a20
COLUMN program FORMAT a25
COLUMN sql_id FORMAT a13
COLUMN blocking_session FORMAT a20
COLUMN state FORMAT a10
COLUMN seconds_in_wait FORMAT 99999
COLUMN p1 FORMAT a15
COLUMN p2 FORMAT a15
COLUMN p3 FORMAT a15
COLUMN operation FORMAT a30
COLUMN options FORMAT a20
COLUMN object_name FORMAT a30
COLUMN cost FORMAT 9999999
COLUMN cardinality FORMAT 99999999
COLUMN buffer_gets FORMAT 999999999
COLUMN executions FORMAT 99999999
COLUMN gets_per_exec FORMAT 999999.99
PROMPT =============================================================================
PROMPT ORACLE SESSION / LOCK / WAIT / TUNING ANALYZER (SQL*Plus)
PROMPT =============================================================================
PROMPT
ACCEPT report_choice PROMPT 'Seciminiz (1=Tam Rapor, 2=SID Detayi): ' DEFAULT '1'
-- -----------------------------------------------------------------------------
-- 1) TAM RAPOR
-- -----------------------------------------------------------------------------
WHENEVER SQLERROR CONTINUE
COLUMN inst_id FORMAT 999
COLUMN cnt FORMAT 99999
-- 1.1 Aktif session wait event ozeti
PROMPT
PROMPT =============================================================================
PROMPT 1) AKTIF SESSIONLARIN WAIT EVENT DAGILIMI
PROMPT =============================================================================
SELECT inst_id, NVL(event, '(cpu / on cpu)') AS event, COUNT(*) AS cnt
FROM gv$session
WHERE status = 'ACTIVE' AND type <> 'BACKGROUND'
GROUP BY inst_id, NVL(event, '(cpu / on cpu)')
ORDER BY inst_id, cnt DESC;
-- 1.2 Bloking Tree (RAC capraz)
PROMPT
PROMPT =============================================================================
PROMPT 2) BLOCKING TREE - KIM KIMI LOCK'LAMIS
PROMPT =============================================================================
WITH block_tree (inst_id, sid, serial#, username, machine, event, seconds_in_wait, sql_id,
blocking_inst, blocking_sid, level_num, chain_id, root_inst, root_sid) AS (
-- Root blocker'lar: blocklayan ve baska bir session tarafindan bloklanmayan
SELECT s.inst_id, s.sid, s.serial#, s.username, s.machine, s.event, s.seconds_in_wait,
s.sql_id,
s.blocking_instance, s.blocking_session,
1 AS level_num,
ROWNUM AS chain_id,
s.inst_id AS root_inst,
s.sid AS root_sid
FROM gv$session s
WHERE s.status = 'ACTIVE' AND s.type <> 'BACKGROUND'
AND s.blocking_session IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM gv$session s2
WHERE s2.blocking_session = s.sid
AND s2.blocking_instance = s.inst_id
)
UNION ALL
-- Recursive: blocker'dan etkilenenler
SELECT s.inst_id, s.sid, s.serial#, s.username, s.machine, s.event, s.seconds_in_wait,
s.sql_id,
s.blocking_instance, s.blocking_session,
bt.level_num + 1,
bt.chain_id,
bt.root_inst,
bt.root_sid
FROM gv$session s
JOIN block_tree bt ON (s.blocking_session = bt.sid AND s.blocking_instance = bt.inst_id)
WHERE s.status = 'ACTIVE' AND s.type <> 'BACKGROUND'
)
CYCLE inst_id, sid SET is_cycle TO 'Y' DEFAULT 'N'
SELECT level_num AS depth,
LPAD(' ', (level_num-1)*2) || 'Inst=' || inst_id || ' Sid=' || sid ||
' User=' || username || ' Event=' || event || ' SQL=' || sql_id AS chain
FROM block_tree
ORDER BY chain_id, level_num;
-- 1.3 Bekleyen sessionlar ve wait aciklamalari
PROMPT
PROMPT =============================================================================
PROMPT 3) BEKLEYEN SESSIONLAR (DETAYLI ACIKLAMALI)
PROMPT =============================================================================
-- Wait event aciklamalari icin bir kac ornek (daha fazlasi eklenebilir)
WITH wait_info AS (
SELECT
s.inst_id, s.sid, s.serial#, s.username, s.machine, s.event, s.seconds_in_wait,
s.blocking_instance, s.blocking_session, s.sql_id,
s.p1, s.p2, s.p3,
CASE
WHEN UPPER(s.event) LIKE 'ENQ: TX%ROW LOCK CONTENTION%' THEN
'Satir seviyesinde row lock beklemesi. Blocklayan session commit/rollback yapmamis.'
WHEN UPPER(s.event) LIKE 'ENQ: TX%INDEX CONTENTION%' THEN
'Index bloğunda ITL veya split kaynaklı TX enqueue beklemesi.'
WHEN UPPER(s.event) LIKE 'ENQ: TM%' THEN
'Tablo/DML lock beklemesi. Genelde FK indeksi eksik veya DDL/DML çakışması.'
WHEN UPPER(s.event) LIKE 'GC BUFFER BUSY%' THEN
'RAC ortamında başka bir instance''ta aktif block erişimi (hot block).'
WHEN UPPER(s.event) LIKE 'LATCH: CACHE BUFFERS CHAINS%' THEN
'Buffer cache hash zinciri latch beklemesi – hot block veya kötü SQL.'
WHEN UPPER(s.event) LIKE 'LOG FILE SYNC%' THEN
'COMMIT sonrası LGWR redo yazma beklemesi – yavaş disk veya sık commit.'
WHEN UPPER(s.event) LIKE 'DB FILE SEQUENTIAL READ%' THEN
'Tekil blok okuma (genelde index erişimi) – I/O beklemesi.'
WHEN UPPER(s.event) LIKE 'DB FILE SCATTERED READ%' THEN
'Çok bloklu okuma (FULL SCAN) – indeks eksikliği veya yanlış plan.'
WHEN UPPER(s.event) LIKE 'DIRECT PATH READ%' THEN
'PGA''ya direkt okuma – full scan / paralel sorgu.'
ELSE
'Bu wait event için özel açıklama yok. Oracle dokümantasyonunu kontrol edin.'
END AS event_desc,
CASE
WHEN UPPER(s.event) LIKE 'ENQ:%' THEN
'Lock Type/Mode için p1/p2/p3 decode ediniz (bu script sadece örnek verir).'
WHEN UPPER(s.event) LIKE 'GC %' THEN
'p1=file#, p2=block#, p3=misc – hangi objeye ait olduğunu dba_extents ile bulun.'
WHEN UPPER(s.event) IN ('DB FILE SEQUENTIAL READ','DB FILE SCATTERED READ','DIRECT PATH READ') THEN
'p1=file#, p2=block#, p3=block_count'
ELSE
'p1,p2,p3 değerlerini event''e göre yorumlayın.'
END AS param_desc
FROM gv$session s
WHERE s.status = 'ACTIVE' AND s.type <> 'BACKGROUND'
AND (s.blocking_session IS NOT NULL OR s.event NOT LIKE '%SQL*Net%')
)
SELECT inst_id, sid, serial#, username, event, seconds_in_wait,
blocking_instance, blocking_session, sql_id,
event_desc AS aciklama,
'p1='||p1||' p2='||p2||' p3='||p3||' -> '||param_desc AS param_yorumu
FROM wait_info
ORDER BY inst_id, seconds_in_wait DESC;
-- -----------------------------------------------------------------------------
-- 2) SID DETAYI
-- -----------------------------------------------------------------------------
WHENEVER SQLERROR EXIT
PROMPT
PROMPT =============================================================================
PROMPT 2) SESSION DETAYI, SQL VE PLAN ANALIZI
PROMPT =============================================================================
ACCEPT v_sid PROMPT 'SID giriniz: '
ACCEPT v_inst PROMPT 'INST_ID giriniz (varsayilan 1): ' DEFAULT '1'
COLUMN sql_fulltext FORMAT a150
COLUMN child_number FORMAT 999
COLUMN plan_hash_value FORMAT 9999999999
COLUMN executions FORMAT 99999999
COLUMN buffer_gets FORMAT 999999999
COLUMN gets_per_exec FORMAT 999999.99
COLUMN elapsed_ms FORMAT 999999999
COLUMN cpu_ms FORMAT 999999999
COLUMN first_load_time FORMAT a20
COLUMN last_load_time FORMAT a20
-- 2.1 Session detay
PROMPT
PROMPT --- SESSION BILGILERI ---
SELECT s.inst_id, s.sid, s.serial#, s.username, s.osuser, s.machine, s.program,
s.module, s.status, s.event, s.seconds_in_wait, s.sql_id, s.sql_child_number,
s.blocking_instance, s.blocking_session,
s.final_blocking_instance, s.final_blocking_session,
s.last_call_et,
'ALTER SYSTEM KILL SESSION '''||s.sid||','||s.serial#||',@'||s.inst_id||''' IMMEDIATE;' AS kill_cmd
FROM gv$session s
WHERE s.sid = &v_sid AND s.inst_id = &v_inst;
-- 2.2 SQL metni
PROMPT
PROMPT --- SQL METNI ---
SELECT sql_fulltext
FROM gv$sqlarea
WHERE sql_id = (SELECT sql_id FROM gv$session WHERE sid = &v_sid AND inst_id = &v_inst AND ROWNUM=1);
-- 2.3 SQL istatistikleri
PROMPT
PROMPT --- SQL ISTATISTIKLERI (gv$sql) ---
SELECT inst_id, child_number, plan_hash_value, executions, buffer_gets,
ROUND(buffer_gets / GREATEST(executions,1), 2) AS gets_per_exec,
disk_reads, elapsed_time/1000 AS elapsed_ms, cpu_time/1000 AS cpu_ms,
first_load_time, last_load_time
FROM gv$sql
WHERE sql_id = (SELECT sql_id FROM gv$session WHERE sid = &v_sid AND inst_id = &v_inst AND ROWNUM=1)
ORDER BY inst_id, child_number;
-- 2.4 Execution plan (DBMS_XPLAN.DISPLAY_CURSOR)
PROMPT
PROMPT --- EXECUTION PLAN (ALLSTATS LAST) ---
SET LONG 20000
SET PAGESIZE 0
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => (SELECT sql_id FROM gv$session WHERE sid = &v_sid AND inst_id = &v_inst AND ROWNUM=1),
cursor_child_no => (SELECT sql_child_number FROM gv$session WHERE sid = &v_sid AND inst_id = &v_inst AND ROWNUM=1),
format => 'ALLSTATS LAST +PEEKED_BINDS +NOTE'
)
);
SET PAGESIZE 1000
-- 2.5 Tuning onerileri (genel bilgi)
PROMPT
PROMPT --- TUNING ONERILERI (GENEL) ---
PROMPT * Plan uzerinde 'Starts' ve 'A-Rows' sutunlarini inceleyin.
PROMPT * E-Rows ile A-Rows arasinda 10 kat fark varsa: istatistikleri guncelleyin veya SQL Profile/Baseline deneyin.
PROMPT * 'TABLE ACCESS FULL' coksa: indeks olusturmayi veya partition pruning'i degerlendirin.
PROMPT * 'NESTED LOOPS' tahmini satir sayisi yuksekse: HASH JOIN onerilir.
PROMPT * 'TempSpc' sutunu doluysa: PGA_AGGREGATE_TARGET artirin veya sorguyu optimize edin.
PROMPT * 'Buffer Gets' yuksekse: gereksiz tekrarli erisim veya indeks eksikligi olabilir.
PROMPT * Predicate'lerde implicit conversion (TO_NUMBER/TO_CHAR) varsa index kullanilamayabilir.
PROMPT
PROMPT =============================================================================
PROMPT RAPOR TAMAMLANDI.
PROMPT =============================================================================
Bir yanıt yazın