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.
Filed under: Performance Management | Tagged: 11g, scratch
