硬分析和软分析
可回忆一下上一章的内容，所有SQL代码都要经过分析、优化和执行阶段。在应用发布一条语句时，Oracle首先査看该语句的经过分析的版本是否存在。如果存在，这种结果称为软分析(soft parse),并认为是库高速缓存命中。如果在分析阶段或执行阶段，Oracle在共享池找不到代玛经过分析的版本或可执行的版本，它将执行硬分析(hard parse).这表示必须将SQL语句重新装入共享池，并进行完整的分析。
在硬分析中，Oracle进行语法和语义检查，检査对象和系统权限，建立最优执行计划，最后把它装入库髙速缓存。硬分析需要占用大量的CPU时间，比软分析效率低(软分析使用以前分析过的语句)。硬分析从头构造所有分析信息，从而要占用更多的资源。除了占用较多CPU时间外，硬分析还要占用大量闩获取，这可能会增加査询的响应时间。理想的方案是分析一次，执行多次。否则，Oracle将不得不进行硬分析。
注意 较高的硬分析率会导致严重的性能问题，因此，减少数据库中硬分析的数目是至关重要的。
软分析只涉及査找库高速缓存中等同的语句并重用它。软分析完全省略了优化SQL语句的主要步骤。软分析中不存在硬分析中那样的分析，它只对新语句进行散列，并将其散列值与库高速缓存中类似语句进行比较。
在软分析中，Oracle只査看必需的权限。例如，即使库高速缓存中存在等同的语句，但如果Oracle在(软)分析步骤中判断你没有必需的权限，相应的语句也不会执行。Oracle建议，如果硬分析率每秒大于100,则认为硬分析过多。
1.使用SQL Trace和TKPROF査看分析信息
第19章说明了如何使用SQL Trace和TKPROF实用程序跟踪SQL语句的执行情况。SQL Trace实用程序提供的最有用的信息中有些与査询的硬、软分析信息有关。下面的例子说明如何得岀査询的分析信息。
(1)使用下面的命令启用会话中的跟踪：
sql> alter session set sql_trace=true;
为保证以前没有任何査询被分析，刷新共享池，从库高速缓存中删除所有sql语句：
sql> alter system flush shared_pool;
(2)使用下面的査询在用户转储目录中创建一个跟踪：
sql> select * from comp_orgs where
created_date > sysdate-5;
输岀文件中sql trace的输出如下：
parsing in cursor #1 len=63 dep=o uid=21 oct=3
lid=21 tim=1326831345 hv=715483o8
select * from comp_orgs where created_date >
sysdate-:"sys_b_0"
end of stmt
parse #1:c=4,e=4,p=0,cr=57,cu=3,mis=1,r=0,dep=0,og=0,tim=1326831345
请注意，mis=1指出这是一个硬分析，因为这条sql语句未在库高速缓存中给出。
(3)接着，使用与前面査询稍微不同的査询形式。其输出是相同的，但Oracle不使用以前分析过的版本，因为步骤2和步骤3中的语句不是等同的。
SELECT * FROM comp_orgs WHERE create_ddate >
(SYSDATE -5);
下面是相应的SQL Trace输出：
PARSING IN CURSOR #1 len=77 dep=0 uid=21 oct=3
lid=21 tim=1326833972
SELECT /* A Hint */ * FROM comp_orgs WHERE
created_date > SYSDATE-:"SYS_B_O"
END OF STMT
PARSE
#l:c=l,e=l,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1326833972
mis=1指示的硬分析说明库高速缓存未命中。没有什么可惊讶的，因为这条语句与前一条不等同, 因此必须从头开始分析。
(4)再次使用原来的査询。现在，Oracle只执行一个软分析，因为这条语句与前一步中的语句是相同的。下面是相应的SQL Trace输出：
PARSING IN CURSOR #1 len=63 dep-0 uid-21 oct-3
lid-21 tim=1326834357
SELECT * FROM comp_orgs WHERE created_date >
SYSDATE-:"SYS_B_0"
END OF STMT
PARSE
#1:c=0,e=0,p=0jcr=0,cu-0,mis=0,r=0,dep=0,og=4,tim=1326834357
步骤4中的语句与步骤1中的语句等同，所以Oracle重用了分析过的版本。mis=0指出不存在硬分析, 只有软分析，这大大节省了资源。
2.度量库高速缓存的效率
可利用简单的比例来判断库高速缓存的尺寸设置是否正确。v$librarycache 数据字典视图提供判断库高速缓存尺寸设置是否有效所需要的信息。
下面的公式给出库高速缓存命中率：
select sum(pinhits)/sum(pins)
library_cache_hit_ratio from v$librarycache;
此公式指出库高速缓存当前具有高于99%的命中率，我们认为这个命中率很好。不过，不应该过分看重库高速缓存和缓冲区高速缓存的高命中率。因为即使命中率高达99.99%,但如果由诸如过度分析这样的事件导致大量等待，数据库的速度也会很慢。应该密切关注系统中的等待事件，不要盲目依赖高命中率。
确定库高速缓存的效率
select namespace, pins, pinhits, reloads from
v$librarycache order by namespace;
如果V$LIBRARYCACHE视图的RELOADS列显示较大的值，则表示许多SQL语句在老化退出后又被重新装入库池。你可能想増加共享池，但如果应用很大，执行的数目很大，或应用不使用绑定变量，则这样做也不能解决问题。
如果SQL语句不完全等同和/或如果它们使用常量而不是绑定变量，将会执行更多的硬分析，而硬分析是非常耗费资源的。可利用Oracle提供的DBMS_SHARED_POOL程序强迫可执行的SQL语句保留在共享池的库高速缓存组件中。此程序包有KEEP和UNKEEP过程，使用它们可将对象保留在共享池中，或从共享池中释放对象。
可使用V$LIBRARY_CACHE_MEMORY视图来确定共享池中当前正在使用的库高速缓存内存对象的数目，以及空闲的函高速缓余内存对象的数目。V$SHARED_POOL_ADVICE视图提供关于各种尺寸的共享池预期可节省的分析时间的信息。
3.优化库高速缓存
可配置某些重要的初始化参数，以有效地使用库高速缓存区。
•使用CURSOR_SHARING(字面替换)参数
优化库高速缓存商主要目的是重用以前分析过的或执行过的代码。最简单的办法是使用绑定变量而不是SQL代码中的直接字面陈述。绑定变量就像占位符：它们允许对SQL语句绑定应用数据。只要语句中变化的部分是输入变量的值，就可以利用绑定变量使Oracle能够重用语句。绑定变量使得能够重用被高速缓存、被分析过的査询版本，从而提高应用的速度。这里举一个使用绑定变量的例子。下面的代码建立一个数值类型的绑定变量：
VARIABLE bindvar NUMBER;
BEGIN
:bindvar
:=7900;
END;
/
现在可以发布以下利用刚创建的绑定变量的SQL语句：
SELECT ename FROM scott.emp WHERE empid =
:bindvar;
可用不同的绑定变量值执行这条语句多次。此语句只分析一次，但执行多次。与使用emp_id列的字面值(如7499)不同，Oracle重用第一次创建的执行计划，而不是为每条这样的语句创建一个独立的执行计划。这大大地削减了硬分析(和较高的闩活动)，从而大大降低CPU使用，缩短了检索数据的时间。例如，下面所有语句都可以利用使用绑定变量查询分析过的版本：
SELECT ename FROM scott.emp WHERE empid = 7499;
SELECT ename FROM scott.emp WHERE empid = 7788;
SELECT ename FROM scott.emp WHERE empid = 7902;
遗憾的是，许多应用使用的是字面值而不是绑定值。通过设置以下的初始化参数，可在某种程度上减轻这种问题：
CURSOR_SHARING=FORCE
或者使用如下的参数:
CURSOR_SHARING=SIMILAR
默认时，CURSOR_SHARING初始化参数设置为EXACT,表示只有各方面都等同的语句才在语句的不同执行之间被共享。CURSOR_SHARING参数的各个值，FORCE或SIMILAR保证Oracle重用语句，即使这些语句并不是在各方面都等同。
例如，如果两条语句在各方面都是等同的，不同之处只在于某些变量的字面值，使用CURSOR.SHARING=FORCE将使Oracle重用库高速缓存中分析过的SQL语句。Oracle用绑定变量值替换相应的字面福使语句等同。
CURSOR_SHARING=FORCE选项强制在所有情况下使用绑定变量，而CURSOR_SHARING=SIMILAR选项只在认为绑定变量不会对优化产生负面影响时才使用它。Oracle建议使用CURSOR_SHARING=SIMILAR而不使用CURSOR_SHARING=FORCE,因为使用CURSOR_SHARING=FORCE有可能使执行计划金坏。
但实际上，CURSOR_SHARING=FORCE对执行计划带来的好处远远超过了坏处。在观察到由于不使用绑定变量而导致大量硬分析的情况下，通过把默认的CURSOR_SHARING=EXACT选项换成CURSOR_SHARING=FORCE选项，可极大地改善数据库的性能。可在init.ora文件或SPFILE中更改这个参数的值，也可以使用ALTER SYSTEM(实例范围)语句或ALTER SESSION(会话范围)语句动态地进行更改。
通过允许用户共享仅在常量值方面不同的语句，CURSOR_SHARING参数使Oracle Database 11g能支持使用类似但不等同的SQL语句的大量用户。这个重要的改变开始于Oracle 8i.
•硬分析量较大的会话
代码清单20.3中的査询能显示自实例启动以来的硬分析数与执行数进行比较的结果。它还显示使用SQL语句的用户的会话ID。
SELECT s.sid, s.value "Hard Parses",
t.value "Executions Count"
FROM
v$sesstat s, v$sesstat t
WHERE
s.sid = t.sid
AND
s.statistic# =
(select statistic# FROM v$statname where name = 'parse count (hard)')
AND
t.statistic# =
(select statistic# FROM v$statname where name = 'execute count')
AND
s.value > 0
ORDER BY
2 desc;
使用CURSOR_SPACE_FOR_TIME参数
默认时，即使应用游标没有关闭也可以重新分配游标。这增加了Oracle的开销，因为需要检査是否从库高速缓存中刷新了游标。控制是否进行这种游标的重新分配的参数为CURSOR_SPACE_FOR_TIME初始化参数，其默认值为FALSE.如果设置此参数为TRUE,则应该保证在应用游标仍然打开时不能重新分配应用游标。init.ora文件中的这个初始化参数应该如下：
CURSOR_SPACE_FOR_TIME=TRUE
提示 如果想设置此参数，则应该保证能得到大量可用的空闲共享池内存.因为为了在库高速缓存中保存游标，此参数将使用更多的共享池内存。
•使用SESSION_CACHED_CURSORS参数
理论上，一个应用应该在独立的游标中得到所有分析过的语句，这样在执行一条新语句时，只需选择分析过的语句并更改变量的值。如果应用对不同的SQL语句重用单一游标，它仍需付出软分析的代价。
在第一次打开一个游标后，Oracle将分析语句，这样在以后就可以重用这个分析过的版本了。这是一个比每当数据库执行相同语句时重新创建游标更好的策略。如果可以高速缓存所有游标，则保留了服务器端的环境，即使客户机关闭游标或对新SQL语句重用它们时也是如此。
我们应该感谢SESSION_CACHED_CURSORS参数在用户重复地分析相同语句时的作用（就像在基于Oracle Forms的应用中，用户在各表单之间切换时那样）。使用SESSION_CACHED_CURSORS参数确保，对于任何需要3个以上分析调用的游标，这些分析请求将自动高速缓存在会话游标高速缓存中。
因此，分析相同语句的新调用避免了分析的开销。使用初始化参数SESSION_CACHED_CURSORS并将其设置为一个较大的数，将使查询处理更有效。虽然软分析比硬分析的成本低，但软分析也可以通过使用SESSION_CACHED_CURSORS参数并将其设置为一个较高的值来减少。
可在初始化参数文件中设置SESSION_CACHED_CURSORS强制会话游标高速缓存，或者使用下面的 ALTER SESSION命令动态地强制会话游标高速缓存：
ALTER SESSION SET SESSION_CACHED CURSORS =
value;
可使用V$SYSSTAT视图来查看SESSION_CACHED_CURSORS参数值的设置是否合适。如果会话游标高速缓存命中值低于会话的总分析计数，则应该立即增大SESSION_CACHED_CURSORS的参数值。
理想的情况应该是SQL语句在会话中分析一次并执行多次。关于绑定变量、游标共享及有关问题 的更好的介绍，请参阅Oracle白皮书"Efficient use of bind variables, cursor_sharing
and related cursor parameters (绑定变量、游标共享及相关游标参数的有效使用)"(http://otn.oracle.com/deploy/performance/pdf/cursor.pdf)
•分析和确定应用系统的規模
当用户数目不断增加时，某些系统会有麻烦。许多系统由于试图増加用户数目而导致性能大大下降。在用户数目增加时，应将焦点集中在系统中不必要的分析上。高度的分析导致闩争用，降低系统性能。这里给出一些准则，帮助总结前面关于库高速缓存、分析、使用特殊初始化参数等内容：
Ø 标准的规则是使尽可能多的代码成为存储代码(程序包、过程、函数)的形式，这样不会有由特殊的SQL所导致的问题。使用特殊的SQL可能会破坏库高速缓存，而且使用此SQL运行具有许多用户的较大应用时效率较低。使用存储代码保证了代码的等同和重用，从而增强了可伸缩性。
Ø 降低硬分析的数目，因为它们代价很高。把硬分析转为软分析的一种办法是使用绑定变量，正如本章前面所述的那样。降低硬分析减少了共享池的闩争用。
Ø 如果系统中没有使用绑定变量，可用CURSOR_SHARING=FORCE参数强迫共享只是字面值有差别的SQL语句。
Ø 注意软分析的数量，而不仅是每个单位的成本(它比硬分析的成本低得多)。大量的软分析增加库高速缓存闩的争用并可能会使数据库性能降低。重点是避免不必要的软分析，大量软分析的开销也是很高的。
Ø 使用SESSION_CACHED_CURSORS初始化参数重用会话中打开的游标。如果某条SQL语句使用重复的分析调用，则Oracle将该语句的会话游标移到会话游标高速缓存中。这样减少了软分析的数量。应该将此参数的值设置为介于OPEN_CURSORS初始化参数的值和会话中使用的游标的数目之间的值。
Ø 使用CURSOR_SPACE_FOR_TIME初始化参数(设置它为TRUE)来防止过早重分配游标。如果你不在乎使用更多的内存，这个特性将能提高应用的伸缩性水平。
Ø 减少用户进行的会话登录/退出活动的数量。由于需要认证用户、验证权限等，导致时间和资源的浪费，増加了开销，还可能会降低伸缩性。此外，用户可能要花更多时间登录系统，而不是执行SQL语句。频繁登录和退出也可能会导致Web服务器和其他资源的争用，増加登录到系统所花的时间。
Ø 为了增强可伸缩性，还必须保证应用共享会话。如果只有共享的SQL,硬分析次数会下降，但软分析次数可能仍然较高。如果某个应用程序能保持对Oracle服务器的持久连接，它不需要为了重用代码而重复执行软分析。
4.设置共享池的尺寸
在Oracle Datebase 11g中，设置共享池尺寸的最好办法是使用SGA_TARGET初始化参数，让Oracle做所有工作，自动管理SGA.可以将SGA_TARGET参数初步设置为接近于公手动管理方式分配的总SGA的某个值。
5.将対象钉在共享池中
如前所述，如果对象代码被必须重复硬分析和执行，数据库的性能最终会下降。我们的目的是让尽可能多的执行过的代码保留在内存中，以便可以再次执行编译过的代码。使用DBMS_SHARED_POOL程序包钉住对象，可避免在库高速缓存中重复装载对象(如前所述，库高速缓存为共享池的组件)。
确定应该钉在共享池中的对象
select type,
count(*) objects,
sum(decooe(kept, 'YES', 1, 0)) kept,
sum(loads) - count(*) reloads
from
v$db_object_cache
group by
type
order by
objects desc;
如果代码清单20.4显示的输出中reloads的数目较高，则需要使用以下命令来确定对象被钉住:
execute
sys.dbms_shared_pool.keep(object_name,object_type);
可首先使用下面的语句在共享池中钉住一个程序包，然后如果有必要，再删除它：
execute sys.dbms_shared_pool.keep(new_emp.pkg,
package);
execute
sys.dbms_shared_pool.unkeep(new_emp.pkg,package);
当然，如果关闭和重启数据库，则共享池不保留钉住的对象。这就是大多数DBA对想钉在共享池中的对象使用脚本，使其在每次数据库启动后立即运行的原因。一般多数对象都较小，所以关于钉住多少对象没必要过于保守。例如，我钉住自己的所有程序包，包括Oracle提供的PL/SQL程序包。
请看一个例子，它给你提供关于大量程序包所占内存量总量的一个直观的概念。下面的査询显示我的数据库中程序包的数最：
SELECT COUNT(*) FROM V$DB_OBJECT_CACHE WHERE
type='PACKAGE';
下面的査询显示在共享池中钉住我的所有程序包所需的内存量:
SELECT SUM(sharable_mem) FROM V$DB_OBJECT_CACHE
WHERE type='PACKAGE';
