{"type": "body", "content": "", "style": "font-family: charter, Optima, Georgia, \"Times New Roman\", \"Times\", serif; max-width: 800px; margin: auto; padding: 1em; line-height: 1.5em; color: #222", "bbox": [224, 0, 832, 1101], "children": [{"type": "nav", "content": "", "style": null, "bbox": [240, 32, 800, 45], "children": [{"type": "ul", "content": "", "style": "padding: 0; text-align: center", "bbox": [240, 32, 800, 27], "children": [{"type": "li", "content": "", "style": "display: inline-block", "bbox": [316, 35, 49, 24], "children": [{"type": "a", "content": "Home", "style": "text-decoration: none; background: #fff; padding: 5px; border-radius: 5px; color: #00e", "bbox": [316, 33, 49, 27], "children": []}]}, {"type": "li", "content": "", "style": "display: inline-block", "bbox": [369, 35, 50, 24], "children": [{"type": "a", "content": "About", "style": "text-decoration: none; background: #fff; padding: 5px; border-radius: 5px; color: #00e", "bbox": [369, 33, 50, 27], "children": []}]}, {"type": "li", "content": "", "style": "display: inline-block", "bbox": [423, 35, 82, 24], "children": [{"type": "a", "content": "Downloads", "style": "text-decoration: none; background: #fff; padding: 5px; border-radius: 5px; color: #00e", "bbox": [423, 33, 82, 27], "children": []}]}, {"type": "li", "content": "", "style": "display: inline-block", "bbox": [510, 35, 52, 24], "children": [{"type": "a", "content": "Topics", "style": "text-decoration: none; background: #fff; padding: 5px; border-radius: 5px; color: #00e", "bbox": [510, 33, 52, 27], "children": []}]}, {"type": "li", "content": "", "style": "display: inline-block", "bbox": [566, 35, 54, 24], "children": [{"type": "a", "content": "Videos", "style": "text-decoration: none; background: #fff; padding: 5px; border-radius: 5px; color: #00e", "bbox": [566, 33, 54, 27], "children": []}]}, {"type": "li", "content": "", "style": "display: inline-block", "bbox": [625, 35, 59, 24], "children": [{"type": "a", "content": "Contact", "style": "text-decoration: none; background: #fff; padding: 5px; border-radius: 5px; color: #00e", "bbox": [625, 33, 59, 27], "children": []}]}, {"type": "li", "content": "", "style": "display: inline-block", "bbox": [689, 35, 80, 24], "children": [{"type": "a", "content": "Consulting", "style": "text-decoration: none; background: #fff; padding: 5px; border-radius: 5px; color: #00e", "bbox": [689, 33, 80, 27], "children": []}]}, {"type": "li", "content": "", "style": "display: inline-block", "bbox": [773, 35, 53, 24], "children": [{"type": "a", "content": "Events", "style": "text-decoration: none; background: #fff; padding: 5px; border-radius: 5px; color: #00e", "bbox": [773, 33, 53, 27], "children": []}]}, {"type": "li", "content": "", "style": "display: inline-block", "bbox": [830, 35, 64, 24], "children": [{"type": "a", "content": "Training", "style": "text-decoration: none; background: #fff; padding: 5px; border-radius: 5px; color: #00e", "bbox": [830, 33, 64, 27], "children": []}]}, {"type": "li", "content": "", "style": "display: inline-block", "bbox": [899, 32, 64, 27], "children": [{"type": "a", "content": "", "style": "text-decoration: none; background: #fff; padding: 5px; border-radius: 5px; color: #00e", "bbox": [899, 33, 34, 27], "children": [{"type": "img", "content": "", "style": "width: 20px; height: 20px; padding: 0; margin: 0; position: relative; top: 5px; max-width: 100%", "bbox": [904, 37, 20, 20], "children": []}]}, {"type": "a", "content": "", "style": "text-decoration: none; background: #fff; padding: 5px; border-radius: 5px; color: #00e", "bbox": [933, 33, 30, 27], "children": [{"type": "img", "content": "", "style": "width: 20px; height: 20px; padding: 0; margin: 0; position: relative; top: 5px; max-width: 100%", "bbox": [938, 37, 20, 20], "children": []}]}]}]}, {"type": "hr", "content": "", "style": "border-style: dashed; color: #ddd", "bbox": [240, 75, 800, 2], "children": []}]}, {"type": "div", "content": "", "style": "text-decoration: none; background: #fff; padding: 5px; border-radius: 5px; text-align: center", "bbox": [240, 85, 800, 154], "children": [{"type": "h1", "content": "", "style": null, "bbox": [245, 111, 790, 27], "children": [{"type": "span", "content": "Bind Variable Peeking \u2013 execution plan inefficiency", "style": "font-size: 0.75em", "bbox": [377, 113, 525, 26], "children": []}]}, {"type": "h2", "content": "Tanel Poder", "style": "font-size: 1em", "bbox": [245, 159, 790, 24], "children": []}, {"type": "h2", "content": "2010-02-02", "style": "font-size: 1em", "bbox": [245, 197, 790, 24], "children": []}]}, {"type": "main", "content": "", "style": null, "bbox": [240, 255, 800, 600], "children": [{"type": "p", "content": "", "style": null, "bbox": [240, 255, 800, 48], "children": [{"type": "a", "content": "Beyond Oracle Wait interface article", "style": null, "bbox": [281, 258, 233, 17], "children": []}]}, {"type": "p", "content": "I have uploaded the test case that you can run here (note that it drops and creates tables T1..T5 in your schema):", "style": null, "bbox": [240, 319, 800, 24], "children": []}, {"type": "p", "content": "", "style": null, "bbox": [240, 359, 800, 24], "children": [{"type": "a", "content": "/ast/02_bind_peeking_nested_loops.sql", "style": null, "bbox": [240, 362, 251, 17], "children": []}]}, {"type": "p", "content": "Basically what I do is this:", "style": null, "bbox": [240, 399, 800, 24], "children": []}, {"type": "ol", "content": "", "style": null, "bbox": [240, 439, 800, 120], "children": [{"type": "li", "content": "I run the query with bind variable values where only a handful of rows match the filter condition. Thus Oracle picks nested loop join (and indexed access path)", "style": null, "bbox": [280, 439, 760, 48], "children": []}, {"type": "li", "content": "", "style": null, "bbox": [280, 487, 760, 72], "children": [{"type": "em", "content": "same", "style": null, "bbox": [375, 490, 32, 17], "children": []}, {"type": "strong", "content": "", "style": null, "bbox": [683, 538, 34, 17], "children": [{"type": "em", "content": "every", "style": null, "bbox": [683, 538, 34, 17], "children": []}]}]}]}, {"type": "p", "content": "", "style": null, "bbox": [240, 575, 800, 24], "children": [{"type": "em", "content": "lots", "style": null, "bbox": [398, 578, 23, 17], "children": []}]}, {"type": "p", "content": "And an interesting thing with my script is that the problem still happens in Oracle 11.1 and 11.2 too!", "style": null, "bbox": [240, 615, 800, 24], "children": []}, {"type": "p", "content": "", "style": null, "bbox": [240, 655, 800, 48], "children": [{"type": "em", "content": "reactive", "style": null, "bbox": [375, 682, 50, 17], "children": []}, {"type": "em", "content": "after", "style": null, "bbox": [608, 682, 30, 17], "children": []}]}, {"type": "p", "content": "", "style": null, "bbox": [240, 719, 800, 96], "children": [{"type": "em", "content": "Update: This is also a problem on Oracle 12.1.02. Shouldn\u2019t Oracle\u2019s adaptive plans avoid\u00a0this issue? Nope! As the adaptive plan decision is done during the 1st execution of the cursor and we correctly pick a nested loops plan for the 1st set of binds (with no or little matching rows). But our problem shows up only during the 2nd execution with \u201cbad\u201d bind variable values in place.", "style": null, "bbox": [240, 722, 798, 89], "children": []}]}, {"type": "p", "content": "", "style": null, "bbox": [240, 831, 800, 24], "children": [{"type": "a", "content": "script", "style": null, "bbox": [424, 834, 35, 17], "children": []}]}]}, {"type": "hr", "content": "", "style": "border-style: dashed; color: #ddd", "bbox": [240, 871, 800, 2], "children": []}, {"type": "div", "content": "", "style": "display: inline-block", "bbox": [240, 881, 709, 152], "children": [{"type": "span", "content": "", "style": "display: inline-block; width: 105px;", "bbox": [240, 993, 105, 24], "children": []}, {"type": "span", "content": "", "style": "display: inline-block; width: 600px;", "bbox": [349, 881, 600, 152], "children": [{"type": "div", "content": "", "style": null, "bbox": [349, 897, 600, 120], "children": [{"type": "ol", "content": "", "style": null, "bbox": [349, 897, 600, 120], "children": [{"type": "li", "content": "", "style": null, "bbox": [389, 897, 560, 96], "children": [{"type": "em", "content": "", "style": null, "bbox": [389, 900, 557, 89], "children": [{"type": "strong", "content": "new format", "style": null, "bbox": [693, 900, 75, 17], "children": []}, {"type": "a", "content": "Advanced Oracle SQL Tuning training", "style": null, "bbox": [389, 924, 247, 17], "children": []}, {"type": "a", "content": "Advanced Oracle Troubleshooting training", "style": null, "bbox": [644, 924, 275, 17], "children": []}, {"type": "a", "content": "Linux Performance & Troubleshooting training", "style": null, "bbox": [389, 948, 304, 17], "children": []}]}]}, {"type": "li", "content": "", "style": null, "bbox": [389, 993, 560, 24], "children": [{"type": "em", "content": "", "style": null, "bbox": [389, 996, 319, 17], "children": [{"type": "a", "content": "Get weekly updates", "style": null, "bbox": [389, 996, 124, 17], "children": []}]}]}]}]}]}]}, {"type": "hr", "content": "", "style": "border-style: dashed; color: #ddd", "bbox": [240, 1041, 800, 2], "children": []}, {"type": "footer", "content": "", "style": "text-align: center", "bbox": [240, 1051, 800, 34], "children": [{"type": "hr", "content": "", "style": "border-style: dashed; color: #ddd", "bbox": [240, 1051, 800, 2], "children": []}, {"type": "a", "content": "Tanel P\u00f5der", "style": "text-decoration: none", "bbox": [572, 1064, 76, 17], "children": []}]}]}
<!DOCTYPE html>

<html><head><title>Bind Variable Peeking – execution plan inefficiency | Tanel Poder Consulting</title></head><body><nav><ul class="menu"><li><a>Home</a></li><li><a>About</a></li><li><a>Downloads</a></li><li><a>Topics</a></li><li><a>Videos</a></li><li><a>Contact</a></li><li><a>Consulting</a></li><li><a>Events</a></li><li><a>Training</a></li><li><a><img class="socialbutton2" src="/files/images/icons/twitter-social.png"/></a><a><img class="socialbutton2" src="/files/images/icons/mastodon.png"/></a></li></ul><hr/></nav><div class="article-meta"><h1><span class="title">Bind Variable Peeking – execution plan inefficiency</span></h1><h2 class="author">Tanel Poder</h2><h2 class="date">2010-02-02</h2></div><main><p>In my <a>Beyond Oracle Wait interface article</a> I troubleshooted a test case where an execution plan somehow went “crazy” and started burning CPU, lots of logical IOs and the query never completed.</p><p>I have uploaded the test case that you can run here (note that it drops and creates tables T1..T5 in your schema):</p><p><a>/ast/02_bind_peeking_nested_loops.sql</a></p><p>Basically what I do is this:</p><ol><li>I run the query with bind variable values where only a handful of rows match the filter condition. Thus Oracle picks nested loop join (and indexed access path)</li><li>Then I run the <em>same</em> query with different bind values, where a lot of rows match the filter condition. Oracle reuses existing execution plan (with nested loops!!!). Oracle ends up looping through a lot of blocks again and again (because nested loop visits the “right” side of the join once for <strong><em>every</em></strong> row coming from the “left” side of the join).</li></ol><p>Using nested loops over <em>lots</em> of rows is a sure way to kill your performance.</p><p>And an interesting thing with my script is that the problem still happens in Oracle 11.1 and 11.2 too!</p><p>Oracle 11g has Adaptive Cursor Sharing, right? This should take care of such a problem, right? Well no, adaptive bind variable peeking is a <em>reactive</em> technique – it only kicks in <em>after</em> the problem has happened!</p><p><em>Update: This is also a problem on Oracle 12.1.02. Shouldn’t Oracle’s adaptive plans avoid this issue? Nope! As the adaptive plan decision is done during the 1st execution of the cursor and we correctly pick a nested loops plan for the 1st set of binds (with no or little matching rows). But our problem shows up only during the 2nd execution with “bad” bind variable values in place.</em></p><p>So feel free to download the <a>script</a>, review it and test it out!</p></main><hr/><div style="display: inline-block"><span style="display: inline-block; width: 105px;"><a><img src="/files/images/tanelpoder_small.png"/></a></span><span style="display: inline-block; width: 600px;"><div><ol><li><em>Check out my 2022 online training classes in a <strong>new format</strong>!<br/><a>Advanced Oracle SQL Tuning training</a>.
        <a>Advanced Oracle Troubleshooting training</a>,
        <a>Linux Performance &amp; Troubleshooting training</a>:
        Learn when it's convenient to you and get your training questions answered even months after taking the class!
      </em></li><li><em><a>Get weekly updates</a> by email or follow Social/RSS</em></li></ol></div></span></div><hr/><footer><hr/>
  © <a>Tanel Põder</a> 2007-2023
  </footer><style>body {
    font-family: charter, Optima, Georgia, "Times New Roman", "Times", serif
    }
body {
    max-width: 800px;
    margin: auto;
    padding: 1em;
    line-height: 1.5em;
    color: #222
    }
.socialbutton2 {
    width: 20px;
    height: 20px;
    padding: 0;
    margin: 0;
    position: relative;
    top: 5px
    }
.menu {
    padding: 0
    }
.menu li {
    display: inline-block
    }
.article-meta, .menu a {
    text-decoration: none;
    background: #fff;
    padding: 5px;
    border-radius: 5px
    }
.menu a {
    color: #00e
    }
.menu, .article-meta, footer {
    text-align: center
    }
.title {
    font-size: 0.75em
    }
h2 {
    font-size: 1em
    }
footer a {
    text-decoration: none
    }
hr {
    border-style: dashed;
    color: #ddd
    }
img, iframe, video {
    max-width: 100%
    }</style></body></html>