sql语句解析过程软解析、硬解析

解析 parse-->执行 execute -->取数据 fetch

解析：获取sql执行计划
硬解析 hard parse 当前shared pool中没有该sql执行计划，library cache miss
软解析 soft parse 有现成的执行计划，直接重用执行计划, library cache hit

解析过程：
1语法检查 syntax check
2语义检查 semantic check
数据字典data dictionary --> 共享池中数据字典缓冲区data dictionary cache
3共享池检查 shared pool check
库缓存library cache 检查是否存在相同sql执行计划 (hash value)
4结合统计信息statistics 制定执行计划execution plan
5缓存执行计划
SGA shared pool library cache shared sql area

1 分析表查看统计信息
分析表，查看表行数、块数、平均行长、空块数等信息。
SCOTT@PROD>analyze table emp compute statistics;
SCOTT@PROD>select num_rows,blocks,avg_row_len,empty_blocks
from user_tables
where table_name='EMP';

2 软解析、硬解析

【1】session 1， sys用户 startup 启动的数据库实例。

【2】新开会话session 2，scott用户登录：
[oracle@oracle ~]$ sqlplus scott/tiger
SCOTT@PROD>select * from emp where empno = 7788;

【3】session1 sys用户查询 v$sql视图
SYS@PROD>desc v$sql;
SYS@PROD>col sql_text for a40
SYS@PROD>select hash_value,sql_text,parse_calls,loads, executions,fetches
from v$sql
where sql_text like '%select * from emp%' and sql_text not like '%v$sql%';

PRASE_CALLS 解析
LOADS 硬解析
EXECUTIONS 执行
FETCHES 获取数据

【4】新开会话session3 scott用户再次执行相同sql，session1 查看解析情况

[oracle@oracle ~]$ sqlplus scott/tiger
SCOTT@PROD>select * from emp where empno = 7788;

SYS@PROD>r

同一用户，不同会话,执行完全相同sql, hash value相同。
第一次硬解析，生成执行计划，并缓存在共享池中 library cache；
第二次软解析，重用shared pool缓存的执行计划。

【5】sql语句中=后面多加一个空格，session1 sys用户再次查询解析情况
SCOTT@PROD>select * from emp where empno = 7788;

SYS@PROD>r

当前sql文本比之前语句中多了一个空格，对应hash_value不同，当作不同sql，进行硬解析。

【6】查询其他员工信息如7566，session 1 sys用户再次查询解析情况
SCOTT@PROD>select * from emp where empno = 7566;

SYS@PROD>r

【7】使用绑定变量的方法执行查询

SCOTT@PROD>var b_empno number;
SCOTT@PROD>exec :b_empno := 7788;
SCOTT@PROD>select * from emp where empno = :b_empno;

SCOTT@PROD>exec :b_empno := 7566;
SCOTT@PROD>select * from emp where empno = :b_empno;

SCOTT@PROD>exec :b_empno := 7369;
SCOTT@PROD>select * from emp where empno = :b_empno;

SYS@PROD>r

使用绑定变量，重用执行计划（软解析），减少硬解析。
解析时带有绑定变量，执行时给绑定变量赋值。

3 查看软解析比率
软解析比率可以查看AWR报告，也可以使用sql进行查询

SYS@PROD>col name for a30
SYS@PROD>select * from v$sysstat where name like '%parse%';

SYS@PROD>with t as (select * from v$sysstat where name = 'parse count (total)'),
h as (select * from v$sysstat where name = 'parse count (hard)')
select (t.value-h.value)/t.value from t,h;

吕星昊
2016.2.23

ITPub博客

【体系结构】sql语句解析过程小实验软解析、硬解析

最新文章

ITPub博客

【体系结构】sql语句解析过程小实验 软解析、硬解析

最新文章

【体系结构】sql语句解析过程小实验软解析、硬解析