Oracle 性能分析与诊断|优化Oracle 软解析率

通常，Oracle 对SQL 语句的处理过程是这样的：当发送一条SQL 语句到Oracle 时，在执行和获取数据之前，Oracle 会对此SQL 语句进行几个步骤的处理。

(1)语法检查(syntax check)：检查此SQL 的拼写是否语法。

(2)语义检查(semantic check)：如检查SQL 语句中的访问对象是否存在，以及该用户是否具备相应的权限。

(3)对SQL 语句进行解析(prase)：利用内部算法对SQL 语句进行解析，生成解析树(parse tree)及执行计划(execution plan)。

(4)执行SQL，返回结果(execute and return)。

其中，软、硬解析就发生在第三步中。Oracle 利用内部的HASH 算法来取得该SQL 语句的HASH 值，然后在Library Cache 里查找是否存在该HASH 值，如果存在，则将此SQL 语句与cache 中的SQL 语句进行比较;如果两个SQL 语句“相同”，就会利用已有的解析树与执行计划，而省略了优化器的相关工作。这就是软解析的过程。

如果上面的2 个条件中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。

创建解析树、生成执行计划对于SQL 的执行来说是开销比较大的工作，因为它需要Oracle在Shared Pool 中重新分配内存，然后再确定执行计划，最终SQL 语句才会被执行;所以，应当尽量避免硬解析，尽量使用软解析。在项目开发中，倡导开发人员对功能相同的SQL 代码要努力保持代码的一致性，在程序中多使用绑定变量，以避免硬解析的发生。

可通过以下SQL 语句查询Oracle 的软解析率，软解析率 = 1-( parse count (hard) / parse count

(total) )：

select 1 - (a.value/b.value)

from v$sysstat a,v$sysstat b

where a.name='parse count (hard)'

and b.name='parse count (total)';

如果软解析率小于0.2，则表示硬解析率太高，不过，如果总解析量(parse count total)偏低，这项值可以被忽略。

软解析率偏低通常是由于存在较多不能被共享利用(重用)的SQL 语句导致的，因此，可以通过查询v$sqlarea 视图来找出Library Cache 中执行次数偏低的SQL 语句，从而分析这些SQL语句为什么不能被重用，查询v$sqlarea 视图的SQL 语句如下所示：

SELECT sql_text

FROM v$sqlarea

WHERE executions < 5

ORDER BY UPPER(sql_text);

另外，也可以查找SQL 执行次数和SQL 解析次数(hard parse)，然后对比两个值的差，查询v$sqlarea 的语句可以这样写：

SELECT sql_text , parse_calls , executions

FROM v$sqlarea

ORDER BY parse_calls;

还可以通过查询v$librarycache 视图的Reloads 值(reparsing 的值)来进行诊断，该值应该接近0，invalidations 的值也应该接近0，否则应该考虑调整shared pool size，通过调整Shared Pool来调整Library Cache。查询v$librarycache 视图的SQL 语句如下所示：

select namespace,gethitratio,pinhitratio,reloads,invalidations

from v$librarycache;

检查v$librarycache 中sql area 的gethitratio 是否超过90%，如果未超过90%，则应该检查应用代码：

Select gethitratio

from v$librarycache

where namespace='SQL AREA';

v$librarycache 中reloads/pins 的比率应该小于1%，如果大于1%，应该考虑增加参数

shared_pool_size 的值:

select sum(pins) "executions", sum(reloads) "cache

misses",sum(reloads)/sum(pins)

from v$librarycache;

reloads/pins>1%有两种可能，一种是library cache 空间不足，另一种是SQL 语句中引用的对象不合法。如果知道具体某个连接的Session，则可以直接查看某个Session 的hard parse 个数：

select a.sid,a.value

from v$sesstat a,v$session b ,v$statname c

where a.sid=b.sid

and a.statistic#=c.statistic#

and a.sid = 137

and c.name='parse count (hard)';

如果确认是由于Library Cache 空间不足导致的软解析率低，则可以通过调整Library Cache来进行优化，需要通过调整Shared Pool 来调整Library Cache：

SELECT shared_pool_size_for_estimate AS pool_size,estd_lc_size,estd_lc_time_

saved

FROM v$shared_pool_advice;

根据shared_pool_advice 的提示来进行调整，其中ESTD_LC_SIZE 是估计Library Cache 的大小值，ESTD_LC_TIME_SAVED 是在当前指定共享池的大小中找到库缓存对象所节省的时间(秒)。

如果不是 Library Cache 空间的问题，则需要考虑对SQL 程序进行调优，下面列举两个调优的例子。

1.书写程序时尽量使用变量，不要过多的使用常量

如果编写 SQL 代码时没有使用绑定变量，则可能造成硬解析率偏高，从而影响SQL 语句的执行效率，读者可做以下实验来进行验证。

(1)创建表格：

SQL>CREATE TABLE m(x int);

(2)创建存储过程proc1，使用绑定变量：

SQL>CREATE OR REPLACE PROCEDURE proc1

AS

BEGIN

FOR i IN 1..10000

LOOP

Execute immediate

'INSERT INTO m VALUES(:x)' USING i;

END LOOP;

END;

/

(3)创建存储过程proc2，不使用绑定变量：

SQL>CREATE OR REPLACE PROCEDURE proc2

AS

BEGIN

FOR i IN 1..10000

LOOP

Execute immediate

'INSERT INTO m VALUES('||i||')' ;

END LOOP;

END;

/

(4)执行proc2 和proc1，对比执行效率。

打开计时开关：

SQL>SET TIMING ON

执行proc2：

SQL> exec proc2;

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.93

清空数据，再执行proc1：

SQL> TRUNCATE TABLE m;

Table truncated.

Elapsed: 00:00:01.76

SQL> exec proc1;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.85

可看到使用绑定变量的proc1 执行效率要比不使用变量的proc2 效率高。

2.修改cursor_sharing 参数为similar，让类似的SQL 语句不做hard parse

有时候我们的应用程序没有使用绑定变量，而修改程序可能有点困难，我们可能需要设置cursor_sharing=similar 来强制ORACLE 使用绑定变量。可用以下语句查看当前Oracle 采用什么样的方式处理共享游标：

Show parameter cursor

设置cursor_sharing=similar 来强制ORACLE 使用绑定变量：

Alter system set cursor_sharing=similar