Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rafal.

Asked: December 16, 2015 - 10:25 am UTC

Last updated: May 30, 2020 - 7:43 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I have a below case in which bind variable peeking seems to not work well.

1. I have a table RETRO where is around 1,5 millions records

2. This table has a three below indexes:
- IK_RETRO_02 ON RETRO(PAR_ID, RETRO_PAYMENT_DATE)
- IK_RETRO_05 ON RETRO(NVL(RETRO_PAYMENT_DATE, STATUS_ID)
- IK_RETRO_PAYMENT_TASK_ID ON RETRO(NVL(PAYMENT_TASK_ID,(-1)))

3. This are statistics for above indexes
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS
IK_RETRO_02 3 73113  484  641   6672   760703   14572798 
IK_RETRO_05 3 75054  60  2274   18289   603546   14653539 
IK_RE..T.._ID2 3 49172  8745  5   86   755098   13896555

4. Parameter _OPTIM_PEEK_USER_BINDS is set to TRUE

5. I'm executing below query
SELECT /*+ monitor gather_plan_statistics */ *
  FROM RETRO R
 WHERE R.PAR_ID = NVL ( :B5, R.PAR_ID)
   AND R.RETRO_PAYMENT_DATE BETWEEN NVL ( :B4, R.RETRO_PAYMENT_DATE) AND NVL ( :B3, R.RETRO_PAYMENT_DATE)
   AND NVL (PAYMENT_TASK_ID, :B2) = :B1;

Where
- :B1 = 11416
- :B2 = -1
- :B3 IS NULL
- :B4 IS NULL
- :B5 = 396

And I have this execution plan. As you can see the index IK_RETRO_02 has been used
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |       |   394K(100)|          |   3224 |00:00:02.20 |    3634 |    296 |
|   1 |  CONCATENATION                        |             |      1 |        |       |            |          |   3224 |00:00:02.20 |    3634 |    296 |
|*  2 |   FILTER                              |             |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |      0 |
|*  3 |    TABLE ACCESS FULL                  | RETRO       |      0 |      1 |   397 |   394K  (1)| 00:00:16 |      0 |00:00:00.01 |       0 |      0 |
|*  4 |   FILTER                              |             |      1 |        |       |            |          |   3224 |00:00:02.18 |    3634 |    296 |
|*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED| RETRO       |      1 |      1 |   397 |   160   (1)| 00:00:01 |   3224 |00:00:02.17 |    3634 |    296 |
|*  6 |     INDEX RANGE SCAN                  | IK_RETRO_02 |      1 |     76 |       |   156   (1)| 00:00:01 |  58909 |00:00:00.60 |     502 |    293 |
--------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
3 - SEL$1_1 / R@SEL$1
5 - SEL$1_2 / R@SEL$1_2
6 - SEL$1_2 / R@SEL$1_2

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(:B7 IS NULL)
3 - filter(("R"."PAR_ID"=TO_NUMBER(TO_CHAR("R"."PAR_ID")) AND NVL("PAYMENT_TASK_ID",TO_NUMBER(:B2))=TO_NUMBER(:B1) AND
"R"."RETRO_PAYMENT_DATE">=NVL(:B4,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE")) AND
"R"."RETRO_PAYMENT_DATE"<=NVL(:B3,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE"))))
4 - filter(:B7 IS NOT NULL)
5 - filter(NVL("PAYMENT_TASK_ID",TO_NUMBER(:B2))=TO_NUMBER(:B1))
6 - access("R"."PAR_ID"=TO_NUMBER(:B7))
filter(("R"."RETRO_PAYMENT_DATE">=NVL(:B4,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE")) AND
"R"."RETRO_PAYMENT_DATE"<=NVL(:B3,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE"))))

6. In case when I replace to '-1' 
SELECT /*+ monitor gather_plan_statistics */ *
  FROM RETRO R
 WHERE R.PAR_ID = NVL ( :B5, R.PAR_ID)
   AND R.RETRO_PAYMENT_DATE BETWEEN NVL ( :B4, R.RETRO_PAYMENT_DATE) AND NVL ( :B3, R.RETRO_PAYMENT_DATE)
   AND NVL (PAYMENT_TASK_ID, -1) = :B1;

Where
- :B1 = 11416
- :B3 IS NULL
- :B4 IS NULL
- :B5 = 396

Then I have this execution plan. As you can see the index IK_RETRO_PAYMENT_TASK_ID has been used, and this is what is seems to be much better.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |      1 |        |       |   204 (100)|          |   3224 |00:00:00.18 |     204 |     25 |
|   1 |  CONCATENATION                        |                          |      1 |        |       |            |          |   3224 |00:00:00.18 |     204 |     25 |
|*  2 |   FILTER                              |                          |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |      0 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| RETRO                    |      0 |      1 |   397 |   102   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
|*  4 |     INDEX RANGE SCAN                  | IK_RETRO_PAYMENT_TASK_ID |      0 |   1679 |       |    10   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
|*  5 |   FILTER                              |                          |      1 |        |       |            |          |   3224 |00:00:00.17 |     204 |     25 |
|*  6 |    TABLE ACCESS BY INDEX ROWID BATCHED| RETRO                    |      1 |      1 |   397 |   102   (0)| 00:00:01 |   3224 |00:00:00.16 |     204 |     25 |
|*  7 |     INDEX RANGE SCAN                  | IK_RETRO_PAYMENT_TASK_ID |      1 |   1679 |       |    10   (0)| 00:00:01 |   3224 |00:00:00.12 |      30 |     25 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
3 - SEL$1_1 / R@SEL$1
4 - SEL$1_1 / R@SEL$1
6 - SEL$1_2 / R@SEL$1_2
7 - SEL$1_2 / R@SEL$1_2

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(:B7 IS NULL)
3 - filter(("R"."PAR_ID"=TO_NUMBER(TO_CHAR("R"."PAR_ID")) AND "R"."RETRO_PAYMENT_DATE">=NVL(:B4,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE")) AND
"R"."RETRO_PAYMENT_DATE"<=NVL(:B3,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE"))))
4 - access("R"."SYS_NC00073$"=TO_NUMBER(:B1))
5 - filter(:B7 IS NOT NULL)
6 - filter(("R"."PAR_ID"=TO_NUMBER(:B7) AND "R"."RETRO_PAYMENT_DATE">=NVL(:B4,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE")) AND
"R"."RETRO_PAYMENT_DATE"<=NVL(:B3,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE"))))
7 - access("R"."SYS_NC00073$"=TO_NUMBER(:B1))



Pleas let me know why in point 5, the plan which I have in point 6 is not used. I'm expecting this because value of :B2 is "-1" and bind variable peeking mechanism is enabled.


------- additional information added 4th Jan 2016 ------------

Number of distinct values for the columns

select count(*) from RETRO; -- the result is 22179267
select count(distinct PAR_ID) from RETRO; -- the result is 484
select count(distinct RETRO_PAYMENT_DATE) from RETRO; -- the result is 60
select count(distinct STATUS_ID) from RETRO; -- the result is 3
select count(distinct PAYMENT_TASK_ID) from RETRO; -- the result is 8745


The DDL for a table:

CREATE TABLE RETRO
(
  CLIENT_ID                    NUMBER(3)        NOT NULL,
  RETRO_ID                     NUMBER(11)       NOT NULL,
  STATUS_ID                    NUMBER(5)        DEFAULT 21                    NOT NULL,
  RETRO_DATE                   DATE             NOT NULL,
  RETRO_QTY_DATE               DATE             NOT NULL,
  RETRO_PAYMENT_DATE           DATE             NOT NULL,
  MOD_CUR_ID                   NUMBER(5)        NOT NULL,
  PAR_CUR_ID                   NUMBER(5)        NOT NULL,
  DET_CUR_ID                   NUMBER(5),
  RETRO_TOTALFEE               NUMBER(15,12),
  RETRO_MOD_CUR_AMOUNT         NUMBER(27,9)     NOT NULL,
  RETRO_PAR_CUR_AMOUNT         NUMBER(27,9)     NOT NULL,
  RETRO_DET_CUR_AMOUNT         NUMBER(27,9),
  MOD_ID                       NUMBER(11),
  PAR_ID                       NUMBER(11)       NOT NULL,
  ORIGIN_PAR_ID                NUMBER(11)       NOT NULL,
  QTY_PAR_ID                   NUMBER(11),
  PG_ID                        NUMBER(11)       NOT NULL,
  LEGAL_PG_ID                  NUMBER(11)       NOT NULL,
  PBC_ID                       NUMBER(11),
  CUST_ID                      NUMBER(11)       NOT NULL,
  PRODUCT_ID                   NUMBER(11)       NOT NULL,
  PRODUCTCATEGORY_ID           NUMBER(11)       DEFAULT 71                    NOT NULL,
  DETAIL_ID                    NUMBER(11),
  CATEGORY_ID                  NUMBER(5),
  ACC_ID                       NUMBER(11),
  CON_ID                       NUMBER(11),
  QTY_ID                       NUMBER(11),
  TRX_ID                       NUMBER(11),
  MOD_DET_VOLUME               NUMBER(21,6),
  RATE_DET_VOLUME              NUMBER(21,6),
  RATE_CALC_VOLUME             NUMBER(21,6),
  RETRO_NO_SHARES              NUMBER(21,6),
  RETRO_COEFFICIENT            NUMBER(6,3)      DEFAULT 100                   NOT NULL,
  RETRO_PRICE                  NUMBER(27,9),
  MP_ID                        NUMBER(11),
  MPG_ID                       NUMBER(11),
  CONS_ID                      NUMBER(11),
  RETRO_CALC_KIND_ID           NUMBER           DEFAULT 122                   NOT NULL,
  RETRO_TIMESTAMP              DATE             DEFAULT SYSDATE               NOT NULL,
  RETRO_CREATED                VARCHAR2(30 BYTE) DEFAULT USER NOT NULL,
  WF_STATUS_ID                 NUMBER(5)        DEFAULT 422                   NOT NULL,
  SRC_ID                       NUMBER(11),
  REC_ID                       NUMBER(11),
  RETRO_TEMPORARY              CHAR(1 BYTE)     DEFAULT 'N'                   NOT NULL,
  RETRO_CORRECTED              CHAR(1 BYTE)     DEFAULT 'N'                   NOT NULL,
  RETRO_MANUAL_FLAG            CHAR(1 BYTE)     DEFAULT 'N'                   NOT NULL,
  RETRO_THRESHOLD_FLAG         CHAR(1 BYTE)     DEFAULT 'N'                   NOT NULL,
  RETRO_REVERSAL               CHAR(1 BYTE)     DEFAULT 'N'                   NOT NULL,
  RETRO_FLAG                   CHAR(1 BYTE)     DEFAULT 'N'                   NOT NULL,
  RETRO_TEXT                   VARCHAR2(100 BYTE),
  RETRO_TEXT_PARTS             VARCHAR2(500 BYTE),
  RP_ID                        NUMBER(11),
  CALC_TASK_ID                 NUMBER(11),
  PAYMENT_TASK_ID              NUMBER(11),
  ORIGIN_PAYMENT_TASK_ID       NUMBER(11),
  ORIGIN_RETRO_PAYMENT_DATE    DATE,
  RETRO_RATE_1                 NUMBER(15,12),
  RETRO_RATE_2                 NUMBER(15,12),
  RETRO_RATE_3                 NUMBER(15,12),
  RETRO_RATE_4                 NUMBER(15,12),
  RETRO_INDEX_FACTOR           NUMBER(15,12),
  RETRO_EXCHANGE_RATE_DET_MOD  NUMBER(21,9),
  RETRO_EXCHANGE_RATE_MOD_PAR  NUMBER(21,9),
  RETRO_EXCHANGE_RATE_DET_PAR  NUMBER(21,9),
  RETRO_AMOUNT_1               NUMBER(27,9),
  RETRO_AMOUNT_2               NUMBER(27,9),
  RETRO_AMOUNT_3               NUMBER(27,9),
  RETRO_AMOUNT_4               NUMBER(27,9),
  RETRO_AMOUNT_5               NUMBER(27,9),
  RETRO_AMOUNT_6               NUMBER(27,9),
  REF_RETRO_ID                 NUMBER(11),
  TAX_ID                       NUMBER(11),
  TAX_AMOUNT                   NUMBER(27,9),
  TAX_NO_DAYS                  NUMBER(3),
  PI_ID                        NUMBER(11),
  PAY_CUR_ID                   NUMBER(5),
  RETRO_PAY_CUR_AMOUNT         NUMBER(27,9),
  TRT_ID                       NUMBER(11),
  RETRO_NO_DAYS                NUMBER(3),
  RETRO_NO_DAYS_ACTUAL         NUMBER(3),
  RETRO_NO_YEARDAYS            NUMBER(3),
  RETRO_NO_DAYS_EFF            NUMBER(3),
  CON_CONSERVATORY_RATE        NUMBER(15,5)
);


CREATE INDEX IK_RETRO_02 ON RETRO
(PAR_ID, RETRO_PAYMENT_DATE);


CREATE INDEX IK_RETRO_05_ ON RETRO
(RETRO_PAYMENT_DATE, STATUS_ID);

CREATE INDEX IK_RETRO_PAYMENT_TASK_ID ON RETRO
(NVL("PAYMENT_TASK_ID",(-1)));


CREATE INDEX IK_RETRO_PAYMENT_TASK_ID2 ON RETRO
(PAYMENT_TASK_ID);



and Connor said...

It would appear to be a limitation of the optimizer. I just tried this even very simple case in 12c


SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table T as
  2  select rownum r,
  3    case when rownum < 10 then 1
  4         when rownum < 20 then 2
  5         when rownum < 30 then 3
  6         when rownum < 40 then null
  7         else 4 end hist,
  8     rpad(rownum,100) padding
  9  from dual
 10  connect by level <= 1000000;

Table created.

SQL>
SQL>
SQL> create index IX on T ( nvl(hist,-1) );

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T',method_opt=>'for all columns size auto');

PL/SQL procedure successfully completed.

SQL>
SQL> variable b number
SQL> exec :b := -1;

PL/SQL procedure successfully completed.

SQL> print b

         B
----------
        -1

SQL>
SQL> set serverout off
SQL> select * from t where nvl(hist,:b) = -1;

...

10 rows selected.

SQL>
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  4gg8qhzwjyjkn, child number 0
-------------------------------------
select * from t where nvl(hist,:b) = -1

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |  4360 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    | 83343 |  8871K|  4360   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NVL("HIST",:B)=(-1))


18 rows selected.

SQL>
SQL> select * from t where nvl(hist,-1) = -1;

...

10 rows selected.

SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  ban9hp75c3zjc, child number 0
-------------------------------------
select * from t where nvl(hist,-1) = -1

Plan hash value: 3947747388

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |       |       |  3570 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |   200K|    21M|  3570   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX   |   200K|       |   393   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."SYS_NC00004$"=(-1))


19 rows selected.

SQL>


Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Does it mean, that is better to not use constants in the PL/SQL code?

Rafal Gruca, January 05, 2016 - 8:54 am UTC

Could you explain me what does "limitation of the optimizer" mean? Is this a bug or it is an expected behavior?

Does it also mean that is better to not use constants in the queries which are in the PL/SQL code? All constants are changed into binding variables and there is a risk that this limitation of the optimizer can occur. Does it mean that is better to hardcode the literal values instead of defining them as the constants?


Connor McDonald
January 06, 2016 - 12:43 am UTC

My view on binds is that you are using them because you anticipate them to either change in future, or you'll be using lots of different ones (and dont want to be hit by parsing costs).

So for

select * from table where primary_key = ?

then definitely use a bind variable, because "?" will change to be all sorts of potential values.

But

select * from table where nvl(col,?) = #

Well, I'd argue that the ONLY value you will ever use for '?' in that query (especially if you have an index defined on the expression is "-1"). You'll never change it, because you'd be changing in the index as well. For '#', then you might keep that as a bind (if you're expecting lots of differing values).

Binds are about avoiding parse costs, and (when building sql on the fly) avoiding sql injection. That (in my view) is *not* the same as saying binds are mandatory everywhere all the time.

Hope this helps.

Thanks

Rafal Gruca, January 06, 2016 - 9:03 am UTC

Ok. I will remember about this.

To be honest I'm a little disappointed that is better to not use constants in the queries. I prefer rather to use constants than hardcode the literal values in the packages.
Connor McDonald
January 06, 2016 - 12:40 pm UTC

You could potentially explore plsql conditional compilation as a means of consolidating your constants yet still having them replaced with literals in your code.

Difference with Binds

Dominic Brooks, January 06, 2016 - 3:28 pm UTC

When you use a constant, the optimizer a) knows the value and b) knows it cannot be NULL.

When you use binds, the plan has to satisfy the conditions where the binds could be null or not null.

Regardless of peeking, the plan has to satisfy conditions where :B2 = :B1, where :B2 != :B1 and where either/both are null.
Chris Saxon
January 07, 2016 - 1:30 am UTC

Thanks for the input.

Although

Dominic Brooks, January 06, 2016 - 3:33 pm UTC

I appreciate I'm commenting on the generic difference between binds and literals from the optimizer perspective whereas I suppose OP's point is that PLSQL knows this is a constant but there is no way for this to be automatically rewritten to use a literal before passing it to the SQL engine or for the SQL engine to somehow be told that this is a constant and rewrite it or something like that
Chris Saxon
January 07, 2016 - 1:31 am UTC

Agreed. There are lots of areas where *theoretically* an optimizer could generate multiple children and hence plans ... but then I'm sure people would then be up in arms about parse times :-)

bind peeking and acs

oj, May 12, 2020 - 8:29 am UTC

Chris,

In your 1st answer, if your R column was a VARCHAR2 and had an unique index on it, would you expect Oracle to, either via bind peek or adaptive cursor sharing (acs), come up with 2 different plans for this;

select * from t where :b1 is null or t.r = :b1

..i.e. if on 1st parse (bind peek), it sees :b1 as NOT NULL, it might use the index. Subsequent execution where :b1 is NULL, it would revert to a full table scan (acs) ? Oracle would then continuously "adapt" to the 2 different plans depending on what bind values is used ?

I'm finding here it seems to stick to full table scan no matter what, maybe I'm missing something ?
Connor McDonald
May 13, 2020 - 2:06 am UTC

Does't look like it

SQL> create table t as
  2  select rownum x, rpad('x',100) y from dual connect by level <= 10
  3  union all
  4  select 100, rpad('x',100) y from dual
  5  connect by level <= 100000
  6  union all
  7  select 100+rownum x, rpad('x',100) y from dual connect by level <= 10;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','t',method_opt=>'for all columns size 200');

PL/SQL procedure successfully completed.

SQL>
SQL> create index ix on t ( x ) ;

Index created.

SQL>
SQL> variable b1 number
SQL> exec :b1 := 5;

PL/SQL procedure successfully completed.

SQL> select count(y) from t where x = :b1;

  COUNT(Y)
----------
         1

1 row selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  dftph6v4hct4w, child number 2
-------------------------------------
select count(y) from t where x = :b1

Plan hash value: 2143077847

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE                      |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |     1 |   104 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("X"=:B1)


20 rows selected.

SQL>
SQL> exec :b1 := 100;

PL/SQL procedure successfully completed.

SQL> select count(y) from t where x = :b1;

  COUNT(Y)
----------
    100000

1 row selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  dftph6v4hct4w, child number 2
-------------------------------------
select count(y) from t where x = :b1

Plan hash value: 2143077847

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE                      |      |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |     1 |   104 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX   |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("X"=:B1)


20 rows selected.

SQL> select count(y) from t where x = :b1;

  COUNT(Y)
----------
    100000

1 row selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  dftph6v4hct4w, child number 3
-------------------------------------
select count(y) from t where x = :b1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   424 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   104 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   100K|     9M|   424   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=:B1)


19 rows selected.

SQL> exec :b1 := 6;

PL/SQL procedure successfully completed.

SQL> select count(y) from t where :b1 is null or x = :b1;

  COUNT(Y)
----------
         1

1 row selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  8hmd0vj8ftqhz, child number 0
-------------------------------------
select count(y) from t where :b1 is null or x = :b1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   424 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   104 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  5002 |   508K|   424   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((:B1 IS NULL OR "X"=:B1))


19 rows selected.

SQL>
SQL> exec :b1 := 6;

PL/SQL procedure successfully completed.

SQL> select count(y) from t where :b1 is null or x = :b1;

  COUNT(Y)
----------
         1

1 row selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  8hmd0vj8ftqhz, child number 0
-------------------------------------
select count(y) from t where :b1 is null or x = :b1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   424 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   104 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  5002 |   508K|   424   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((:B1 IS NULL OR "X"=:B1))


19 rows selected.

SQL>
SQL> exec :b1 := 6;

PL/SQL procedure successfully completed.

SQL> select count(y) from t where :b1 is null or x = :b1;

  COUNT(Y)
----------
         1

1 row selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  8hmd0vj8ftqhz, child number 0
-------------------------------------
select count(y) from t where :b1 is null or x = :b1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   424 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   104 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  5002 |   508K|   424   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((:B1 IS NULL OR "X"=:B1))


19 rows selected.

SQL>
SQL> exec :b1 := 6;

PL/SQL procedure successfully completed.

SQL> select count(y) from t where :b1 is null or x = :b1;

  COUNT(Y)
----------
         1

1 row selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  8hmd0vj8ftqhz, child number 0
-------------------------------------
select count(y) from t where :b1 is null or x = :b1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   424 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   104 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  5002 |   508K|   424   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((:B1 IS NULL OR "X"=:B1))


19 rows selected.

SQL>
SQL> exec :b1 := 6;

PL/SQL procedure successfully completed.

SQL> select count(y) from t where :b1 is null or x = :b1;

  COUNT(Y)
----------
         1

1 row selected.

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  8hmd0vj8ftqhz, child number 0
-------------------------------------
select count(y) from t where :b1 is null or x = :b1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   424 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   104 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  5002 |   508K|   424   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((:B1 IS NULL OR "X"=:B1))


19 rows selected.

SQL>
SQL>


adaptive cursor sharing not working

oj, May 15, 2020 - 10:00 am UTC

Thanks Connor, but do you think it's ACS behaviour is right ?

Are we missing something that makes it NOT work ?

Actually after a pause, I wonder if the column being NOT NULL may be needed, although here I'm not allowed to make it NOT NULL for our table in question.
Connor McDonald
May 18, 2020 - 2:50 am UTC

I think its more that ACS is predominantly about the distribution of data, so the code path I suspect (without proof) is more targetted around that. To optimize for the ":b1 is null" case, we need to add smarts to eliminate that predicate by examining the value of the bind variable. I'm not saying its impossible, but it looks like that has not (yet) been implemented.

ACS not working

oj, May 29, 2020 - 6:46 am UTC

Thanks again Connor. Out of interest, did you do the last test using Oracle 19c?
Connor McDonald
May 30, 2020 - 7:43 am UTC

Yes that was done on 19c
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library