TOM大叔的调优书中说过一句话，具体英文就忘了，大概意思就是：如果有人让我写本怎样让ORACLE性能最慢的书的话我会将取消绑定变量(bind variable)做为书的第一章和最后一章(他的意思是他很有幽默~~!)，可见绑定变量的重要性。
绑定变量大多用在OLTP(online transaction process)中，在OLAP(online analizy process)中就没必要用BIND VARIABLE了。
要了解绑定变量的作用首先得了解下SHARED_POOL library cache中的SQL解析。
SQL 解析分为硬解析(hard parse)跟软解析(soft parse)。所谓硬解析就是真正的将产生的SQL用自己内部的算法完全解析一遍并将解析后的结果存入库缓存(library cache)，而软解析就是发现库缓存中已经存在解析过的该SQL语句，从而直接利用，跳过重新解析的步骤。
所以SQL解析的步骤就是：先判断库缓存中是否存在该sql的解析，存在就不用再解析（软解析），没有就解析一遍（硬解析），并存入库缓存。
下面做个实验：
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 276 143509059
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 276 143509059
--这里value记录的就是硬解析次数，上面这个select语句同样第一次会被解析，之后就软解析了。
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 281 143509059
-- 过会再执行此句，可以看到这里VALUE变成281了，有人可能会有疑问，不是276的吗，怎么变了？原因是库内部平时也在做各种操作，比如状态收集等， 可能会产生硬解析，而且执行一段时间之后，ORACLE会根据最近最少使用原则将此SQL解析踢出共享池，再执行就要重新解析一遍了。
--为了尽量不影响我们的分析，将库以只读启动：
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 62915940 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
SQL> alter database open read only
2 ;
数据库已更改。
SQL> col name for a20
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 188 143509059
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 188 143509059
SQL> select * from t where id=2009;
ID
----------
2009
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 190 143509059
--解析id=2009的查询
SQL> select * from t where id=2009;
ID
----------
2009
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 190 143509059
--此前解析过，所以再此查询2009时VALUE不变，软解析。
SQL> select * from t where id= 2009;
ID
----------
2009
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 191 143509059
--此处注意，2009前面有个空格，也会被判断为跟原来的SQL不匹配。
SQL> select * from t where Id=2009;
ID
----------
2009
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 192 143509059
--此处的Id的I是大写，也会跟原来的查询不匹配，所以硬解析。
SQL> select * from t where id=2010;
ID
----------
2010
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 194 143509059
--2010跟2009的查询SQL解析不共享，所以也要硬解析。
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ---------------------- ---------
cursor_sharing string EXACT
此外，cursor_sharing参数也会影响ORACLE判断SQL是否共享，此处用的是EXACT，也是10G默认的，11G中已不是。以后我会单独讨论下这个参数。
可以想象，如果对10W个不同的ID每个都查询一次，硬解析的次数是很大的，会导致性能的下降。而用绑定变量来代替ID的值，10W条数据的查询会被共享为一条SQL，只硬解析一次，带来的性能提升也是很明显的。
下面也对绑定变量做个实验，向T表插入10W条数据。可以看出用绑定变量跟不用的巨大差异。
SQL> set timing on
SQL> truncate table t;
Table truncated.
Elapsed: 00:00:00.04
--p1不用绑定变量
SQL> create or replace procedure p1
2 as
3 begin
4 for i in 1..100000 loop
5 execute immediate 'insert into t values('||i||')';
6 end loop;
7 end;
8 /
Procedure created.
Elapsed: 00:00:00.06
--p2用绑定变量
SQL> create or replace procedure p2
2 as
3 begin
4 for i in 1..100000 loop
5 execute immediate 'insert into t values(:a)' using i;
6 end loop;
7 end;
8 /
Procedure created.
Elapsed: 00:00:00.04
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 143800 143509059
1 row selected.
Elapsed: 00:00:00.01
SQL> exec p1;
PL/SQL procedure successfully completed.
Elapsed: 00:01:10.01
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 243800 143509059
1 row selected.
Elapsed: 00:00:00.01
SQL> truncate table t;
Table truncated.
Elapsed: 00:00:00.12
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 243801 143509059
1 row selected.
Elapsed: 00:00:00.03
SQL> exec p2;
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.67
SQL> select * from v$sysstat where statistic#=331;
STATISTIC# NAME CLASS VALUE STAT_ID
---------- -------------------- ---------- ---------- ----------
331 parse count (hard) 64 243803 143509059
1 row selected.
Elapsed: 00:00:00.00
可见差异之大。。
做 实验的时候发现过程p2中用insert into t values(i)代替execute immediate 'insert into t values(:a)' using i的话，消耗的时间是相近的，也就是说前者也会被替换成使用绑定变量的方式。感兴趣的朋友可以试一下。不足的地方欢迎指正～
转自：http://hi.baidu.com/kywinder/item/8dc7972656a6019db7326341
相关推荐
ORACLE有些sql语句,不确定它是否会增加硬解析次数，对其它进行测试，是否会增加硬解析。
在数据库中硬解析是万恶之源，为大家提供一个查找并且定位oracle硬解析问题SQL语句脚本
软解析是 Oracle 数据库中的一种优化机制，它可以减少硬解析的次数，提高系统的整体性能。 Oracle 数据库提供了 shared_pool_size 参数来缓存 SQL 语句，以便重用已经解析的 SQL 语句。然而，如果 shared_pool_...
如果发现硬解析次数过高，可能需要对SQL语句进行优化，或者调整数据库的配置，以确保系统的高效运行。 总之，Oracle的硬解析和软解析是数据库解析策略的两个关键方面。理解它们的工作原理，并能有效地控制和优化，...
每事务硬解析次数是指每事务产生的硬解析次数。 30. Sorts/Sec（每秒排序次数） 每秒排序次数是指每秒产生的排序次数。 31. Sorts/Tx（每事务排序次数） 每事务排序次数是指每事务产生的排序次数。 32. ...
6. parse count (hard): 表示在shared pool中因未命中而导致的硬解析次数。硬解析会导致额外的CPU和资源消耗。 7. parse count (total): 包括软解析和硬解析在内的总解析次数，用于计算软解析命中率。 8. parse ...
- **减少硬解析和软解析**：硬解析是完全解析SQL，而软解析利用已解析的SQL（缓存在库缓存中）。通过配置`session_cached_cursors`，可以缓存游标以减少软解析，提高执行效率。 4. **优化访问路径**： - **索引...
除此之外，还有其他优化策略，比如使用绑定变量代替硬解析，减少解析次数；使用覆盖索引避免全表扫描；通过分析和统计信息更新确保查询计划的准确性；合理设计索引，包括复合索引和函数索引；避免全表扫描，利用索引...
- **性能优化**：了解关键统计项如`CPU used by this session`（所有Session的CPU使用量）、`db block changes`（数据块变更次数）、`execute count`（执行的SQL语句数量）、`parse count (hard)`（硬解析次数）等...
《机械设计》第八版课后习题集答案解析主要涵盖了材料疲劳极限、简化的等寿命寿命曲线绘制、机械零件的设计与安全系数计算等多个知识点。以下是这些知识点的详细解析： 1. **材料疲劳极限与循环次数的关系**： ...
硬解析次数高可能表明应用程序效率不高或数据库配置需要优化。 报告还提到了与SQL相关的统计信息，其中包括逻辑读取、物理读取、执行次数、调用和解析次数以及共享内存占用等。这些信息有助于分析SQL语句的性能问题...
2. **减少硬解析**：通过优化SQL语句、合理设置`cursor_sharing`参数以及调整`shared_pool_size`等措施减少硬解析次数。 3. **提高SQL重用率**：利用SQL绑定变量等技术提高SQL语句的重用率，减少不必要的解析操作。 ...
硬解析次数过多可能提示SQL重用率低的问题。 - **Hard parses**：硬解析次数。硬解析过多会增加系统的负担，可能需要优化SQL或调整共享池配置。 - **Sorts**：每秒/每事务的排序次数。频繁的排序操作可能与表结构...
绑定变量通过减少硬解析次数，降低了每次执行SQL语句时所需的资源开销，提高了整体性能。特别是在OLTP环境中，事务处理频繁且快速，绑定变量的应用对于保持系统的响应时间和吞吐量至关重要。 ### 如何使用绑定变量 ...
- **Hard Parses**：硬解析次数过多可能意味着SQL语句的重用率不高，应考虑使用绑定变量或适当增加共享池大小以提高SQL重用率。 - **Redo Size**：较高的redo生成量可能意味着较高的事务活动或写入操作，需要注意...
6. `parse count (hard)`：在Shared Pool中未命中的解析调用次数，反映了硬解析的频率。 7. `parse count (total)`：总的解析调用次数，包括软解析和硬解析。 8. `parse time cpu`：总CPU解析时间（以10毫秒为单位）...
5. **硬解析问题**：硬解析次数过高可能是由于SQL语句重用不足导致的，需要进一步检查SQL语句的编写方式以及相关的数据库参数配置。 #### 四、进一步行动 针对以上分析，建议采取以下措施： 1. **优化快照周期**...