{"id":1015,"date":"2026-07-13T15:57:20","date_gmt":"2026-07-13T12:57:20","guid":{"rendered":"https:\/\/oraclius.com.tr\/?p=1015"},"modified":"2026-07-15T01:23:38","modified_gmt":"2026-07-14T22:23:38","slug":"oracle-lock-wait-analyzer","status":"publish","type":"post","link":"https:\/\/oraclius.com.tr\/tr\/oracle-lock-wait-analyzer\/","title":{"rendered":"Oracle lock wait analyzeR"},"content":{"rendered":"\n

Bu ger\u00e7ekten kapsaml\u0131 bir DBA arac\u0131 \u2014 RAC ortam\u0131nda lock\/wait analizi + plan tuning \u00f6nerileri. Tek dosyada, men\u00fc tabanl\u0131, cx_Oracle<\/code> kullanan Python 3.6 uyumlu bir script.<\/p>\n\n\n\n

K\u0131sa \u00f6zet \u2014 script neler yap\u0131yor:<\/strong><\/p>\n\n\n\n

Ba\u011flant\u0131:<\/strong> \u00c7al\u0131\u015ft\u0131rd\u0131\u011f\u0131n\u0131zda host\/port\/service_name(veya SID)\/kullan\u0131c\u0131\/\u015fifre\/SYSDBA sorar, cx_Oracle<\/code> (yoksa oracledb<\/code> thin mode) ile ba\u011flan\u0131r. Python 3.6 uyumlu, f-string kullan\u0131lmad\u0131.<\/p>\n\n\n\n

Men\u00fc:<\/strong><\/p>\n\n\n\n

    \n
  1. Aktif sessionlar\u0131n wait event \u00f6zet da\u011f\u0131l\u0131m\u0131 (instance baz\u0131nda, gv$session<\/code>)<\/li>\n\n\n\n
  2. Blocking Tree<\/strong> \u2014 kim kimi lock’lam\u0131\u015f, RAC’ta cross-instance dahil, blocking_session\/blocking_instance<\/code> \u00fczerinden recursive a\u011fa\u00e7 kurup ekrana bas\u0131yor<\/li>\n\n\n\n
  3. Bekleyen session’lar listesi \u2014 bir SID se\u00e7ince o session’\u0131n wait event’ini a\u00e7\u0131kl\u0131yor<\/strong> (neden bekliyor, olas\u0131 nedenler, \u00f6nerilen aksiyon)<\/li>\n\n\n\n
  4. Belirli bir SID i\u00e7in tam detay: kilitlenen obje, blocker zinciri, kill komutu, \u00e7al\u0131\u015ft\u0131rd\u0131\u011f\u0131 SQL metni, gv$sql<\/code> istatistikleri, DBMS_XPLAN.DISPLAY_CURSOR(...,'ALLSTATS LAST')<\/code> ile execution plan (ba\u015far\u0131s\u0131z olursa gv$sql_plan_statistics_all<\/code> fallback’i), ve plan \u00fczerinde otomatik tuning analizi (full scan, nested loop patlamas\u0131, sort\/hash spill, y\u00fcksek maliyetli hash join vs.)<\/li>\n\n\n\n
  5. Hepsini tek raporda birle\u015ftiren tam rapor<\/li>\n<\/ol>\n\n\n\n

    Wait event bilgi taban\u0131:<\/strong> enq: TX<\/code>, enq: TM<\/code>, gc buffer busy<\/code>, gc cr request<\/code>, latch: cache buffers chains<\/code>, buffer busy waits<\/code>, log file sync<\/code>, db file sequential\/scattered read<\/code>, direct path read(temp)<\/code>, cursor: pin S wait on X<\/code>, library cache<\/code>, row cache lock<\/code> gibi en yayg\u0131n event’ler i\u00e7in a\u00e7\u0131klama + olas\u0131 nedenler + \u00f6nerilen aksiyon listesi var; tan\u0131nmayan event’ler i\u00e7in genel bir fallback mesaj\u0131 veriyor.<\/p>\n\n\n\n

    Birka\u00e7 not:<\/p>\n\n\n\n

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