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%';

  

 

 

测试结果

 

2	2FC3F204	1136223711	7	SELECT * 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%';

 

 测试结果

9	2FE57024	1923906214	1	SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 
10	2FE57024	1923906214	1	SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 
11	2FE57024	1923906214	1	SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 
12	2FE57024	1923906214	1	SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 
13	2FE57024	1923906214	1	SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 
14	2FE57024	1923906214	1	SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 
15	2FE57024	1923906214	1	SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1" 

 

 

 

 

 

 

结果：

 

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