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

GOOD NEWS is, It will kick in soon.

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

According to the "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 with 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 a skew data. Almost all rows is 10. Also I will create an index to observe an access path change in execution plan and i will collect table statistics and create a histogram on equality predicate filter column to make sure that when table is queried with an equality predicate, cursor is marked as bind sensitive.

 

 

At this time, i will run a bind sensitive sql and show how execution plan changes according to the bind parameter value. First set of commands will be run in an sqlplus session. Below is the output of the commands run in sqlplus.

Sample query is executed with bind value 1 on first execution. Sql statement is marked as bind sensitive. Table is accessed via index range scan. Next two execution has been done with bind value 10. As almost all rows of table is 10, plan change observed on the third execution. A new child cursor  has been created which uses full table scan.

The results observed so far was the expected behavior.

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

This time, Sample query is executed with bind value 1 and 10 in TOAD. For both of bind values, table is accessed via 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 sqlplus and TOAD. First one belongs to sqlplus session and the following belongs to TOAD session.

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

--When using sqlplus

--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 cursor is parsed without bind peeking.

*******************************************
Peeked values of the binds in SQL statement
*******************************************

----- 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.