当前位置:数据库 > Oracle >>

Oracle对Sql语句的软解析和硬解析

  经常在论坛中,有人会在针对SQL优化方向提出:要避免SQL进行硬解析,从而提高SQL执行的效率。避免硬解析,确实是高效利用shared_pool的一种重要策略。通常情况下,作为开发人员,我们需要记住,为了最高效的利用共享池,我们编写的sql最好是可以共享的,比如绑定变量就是一个避免硬解析从而提高共享的有效手段。我们举一个相对极端的例子,在这里我们使用动态sql来模拟硬解析的场景:
 
ChenZw> drop table foo purge;

表已删除。

已用时间:  00: 00: 00.05
ChenZw> create table foo (x int);

表已创建。

已用时间:  00: 00: 00.07

ChenZw> create or replace procedure proc
  2  as
  3  begin
  4      for i in 1..100000 loop
  5      execute immediate
  6      'insert into foo values ('||i||')';
  7      end loop;
  8  end;
  9  /

过程已创建。

已用时间:  00: 00: 00.05
ChenZw> exec  proc;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 47.75
ChenZw>

 

 
可以看到上面的执行时间是47.75秒,我们可以去查看能够体现Shared_pool里面共享sql区域中的一个数据字典中的内容:
 
 
我们发现在其中,最早的那个解析是插入96581的那条数据,最末的解析是插入100000的那条解析,一共是6281条数据。
 
现在我们换一种方式重新来执行上面的语句,我们使用动态sql的绑定变量的方式来写这个sql语句,我们尝试的代码和结果如下:
 
ChenZw> drop table foo purge;

表已删除。

已用时间:  00: 00: 00.13
ChenZw> create table foo (x int);

表已创建。

已用时间:  00: 00: 00.03
ChenZw> drop procedure proc;

过程已删除。

已用时间:  00: 00: 00.04
ChenZw> create or replace procedure proc
  2  as
  3  begin
  4      for i in 1..100000 loop
  5      execute immediate
  6      'insert into foo values (:x)' using i;
  7      end loop;
  8  end;
  9  /

过程已创建。

已用时间:  00: 00: 00.06
ChenZw> alter system flush shared_pool;

系统已更改。

已用时间:  00: 00: 00.92
ChenZw> exec proc;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 04.50
ChenZw>

 

 
我们查看保存在v$sql中的内容,可以看到如下的内容:
 
 
可以很清楚的看到,插入语句被解析了一次,而调用了十万次,因此,执行效率从第一次的48秒钟,变为现在的5秒钟。
 
那两个到底有什么区别呢?这个例子跟sql语句的软解析和硬解析又有什么关系呢?
 
下面是Oracle Concepts Guide中给出的图:
 
 
先给出一个结论好了,上面第一种情况,就是sql硬解析次数太多而导致了执行效率低下,第二种情况,因为降低了sql的硬解析,从而提高了sql的运行效率。
 
第一种情况,当第一条insert into foo values (1)执行的时候,因为没有采用绑定变量的方式,因此在上述结构图的SGA中,首先对该条语句进行判断语法校验,确认权限等等各种准备工作之后,通过hash得形成一个解析后的信息,放置到SGA中。然后当insert into foo values (2)来执行的时候呢,做了同样的工作。
 
第二种情况,当第一条insert into foo values (:x)执行的时候,也是对该条语句进行语法判断等等准备工作之后,将解析之后的信息,放到了SGA当中,但是当第二条语句来到的时候,Oracle就不需要再做结息的工作了,直接将上一次执行之后的结果拿出来运行就好了。
 
所以,我们可以看到软解析和硬解析的区别了。
 
如果Oracle在sql进行解析的时候,能够从SGA中找到之前曾经解析过的信息直接执行的情况,被称作SQL的软解析。如果Oracle在sql进行解析的时候,找不到可以拿来就用的sql,必须重新解析信息的情况,就是SQL的硬解析,sql的软解析与硬解析在效率上大概有接近50-60倍性能的差距(源自某个论坛上一个Oracle
高手的试验结果,具体地址忘记了)。
 
--作者 陈字文(热衷于PM\ORACLE\JAVA等,欢迎同行交流):ziwen#163.com 扣扣:4零9零2零1零零
 
另外,通过上面的例子可以看到,存储解析结果的内存空间并不是非常大的,例如我们第一个例子,解析了10万次,但是仅仅存放了6281条解析后的数据。通过对相关语句的分析,我们也可以知道,该块内存的算法应该是最近最少使用算法。
 
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,