学习《Oracle 9i10g编程艺术》的笔记 (五)

 1.尽可能地使用绑定变量 不用开关CURSOR_SHARING=FORCE 

如果你愿意，这个特性会实现一个自动绑定器（auto-binder）。
如果有一个查询编写为SELECT * FROM EMP WHERE EMPNO = 1234，自动绑定器会悄无声息地把它改写成
SELECT * FROM EMP WHERE EMPNO = :x。这确实能动态地大大减少硬解析数，并减少前面讨论的库闩等待
时间——但是（凡事总有个“但是”），它可能有一些副作用。游标共享的一个常见副作用如下所示：
ops$tkyte@ORA10G> select /* TAG */ substr( username, 1, 1 )
2 from all_users au1
3 where rownum = 1;
S
-
B
ops$tkyte@ORA10G> alter session set cursor_sharing=force;
Session altered.
ops$tkyte@ORA10G> select /* TAG */ substr( username, 1, 1 )
2 from all_users au2
3 where rownum = 1;
SUBSTR(USERNAME,1,1)

这里到底发生了什么？为什么到第二个查询时SQL*Plus 报告的列突然变得这么大？要知道，这还是
同一个查询呀！如果查看一下游标共享设置为我们做了些什么，原因就会很清楚了（还会明白其他一些问
题）：
游标共享会删除查询中的信息。它找到每一个直接量（literal），包括内置求子串函数（substr）的
参数，直接量就是我们使用的常量。它把这些直接量从查询中删除，并代之以绑定变量。SQL 引擎再也不
知道这个列是长度为1 的子串，它的长度是不确定的。另外，可以看到where rownum = 1 现在也已经绑定。
看上去似乎不错；不过，优化器把一个重要的信息也一并删除了。它不知道“这个查询将获取一行”；现在
只认为“这个查询将返回前N 行，而N 可能是任何值”。实际上，如果加上SQL_TRACE=TRUE 后再运行这些
查询，你会发现每个查询使用的查询计划都不同，它们完成的工作量也大相径庭。考虑以下查询：
------------------------------
B
ops$tkyte@ORA10G> select sql_text from v$sql
2 where sql_text like 'select /* TAG */ %';
SQL_TEXT
-------------------------------------------------------------------------------
select /* TAG */ substr( username, 1, 1 ) from all_users au1 where rownum = 1
select /* TAG */ substr( username, :"SYS_B_0", :"SYS_B_1" ) from all_users au2 where
rownum = :"SYS_B_2"
select /* TAG */ substr( username, 1, 1 )
from all_users au1
where rownum = 1
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ------
---- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 77 0
1
------- ------ -------- ---------- ---------- ---------- ------
---- ----------
total 4 0.00 0.00 0 77 0
1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 412
Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=77 pr=0 pw=0 time=5767 us)
1 HASH JOIN (cr=77 pr=0 pw=0 time=5756 us)
1028 HASH JOIN (cr=70 pr=0 pw=0 time=8692 us)
9 TABLE ACCESS FULL TS$ (cr=15 pr=0 pw=0 time=335 us)
1028 TABLE ACCESS FULL USER$ (cr=55 pr=0 pw=0 time=2140 us)
4 TABLE ACCESS FULL TS$ (cr=7 pr=0 pw=0 time=56 us)
********************************************************************************
select /* TAG */ substr( username, :"SYS_B_0", :"SYS_B_1" )
from all_users au2
where rownum = :"SYS_B_2"
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ------
---- ----------
查询计划有一些微小的差别（有时甚至完全不同）；另外它们的工作量也有很大差异。所以，打开游
标共享确实需要特别谨慎（而且需要进行充分测试）。游标共享可能会改变应用的行为（例如，列宽发生变
化），而且由于它删除了SQL 中的所有直接量，甚至包括那些绝对不会变化的直接量，所以可能会对查询计
划带来负面影响。
另外，与解析和优化大量各不相同的查询相比，尽管使用CURSOR_SHARING = FORCE 会让运行速度更
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 85 0
1
------- ------ -------- ---------- ---------- ---------- ------
---- ----------
total 4 0.00 0.00 0 85 0
1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 412
Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT (cr=85 pr=0 pw=0 time=3309 us)
1 FILTER (cr=85 pr=0 pw=0 time=3301 us)
1028 HASH JOIN (cr=85 pr=0 pw=0 time=5343 us)
1028 HASH JOIN (cr=70 pr=0 pw=0 time=7398 us)
9 TABLE ACCESS FULL TS$ (cr=15 pr=0 pw=0 time=148 us)
1028 TABLE ACCESS FULL USER$ (cr=55 pr=0 pw=0 time=1079 us)
9 TABLE ACCESS FULL TS$ (cr=15 pr=0 pw=0 time=90 us)

快，但同时我也发现，倘若开发人员确实在查询中使用了绑定变量，查询的速度就比使用游标共享要快。
这不是因为游标共享代码的效率不高，而是因为程序本身的效率低下。在许多情况下，如果应用没有使用
绑定变量，也不会高效地解析和重用游标。因为应用认为每个查询都是惟一的（并把查询分别建立为不同的
语句），所以绝对不会多次使用一个游标。事实上，如果程序员刚开始就使用了绑定变量，他（或她）就能
只解析一次查询，然后多次重用它。正是这种解析开销降低了总体性能。
实质上讲，一定要记住重要的一点，只打开CURSOR_SHARING = FORCE 并不一定能解决你的问题。而
且游标共享还可能带来新的问题：在有些情况下CURSOR_SHARING 是一个非常有用的工具，但它不是银弹。
开发得很好的应用从不需要游标共享。从长远来看，要尽可能地使用绑定变量，而在需要时才使用常量，
这才是正确的做法。

 

 

 

2.

在整个开发阶段，你都要把性能作为一个目标精心地设计，合理地构建，并且不断地测试。绝对不
能把它当作马后炮，事后才想起来。我真是很奇怪，为什么那么多人根本不对应用调优，就草率地把应用
交付到客户手里，匆匆上马，并运行起来。我见过一些应用除了主键索引外，居然没有其他的任何索引。
从来没有对查询执行过调优，也没有执行过压力测试。应用的用户数很少，从未让更多的用户试用过。这
些应用总是把调优当成产品安装的一部分。对我来说，这种做法绝对不可接受。最终用户应该第一天就拿
到一个响应迅速、充分优化的系统。肯定还有许多“产品问题”需要处理，但不能让用户从一开始就领教
糟糕的性能。对用户来说，一个新应用里有几个bug 尚能容忍，但你别指望他们能耐心地在屏幕前等待漫
长的时间。