没有使用绑定变量导致大量硬解析

提到shared pool，不得不提绑定变量，在LOTP线业务系统中是很关键的一个性能基准。
可以举一个例子来简单说明一下。
1、创建一个表test_var，然后传入两个变量值，类型不同，查看执行的情况。

SH@prod>create table test_var as select object_id id,object_name name from user_objects where rownum<3;

Table created.

Elapsed: 00:00:00.78
SH@prod>update test_var set name=‘aaa’ where rownum<2;

1 row updated.

Elapsed: 00:00:00.12
SH@prod>update test_var set name=‘bbb’ where name!=‘aaa’;

1 row updated.

Elapsed: 00:00:00.15
SH@prod>alter system flush shared_pool;
System altered.

SH@prod>variable name varchar2(100);
SH@prod>exec :name:=‘aaa’;

PL/SQL procedure successfully completed.

SH@prod>select * from test_var where name=:name;

ID NAME

---

 13962 aaa

运行语句之后，查看sql_id和hash值，从v$sqlarea中可以查看versio{n}_{c}ount，如果发生了硬解析，versio{n}_{c}ount就会递增。SH@prod>selectsq{l}_{i}d,has{h}_{v}alue,address,chil{d}_{a}ddressfromv$sql where sql_text like ‘select * from test_var%’;

SQL_ID HASH_VALUE ADDRESS CHILD_ADDRESS

---

4untqsnaxb0x8 366314408 0000000099ACEA38 00000000968AB3F8

SH@prod>col sql_text for a50
SH@prod>select sql_text,version_count from v$sqlarea where sql_text like ‘select * from test_var%’;

SQL_TEXT VERSION_COUNT

---

select * from test_var where name=:name 1

然后再赋另外一个值，查看version_count 会不会递增

SH@prod>exec :name:=‘bbb’;

PL/SQL procedure successfully completed.

SH@prod>select * from test_var where name=:name;

ID NAME

---

 13961 bbb

SH@prod>select sql_id,hash_value,address,child_address from v$sql where sql_text like ‘select * from test_var%’;

SQL_ID HASH_VALUE ADDRESS CHILD_ADDRESS

---

4untqsnaxb0x8 366314408 0000000099ACEA38 00000000968AB3F8

SH@prod>select sql_text,version_count from v$sqlarea where sql_text like ‘select * from test_var%’;

SQL_TEXT VERSION_COUNT

---

select * from test_var where name=:name 1

可以看到没有发生任何的变化，说明绑定变量起作用了，无须再次硬解析。

修改数据类型，查看效果。把变量类型从varchar2改为char。

SH@prod>variable name char(3);
SH@prod>exec :name:=‘aaa’;

PL/SQL procedure successfully completed.

SH@prod>select * from test_var where name=:name;

ID NAME

---

 13962 aaa

SH@prod>select sql_id,hash_value,address,child_address from v$sql where sql_text like ‘select * from test_var%’;

SQL_ID HASH_VALUE ADDRESS CHILD_ADDRESS

---

4untqsnaxb0x8 366314408 0000000099ACEA38 00000000968AB3F8
4untqsnaxb0x8 366314408 0000000099ACEA38 000000007B034098

SH@prod>select sql_text,version_count from v$sqlarea where sql_text like ‘select * from test_var%’;

SQL_TEXT VERSION_COUNT

---

select * from test_var where name=:name 2

可以看出不同之处就是child_address，说明走了两次硬解析。