oracle cursor_sharing similar 关于设值的一些问题

?关于oracle cursor_sharing= similar参数的一些测试

?

?

当你通过alter system set cursor_sharing="SIMILAR" 时

?

?

buyer_id为字符串类型，如果显示的给标明， 如下时，测试结果只会编译一次

?

?测试数据

select address,hash_value, executions, sql_text from v$sql  v$sql where upper(sql_text) like 'SELECT * FROM APPLY_BUYER%';SELECT * FROM APPLY_BUYER  where buyer_id='13111'and buyer_name='a';SELECT * FROM APPLY_BUYER  where buyer_id='13112'and buyer_name='b';SELECT * FROM APPLY_BUYER  where buyer_id='13113'and buyer_name='c';SELECT * FROM APPLY_BUYER  where buyer_id='13114'and buyer_name='d';SELECT * FROM APPLY_BUYER  where buyer_id='13115'and buyer_name='e';SELECT * FROM APPLY_BUYER  where buyer_id='13116'and buyer_name='f';SELECT * FROM APPLY_BUYER  where buyer_id='13117'and buyer_name='g';select address,hash_value, executions, sql_text from v$sql  v$sql where upper(sql_text) like 'SELECT * FROM APPLY_BUYER%';

??

?

?

测试结果

?

22FC3F20411362237117SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0"and buyer_name=:"SYS_B_1" 

?

?

?如果我们传的数字时，让系统转化时，编译每次都不会一样，但是他的地址和编码都是一样？不太明白

?

测试数据

?

select address,hash_value, executions, sql_text from v$sql  v$sql where upper(sql_text) like 'SELECT * FROM APPLY_BUYER%';SELECT * FROM APPLY_BUYER  where buyer_id=13101 and buyer_name='aa';SELECT * FROM APPLY_BUYER  where buyer_id=13102 and buyer_name='bb';SELECT * FROM APPLY_BUYER  where buyer_id=13103 and buyer_name='cc';SELECT * FROM APPLY_BUYER  where buyer_id=13104 and buyer_name='dd';SELECT * FROM APPLY_BUYER  where buyer_id=13105 and buyer_name='ee';SELECT * FROM APPLY_BUYER  where buyer_id=13106 and buyer_name='ff';SELECT * FROM APPLY_BUYER  where buyer_id=13107 and buyer_name='gg';select address,hash_value, executions, sql_text from v$sql  v$sql where upper(sql_text) like 'SELECT * FROM APPLY_BUYER%';

?

?测试结果

92FE5702419239062141SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 102FE5702419239062141SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 112FE5702419239062141SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 122FE5702419239062141SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 132FE5702419239062141SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 142FE5702419239062141SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 152FE5702419239062141SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 

?

?

?

?

?

?

结果：

?

?? 设值时，类型是由开发人员去设，不要由系统去转！很浪费性能的

?