{"id":1020,"date":"2026-07-21T11:53:17","date_gmt":"2026-07-21T08:53:17","guid":{"rendered":"https:\/\/oraclius.com.tr\/?p=1020"},"modified":"2026-07-29T17:29:26","modified_gmt":"2026-07-29T14:29:26","slug":"oracle-sql-patch-koda-dokunmadan-sql-performansini-yonetmek","status":"publish","type":"post","link":"https:\/\/oraclius.com.tr\/tr\/oracle-sql-patch-koda-dokunmadan-sql-performansini-yonetmek\/","title":{"rendered":"Oracle SQL Patch: Koda Dokunmadan SQL Performans\u0131n\u0131 Y\u00f6netmek"},"content":{"rendered":"\n

Gece yar\u0131s\u0131 gelen bir performans alarm\u0131n\u0131 d\u00fc\u015f\u00fcn\u00fcn. Normalde bir saniyede d\u00f6nen bir sorgu art\u0131k k\u0131rk saniye s\u00fcr\u00fcyor. SQL metni de\u011fi\u015fmemi\u015f, tablo yap\u0131s\u0131 ayn\u0131, uygulama taraf\u0131nda yeni bir s\u00fcr\u00fcm de yay\u0131nlanmam\u0131\u015f \u2014 ama execution plan bamba\u015fka. \u00dcst\u00fcne bir de uygulama ekibi yeni s\u00fcr\u00fcm\u00fc ancak \u00f6n\u00fcm\u00fczdeki hafta \u00e7\u0131karabilece\u011fini s\u00f6yl\u00fcyor.<\/p>\n\n\n\n

B\u00f6yle anlarda sorulmas\u0131 gereken do\u011fru soru \u015fu: kodu de\u011fi\u015ftirmeden Optimizer’\u0131n karar\u0131n\u0131 de\u011fi\u015ftirebilir miyim? Oracle Database’de bu sorunun cevab\u0131 SQL Patch<\/strong>.<\/p>\n\n\n\n

SQL Patch Nedir?<\/h2>\n\n\n\n

SQL Patch, DBMS_SQLDIAG<\/code> paketi \u00fczerinden sunulan ve belirli bir SQL ifadesine d\u0131\u015far\u0131dan Hint enjekte etmenizi sa\u011flayan bir mekanizmad\u0131r. En kritik \u00f6zelli\u011fi \u015fudur: SQL metnini hi\u00e7bir \u015fekilde de\u011fi\u015ftirmez.<\/strong> Uygulama ayn\u0131 SQL’i g\u00f6ndermeye devam eder; Oracle Optimizer bu SQL’i derlerken (parse ederken) arka planda patch i\u00e7indeki Hint’leri plana enjekte eder.<\/p>\n\n\n\n

Bu \u00f6zellik \u00f6zellikle kaynak koduna eri\u015fiminizin olmad\u0131\u011f\u0131 paket uygulamalarda (SAP, Oracle E-Business Suite, Logo gibi) hayat kurtar\u0131r \u2014 SQL metnine dokunma \u015fans\u0131n\u0131z olmasa bile Optimizer’\u0131n davran\u0131\u015f\u0131n\u0131 y\u00f6nlendirebilirsiniz.<\/p>\n\n\n\n

Uygulama \u2192 SELECT ... FROM ORDERS ...\n                    \u2502\n                    \u25bc\n            Oracle SQL Parser\n                    \u2502\n                    \u25bc\n      Bu SQL i\u00e7in tan\u0131ml\u0131 bir Patch var m\u0131?\n          \u2502                         \u2502\n        Hay\u0131r                     Evet\n          \u2502                         \u2502\n          \u25bc                         \u25bc\n   Normal plan \u00fcretilir     Patch'teki Hint'ler\n                             plana enjekte edilir\n                    \u2502\n                    \u25bc\n             Execution Plan<\/code><\/pre>\n\n\n\n
Ne Zaman \u0130\u015fe Yarar?<\/h2>\n\n\n\n
    \n
  • Kaynak koduna eri\u015femedi\u011finiz paket uygulamalarda<\/li>\n\n\n\n
  • Yeni bir release beklemeden acil bir performans m\u00fcdahalesi gerekti\u011finde<\/li>\n\n\n\n
  • Optimizer yanl\u0131\u015f bir eri\u015fim yolu veya join s\u0131ras\u0131 se\u00e7ti\u011finde<\/li>\n\n\n\n
  • Y\u0131llar \u00f6nce koda g\u00f6m\u00fclm\u00fc\u015f, tablo b\u00fcy\u00fcd\u00fck\u00e7e zararl\u0131 hale gelmi\u015f bir Hint’i etkisiz k\u0131lmak gerekti\u011finde<\/li>\n<\/ul>\n\n\n\n
    Ad\u0131m 1: SQL_ID ve Query Block Tespiti<\/h2>\n\n\n\n
    \u00d6ncelikle sorunlu sorgunun SQL_ID<\/code> de\u011ferini buluyoruz:<\/p>\n\n\n\n
    SELECT sql_id, sql_text\nFROM   v$sql\nWHERE  sql_text LIKE '%ORDERS%';<\/code><\/pre>\n\n\n\n
    Ard\u0131ndan mevcut plan\u0131 OUTLINE<\/strong> format\u0131nda g\u00f6r\u00fcnt\u00fcl\u00fcyoruz \u2014 bu format, sorgudaki her tablonun hangi query block’a ait oldu\u011funu (@\"SEL$1\"<\/code> gibi) g\u00f6sterir:<\/p>\n\n\n\n
    SELECT * FROM TABLE(\n  DBMS_XPLAN.DISPLAY_CURSOR('amz7zfdk33czb', NULL, 'ADVANCED +HINT_REPORT')\n);<\/code><\/pre>\n\n\n\n
    Query block isimlerini bilmek \u00f6nemlidir, \u00e7\u00fcnk\u00fc SQL Patch’e yazaca\u011f\u0131n\u0131z Hint’lerin Optimizer taraf\u0131ndan do\u011fru tabloya ve do\u011fru sorgu blo\u011funa uygulanmas\u0131n\u0131 garanti eder. \u0130lk bak\u0131\u015fta karma\u015f\u0131k g\u00f6r\u00fcnse de OUTLINE \u00e7\u0131kt\u0131s\u0131ndan kopyala-yap\u0131\u015ft\u0131r yaparak kolayca do\u011fru s\u00f6zdizimine ula\u015f\u0131rs\u0131n\u0131z.<\/p>\n\n\n\n
    Ad\u0131m 2: Patch Olu\u015fturma<\/h2>\n\n\n\n
    Temel s\u00f6zdizimi \u015f\u00f6yledir:<\/p>\n\n\n\n
    DECLARE\n    l_patch VARCHAR2(100);\nBEGIN\n    l_patch := DBMS_SQLDIAG.CREATE_SQL_PATCH(\n    sql_id    => 'amz7zfdk33czb',\n    hint_text => q'[INDEX(@\"SEL$1\" \"ORDERS\"@\"SEL$1\" ORDERS_IDX1)]',\n    name      => 'patch_orders_idx'\n    );\nEND;\n\/<\/code><\/pre>\n\n\n\n
    \u0130ki pratik not:<\/p>\n\n\n\n
      \n
    • sql_id<\/code> yerine sql_text<\/code> parametresi de kullan\u0131labilir. Sorgu shared pool’dan d\u00fc\u015fm\u00fc\u015fse sql_id<\/code> ile patch olu\u015fturamazs\u0131n\u0131z; bu durumda sql_text<\/code> kullanmak sizi bu ba\u011f\u0131ml\u0131l\u0131ktan kurtar\u0131r.<\/li>\n\n\n\n
    • Prosed\u00fcr\u00fc \u00e7al\u0131\u015ft\u0131rmak i\u00e7in CREATE ANY SQL PATCH<\/code> sistem yetkisine (kald\u0131rmak i\u00e7in DROP ANY SQL PATCH<\/code>‘e) sahip olman\u0131z gerekir.<\/li>\n<\/ul>\n\n\n\n
      <\/p>\n\n\n\n
      \n\n\n\n
      Senaryo 1: Sorguya Yeni Bir Hint Eklemek<\/h2>\n\n\n\n
      A\u015fa\u011f\u0131daki sorguda ORDERS.CUSTOMER_ID<\/code> \u00fczerinde indeks bulunmas\u0131na ra\u011fmen Optimizer Full Table Scan yap\u0131yor olsun:<\/p>\n\n\n\n
      SELECT * FROM orders WHERE customer_id = :b1;<\/code><\/pre>\n\n\n\n
      Uygulama kodunu de\u011fi\u015ftirmeden ORDERS_IDX1<\/code> indeksini zorlamak i\u00e7in yukar\u0131daki patch yeterlidir. Bir sonraki parse’da sorgu otomatik olarak indeksi kullan\u0131r.<\/p>\n\n\n\n
      <\/p>\n\n\n\n
      \n\n\n\n
      Senaryo 2: Koddaki Hatal\u0131 Hint’i Devre D\u0131\u015f\u0131 B\u0131rakmak<\/h2>\n\n\n\n
      Bir geli\u015ftiricinin y\u0131llar \u00f6nce sorguya ekledi\u011fi \/*+ FULL(t2) *\/<\/code> Hint’i, tablo b\u00fcy\u00fcd\u00fck\u00e7e sistemi yava\u015flatmaya ba\u015flam\u0131\u015f olsun:<\/p>\n\n\n\n
      SELECT \/*+ FULL(t2) *\/ SUM(t1.amount)\nFROM   orders t1, customers t2\nWHERE  t1.customer_id = t2.id;<\/code><\/pre>\n\n\n\n
      Burada dikkat edilmesi gereken bir nokta var: patch’e sadece yeni bir Hint eklerseniz, bu Hint koddaki mevcut Hint’le birle\u015fir<\/strong>, onun yerini almaz. Var olan bir Hint’i tamamen ge\u00e7ersiz k\u0131lmak i\u00e7in IGNORE_OPTIM_EMBEDDED_HINTS<\/code> parametresini kullanman\u0131z gerekir:<\/p>\n\n\n\n
      DECLARE\n    l_patch VARCHAR2(100);\nBEGIN\n    l_patch := DBMS_SQLDIAG.CREATE_SQL_PATCH(\n    sql_id    => 'amz7zfdk33czb',\n    hint_text => 'IGNORE_OPTIM_EMBEDDED_HINTS',\n    name      => 'patch_disable_full_hint'\n    );\nEND;\n\/<\/code><\/pre>\n\n\n\n
      Bu patch devreye girdi\u011finde, SQL i\u00e7indeki t\u00fcm g\u00f6m\u00fcl\u00fc Hint’ler yok say\u0131l\u0131r ve Optimizer kendi karar\u0131n\u0131 verir.<\/p>\n\n\n\n
      <\/p>\n\n\n\n
      \n\n\n\n
      Senaryo 3: Eski Hint’i Silip Yerine Yenisini Koymak<\/h2>\n\n\n\n
      Genellikle en kullan\u0131\u015fl\u0131 y\u00f6ntem budur: koddaki hatal\u0131 Hint’i iptal edip ayn\u0131 anda kendi Hint’inizi devreye almak.<\/p>\n\n\n\n
      DECLARE\n    l_patch VARCHAR2(100);\nBEGIN\n    l_patch := DBMS_SQLDIAG.CREATE_SQL_PATCH(\n    sql_id    => 'amz7zfdk33czb',\n    hint_text => q'[\n        IGNORE_OPTIM_EMBEDDED_HINTS\n        LEADING(@\"SEL$1\" \"T2\"@\"SEL$1\" \"T1\"@\"SEL$1\")\n        INDEX(@\"SEL$1\" \"T2\"@\"SEL$1\" CUST_IDX)\n        ]',\n        name      => 'patch_override_and_rehint'\n    );\nEND;\n\/<\/code><\/pre>\n\n\n\n
      Sonu\u00e7: koddaki FULL(t2)<\/code> Hint’i devre d\u0131\u015f\u0131 kal\u0131r, Optimizer yaln\u0131zca sizin verdi\u011finiz LEADING<\/code> ve INDEX<\/code> Hint’lerine g\u00f6re plan \u00fcretir.<\/p>\n\n\n\n
      <\/p>\n\n\n\n
      \n\n\n\n
      Bonus: OPT_PARAM ile Optimizer Parametresini Tek Sorguda De\u011fi\u015ftirmek<\/h2>\n\n\n\n
      SQL Patch, eri\u015fim yolu veya join s\u0131ras\u0131 gibi klasik Hint’lerle s\u0131n\u0131rl\u0131 de\u011fil. OPT_PARAM<\/code> Hint’i sayesinde, t\u00fcm sistemi etkileyecek bir spfile de\u011fi\u015fikli\u011fi yapmadan sadece tek bir sorgu i\u00e7in optimizer parametresini ge\u00e7ici olarak ayarlayabilirsiniz:<\/p>\n\n\n\n
      DECLARE\n    l_patch VARCHAR2(100);\nBEGIN\n    l_patch := DBMS_SQLDIAG.CREATE_SQL_PATCH(\n    sql_id    => 'amz7zfdk33czb',\n    hint_text => q'[OPT_PARAM('optimizer_index_cost_adj' 20)]',\n    name      => 'patch_orders_cost_adj'\n    );\nEND;\n\/<\/code><\/pre>\n\n\n\n
      Bu, \u00f6zellikle tek bir sorgunun indeks kullan\u0131m\u0131na kar\u015f\u0131 “isteksiz” davrand\u0131\u011f\u0131 ama global parametreyi de\u011fi\u015ftirmek istemedi\u011finiz durumlarda \u00e7ok i\u015fe yarar.<\/p>\n\n\n\n
      <\/p>\n\n\n\n
      \n\n\n\n
      Patch’in Kullan\u0131ld\u0131\u011f\u0131n\u0131 Do\u011frulamak<\/h2>\n\n\n\n
      Plan\u0131 yeniden g\u00f6r\u00fcnt\u00fcledi\u011finizde, alt\u0131ndaki Note<\/strong> b\u00f6l\u00fcm\u00fc patch’in devrede oldu\u011funu do\u011frular:<\/p>\n\n\n\n
      Note\n-----\n   - SQL patch \"patch_override_and_rehint\" used for this statement<\/code><\/pre>\n\n\n\n
      Sistemdeki t\u00fcm aktif patch’leri listelemek i\u00e7in:<\/p>\n\n\n\n
      SELECT name, sql_text, status, created\nFROM   dba_sql_patches;<\/code><\/pre>\n\n\n\n
      <\/p>\n\n\n\n
      \n\n\n\n
      Patch’i Kald\u0131rmak<\/h2>\n\n\n\n
      Kod d\u00fczeltildi\u011finde veya patch’e art\u0131k ihtiya\u00e7 kalmad\u0131\u011f\u0131nda:<\/p>\n\n\n\n
      BEGIN\n    DBMS_SQLDIAG.DROP_SQL_PATCH(name => 'patch_override_and_rehint');\nEND;\n\/<\/code><\/pre>\n\n\n\n
      <\/p>\n\n\n\n
      \n\n\n\n
      SQL Patch, SQL Profile ve SQL Plan Baseline Fark\u0131<\/h2>\n\n\n\n
      Bu \u00fc\u00e7 mekanizma s\u0131k s\u0131k kar\u0131\u015ft\u0131r\u0131l\u0131r ama ama\u00e7lar\u0131 farkl\u0131d\u0131r:<\/p>\n\n\n\n
      \u00d6zellik<\/th>SQL Patch<\/th>SQL Profile<\/th>SQL Plan Baseline<\/th><\/tr><\/thead>
      SQL metnini de\u011fi\u015ftirir mi<\/td>Hay\u0131r<\/td>Hay\u0131r<\/td>Hay\u0131r<\/td><\/tr>
      Manuel Hint eklenebilir mi<\/td>Evet<\/td>Hay\u0131r<\/td>Dolayl\u0131 yoldan<\/td><\/tr>
      Belirli bir plan\u0131 sabitler mi<\/td>Hay\u0131r<\/td>Hay\u0131r<\/td>Evet<\/td><\/tr>
      Temel amac\u0131<\/td>Optimizer davran\u0131\u015f\u0131n\u0131 manuel y\u00f6nlendirmek<\/td>Optimizer’\u0131n maliyet tahminini iyile\u015ftirmek<\/td>Bilinen iyi bir plan\u0131 koruma alt\u0131na almak<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
      K\u0131sacas\u0131: Optimizer’a “\u015funu yap” demek istiyorsan\u0131z SQL Patch, Optimizer’\u0131n daha do\u011fru tahmin yapmas\u0131na yard\u0131mc\u0131 olmak istiyorsan\u0131z SQL Profile, bilinen iyi bir plan\u0131 sabitlemek istiyorsan\u0131z SQL Plan Baseline devreye girer.<\/p>\n\n\n\n
      <\/p>\n\n\n\n
      \n\n\n\n
      Dikkat Edilmesi Gerekenler<\/h2>\n\n\n\n
        \n
      • Ge\u00e7ici bir m\u00fcdahale arac\u0131d\u0131r.<\/strong> Kal\u0131c\u0131 \u00e7\u00f6z\u00fcm her zaman istatistiklerin, indeks yap\u0131s\u0131n\u0131n veya SQL’in kendisinin d\u00fczeltilmesidir.<\/li>\n\n\n\n
      • E\u015fle\u015ftirme mant\u0131\u011f\u0131n\u0131 bilin.<\/strong> Patch, SQL metninin normalize edilmi\u015f haline g\u00f6re e\u015fle\u015fir; bo\u015fluk ve anahtar kelimelerin b\u00fcy\u00fck\/k\u00fc\u00e7\u00fck yaz\u0131m\u0131 sorun \u00e7\u0131karmaz. Ancak literal de\u011ferlerin de\u011fi\u015fmesi veya SQL’in yap\u0131sal olarak farkl\u0131la\u015fmas\u0131 e\u015fle\u015fmeyi bozar.<\/li>\n\n\n\n
      • Multitenant ortamda PDB bazl\u0131d\u0131r.<\/strong> CDB$ROOT’ta olu\u015fturdu\u011funuz bir patch di\u011fer PDB’leri etkilemez; her PDB kendi patch’lerini ayr\u0131 y\u00f6netir.<\/li>\n\n\n\n
      • Yaz\u0131m hatalar\u0131 sessizce yutulur.<\/strong> Yanl\u0131\u015f yaz\u0131lan bir Hint, hata vermeden yok say\u0131l\u0131r. Bu y\u00fczden Hint’i patch’e koymadan \u00f6nce mutlaka OUTLINE \u00e7\u0131kt\u0131s\u0131yla do\u011frulay\u0131n ve \u00f6nce test ortam\u0131nda deneyin.<\/li>\n<\/ul>\n\n\n\n
        <\/p>\n\n\n\n
        \n\n\n\n
        Sonu\u00e7<\/h2>\n\n\n\n
        SQL Patch, kriz anlar\u0131nda yeni bir release beklemeden, paket uygulaman\u0131n i\u00e7 yap\u0131s\u0131na dokunmadan Optimizer’\u0131n davran\u0131\u015f\u0131n\u0131 de\u011fi\u015ftirmenizi sa\u011flayan esnek bir ara\u00e7t\u0131r. \u00d6zellikle IGNORE_OPTIM_EMBEDDED_HINTS<\/code> ile koddaki hatal\u0131 Hint’leri ge\u00e7ersiz k\u0131labilmek, veritaban\u0131 y\u00f6neticilerinin elindeki en pratik tekniklerden biridir \u2014 ama unutmay\u0131n, as\u0131l hedef her zaman k\u00f6k nedeni \u00e7\u00f6zmek olmal\u0131.<\/p>\n\n\n\n
        <\/p>\n\n\n\n
        \n\n\n\n
        S\u0131k Sorulan Sorular<\/h2>\n\n\n\n
        SQL Patch SQL metnini de\u011fi\u015ftirir mi?<\/strong> Hay\u0131r. SQL metni ayn\u0131 kal\u0131r; Oracle yaln\u0131zca parse a\u015famas\u0131nda Optimizer’a ek Hint uygular.<\/p>\n\n\n\n
        Patch kal\u0131c\u0131 m\u0131d\u0131r?<\/strong> Evet. Siz DROP_SQL_PATCH<\/code> ile kald\u0131rana kadar veritaban\u0131nda kal\u0131r.<\/p>\n\n\n\n
        RAC ortam\u0131nda \u00e7al\u0131\u015f\u0131r m\u0131?<\/strong> Evet. Patch veritaban\u0131 seviyesinde sakland\u0131\u011f\u0131 i\u00e7in t\u00fcm instance’lar taraf\u0131ndan kullan\u0131l\u0131r.<\/p>\n\n\n\n
        Her performans sorununu \u00e7\u00f6zer mi?<\/strong> Hay\u0131r. G\u00fcncel olmayan istatistikler, eksik indeksler veya hatal\u0131 tasar\u0131m gibi k\u00f6k nedenler varsa \u00f6nce onlar\u0131n giderilmesi gerekir. SQL Patch, do\u011fru analiz sonras\u0131nda uygulanan hedefe y\u00f6nelik bir m\u00fcdahaledir.<\/p>\n\n\n\n
        <\/p>\n\n\n\n
        \n\n\n\n
        <\/p>\n","protected":false},"excerpt":{"rendered":"
        Oracle SQL Patch nedir, nas\u0131l \u00e7al\u0131\u015f\u0131r? DBMS_SQLDIAG ile uygulama kodunu de\u011fi\u015ftirmeden Hint eklemeyi, hatal\u0131 Hint’leri devre d\u0131\u015f\u0131 b\u0131rakmay\u0131 ve do\u011fru kullan\u0131m pratiklerini \u00f6rneklerle \u00f6\u011frenin.<\/p>\n","protected":false},"author":1,"featured_media":1021,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[43],"tags":[],"class_list":["post-1020","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-genel-en"],"_links":{"self":[{"href":"https:\/\/oraclius.com.tr\/tr\/wp-json\/wp\/v2\/posts\/1020","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=1020"}],"version-history":[{"count":2,"href":"https:\/\/oraclius.com.tr\/tr\/wp-json\/wp\/v2\/posts\/1020\/revisions"}],"predecessor-version":[{"id":1032,"href":"https:\/\/oraclius.com.tr\/tr\/wp-json\/wp\/v2\/posts\/1020\/revisions\/1032"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/oraclius.com.tr\/tr\/wp-json\/wp\/v2\/media\/1021"}],"wp:attachment":[{"href":"https:\/\/oraclius.com.tr\/tr\/wp-json\/wp\/v2\/media?parent=1020"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oraclius.com.tr\/tr\/wp-json\/wp\/v2\/categories?post=1020"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oraclius.com.tr\/tr\/wp-json\/wp\/v2\/tags?post=1020"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}