Oracle中每条sql在执行前都要解析,解析分为硬解析、软解析、软软解析。

Oracle会缓存DML语句,相同的DML语句会进行软解析。但不会缓存DDL语句,所以DDL每次都做硬解析。硬解析是一个很耗时的操作,所以应用程序内部很少执行执行DDL。DDL一般在部署前执行。

 

sql语句执行步骤:

1.语法检查(syntax check)
2.语义检查(symantic check): 对象是否存在,是否有权限。
3.sql解析(parse): 利用内部算法对sql进行解析,生成解析树及执行计划。
4.执行sql,返回结果(execute and return)

 

首先了解一下sql解析时用到的内存结构——shared pool:
shared pool是一块内存池,里边又被分成了很多小的区块,每个块有他们的作用
1.free (空闲)
2.library cache (库缓存,缓存sql语句以及sql所对应的执行计划)
3.row cache (字典缓存——库里有多少表,多少用户,多少个列,列的名字,列的数据类型,每个表多大等等都属于数据库自身信息。)

 

一个sql 语句,进入到数据库后,server process 会拿着sql语句到shared pool中的library cache 里边去找,看sql语句以前是否有执行过。也就是在library cache 里面看有没有这条sql语句以及sql语句所对应的执行计划。(此过程是通过对传递进来的SQL语句使用HASH函数运算得出HASH值,与共享池中现有语句的HASH值进行比较看是否一一对应。现有数据库中SQL语句的HASH值我们可以通过访问vsql、vsql、vsqlarea、v$sqltext等数据字典中的HASH_VALUE列查询得出。)


Parse主要分为三种:

1.Hard Parse (硬解析)
2.Soft Parse (软解析)
3.Soft Soft Parse

 

Hard Parse:对提交的Sql完全重新从头进行解析(当在shared Pool中找不到时候将会进行此操作),总共有一下5个执行步骤:
1.语法分析
2.权限与对象检查
3.在共享池中检查是否有完全相同的之前完全解析好的. 如果存在,直接跳过4和5,运行Sql, 此时算soft parse.
4.选择执行计划
5.产生执行计划

 

注:创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作,所以,应当极力避免硬解析,尽量使用软解析。这就是在很多项目中,倡导开发设计人员对功能相同的代码要努力保持代码的一致性,以及要在程序中多使用绑定变量的原因。


Soft Parse: 在Shared Pool中找到了与之完全相同的Sql解析好的结果后会跳过Hard Parse中的后面的两个步骤。

 

Soft Soft Parse:当设置了session_cursor_cache这个参数之后,Cursor被直接Cache在当前Session的PGA中的,在解析的时候只需要对其语法分析、权限对象分析之后就可以转到PGA中查找了,如果发现完全相同的Cursor,就可以直接去取结果了,也就就是实现了 Soft Soft Parse.

 

如果SQL语句的HASH值一致,那么ORACLE事实上还需要对SQL语句的语义进行再次检测,以决定是否一致。那么为什么Oracle需要再次对语句文本进行检测呢?不是SQL语句的HASH值已经对应上了?事实上就算是SQL语句的HASH值已经对应上了,并不能说明这两条SQL语句就已经可以共享了。
 
例如:假如用户SYS有自己的一张表EMP,他要执行查询语句:select * from emp; 用户SYSTEM也有一张EMP表,同样要查询select * from emp;这样他们两条语句在文本上是一模一样的,他们的HASH值也会一样,但是由于涉及到查询的相关表不一样,他们事实上是无法共享的. 

 

下面我们来看实验:

数据库版本:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

 

create table test as select * from user_objects where 1<>1;

begin
  dbms_stats.gather_table_stats('TOUGH','TEST');
end;  

alter system flush shared_pool;

 

  • 硬解析

select * from test where object_id=20;
select * from test where object_id=30;

 

  • 软解析

select * from test where object_id=40;
select * from test where object_id=40;

select * from test where object_id=50;
select * from test where object_id=50;
select * from test where object_id=50;
select * from test where object_id=50;

 

  • 软软解析

begin
  for i in 1 .. 4 loop
    execute immediate 'select * from test where object_id=:i'
      using i;
  end loop;
end;

 

查看解析情况:

select sql_text, s.parse_calls, loads, executions
from   v$sql s
where  sql_text like 'select * from test where object_id%'
order  by 1, 2, 3, 4;

 

SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
select * from test where object_id=20  1 1 1
select * from test where object_id=30  1 1 1
select * from test where object_id=40  2 1 2
select * from test where object_id=50  4 1 4
select * from test where object_id=:i 1 1 4

 

object_id=20 -> 因为没有缓存此条sql,所以硬解析

object_id=30 -> 因为没有缓存此条sql,所以硬解析

object_id=40 -> 因为第一次执行已经缓存此条sql,所以软解析次数为2,硬解析次数为1

object_id=50 -> 因为第一次执行已经缓存此条sql,所以软解析次数为4,硬解析次数为1

object_id=:i -> 用了动态绑定变量,尽管执行了4次,但只做了一次硬解析和一次软解析

 

字段解释:

PARSE_CALLS - 解析的次数
LOADS - 硬解析的次数
EXECUTIONS - 执行的次数