In my Beyond Oracle Wait interface article 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.
I have uploaded the test case that you can run here (note that it drops and creates tables T1..T5 in your schema):
/ast/02_bind_peeking_nested_loops.sql
Basically what I do is this:
- 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)
- Then I run the same 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 every row coming from the “left” side of the join).
Using nested loops over lots of rows is a sure way to kill your performance.
And an interesting thing with my script is that the problem still happens in Oracle 11.1 and 11.2 too!
Oracle 11g has Adaptive Cursor Sharing, right? This should take care of such a problem, right? Well no, adaptive bind variable peeking is a reactive technique – it only kicks in after the problem has happened!
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.
So feel free to download the script, review it and test it out!