Oracle Binding variables and histograms

One, the spy of the binding variable (PEEK)

1, Oracle in the processing of SQL with binding variables, only in hard parsing will "pry" the value of the binding variable in SQL, and then based on the value of the detected to determine the entire SQL execution plan. Parameters: _optim_peek_user_binds

2, the case of the binding variable snooping
CREATE TABLE T8 (id int, name VARCHAR2 (100));
Begin
For I in 1. 1000 loop
INSERT into T8 values (I, ' Gyj ' | | i);
End Loop;
Commit
End
CREATE INDEX T_IDX on T8 (ID);
exec dbms_stats.gather_table_stats (user, ' T8 ', cascade=>true);
Select Id,count (*) from T8 GROUP by ID;
variable n number;
Exec:n: = 1;
Select COUNT (*) from T8 where id =: n;
SELECT * FROM table (dbms_xplan.display_cursor);

3, and then insert the value
Begin
For I in 1. 10000 loop
INSERT into T8 values (1, ' Gyj ' | | i);
End Loop;
Commit
End
exec dbms_stats.gather_table_stats (user, ' T8 ', cascade=>true);
variable n number;
Exec:n: = 1;
Select COUNT (*) from T8 where id =: n;
SELECT * FROM table (dbms_xplan.display_cursor);

Two, histogram

1, role: When a column of data distribution is uneven, in order to allow the CBO to generate the best execution plan, we may need to collect histograms on the table, the largest number of histograms (Bucket) is 254

2, two kinds of histograms
A. Frequency histogram, when the column Distinct_keys less than 254,oracle will automatically create a frequency histogram, and the number of barrels (BUCKET) equals Distinct_keys.

B. A highly balanced histogram that automatically creates a highly balanced histogram when the column Distinct_keys is larger than 254,oracle.

3. Generate histograms
BEGIN
Dbms_stats. Gather_table_stats (ownname=> ' GYJ ',
TabName => ' T8 ',
Estimate_percent => 100,
Method_opt => ' For all columns size skewonly ',
No_invalidate => FALSE,
Degree => 1,
Cascade => TRUE);
End;

/

Iii. binding variables peering at bind peek and histogram interaction

1, the collection histogram does not bind the variable to adopt hard coding hard Code
SELECT * from V$version;
Select Id,count (*) from T8 GROUP by ID;
BEGIN
Dbms_stats. Gather_table_stats (ownname=> ' GYJ ',
TabName => ' T8 ',
Estimate_percent => 100,
Method_opt => ' For all columns size skewonly ',
No_invalidate => FALSE,
Degree => 1,
Cascade => TRUE);
End;
/
Select COUNT (*) from T8 where id = 1;
Select COUNT (*) from T8 where id = 2;
Not applicable with binding variables hard parse based histograms can get good cardinality (cardinality)

2, the following is the use of binding variables and Peep + histogram exists when the situation
(1) Clear cache
alter system flush Shared_pool;
alter system flush Buffer_cache;

  (2) Statistical histogram
 begin
 dbms_stats. Gather_table_stats (ownname=> ' GYJ ',
 tabname          => ' T8 ',
  Estimate_percent =>,
 method_opt       => ' for all columns size 254 ',
 no_inval Idate    => FALSE,
 degree           => 1,
 cascade   &nbs P      => TRUE);
End;
/
(3) Test 1 and 2 unevenness values
 select id,count (*) from T8 Group by ID;
 variable n number;
 exec:n : = 1;
 select Count (*) from T8  where id =: n;
 select * FROM table (dbms_xplan.display_cursor);
 select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware,plan_hash_value from V$sql where sql_id = ' 7zy48bjbwdjff ';
 exec:n: = 2;
 select Count (*) from T8  where id =: n;
 select * FROM table (dbms_xplan.display_cursor);
 select CHild_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware,plan_hash_value from v$sql where sql_id = ' 7zy48bjbwdjff ';

(4) Another 2 value to execute a query
Exec:n: = 2;
Select COUNT (*) from T8 where id =: n;
SELECT * FROM table (dbms_xplan.display_cursor);
Select Child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware,plan_hash_value from v$sql where sql_id = ' 7zy48bjbwdjff ';

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

(5) Another 1 value to execute a query:
Exec:n: = 1;
Select COUNT (*) from T8 where id =: n;
SELECT * FROM table (dbms_xplan.display_cursor);
Select Child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware,plan_hash_value from v$sql where sql_id = ' 7zy48bjbwdjff ';