Oracle SQL的硬解析、软解析、软软解析

转载

小叔叔书 2021-08-09 22:49:11 博主文章分类：Oracle

文章标签 sql sql语句缓存执行计划 oracle 文章分类 Oracle 数据库

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