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.

 

	CREATE TABLE SYSTEM.ACS_TEST_TABLE (COL1 NUMBER, COL2 NUMBER);
	-- ALMOST ALL OF THEM 10, ONLY 10 ROWS is different
	INSERT INTO SYSTEM.ACS_TEST_TABLE
	SELECT CASE WHEN ROWNUM < 10 THEN ROWNUM ELSE 10 END, ROWNUM
	FROM DUAL CONNECT BY LEVEL<= 500000;
	COMMIT;
	CREATE INDEX SYSTEM.INDEX1_TESTTAB ON SYSTEM.ACS_TEST_TABLE(COL1);
	EXEC DBMS_STATS.GATHER_TABLE_STATS ('SYSTEM','ACS_TEST_TABLE');
	-- CREATE HISTOGRAM -- To make sure that sql is marked as bind_sensitive when equality predicates are used.
	EXEC DBMS_STATS.GATHER_TABLE_STATS ('SYSTEM','ACS_TEST_TABLE', METHOD_OPT=>'FOR COLUMNS COL1 SIZE 254');

view raw create_ACS_testcase.txt hosted with ❤ by GitHub

 

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.

	SYS@bltdb1 10/12/23 16:15 > alter system flush shared_pool;
	SYS@bltdb1 10/12/23 15:42 > var B1 NUMBER;
	SYS@bltdb1 10/12/23 15:43 > exec :b1 := 1;
	SYS@bltdb1 10/12/23 16:20 > col plan_table_output format a150
	SYS@bltdb1 10/12/23 16:21 > SELECT MAX (COL2) AS MAXDATA, COUNT(*) AS COUNTDATA, SUM(COL2) AS SUMDATA FROM SYSTEM.ACS_TEST_TABLE WHERE COL1=:B1;
	MAXDATA COUNTDATA SUMDATA
	--------------- --------------- ---------------
	1 1 1
	1 row selected.
	SYS@bltdb1 10/12/23 16:21 > select * from dbms_xplan.display_cursor(format=>'+peeked_binds');
	PLAN_TABLE_OUTPUT
	------------------------------------------------------------------------------------------------------------------------------------------------------
	SQL_ID a39ruh5sdz1k5, child number 0
	-------------------------------------
	SELECT MAX (COL2) AS MAXDATA, COUNT(*) AS COUNTDATA, SUM(COL2) AS
	SUMDATA FROM SYSTEM.ACS_TEST_TABLE WHERE COL1=:B1
	Plan hash value: 1498943009
	-------------------------------------------------------------------------------------------------------
	| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
	-------------------------------------------------------------------------------------------------------
	| 0 | SELECT STATEMENT | | | | 4 (100)| |
	| 1 | SORT AGGREGATE | | 1 | 8 | | |
	| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ACS_TEST_TABLE | 1 | 8 | 4 (0)| 00:00:01 |
	|* 3 | INDEX RANGE SCAN | INDEX1_TESTTAB | 1 | | 3 (0)| 00:00:01 |
	-------------------------------------------------------------------------------------------------------
	Peeked Binds (identified by position):
	--------------------------------------
	1 - :B1 (NUMBER): 1
	PLAN_TABLE_OUTPUT
	------------------------------------------------------------------------------------------------------------------------------------------------------
	Predicate Information (identified by operation id):
	---------------------------------------------------
	3 - access("COL1"=:B1)
	SYS@bltdb1 10/12/23 16:43 > col SQL_ID format a15
	SYS@bltdb1 10/12/23 16:43 > col IS_SHAREABLE format a15
	SYS@bltdb1 10/12/23 16:44 > col IS_BIND_SENSITIVE format a20
	SYS@bltdb1 10/12/23 16:44 > col IS_BIND_AWARE format a15
	SYS@bltdb1 10/12/23 16:45 > SELECT SQL_ID,CHILD_NUMBER,IS_SHAREABLE,IS_BIND_SENSITIVE,IS_BIND_AWARE FROM V$SQL WHERE SQL_ID='a39ruh5sdz1k5';
	SQL_ID CHILD_NUMBER IS_SHAREABLE IS_BIND_SENSITIVE IS_BIND_AWARE
	--------------- --------------- --------------- -------------------- ---------------
	a39ruh5sdz1k5 0 Y Y N
	-- Sql is executed with bind value 1 on first execution. Sql is marked as bind sensitive. Table is accessed via index range scan.
	-- Next two execution will be with bind value 10.
	-- Plan change will be observed on second execution as almost all rows of table is 10. A new child cursor will be created which uses full table scan.
	SYS@bltdb1 10/12/23 16:53 > exec :b1 := 10;
	SYS@bltdb1 10/12/23 16:53 > SELECT MAX (COL2) AS MAXDATA, COUNT(*) AS COUNTDATA, SUM(COL2) AS SUMDATA FROM SYSTEM.ACS_TEST_TABLE WHERE COL1=:B1;
	MAXDATA COUNTDATA SUMDATA
	--------------- --------------- ---------------
	500000 499991 125000249955
	SYS@bltdb1 10/12/23 16:53 > SELECT MAX (COL2) AS MAXDATA, COUNT(*) AS COUNTDATA, SUM(COL2) AS SUMDATA FROM SYSTEM.ACS_TEST_TABLE WHERE COL1=:B1;
	MAXDATA COUNTDATA SUMDATA
	--------------- --------------- ---------------
	500000 499991 125000249955
	SYS@bltdb1 10/12/23 16:53 > select * from dbms_xplan.display_cursor(format=>'+peeked_binds');
	PLAN_TABLE_OUTPUT
	------------------------------------------------------------------------------------------------------------------------------------------------------
	SQL_ID a39ruh5sdz1k5, child number 1
	-------------------------------------
	SELECT MAX (COL2) AS MAXDATA, COUNT(*) AS COUNTDATA, SUM(COL2) AS
	SUMDATA FROM SYSTEM.ACS_TEST_TABLE WHERE COL1=:B1
	Plan hash value: 2424476182
	-------------------------------------------------------------------------------------
	| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
	-------------------------------------------------------------------------------------
	| 0 | SELECT STATEMENT | | | | 248 (100)| |
	| 1 | SORT AGGREGATE | | 1 | 8 | | |
	|* 2 | TABLE ACCESS FULL| ACS_TEST_TABLE | 499K| 3906K| 248 (2)| 00:00:01 |
	-------------------------------------------------------------------------------------
	Peeked Binds (identified by position):
	--------------------------------------
	1 - :B1 (NUMBER): 10
	Predicate Information (identified by operation id):
	PLAN_TABLE_OUTPUT
	------------------------------------------------------------------------------------------------------------------------------------------------------
	---------------------------------------------------
	2 - filter("COL1"=:B1)
	SYS@bltdb1 10/12/23 16:54 > SELECT SQL_ID,CHILD_NUMBER,IS_SHAREABLE,IS_BIND_SENSITIVE,IS_BIND_AWARE FROM V$SQL WHERE SQL_ID='a39ruh5sdz1k5';
	SQL_ID CHILD_NUMBER IS_SHAREABLE IS_BIND_SENSITIVE IS_BIND_AWARE
	--------------- --------------- --------------- -------------------- ---------------
	a39ruh5sdz1k5 0 N Y N
	a39ruh5sdz1k5 1 Y Y Y

view raw set_of_commands_sqlplus.txt hosted with ❤ by GitHub

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. 

	SQL > alter system flush shared_pool;
	-- 1 is entered in bind value prompt for B1
	SQL> SELECT MAX (COL2) AS MAXDATA, COUNT(*) AS COUNTDATA, SUM(COL2) AS SUMDATA FROM SYSTEM.ACS_TEST_TABLE WHERE COL1=:B1;
	MAXDATA COUNTDATA SUMDATA
	1 1 1
	SQL> select * from dbms_xplan.display_cursor(format=>'+peeked_binds');
	SQL_ID a39ruh5sdz1k5, child number 0
	-------------------------------------
	SELECT MAX (COL2) AS MAXDATA, COUNT(*) AS COUNTDATA, SUM(COL2) AS
	SUMDATA FROM SYSTEM.ACS_TEST_TABLE WHERE COL1=:B1
	Plan hash value: 1498943009
	-------------------------------------------------------------------------------------------------------
	| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
	-------------------------------------------------------------------------------------------------------
	| 0 | SELECT STATEMENT | | | | 191 (100)| |
	| 1 | SORT AGGREGATE | | 1 | 8 | | |
	| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ACS_TEST_TABLE | 50000 | 390K| 191 (1)| 00:00:01 |
	|* 3 | INDEX RANGE SCAN | INDEX1_TESTTAB | 50000 | | 100 (0)| 00:00:01 |
	-------------------------------------------------------------------------------------------------------
	Predicate Information (identified by operation id):
	---------------------------------------------------
	3 - access("COL1"=TO_NUMBER(:B1))
	-- No bind peeking took place, no info available.
	SQL> SELECT SQL_ID,CHILD_NUMBER,IS_SHAREABLE,IS_BIND_SENSITIVE,IS_BIND_AWARE FROM V$SQL WHERE SQL_ID='a39ruh5sdz1k5';
	SQL_ID CHILD_NUMBER IS_SHAREABLE IS_BIND_SENSITIVE IS_BIND_AWARE
	a39ruh5sdz1k5 0 Y N N
	-- Sql is not marked as bind sensitive.
	-- Next two execution will be with bind value 10.
	-- Same plan is used, no plan change observed when same identical statement run in TOAD.
	-- 10 is entered in bind value prompt for B1
	SQL> SELECT MAX (COL2) AS MAXDATA, COUNT(*) AS COUNTDATA, SUM(COL2) AS SUMDATA FROM SYSTEM.ACS_TEST_TABLE WHERE COL1=:B1;
	MAXDATA COUNTDATA SUMDATA
	500000 499991 125000249955
	-- 10 is entered in bind value prompt for B1
	SQL> SELECT MAX (COL2) AS MAXDATA, COUNT(*) AS COUNTDATA, SUM(COL2) AS SUMDATA FROM SYSTEM.ACS_TEST_TABLE WHERE COL1=:B1;
	MAXDATA COUNTDATA SUMDATA
	500000 499991 125000249955
	SQL> select * from dbms_xplan.display_cursor(format=>'+peeked_binds');
	SQL_ID a39ruh5sdz1k5, child number 0
	-------------------------------------
	SELECT MAX (COL2) AS MAXDATA, COUNT(*) AS COUNTDATA, SUM(COL2) AS
	SUMDATA FROM SYSTEM.ACS_TEST_TABLE WHERE COL1=:B1
	Plan hash value: 1498943009
	-------------------------------------------------------------------------------------------------------
	| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
	-------------------------------------------------------------------------------------------------------
	| 0 | SELECT STATEMENT | | | | 191 (100)| |
	| 1 | SORT AGGREGATE | | 1 | 8 | | |
	| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ACS_TEST_TABLE | 50000 | 390K| 191 (1)| 00:00:01 |
	|* 3 | INDEX RANGE SCAN | INDEX1_TESTTAB | 50000 | | 100 (0)| 00:00:01 |
	-------------------------------------------------------------------------------------------------------
	Predicate Information (identified by operation id):
	---------------------------------------------------
	3 - access("COL1"=TO_NUMBER(:B1))
	-- No bind peeking took place, no info available.
	SQL> SELECT SQL_ID,CHILD_NUMBER,IS_SHAREABLE,IS_BIND_SENSITIVE,IS_BIND_AWARE FROM V$SQL WHERE SQL_ID='a39ruh5sdz1k5';
	SQL_ID CHILD_NUMBER IS_SHAREABLE IS_BIND_SENSITIVE IS_BIND_AWARE
	a39ruh5sdz1k5 0 Y N N

view raw set_of_commands_TOAD.txt hosted with ❤ by GitHub

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.