绑定变量窥探和直方图

绑定变量窥视cbo的一个附属功能,关闭他不会影响是用cbo还是rbo,就算关闭了绑定变量窥视,cbo还是会利用别的统计信息(num_distinct,density等)来评估cost和cardinality,只

是无法使用直方图信息而已:


SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
 FROM SYS.x$ksppi x, SYS.x$ksppcv y
 WHERE x.inst_id = USERENV ('Instance')
 AND y.inst_id = USERENV ('Instance')
 AND x.indx = y.indx
 AND x.ksppinm LIKE '%&par%';  2    3    4    5    6  
Enter value for par: peek_user
old   6:  AND x.ksppinm LIKE '%&par%'
new   6:  AND x.ksppinm LIKE '%peek_user%'

NAME VALUE DESCRIB
------------------------------ -------------------- ------------------------------

_optim_peek_user_binds TRUE enable peeking of user binds


当前开启绑定变量,开始测试:
SQL> alter system flush shared_pool;

System altered.

SQL> select sql_text,sql_id,a.executions from v$sql a 
where a.parsing_schema_name='SCOTT'
order by last_active_time desc;  2    3