ACS and bind variable peeking does not kick in when using TOAD

GOOD NEWS is, It will kick in soon.

I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there.

Last week, when I was investigating a performance issue, I observed that an identical query resulted in different execution plans in TOAD and sqlplus.

According to "Doc ID 1671642.1 How To Diagnose Why an Identical Query Has Different Plans (and Performance) in Different Environments", there may be different factors in plan selection. Although not all, some of them are as follows:

Statistics
  * Base Object Statistics
  * System Statistics
  * Dynamic Statistics (OPTIMIZER_DYNAMIC_SAMPLING)
  * Statistics Feedback (_OPTIMIZER_USE_FEEDBACK)

Physical Differences
  * Physical and Logical Layout of the Database
    * Different volume and distribution of data
    * Different block sizes (DB_BLOCK_SIZE)
  * Schema Changes

Settings and Features
  * Parallelism
  * Baselines, Profiles and Stored Outlines
  * Bind Variables

Optimizer and System Parameters
  * SPFILE/PFILE
  * PGA and SORT AREA
  * Optimizer Parameters (OPTIMIZER_FEATURES_ENABLE, OPTIMIZER_INDEX_CACHING, CURSOR_SHARING etc.)
  * Client Settings ( NLS settings etc )

In this specific case, the root cause of the issue was related to ACS.

I will show a mini demo, how different clients(sqlplus vs TOAD) may affect a sql performance and what goes on behind the scenes. Before starting the test case, these are the parameters play role in ACS behavior.

To be Bind Sensitive, a cursor needs to use Bind Variable;

* Either directly; 
* Through cursor_sharing set to FORCE

Query must be in one of the following forms. 

    * With A range predicate (simple statistics) 

    SQL> select count(1) from acs_test_table where age <= :p_age; 

    * An equality predicate (histogram) 

    SQL> select count(1) from acs_test_table where age = :p_age; 

    * A predicate on partition key (simple statistics) 

    SQL> select count(1) from acs_test_table where age = :p_age;

First, I will create a table with 500K rows with skewed data. Almost all rows have the value 10. Also, I will create an index to observe an access path change in the execution plan, and I will collect table statistics and create a histogram on the equality predicate filter column to make sure that when the table is queried with an equality predicate, the cursor is marked as bind-sensitive.

 

 

At this time, I will run a bind-sensitive SQL and show how the execution plan changes according to the bind parameter value. The first set of commands will be run in an SQL*Plus session. Below is the output of the commands run in SQL*Plus.

The sample query is executed with a bind value of 1 on the first execution. The SQL statement is marked as bind-sensitive. The table is accessed via an index range scan. The next two executions have been done with a bind value of 10. As almost all rows of the table are 10, a plan change is observed on the third execution. A new child cursor has been created which uses a full table scan.

The results observed so far were the expected behavior.

After flushing the shared pool, I will run the same set of commands within TOAD version 16.1.53. Results are different and that is not expected. 

This time, the sample query is executed with bind values 1 and 10 in TOAD. For both of the bind values, the table is accessed via an index range scan. No child cursors are created on subsequent executions and no plan change occurred.

Adaptive Cursor Sharing did not take place when using TOAD. 

Here is the outline data of the cursor when using SQL*Plus and TOAD. The first one belongs to the SQL*Plus session and the following belongs to the TOAD session.

select plan_table_output from dbms_xplan.display_cursor('a39ruh5sdz1k5',format=>'ADVANCED');

--When using SQL*Plus

--When using TOAD

As outline data shows, Bind peeking does not kick in when using TOAD. Also in 10053 event tracing, we can see that "No bind buffers allocated" as the cursor is parsed without bind peeking.

----- Bind Info (kkscoacd) -----
 Bind#0
  oacdty=01 mxl=32(01) mxlc=01 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000010 frm=01 csi=39 siz=32 off=0
  No bind buffers allocated
 

I found that this issue was first reported in June 2015 in Toad World® Forums "Bind variable peeking doesn’t work ".

I have revived old thread and thankfully, John Dorlon  replied my post and investigated the issue. As John says, TOAD use an OCI "Describe" command (as documented here) before query execution to learn the datatypes and there is a bug (Bug 9630092) related with OCI_DESCRIBE_ONLY, also Patrick Jolliffe has a blog post(Bind Variable Peeking Problems via OCI) about it, bad news is that the solution offered with bug fix ("_fix_control"='9630092:ON') did not help the issue. 

Here is the good news, John made a change so that TOAD don't do the describe if OCI Array Buffers size option changed to "Manual". (Under View menu, Toad Options, Oracle, General) and it is available with Toad Beta 17.1.next, To Be Released 11-Dec-2023.

I have also tested this behavior with Oracle SQL Developer(21.4.3)  and DBeaver(23.3.0). SQLDeveloper was the only capable product for running sql statements as bind aware. With the next beta release, TOAD will also have the capability to work as bind aware.

Hope it helps.