PLSQL_性能优化系列07_Oracle Parse Bind Variables解析绑定变量

2014-09-25 Created By BaoXinjian

一、绑定变量用法和使用场合

使用绑定变量的重要性：如果不使用绑定变量而使用常量，会导致大量硬解析。由于硬解析的种种危害，不使用绑定变量往往是影响oracle性能和扩展性的最大问题

以下为一些错误写法和正确写法的例子

1. PLSQL中普通查询

(1). 错误写法

SELECT * FROM emp WHERE empno=123;

(2). 正确写法（未使用绑定变量）

Empno:=123;
SEELCT* FROM emp WHERE empno=:empno;

2. PLSQL中在使用动态SQL

(1). 错误的写法

sqlstr:= 'select * from emp where empno='||empno;Execute immediate for sqlstr;

EXECUTE IMMEDIATE FOR sqlstr;

(2). 正确的写法

sqlstr:= 'select * from  empno='||empno; 

EXECUTE IMMEDIATE FOR sqlstr; 

因为前者使用字符串拼接较容易，很多人会这么用。

二、如何判断和定位系统中未使用绑定变量的语句

在awr的load profile部分，有个Hard parses指标，表示每秒的hard parse。

另外在Instance Efficiency Percentages部分，Soft Parse %这个指标反映的是硬解析占所有解析的比例。

这两个指标一个是绝对值，一个是相对值。每秒hard parse指标应该比较低，而soft parse%应该较高（有人说应大于95%）。

具体合理指标和系统大小、业务量、业务类型都有关，可以参考的是siebel系统中这两个值是11和98%。如果这两个指标超出合理范围，则说明硬解析太多，应引起重视，分析产生的原因。

例子： 未使用绑定变量是导致硬解析的最常见原因，那么如何找出这些SQL

Step1. 可以用以下语句找到哪些SQL：

  SELECT substr(sql_text,1,50) "SQL",  count(*), sum(executions) "TotExecs" 
    FROM v$sqlarea 
   WHERE executions < 5 
GROUP BY substr(sql_text,1,50) 
  HAVING count(*)> 30 
ORDER BY 2; 

Step2. 用以下语句找到运行这些sql的用户和模块

SELECT service, module, parsing_schema_name, sql_text 
  FROM v$sql where sql_text LIKE 'select rownum as ….id%’;

三、减少解析，包括硬解析和软解析

1. 问题由来

Sql优化(六) 中我们介绍了soft parse/hard parse的概念，以及通过使用绑定变量减少hard parse的技术。

在生产环境中，我们发现soft parse太多也会引起性能问题，例如较高的library cachelatch contention等待，尽管soft parse相比hard parse，性能开销已经小很多。

最高境界是no parse；减少parse的诀窍是oracle的cursor。

2. Sql的执行过程和parse分类，oracle运行sql时，过程如下：

(1). Sql cursor是否open?如果是则跳到5） 这种情况即为no parse，为方便比较，我们也作为parse的一种类型

(2). cursor是否在session cache中(pga)，如果存在，则跳到5）这种情况oracle专家tom称其softer soft parse

(3). 进行syntax check和 semantic check，然后在shared pool的hash表中寻找，如果匹配到则跳到step 5)，这称为soft parse

(4). 如果匹配失败，则需要security-check,optimize，生成query plan等等，这称为hard parse(硬解析)，可以想像成源程序先编译后运行。

(5). execute

3. 各类parse的开销

我们分别比较上面几种parse类型的开销

可见hard parse开销最大，soft parse其次。Parse引起的latch contention不仅影响程序运行速度，而且影响程序的扩展性(scalable)

4.  如何减少hard parse

(1). 使用绑定变量

这是编程方法中最影响性能的因素之一，具体做法由其他文章介绍

(2). 编程规范，良好的编程习惯

编程规范可以规定表名、关键字是否用大写，空格怎么用等等，所有程序员遵循统一的规范。

举个例子说明其意义，在数据库中cursor_sharing缺省值为exact，这意味着oracle对sql进行匹配时，以下两句是不匹配的，第二句会引起hard parse

select count(*) from test_table where tracking_id=1234567688;

select count(*) from TEST_TABLE where tracking_id=1234567688;

当然第一点远比第二点重要，大家可以想象。

5. 如何减少soft parse

即no parse和softer soft parse，诀窍是oracle 的cursor，具体来说有两种方法

Step1. Skip parse

在子程序中，跳过parse,采用以下写法：

if (firsttime)

parse

end if

bind

execute

而不要这么写，因为每次调用都进行了parse

parse

bind

execute

close

例如在java中，通过prepareStatement，每个session对该sql prepare一次，而不是每次调用都prepare一次。

2. PLSQL自动cache cursor

在PLSQL中，所有static sql都是被cache的，重复调用时不会进行soft parse。注意动态sql除外。

declare
   i number;
   j number;
   k number;
begin
   i:=1;
   k:=12345678;
   while i<=10000 loop
     select count(*) into j from test_table where tracking_id=k;
     i:=i+1;
   end loop;
end;
/

3. SESSION_CACHED_CURSORS参数

如果该参数非0，则在sqlplus中，当同一sql进行了三次soft parse，oracle会将cursor 移到cache中，第4次调用时则不需soft parse，但仍会注册为parse,

parse count (total)仍会增加,同时session cursor cache hits也会增加。

该参数影响以下工具:

• 1)Sqlplus
• 2)Plsql中的native dynamic sql
• 3)Java中不好的写法，如不进行prepare而直接execute 的sql
• 4)Oracle产生的recursive sql

各个版本的区别：

Oracle9i中session_cached_cursors默认为0,oracle 10g中似乎为20，ora11g默认为50，因此在oracle9i中，如果要使用此特性，需要修改默认值。

另外一点要注意的是，soft parse表示一个session进行了hard pasre之后，只要仍在shared pool中，所以其他session都不需再hard parse。

而session_cached_cursor，是针对同一session而言的。因此如果一个程序频繁logon/logoff，是无法用到这一特性的。

Thanks and Regards