Step by step test 11g adaptive cursor

DB version: 11.1.0.6

–Adaptive Cursors in 11g

–create a test table
T@ORCL11G>alter system flush shared_pool;
System altered.
T@ORCL11G>create table t2
as
select case when rownum = 1 then 1 else 99 end id, a.*
from dba_objects a
/

Table created.
T@ORCL11G>create index t2_id on t2(id);
Index created.

T@ORCL11G> begin
dbms_stats.gather_table_stats
( user, ‘T2’,
estimate_percent => 100,
method_opt=> ‘for all indexed columns’,cascade=>TRUE);
end;
/
PL/SQL procedure successfully completed.
–gather 100% is necessary as the data is skew

–bind 99, which have many duplicated rows
T@ORCL11G>variable id number
T@ORCL11G>exec :id :=99
PL/SQL procedure successfully completed.

T@ORCL11G>select * from t2 where id=:id;
…..
68868 rows selected.

–check the LAST EXECUTED SQL plan
T@ORCL11G>select * from table(dbms_xplan.display_cursor(null,null,’typical +peeked_binds’));

PLAN_TABLE_OUTPUT
—————————————————————————————————
SQL_ID  g852rfbbqn2f9, child number 0
————————————-
select * from t2 where id=:id

Plan hash value: 1513984157

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |       |       |   293 (100)|          |
|*  1 |  TABLE ACCESS FULL| T2   | 68868 |  6994K|   293   (1)| 00:00:04 |
————————————————————————–

Peeked Binds (identified by position):
————————————–

1 – :ID (NUMBER): 99

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

1 – filter(“ID”=:ID)

23 rows selected.

–it is “TABLE ACCESS FULL” now
–let’s check the bina related statistics
T@ORCL11G>select sql_id, is_bind_sensitive, is_bind_aware from v$sql where sql_text=’select * from t2 where id=:id’;

SQL_ID        I I
————- – –
g852rfbbqn2f9 Y N
–is_bind_sensitive=Y, means oracle knows this sql is bind sensitive
–is_bind_aware=N, means oracle is not aware yet of a performance issue with regards to the bind values

–let’s change the bind value and run again
T@ORCL11G>exec :id := 1;

PL/SQL procedure successfully completed.

T@ORCL11G>select * from t2 where id=:id;
ID OWNER
———- —————
OBJECT_NAME
————————————————————————————————————-
——–
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED            LAST_DDL_TIME
—————————— ———- ————– ——————- —————— ————-
TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
——————- ——- – – – ———- ——————————
1 SYS
ICOL$
20              2 TABLE               15-OCT-07          15-OCT-07
2007-10-15:10:09:08 VALID   N N N          1

–let’s check the plan
T@ORCL11G>select * from table(dbms_xplan.display_cursor(null,null,’typical +peeked_binds’));
PLAN_TABLE_OUTPUT
—————————————————————————————————
SQL_ID  g852rfbbqn2f9, child number 0
————————————-
select * from t2 where id=:id

Plan hash value: 1513984157

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |       |       |   293 (100)|          |
|*  1 |  TABLE ACCESS FULL| T2   | 68868 |  6994K|   293   (1)| 00:00:04 |
————————————————————————–

Peeked Binds (identified by position):
————————————–

1 – :ID (NUMBER): 99

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

1 – filter(“ID”=:ID)

23 rows selected.

–the same plan 1513984157 as previous, but oracle expected to get 68868 rows, but it actually got 1 row

–let’s run the same sql with same bind variable again

T@ORCL11G>select * from t2 where id=:id;

ID OWNER
———- —————
OBJECT_NAME
————————————————————————————————————–
——–
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED            LAST_DDL_TIME
—————————— ———- ————– ——————- —————— ————–
TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
——————- ——- – – – ———- ——————————
1 SYS
ICOL$
20              2 TABLE               15-OCT-07          15-OCT-07
2007-10-15:10:09:08 VALID   N N N          1

–let’s check the plan
T@ORCL11G>select * from table(dbms_xplan.display_cursor(null,null,’typical +peeked_binds’));
PLAN_TABLE_OUTPUT
————————————————————————————–
SQL_ID  g852rfbbqn2f9, child number 1
————————————-
select * from t2 where id=:id

Plan hash value: 3119810522

————————————————————————————-
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————-
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |   104 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T2_ID |     1 |       |     1   (0)| 00:00:01 |

————————————————————————————-

Peeked Binds (identified by position):
————————————–

1 – :ID (NUMBER): 1

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

2 – access(“ID”=:ID)

24 rows selected.

–You see the execution plan is changed, cool!

–let’s check  bind aware or not
T@ORCL11G>select sql_id, child_number, is_bind_sensitive, is_bind_aware from v$sql where sql_text=’select * from t2 where id=:id’;
SQL_ID        CHILD_NUMBER I I
————- ———— – –
g852rfbbqn2f9            0 Y N
g852rfbbqn2f9            1 Y Y  –you can set is_bind_aware=Y in this new line. That means oracle know there is a problem

–let’s test another id which is not exist
T@ORCL11G>exec :id := 50;

PL/SQL procedure successfully completed.

T@ORCL11G>select * from t2 where id=:id;
no rows selected

T@ORCL11G>select * from table(dbms_xplan.display_cursor(null,null,’typical +peeked_binds’));
PLAN_TABLE_OUTPUT
——————————————————————————————–
——————————–
SQL_ID  g852rfbbqn2f9, child number 2
————————————-
select * from t2 where id=:id

Plan hash value: 3119810522

————————————————————————————-
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————-
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |   104 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T2_ID |     1 |       |     1   (0)| 00:00:01 |
————————————————————————————-

Peeked Binds (identified by position):
————————————–

1 – :ID (NUMBER): 50

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

2 – access(“ID”=:ID)

24 rows selected.

–it is still using index, but it is a new plan with child number 2

–try another non exist id 2 times

T@ORCL11G>exec :id := 88
PL/SQL procedure successfully completed.

T@ORCL11G>select * from t2 where id=:id;
no rows selected

T@ORCL11G>select * from table(dbms_xplan.display_cursor(null,null,’typical +peeked_binds’));
PLAN_TABLE_OUTPUT
————————————————————————————————-
SQL_ID  g852rfbbqn2f9, child number 2
————————————-
select * from t2 where id=:id

Plan hash value: 3119810522

————————————————————————————-
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————-
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |   104 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T2_ID |     1 |       |     1   (0)| 00:00:01 |
————————————————————————————-

Peeked Binds (identified by position):
————————————–

1 – :ID (NUMBER): 50

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

2 – access(“ID”=:ID)

24 rows selected.

T@ORCL11G>select * from t2 where id=:id;
no rows selected

T@ORCL11G>select * from table(dbms_xplan.display_cursor(null,null,’typical +peeked_binds’));
PLAN_TABLE_OUTPUT
————————————————————————————————-
——————————–
SQL_ID  g852rfbbqn2f9, child number 2
————————————-
select * from t2 where id=:id

Plan hash value: 3119810522

————————————————————————————-
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————-
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |   104 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T2_ID |     1 |       |     1   (0)| 00:00:01 |
————————————————————————————-

Peeked Binds (identified by position):
————————————–

1 – :ID (NUMBER): 50

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

2 – access(“ID”=:ID)

24 rows selected.

–you can see, the 2 are using the same plan with child number 2

–let’s use sql_id to show all plans of this sql
T@ORCL11G>select * from table(dbms_xplan.display_cursor(‘g852rfbbqn2f9’,null,’typical +peeked_binds));

PLAN_TABLE_OUTPUT
—————————————————————————————————
SQL_ID  g852rfbbqn2f9, child number 0
————————————-
select * from t2 where id=:id

Plan hash value: 1513984157

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |       |       |   293 (100)|          |
|*  1 |  TABLE ACCESS FULL| T2   | 68868 |  6994K|   293   (1)| 00:00:04 |
————————————————————————–

Peeked Binds (identified by position):
————————————–

1 – :ID (NUMBER): 99

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

1 – filter(“ID”=:ID)

SQL_ID  g852rfbbqn2f9, child number 1
————————————-
select * from t2 where id=:id

Plan hash value: 3119810522

————————————————————————————-
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————-
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |   104 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T2_ID |     1 |       |     1   (0)| 00:00:01 |
————————————————————————————-

Peeked Binds (identified by position):
————————————–

1 – :ID (NUMBER): 1

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

2 – access(“ID”=:ID)

SQL_ID  g852rfbbqn2f9, child number 2
————————————-
select * from t2 where id=:id

Plan hash value: 3119810522

————————————————————————————-
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————-
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |   104 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T2_ID |     1 |       |     1   (0)| 00:00:01 |
————————————————————————————-

Peeked Binds (identified by position):
————————————–

1 – :ID (NUMBER): 50

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

2 – access(“ID”=:ID)

71 rows selected.

–Oracle is not BLIND now even bind variable is used if there is accurate statistics.

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to Step by step test 11g adaptive cursor

  1. neworacledba says:

    11g adaptive cursor feature has been explained very clear in this nice post

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: