Anyone who has attended a course of sql or have read books or records connected with "variable binding" and know it is a concept closely linked to the performance of applications. Especially in OLTP systems is strongly recommended to use bind variables in statements to avoid hard parsing and so minimize the use of shared pool and get a better performance to skip the parsing stage in successive runs to the first execution of a sentence given.
The fact of "binder" although this circumscribed within good practice also has certain problems and you do not know what values \u200b\u200bwill be used to instantiate and bind variables is not clear for the optimizer to build plan. From 9i there is a mechanism called "bind peeking" that allows the optimizer know the values \u200b\u200bof the first instantiation and therefore put together a concrete plan. This new feature introduced new problems. The binding and histograms do not get quite right. Remember that the optimizer histograms and help you provide the data distribution.
As I said, the binder the optimizer does not know in advance that instantiate each variable value bind and therefore can not adapt the plan to the input values. If the data distribution is uniform this is not a problem, but what happens if the distribution is uneven?. What happens if the first instantiation is generated a plan to use full scan because the input value is low selectivity and then following instantiations of values \u200b\u200bhave high selectivity?. The latter usually require access to indices, but the plan was set with the first instantiation and therefore will use full scan when really should use index access, imagine how difficult it can be this. For example, one morning a programmer to run a query instance with a value or unusual edge to make a complex report that covers a high percentage of rows and cocked a plan with full access on a large table scan, then if the cursor remains in memory, the applications will use the same cursor for specific searches and will use the query plan generated by the odd (which used full scan), it sounds chaotic, no?. In 11g R1
added a new feature called "adaptive cursors" that solves the problem of 'bind peeking. " Here I show you some tests I did:
For the test I create a simple table with two columns X and Y. The column and has 3 possible values \u200b\u200b(A, B and C). Where A is very low selectivity, B and C is half selectivity has high selectivity.
rop @ DESA11G> create table t (x int, char (1)) Table created. Cree
table T with PCTFREE to 90% in order to generate many blocks with not many rows.
rop @ DESA11G> insert into tConsistent
2 select t_seq.nextval,
3 case when (rownum Between 1 and 4 million) then 'A'
4 when (rownum Between 4000001 and 5000000) then 'B'
5 when (rownum Between 5000001 and 5000010) then 'C'
6 end
7 from dual 8 connect by rownum
5000010 rows created.
rop @ DESA11G> select bytes, blocks from user_segments WHERE segment_name = 'T';
BYTES BLOCKS ---------- ---------- 82 944 679 477 248
I created a table that weighs more than 600Mb. An index
Now, I gather statelet
rop @ DESA11G> create index on t_idx t (y);
Index created.
rop @ DESA11G> begin
2 dbms_stats.gather_table_stats (ownname => user,
3 tabname => 'T',
4 method_opt => 'for all indexed columns',
5 cascade => true) end
6 ;
7 /
PL / SQL procedure successfully completed.
rop @ DESA11G> select and count (1)
2 from t 3 group by y;
AND COUNT (1)
- ----------
A B 1000000 4000000
C 10
In the last query is the distribution of the column and table T.
I will run a query and I will instantiate the bind variable: v with the value 'A' to arm a plan that uses full_scan:
rop @ DESA11G> variable v char (1);
@ rop DESA11G> exec: v: = 'A';
PL / SQL procedure successfully completed.
rop @ DESA11G> set @ Car train on
rop DESA11G> select avg (x) from t where and =: v;
AVG (X) ----------
21640320.5
Plan -----------------------------------------------
Execution
----------- Plan hash value: 1842905362
---------------------------------------------------------------------------
2 - filter ("Y" =: V)
Statistics --------------------------------
-------------------------- 329 recursive calls 0 db block gets
82086 82045 
gets physical reads 0 redo
size 243 bytes Sent via SQL * Net to client 233 bytes
Received via SQL * Net from client 2 SQL * Net
roundtrips to / from client
7 sorts (memory)
0 sorts (disk) 1 rows processed
The plan effectively used full_scan. Note the large number of physical reads.
us consult the V $ SQL:
rop @ DESA11G> select sql_id, child_number, is_bind_sensitive, is_bind_aware
2 from v $ sql WHERE
3 sql_text = 'select avg (x) from t where and =: v';
SQL_ID
IS_BIND_SENSITIVE CHILD_NUMBER IS_BIND_AWARE
------------- -------------------- ------------ - --------------
d9p5ax32fmqdn 0 YN
As shown in 11g adding new columns to the view v $ sql for the bind variables. Instances and
full scan to return only 10 values, we observe physical readings again.
: = 'C', which has very high selectivity:
rop @ DESA11G> exec: v: = 'C';
PL / SQL procedure successfully completed.
rop@DESA11G> set autotr on
rop@DESA11G> select avg(x) from t where y = :v;
AVG(X)
----------
24640325.5
Plan de Ejecución
---------------------------------------------------------- Plan hash value: 1842905362
---------------------------------------------------------------------------
233 Bytes Received via SQL * Net from client 2 SQL * Net
roundtrips to / from client
0 sorts (memory) 0 sorts (disk) 1 rows processed
Used
I will refer back to the v $ sql:
2 from v $ sql WHERE
3 sql_text = 'select avg (x) from t where and =: v ';
SQL_ID
CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE
------------- ------------ --------------- ----- ---------------
d9p5ax32fmqdn 0 YN
Follow as before.
Vuelvo repeat the previous query:
Rop DESA11G @> select avg (x) by t where y =: v;
AVG (X) ----------
24640325.5
Implementation Plan
---------------------------------------------- ------------
Plan hash value: 1842905362
------------------------------
--------------------------------------------- 1 4 physical reads 0 redo size
Sent 243 bytes via SQL * Net to client 233
Bytes Received via SQL * Net from client 2 SQL * Net
roundtrips to / from client
0 sorts (memory)
0 sorts (disk) 1 rows processed
3* order by child_number,bucket_id
The plan continues to set full access scan, but now look at physical readings. Were only 4!.
rop @ DESA11G> set off
Car train rop @ DESA11G> select sql_id, child_number, is_bind_sensitive, is_bind_aware
2 from v $ sql WHERE
3 sql_text = 'select avg (x) from t where and =: v';
SQL_ID
CHILD_NUMBER IS_BIND_SENSITIVE
IS_BIND_AWARE ------------- ------------ -------------------- --- ------------
d9p5ax32fmqdn 0 1 YY YN
d9p5ax32fmqdn
The query now shows another sqlid who is the son of the original with is_bind_aware column "Y". The latest plan showed full scan but does not match the few physical reads and logical, because the method I used to get the plan (dbms_xplan.diplay not have the option to indicate the child) does not show the plan but only 1 child plan parent (child = 0). Then I will show a trace 10046 actually used access index (also can be done with dbms_xplan.display_cursor indicating the child), which closes with the small number of readings needed. Consulting
new view of "adaptive cursors" is seen as Oracle keeps track of the executions and automatically adapts to the abrupt changes of selectivity to instantiate the variables:
rop @ DESA11G> select child_number,
2 bind_set_hash_value,
3 Peeke,
4 Executions,
5 rows_processed, buffer_gets
6, 7 cpu_time
from v $ sql_cs_statistics 8 9 WHERE
sql_id = 'd9p5ax32fmqdn';
CHILD_NUMBER
BIND_SET_HASH_VALUE P Executions ROWS_PROCESSED BUFFER_GETS CPU_TIME
------- ----- ------------------- - ---------- -------------- ----------- ----------
1 2477564004 Y 1 21 4 0
0 816821622 Y 1 4000001 82086 0
rop@DESA11G> ed
Escrito file afiedt.buf
1 select * from v$sql_cs_histogram
2 where sql_id ='d9p5ax32fmqdn'
rop@DESA11G> /
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT 
---------------- ---------- ------------- ------------ ---------- ----------
000000044B9DC7B8 3303659956 d9p5ax32fmqdn 0 0 1 ---------------- ---------- ------------- ------------ ---------- ---------- 000000044B9DC7B8 3303659956 d9p5ax32fmqdn
000000044B9DC7B8 0 1 0 0 2 1 3303659956 d9p5ax32fmqdn 000000044B9DC7B8
3303659956 d9p5ax32fmqdn
000000044B9DC7B8 1 0 3 1 1 0 3303659956 d9p5ax32fmqdn
000000044B9DC7B8 3303659956 d9p5ax32fmqdn
1 2 0 6 rows selected.
Recording and measuring activity was detected quickly internally that the plan was inadequate and was changed.
below shows the result of the event trace with 10046:
The principal or parent cursor:
************************* ************************************************** *****
Optimizer mode: ALL_ROWS
SQL ID: d9p5ax32fmqdn
Plan Hash: 1842905362
select avg(x)
from
t where y = :v
call count cpu elapsed disk query current rows <= 5000010;
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 6 12.58 11.26 246075 246111 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 12.58 11.26 246075 246111 0 3
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Parsing user id: 82
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=82037 pr=82025 pw=0 time=0 us)
4000000 TABLE ACCESS FULL T (cr=82037 pr=82025 pw=0 time=19822 us cost=22641 size=28010066 card=4001438)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6
0.00 0.00 direct path read 1956 0.23 1.58 SQL * Net message
from client 6 0.00 0.04
****************************** **************************************************
The cursor Child 1:
*************************************** *****************************************
SQL ID: 804rjbx6snjv4
Plan Hash: 3178687684
select avg(x)
from
t where y = :v
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 82
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=4 pr=0 pw=0 time=0 us)
10 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=0 us cost=4 size=7 card=1)
10 INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 83154)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00 SQL * Net message
from client 2 0.00 0.01
********************************* ***********************************************
Once again we see that version after version will add "corrections" to the optimizer for costs to reduce the margin of error and stabilize the systems.
847 bytes sent via SQL*Net to client