1
ORACLE10G 数据库开发
优化指南
2
目录
目录....................................................................................................................................................2
1：绑定变量......................................................................................................................................3
2：不要频繁提交，不要使用自动提交..........................................................................................5
3：索引..............................................................................................................................................7
什么情况下适合建立索引：....................................................................................................7
如何使优化器使用索引：........................................................................................................8
4：高效 SQL ...................................................................................................................................10
查询中使用索引......................................................................................................................10
表联结查询..............................................................................................................................11
5：常见的、流传很广的传言........................................................................................................13
6：临时表的使用............................................................................................................................15
7：autotrace.....................................................................................................................................16
8：收集统计信息............................................................................................................................17
3
1：绑定变量
绑定变量是一个数据库开发人员所应该具备的最基本的习惯之一。
但是在现实中，很多开发人员完全不使用绑定变量，甚至根本不知道绑定变量是何物。
这个很大程度上是因为他们不了解使用或者不使用绑定变量对数据库造成的影响。如果他们
知道了，我敢肯定他们一定会很吃惊。
任何一句提交给 ORACLE 数据库运行的语句，在正式运行前，都需要通过数据库引擎
的解析。解析分为 2 种，一种是硬解析，一种是软解析。为了理解方便，简单的说，如果你
使用了绑定变量，那么数据库会倾向于使用软解析，如果你没有使用绑定变量，那么数据库
会倾向于使用硬解析。
从字面上理解，硬解析使用的资源会比软解析多。但是到底有多少？很难在这个文章里
面进行全面描写，但是可以从其中的一个方面进行理解：在一个 SQL 语句的硬解析中，会
产生数百个乃至数千个 LATCH，而这些 LATCH 锁住的都是数据库必须在实例级别串行访
问的资源，并且请注意，这么多的 LATCH 是硬解析一句 SQL 语句中产生的！软解析则不
需要产生这些 LATCH。单从这一点就可以知道，硬解析对数据库而言是一个极其昂贵的操
作。如果开发人员在一个循环里面调用了一个 SQL 语句而又没有绑定变量，那么可以想象
对数据库会造成多大的压力！
****************************************************************************
LATCH 不等于锁 LOCK，它是一种轻量级的 LOCK，用来保证对资源的串行访问。对
于被 LOCK 住资源的申请，如果不能满足，那么申请者会被阻塞以后进入一个等待队列等
待，当资源被释放的时候，按照先到先得的顺序唤醒队列里面的第一个等待者。而对被
LATCH 住的资源的申请，如果不能满足，那么申请的进程不会被阻塞也不会等待，它只会
在很短的一个时间以后再次重新申请。申请 LATCH 住资源的进程能否得到这个资源全凭运
气：它们不会排队等待，而是随机的去碰运气—不停的尝试申请。
****************************************************************************
CURSOR_SHAREING 参数是 ORACLE 提供的一个对于不使用绑定变量的解决方案，
它如果设置成 FORCE，可以在数据库实例上对所有提交给数据库的 SQL 语句强制进行绑定
变量。这看来好象是对开发人员的一种解放，但是，请注意下面 2 点：
1：CURSOR_SHAREING 会引入它自己的额外开销。
2：它有可能会引发一些与手动绑定变量不同的、不可意料的 SQL 执行返回结果，这
增加了测试和排错的难度。
CURSOR_SHAREING 绝对不是解决绑定变量的万能方法，相反，它这是 ORACLE
提供的一种不得已而为之的解决方案，只有当运行系统因为硬解析而不堪重负，而短期又不
可能全面重写代码的情况下使用的，而且使用以前一定要经过充分严格的测试。
当然，过度绑定变量是有可能导致某个查询性能下降的，实际中也会有这样的场景。
但是，相比较而言，不使用绑定变量而带来的性能问题要更普遍和严重得多。
以 JAVA 为例，所有提交给数据库执行的 SQL 语句应该如下面这样绑定变量。
{
Connection con = DriverManager.getConnection
("jdbc:oracle:oci:@database","scott","tiger");
4
conn.setAutoCommit(false);//把自动提交修改为非自动提交。
String v_sql = 'select name from table_a where id = ? '; //嵌入绑定变量
ReparedStatement stmt = conn.prepareStatement( v_sql );
stmt.setString(1, 'xxxx' ); //为绑定变量赋值
stmt.executeQuery();
conn.commit();
}
所以，请开发人员特别注意：除非有充分的理由，否则，请确保你提交给数据库执行
的任何一句 SQL 语句都是手工绑定了变量的。
5
2：不要频繁提交，不要使用自动提交
要根据业务逻辑的需求，保证业务数据一致性的情况下提交。
实际上，开发人员使用频繁提交、自动提交主要是他们的认识里面有下面 2 条
1：在做删除和修改的过程中，对数据行的锁定是稀有资源，而且会阻塞对数据库相应行
的读。
2：ODBC，JDBC 模型中事务默认都是自动提交（隐式提交）。
对 于 第 一 点 ， 不 能 说 是 错 误 的 ， 因 为 在 其 他 的 主 流 数 据 库 （ DB2 ， SYBASE ，
SQLSERVER）中，这个概念是正确的。但是在 ORACLE 中，完全不是这么回事情。ORACLE
的并发模型以及对于行锁的实现方法使得它对 10000 行数据的写锁定所消耗的资源与对 1
行数据的写锁定所消耗的资源是完全相同的--它们实际上都不消耗任何额外资源。而且，任
何的写锁定都不会阻塞其他会话对该数据的读取。
对于第 2 点，开发人员应该手动把它修改成非自动提交。在 ODBC 中默认设置是自动提
交是很好理解的，因为 ODBC 是为 SQLSERVER 的开发人员设计的，SQLSERVER 的并发
模型使得锁定是一种非常稀有的资源，必须尽快提交并且释放锁定。但是这个特性不应该被
继承到 JDBC 中。JDBC 被用来设计支持企业级的应用，而事务则是企业级应用的核心特征，
自动提交几乎肯定会破坏事务的原子性。
以 JAVA 为例，我认为任何与数据库事务相关的代码都应该是这样的：
{
Connection con = DriverManager.getConnection
("jdbc:oracle:oci:@database","scott","tiger");
conn.setAutoCommit(false);//把自动提交修改为非自动提交。
//业务代码
//业务代码
//业务代码
conn.commit();//根据业务需求最后再一并提交。
}
如果不把自动提交修改成非自动提交，那么代码是极其糟糕的。
实际上，频繁的递交还会大大加强 ORACLE 数据库的压力，导致性能下降。这是因为
ORACLE 的多版本控制和行级锁定的特征使得它提交 1 行数据所产生的负荷与提交 10000
行数据所产生的负荷几乎相等。很显然，对比下面的这两段代码：
代码 1（糟糕，如果使用自动提交，那么即使没有代码中的这 2 句 commit，也等效与下
面的代码）：
for (int i=0;i<10000;i++)
{
delete from table1 where ....
commit;
insert into table2 where ....
commit;
}
代码 2（优良）：
