16 | 10 | 2017
Latest Articles
Popular Articles

11G

Intelligent cursor sharing : enhanced bind variable peeking

User Rating:  / 0
PoorBest 

Enhanced bind variable peeking.


Introduction :


This article is about skewed table data, histograms, bind variables, bind variable peeking and 11G optimizer improvements.


Since we are good DBA' s we should know :

1. when a query requires the access/"block visit" of the vast majority of the table blocks ... the optimizer should go for a full table scan (FTS)

2. when a query requires only the access/"block visit" of a few table blocks ... the optimizer should go for a B-tree index access


We know when we have queries like

select * from table_1 where field_1 = a_given_value

that

1. it might be interesting to have a B-tree index on table_1.field_1 if field_1 has a lot of DISTINCT values.

2. it is NOT interesting to have a B-tree index on table_1.field_1 if field_1 has NOT a lot of DISTINCT values ( only a few ) AND if table_1.field_1 is NOT SKEWED.
( read even distribution, read every value has more or less an equal amount of entries )

3. it is interesting to have a B-tree index on table_1.field_1 if field_1 has NOT a lot of DISTINCT values ( only a few ) EVEN THOUGH table_1.field_1 is SKEWED.
( read ONeven distribution, read some value have a lot of entries whereas some entries are rare ) HOWEVER in this particular the access path depends on the indexed value, sometimes the index can be very usefull whereas in some other cases the full table scan is preferred.


When we execute a query like

select * from table_1 where field_1 = a_given_value

and a_given_value is rare (only) a few rows then index access could be the optimal plan


when we execute a query like

select * from table_1 where field_1 = a_given_value

and a_given_value requires the access to the vast majority of the table rows then we should go for a full table scan


Since we are good DBA' s we should know we should use bind variables for any OLTP database.

During the parse of query in order to tell to the optimizer ... "Dear optimizer this data is skewed, oneven distribution" Oracle has introduced HISTOGRAMS.
So when a the table statistics tells to the optimizer this data is skewed, oneven distribution the optimizer would

1. use these histograms if literals where used in the query and your release was prior to 9i during the HARD PARSE ( first time )

2. also even when bind variables were used these histograms where used and if your release was 9i or 10G. This was possible because of bind variable peeking.
( assuming you didn' touch _optim_peek_user_binds )


But prior to 11G we were stuck with

1. we have to use bind variables in any OLTP environment in order to reduce hard parsing
2. we should ideally have different explains plans depending on the value of the bind variable

What we would like is

Tell to the optimizer ... the execution plan of this query depends on the value of the bind variable ... you should peek for it and parse and additional explain plan if needed and reuse the existing explain plan if not needed.

And ... this is what the optimizer is doing for us in 11G.


Since Methusalem was born the Oracle dictionary contained the dynamic performance view v$sql.
Even though with every new release there is some additional information. ( we get something in place for the ever increasing memory requirements of the Oracle instance )
In 11G v$sql has at least 3 additional fields which are of special interest with respect to this topic.

v$sql.is_bind_sensitive : "Indicates whether the cursor is bind sensitive (Y) or not (N). A query is considered bind-sensitive if the optimizer peeked at once of its bind variable values when computing predicate selectivities and where a change in a bind variable value may cause the optimizer to generate a different plan."
v$sql.is_bind_aware     : "Indicates whether the cursor is bind aware (Y) or not (N). A query is considered bind-aware if it has been marked to use extended cursor sharing. The query would already have been marked as bind-sensitive."
v$sql.is_shareable      : "Indicates whether the cursor can be shared (Y) or not (N)"

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_3042.htm#i1417057


It is time for some gym :
In this example I create tables T_PRECIOUSMETALS_PM and T_EXCHANGERATE_ER. 4 precious metals are traded. The vast majority are the transactions related to Gold. Transaction for Silver and Platina are more rare but very rare are those relate to the precious metal Palladium.
The population of the table T_EXCHANGERATE_ER is artificial but I guess this example is representative for uneven distribution  ... a few distinct values --- Gold, Silver, Platina, Palladium --- some of them are very common — Gold --- some of them are really rare --- Palladium.


SQL> create tablespace PM;

Tablespace created.

SQL> grant create session,create table,create sequence to PM identified by PM;

Grant succeeded.

SQL> alter user PM default tablespace PM temporary tablespace TEMP;

User altered.

SQL> alter user PM quota unlimited on PM;

User altered.

SQL> grant select any dictionary to pm;

Grant succeeded.

SQL> connect PM/PM
Connected.

SQL> create table T_PRECIOUSMETALS_PM (PM_PK varchar2(2),PM_NAME varchar2(20),PM_IS_PGM varchar2(3) not null, primary key (PM_PK)) organization index initrans 2 tablespace PM;

Table created.

SQL> insert into T_PRECIOUSMETALS_PM values ('RU','Ruthenium','YES');

1 row created.

SQL> insert into T_PRECIOUSMETALS_PM values ('PD','Palladium','YES');

1 row created.

SQL> insert into T_PRECIOUSMETALS_PM values ('OS','Osmium','YES');

1 row created.

SQL> insert into T_PRECIOUSMETALS_PM values ('IR','Iridium','YES');

1 row created.

SQL> insert into T_PRECIOUSMETALS_PM values ('PT','Platinium','YES');

1 row created.

SQL> insert into T_PRECIOUSMETALS_PM values ('Au','Gold','NO');

1 row created.

SQL> insert into T_PRECIOUSMETALS_PM values ('Ag','Silver','NO');

1 row created.

SQL> commit;

Commit complete.

SQL> create table T_EXCHANGERATE_ER (ER_PK number, ER_FK_PM varchar2(2) not null, ER_RATE number not null, ER_TIME timestamp default systimestamp not null , primary key (ER_PK)) organization heap initrans 6 tablespace PM;

Table created.

SQL> alter table T_EXCHANGERATE_ER add constraint FK_ER_PM foreign key (ER_FK_PM) references T_PRECIOUSMETALS_PM (PM_PK);

Table altered.

SQL> create index IDX_FK_ER_PM on T_EXCHANGERATE_ER ( ER_FK_PM ) initrans 6 tablespace PM;

Index created.

SQL> create sequence SEQ_ER cache 1000;

Sequence created.


We populate the t_exchangerate_er table with some data. For the precious metal Gold we assume many trading (100.000 entries) whereas for others we assume less.
For Paladium we assume only 200 entries. So we get a skewed data distribution. So given the fact ER_FK_PM is B-tree indexed there might be some queries which should ideally be parsed to use the index whereas ( when we query for palladium )
whereas for some other queries a full table scan might be appropriate.

SQL> declare
2  begin
3  for i in 1 .. 100000 loop
4  insert into t_exchangerate_er values (seq_er.nextval,'Au',dbms_random.value (850,880),systimestamp);
5  end loop;
6  end;
7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


SQL> declare
2  begin
3  for i in 1 .. 20000 loop
4  insert into t_exchangerate_er values (seq_er.nextval,'Ag',dbms_random.value (10,11),systimestamp);
5  end loop;
6  end;
7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> declare
2  begin
3  for i in 1 .. 5000 loop
4  insert into t_exchangerate_er values (seq_er.nextval,'PT',dbms_random.value (890,920),systimestamp);
5  end loop;
6  end;
7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> declare
2  begin
3  for i in 1 .. 200 loop
4  insert into t_exchangerate_er values (seq_er.nextval,'PD',dbms_random.value (180,185),systimestamp);
5  end loop;
6  end;
7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


SQL> exec dbms_stats.gather_table_stats('PM','T_EXCHANGERATE_ER',cascade=>true);


PL/SQL procedure successfully completed.


SQL> select table_name, column_name, histogram from user_tab_cols;

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM


T_EXCHANGERATE_ER              ER_TIME                        NONE
T_EXCHANGERATE_ER              ER_RATE                        NONE
T_EXCHANGERATE_ER              ER_FK_PM                       FREQUENCY
T_EXCHANGERATE_ER              ER_PK                          NONE
T_PRECIOUSMETALS_PM            PM_NAME                        NONE
T_PRECIOUSMETALS_PM            PM_PK                          NONE
T_PRECIOUSMETALS_PM            PM_IS_PGM                      NONE

7 rows selected.

The T_EXCHANGERATE_ER.ER_FK_PM contains skwed data.

http://www.psoug.org/reference/histogram.html

 

This is the explain plan when we query for transactions related to Gold. ( assume not using bind variables )
The full table scan is used and is appropriate


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

PLAN_TABLE_OUTPUT


SQL_ID  dmjyqnq52p16j, child number 0


select max(er_rate),min(er_rate) from t_exchangerate_er whereer_fk_pm='Au'

Plan hash value: 3321163704

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

| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time      |

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

|   0 | SELECT STATEMENT   |                   |       |       |   240 (100)|        |

|   1 |  SORT AGGREGATE    |                   |     1 |    25 |            |        |

|*  2 |   TABLE ACCESS FULL| T_EXCHANGERATE_ER |   100K|  2446K|   240   (1)| 00:00:03  |

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


Predicate Information (identified by operation id):


2 - filter("ER_FK_PM"='Au')


This is the explain plan when we query for transactions related to Palladium. ( assume not using bind variables )
The index range scan is used and is appropriate

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

PLAN_TABLE_OUTPUT


SQL_ID  12br0hxbcazk3, child number 0


select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm='PD'

Plan hash value: 713366416

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

| Id  | Operation                    | Name              | Rows  | Bytes | Cost(%CPU) | Time     |

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

|   0 | SELECT STATEMENT             |                   |       |       |     3 (100) |          |

|   1 |  SORT AGGREGATE              |                   |     1 |    25 |       |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| T_EXCHANGERATE_ER |   226 |  5650 |     3   (0) | 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | IDX_FK_ER_PM      |   226 |       |     1   (0) | 00:00:01 |

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


Predicate Information (identified by operation id):

3 - access("ER_FK_PM"='PD')

 

Question : But we should use bind variabes in an OLTP database, shouldn' t we ?
Answer   : Yes, we should


SQL> var i varchar2(2);


We query for Gold and the optimizer parses the appropriate full table scan.

SQL> exec :i := 'Au';

PL/SQL procedure successfully completed.

SQL> select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm=:i;

MAX(ER_RATE) MIN(ER_RATE)


879.999964   850.000094

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

PLAN_TABLE_OUTPUT


SQL_ID  652db6t49j43y, child number 0


select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm=:i

Plan hash value: 3321163704

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

| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |                   |       |       |   240 (100)|        |

|   1 |  SORT AGGREGATE    |                   |     1 |    25 |            |        |

|*  2 |   TABLE ACCESS FULL| T_EXCHANGERATE_ER |   100K|  2446K|   240   (1)| 00:00:03 |

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


Predicate Information (identified by operation id):


2 - filter("ER_FK_PM"=:I)


We query for silver.


SQL> exec :i := 'Ag';

PL/SQL procedure successfully completed.

SQL> select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm=:i;

MAX(ER_RATE) MIN(ER_RATE)


10.9999493   10.0000279

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

PLAN_TABLE_OUTPUT


SQL_ID  652db6t49j43y, child number 0


select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm=:i

Plan hash value: 3321163704

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

| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |                   |       |       |   240 (100)|        |

|   1 |  SORT AGGREGATE    |                   |     1 |    25 |            |        |

|*  2 |   TABLE ACCESS FULL| T_EXCHANGERATE_ER |   100K|  2446K|   240   (1)| 00:00:03 |

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


Predicate Information (identified by operation id):


2 - filter("ER_FK_PM"=:I)


We query for Palladium. Remember we got only a few entries for Palladium, so we would like to use and index range scan.

SQL> exec :i := 'PD';

PL/SQL procedure successfully completed.

SQL> select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm=:i;

MAX(ER_RATE) MIN(ER_RATE)


184.963623   180.070116

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

PLAN_TABLE_OUTPUT


SQL_ID  652db6t49j43y, child number 0


select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm=:i

Plan hash value: 3321163704

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

| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |                   |       |       |   240 (100)|          |

|   1 |  SORT AGGREGATE    |                   |     1 |    25 |            |          |

|*  2 |   TABLE ACCESS FULL| T_EXCHANGERATE_ER |   100K|  2446K|   240   (1)| 00:00:03 |

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


Predicate Information (identified by operation id):


2 - filter("ER_FK_PM"=:I)


20 rows selected.


We are disappointed. We would like to have the index range scan. The full table scan is certainly not the optimal plan here.
If we execute the same query a few times more we notice the optimizer parse us another and better plan, because of the v$sql.is_bind_aware and v$sql.is_bind_sensitive information


SQL> select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm=:i;

MAX(ER_RATE) MIN(ER_RATE)


184.963623   180.070116

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

PLAN_TABLE_OUTPUT


SQL_ID  652db6t49j43y, child number 0


select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm=:i

Plan hash value: 3321163704

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

| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |                   |       |       |   240 (100)|        |

|   1 |  SORT AGGREGATE    |                   |     1 |    25 |            |        |

|*  2 |   TABLE ACCESS FULL| T_EXCHANGERATE_ER |   100K|  2446K|   240   (1)| 00:00:03 |

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


Predicate Information (identified by operation id):


2 - filter("ER_FK_PM"=:I)


20 rows selected.

 

SQL> select child_number,is_bind_sensitive,is_bind_aware from v$sql where sql_id='652db6t49j43y';

CHILD_NUMBER I I


0 Y N

So the cursor is marked as bind sensitive

SQL> select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm=:i;

MAX(ER_RATE) MIN(ER_RATE)


184.963623   180.070116

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

PLAN_TABLE_OUTPUT


SQL_ID  652db6t49j43y, child number 1


select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm=:i

Plan hash value: 713366416

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

| Id  | Operation                    | Name              | Rows  | Bytes | Cost(%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |                   |       |       |     3 (100)|          |

|   1 |  SORT AGGREGATE              |                   |     1 |    25 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| T_EXCHANGERATE_ER |   226 |  5650 |     3   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | IDX_FK_ER_PM      |   226 |       |     1   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):


3 - access("ER_FK_PM"=:I)


21 rows selected.

SQL> select child_number,is_bind_sensitive,is_bind_aware from v$sql where sql_id='652db6t49j43y';

CHILD_NUMBER I I


0 Y N
1 Y Y

 

Do we get the full time scan again when the bind variable :i equals to Gold ?


SQL> exec :i := 'Au';

PL/SQL procedure successfully completed.

SQL> select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm=:i;

MAX(ER_RATE) MIN(ER_RATE)


879.999964   850.000094

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

PLAN_TABLE_OUTPUT


SQL_ID  652db6t49j43y, child number 2


select max(er_rate),min(er_rate) from t_exchangerate_er where er_fk_pm=:i

Plan hash value: 3321163704

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

| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |                   |       |       |   240 (100)|          |

|   1 |  SORT AGGREGATE    |                   |     1 |    25 |            |          |

|*  2 |   TABLE ACCESS FULL| T_EXCHANGERATE_ER |   100K|  2446K|   240   (1)| 00:00:03 |

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


Predicate Information (identified by operation id):


2 - filter("ER_FK_PM"=:I)


20 rows selected.


Answer : Yes
However we notice there is an additional child cursor, child number 2
Let' s take a look to the value for v.sql.is_shareable


SQL> select child_number,is_bind_sensitive,is_bind_aware,is_shareable from v$sql
where sql_id='652db6t49j43y';

CHILD_NUMBER I I I


0 Y N N
1 Y Y Y
2 Y Y Y


The initial entry has is_shareable flagged to N (No) and is supposed to be aged out of the library cache first.

So we have one identical statement, the optimizer has detected the predicate column contains skewed data and uses different explains plans depending on the value of the bind variable at that moment.
It sounds to me whenever a bind variable is used and whenever the statement is flagged as is_bind_aware = Y there bind peeking occurs all the time .. an appropriate plan can be parsed if not yet present.
So no more reasons left for not using bind variables.


So far this is my favourite 11G feature.