关于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"
结果:
设值时,类型是由开发人员去设,不要由系统去转!很浪费性能的