首页 > 代码库 > PLSQL_性能优化系列07_Oracle Parse Bind Variables解析绑定变量

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

2014-09-25 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,是无法用到这一特性的。

 

********************作者:鲍新建********************

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