Re: adaptive cursor sharing and bind peeking

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Fri, 31 May 2013 10:33:23 +0200
Message-ID: <CAJu8R6iFMi8mj6X0zYpMKDE3y4WFGBhg_YkQhDFPRh9cg1kVbQ_at_mail.gmail.com>

Dear Christian,
May be I have not understood clearly the original poster question. For me the question was whether the CBO (for cursors that are bind aware) will always peek at the passed bind variable and optimize it or not?

This is why I answered that even if the cursor is bind aware the CBO will not always use the passed bind variable to optimize a new plan (child cursor). Instead it will share the plan that has been optimized for the bind variable having the same *“range of selectitivity*” as the passed bind variable

Here below what I mean (correct me please)

sql> exec :n := 'Y1'

sql> select /*+ bind_variable */ count(*), max(col2) from t1 where flag :n;

Plan hash value: 3625400295

---

| Id | Operation | Name |

---

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | TABLE ACCESS BY INDEX ROWID| T1 |

| 3 | INDEX RANGE SCAN | I1 |

---

Peeked Binds (identified by position):

---

    1 - :N (VARCHAR2(30), CSID8): 'Y1' sql> start c:\isbind (first I (is_bind_aware), second I(Is_bind_sensitive), Third I (Is_shareable)

SQL_ID        CHILD_NUMBER I I I SIG                   EXECUTIONS

PLAN_HASH_VALUE

• ------------ - - - -------------------- --------------

  ---

8xujk8a1g65x6 0 N Y Y 9686445671300360182 1

    3625400295

*-- passed variable = peeked variable*

sql> exec :n := 'N1'

sql> select /*+ bind_variable */ count(*), max(col2) from t1 where flag :n;

Plan hash value: 3625400295

---

| Id | Operation | Name |

---

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | TABLE ACCESS BY INDEX ROWID| T1 |

| 3 | INDEX RANGE SCAN | I1 |

---

 Peeked Binds (identified by position):

---

    1 - :N (VARCHAR2(30), CSID8): 'Y1'  sql> start c:\isbind

 SQL_ID CHILD_NUMBER I I I SIG
    EXECUTIONS
PLAN_HASH_VALUE

• ------------ - - - ---------------------------------------- ---------- ---------------

8xujk8a1g65x6            0 N Y Y
9686445671300360182                               2      3625400295

*-- passed variable != peeked variable*

*-- Second run of the same query with the same bind variable*

sql> select /*+ bind_variable */ count(*), max(col2) from t1 where flag :n;

 Plan hash value: 3724264953

---

| Id | Operation | Name |

---

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | TABLE ACCESS FULL| T1 |

---

 Peeked Binds (identified by position):

---

    1 - :N (VARCHAR2(30), CSID8): 'N1' sql> start c:\isbind

 SQL_ID CHILD_NUMBER I I I SIG
    EXECUTIONS
PLAN_HASH_VALUE

• ------------ - - - ---------------------------------------- ---------- ---------------

8xujk8a1g65x6            0 N Y Y
9686445671300360182                               2      3625400295

8xujk8a1g65x6            1 Y Y Y
9686445671300360182                               1      3724264953

• *Passed variable = peeked variable and the new child cursor (n°1) is bind aware for the full table scan*
• Back to the initial variable

sql> exec :n := 'Y1'

sql> select /*+ bind_variable */ count(*), max(col2) from t1 where flag :n;

Plan hash value: 3625400295

---

| Id | Operation | Name |

---

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | TABLE ACCESS BY INDEX ROWID| T1 |

| 3 | INDEX RANGE SCAN | I1 |

---

 Peeked Binds (identified by position):

---

    1 - :N (VARCHAR2(30), CSID8): 'Y1' sql> start c:\isbind

 SQL_ID CHILD_NUMBER I I I SIG
    EXECUTIONS
PLAN_HASH_VALUE

• ------------ - - - ---------------------------------------- ---------- ---------------

8xujk8a1g65x6            0 N Y N
9686445671300360182                               2      3625400295

8xujk8a1g65x6            1 Y Y Y
9686445671300360182                               1      3724264953

8xujk8a1g65x6            2 Y Y Y
9686445671300360182                               1      3625400295

*-- passed variable = peeked variable and the new child cursor n°2 is bind aware for index range scan*

sql> exec :n := 'N1'

sql> select /*+ bind_variable */ count(*), max(col2) from t1 where flag :n;

Plan hash value: 3724264953

---

| Id | Operation | Name |

---

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | TABLE ACCESS FULL| T1 |

---

 Peeked Binds (identified by position):

---

    1 - :N (VARCHAR2(30), CSID8): 'N1' sql> start c:\isbind

SQL_ID CHILD_NUMBER I I I SIG
   EXECUTIONS
PLAN_HASH_VALUE

• ------------ - - - ---------------------------------------- ---------- ---------------

8xujk8a1g65x6            0 N Y N 9686445671300360182
                         2      3625400295

8xujk8a1g65x6            1 Y Y Y
9686445671300360182                               2      3724264953

8xujk8a1g65x6            2 Y Y Y
9686445671300360182                               1      3625400295

*-- passed variable = peeked variable and child cursor n°1 is reused*

sql> exec :n := 'Y1'

sql> select /*+ bind_variable */ count(*), max(col2) from t1 where flag :n;

Plan hash value: 3625400295

---

| Id | Operation | Name |

---

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | TABLE ACCESS BY INDEX ROWID| T1 |

| 3 | INDEX RANGE SCAN | I1 |

---

Peeked Binds (identified by position):

---

    1 - :N (VARCHAR2(30), CSID8): 'Y1' sql> start c:\isbind

SQL_ID CHILD_NUMBER I I I SIG
   EXECUTIONS
PLAN_HASH_VALUE

• ------------ - - - ---------------------------------------- ---------- ---------------

8xujk8a1g65x6            0 N Y N
9686445671300360182                               2      3625400295

8xujk8a1g65x6            1 Y Y Y
9686445671300360182                               2      3724264953

8xujk8a1g65x6            2 Y Y Y
9686445671300360182                               2      3625400295

*-- passed variable = peeked variable and child cursor n°2 is reused*

*-- Use of a new bind variable 'Y2' which has the same range of selectivity as 'Y1'*

sql> exec :n := 'Y2';

sql> select /*+ bind_variable */ count(*), max(col2) from t1 where flag :n;

---

| Id | Operation | Name |

---

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | TABLE ACCESS BY INDEX ROWID| T1 |

| 3 | INDEX RANGE SCAN | I1 |

---

 Peeked Binds (identified by position):

---

    1 - :N (VARCHAR2(30), CSID8): 'Y1' sql> start c:\isbind

SQL_ID        CHILD_NUMBER I I I SIG
                            EXECUTIONS PLAN_HASH_VALUE

------------- ------------ - - - ----------------------------------------

---------- ---------------

8xujk8a1g65x6            0 N Y N
9686445671300360182                               2      3625400295

8xujk8a1g65x6            1 Y Y Y
9686445671300360182                               3      3724264953

8xujk8a1g65x6            2 Y Y Y
9686445671300360182                               3      3625400295

*-- passed variable != peeked variable and child cursor n°2 is reused*

*-- Use of a new bind variable 'N2' which has the same range of selectivity as 'N1'*

sql> exec :n := 'N2'

sql> select /*+ bind_variable */ count(*), max(col2) from t1 where flag :n;

Plan hash value: 3724264953

---

| Id | Operation | Name |

---

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | TABLE ACCESS FULL| T1 |

---

 Peeked Binds (identified by position):

---

    1 - :N (VARCHAR2(30), CSID8): 'N1' sql> start c:\isbind

SQL_ID CHILD_NUMBER I I I SIG
   EXECUTIONS
PLAN_HASH_VALUE

• ------------ - - - ---------------------------------------- ---------- ---------------

8xujk8a1g65x6            0 N Y N
9686445671300360182                               2      3625400295

8xujk8a1g65x6            1 Y Y Y 9686445671300360182
              4      3724264953

8xujk8a1g65x6            2 Y Y Y
9686445671300360182                               3      3625400295

*-- passed variable != peeked variable and child cursor n°1 is reused*

*-- Use of a variable that doesn't exist (selectivity 0 in the same range of selectivity as ‘Y1’)*

sql> exec :n :='Z'

sql> select /*+ bind_variable */ count(*), max(col2) from t1 where flag :n;

Plan hash value: 3625400295

---

| Id | Operation | Name |

---

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | TABLE ACCESS BY INDEX ROWID| T1 |

| 3 | INDEX RANGE SCAN | I1 |

---

Peeked Binds (identified by position):

---

    1 - :N (VARCHAR2(30), CSID8): 'Y1' sql> start c:\isbind

SQL_ID CHILD_NUMBER I I I SIG
EXECUTIONS PLAN_HASH_VALUE

• ------------ - - - ---------------------------------------- ---------- ---------------

8xujk8a1g65x6            0 N Y N
9686445671300360182                               2      3625400295

8xujk8a1g65x6            1 Y Y Y
9686445671300360182                               4      3724264953

8xujk8a1g65x6            2 Y Y Y
9686445671300360182                               4      3625400295

*-- passed variable != peeked variable and child cursor n°2 is reused *

And so on until I use a bind variable that is not in the range of selectivity that corresponds to my two child cursors that are bind aware and bind sensitive.

Best regards

Mohamed Houri

2013/5/31 Christian Antognini <Christian.Antognini_at_trivadis.com>

> Hi Carlos
>
> > Once it is bind aware on every execution it looks at the values of binds
> and compares to acs selectivity profile for this sql
>
> When a cursor is open, its execution plan cannot be changed. As a result,
> the peeking and everything else that goes with it in case of a bind-aware
> cursor can only be done when a parse call is performed. This is also the
> reason why static cursors in PL/SQL loops or Java applications using
> client-side statement caching cannot take advantage of ACS.
>
>
> HTH
> Chris Antognini
>
> Troubleshooting Oracle Performance, Apress 2008
> http://top.antognini.ch
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 31 2013 - 10:33:23 CEST