ITPub博客

首页 > 数据库 > Oracle > Oracle的硬解析和软解析

Oracle的硬解析和软解析

原创 Oracle 作者:muxinqing 时间:2014-03-17 21:37:44 0 删除 编辑

提到软解析(soft parse)和硬解析(hard
parse),就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle,在执行和获取结果前,Oracle对此sql将进行几个步骤的处理过程:

1、语法检查(syntax
check)
检查此sql的拼写是否语法。
2、语义检查(semantic
check)
诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
3、对sql语句进行解析(parse)
利用内部算法对sql进行解析,生成解析树(parse
tree)及执行计划(execution plan)。
4、执行sql,返回结果(execute and
return)

其中,软、硬解析就发生在第三个过程里。
Oracle利用内部的hash算法来取得该sql的hash值,然后在library
cache里查找是否存在该hash值;
假设存在,则将此sql与cache中的进行比较;
假设“相同”,就将利用已有的解析树与执行计划,而省略了优化器的相关工作。这也就是软解析的过程。
诚然,如果上面的2个假设中任有一个不成立,那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。

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


软解析、硬解析的一个小测试:


SQL> create global temporary table sess_event on commit preserve rows as select * from v$session_event where 1=0;

Table created

SQL
> insert into sess_event
2 select * from v$session_event
3 where sid=(select sid from v$mystat where rownum=1);

9 rows inserted

SQL
>
SQL
> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%'
3 ;

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 32127
parse
count (hard) 30365
parse
count (failures) 1

SQL
>
SQL
> begin
2 for i in 1..10000 loop
3 execute immediate 'insert into test values('||i||','||i||')';
4 end loop;
5 commit;
6 end;
7 /

PL
/SQL procedure successfully completed

SQL
>
SQL
> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%';

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 42201
parse
count (hard) 40379
parse
count (failures) 1

没有使用绑定变量(这种写法往往有人误认为使用了绑定,这里就不多解释了)总解析次数:10074
硬解析次数:10014
上边2个值除了我们的循环10000次外,还包括其他一些系统表的读写,所以解析次数大于10000,但是我们要注意下面的结果,对于每个SQL
都有一个版本,也就是ORACLE对于每个不同的SQL分别执行了硬解析,下面是共享池最后缓存的数据(部分已经被覆盖)


SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
insert into test values(9630,9630) 1 1 1
insert into test values(9950,9950) 1 1 1
insert into test values(9625,9625) 1 1 1
insert into test values(9592,9592) 1 1 1
insert into test values(9940,9940) 1 1 1
insert into test values(9897,9897) 1 1 1
insert into test values(9679,9679) 1 1 1
insert into test values(9850,9850) 1 1 1
insert into test values(9744,9744) 1 1 1
insert into test values(9938,9938) 1 1 1
insert into test values(9977,9977) 1 1 1
insert into test values(9907,9907) 1 1 1
insert into test values(9809,9809) 1 1 1
insert into test values(9800,9800) 1 1 1
insert into test values(9645,9645) 1 1 1
insert into test values(9724,9724) 1 1 1
insert into test values(9799,9799) 1 1 1
insert into test values(9818,9818) 1 1 1
insert into test values(9642,9642) 1 1 1
insert into test values(9624,9624) 1 1 1

中间内容略

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
insert into test values(9838,9838) 1 1 1
insert into test values(9716,9716) 1 1 1
insert into test values(9691,9691) 1 1 1
insert into test values(9831,9831) 1 1 1
insert into test values(9992,9992) 1 1 1
insert into test values(9883,9883) 1 1 1
insert into test values(9865,9865) 1 1 1
insert into test values(9901,9901) 1 1 1
insert into test values(9657,9657) 1 1 1
insert into test values(10000,10000) 1 1 1
insert into test values(9659,9659) 1 1 1
insert into test values(9746,9746) 1 1 1
insert into test values(9695,9695) 1 1 1
insert into test values(9869,9869) 1 1 1
insert into test values(9804,9804) 1 1 1
insert into test values(9843,9843) 1 1 1

435 rows selected

SQL
> truncate talbe sess_event;

truncate talbe sess_event

ORA
-03290: 无效的截断命令 - 缺失 CLUSTER 或 TABLE 关键字

SQL
> truncate table sess_event;

Table truncated

SQL
> alter system flush shared_pool;

System altered

SQL
>
SQL
> insert into sess_event
2 select * from v$session_event
3 where sid=(select sid from v$mystat where rownum=1);

10 rows inserted

SQL
>
SQL
> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%';

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 42643
parse
count (hard) 40456
parse
count (failures) 2

SQL
>
SQL
> begin
2 for i in 1..10000 loop
3 execute immediate 'insert into test values(:v1,:v2)' using i,i;
4 end loop;
5 commit;
6 end;
7 /

PL
/SQL procedure successfully completed

SQL
>
SQL
> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%';

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 42688
parse
count (hard) 40466
parse
count (failures) 2

下面看下使用绑定变量的情况(真正使用了绑定)总解析次数:45
硬解析次数:10
我们可以看出差异是多么大了,呵呵,对于SQL本身只有一次软解析,执行次数为10000


SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
begin for i in 1..10000 loop execute immediate 'insert into test values(:v1,:v2 1 1 1
update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 1 1
insert into test values(:v1,:v2) 1 1 10000
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1

SQL> alter system flush shared_pool;

System altered

但是我们要注意一个情况,即时同样的SQL如果没有使用绑定变量,ORACLE也会对其执行一次软解析的,但是没有硬解析,如下:
每执行一次SQL,也会同时产生其他写系统表等很多相关的软解析包括查询SQL本身。硬解析不变。


SQL> insert into test values('1','1111111111');

1 row inserted

SQL
> commit;

Commit complete

SQL
> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 1 1
insert into test values('1','1111111111') 1 1 1
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1

SQL
> insert into test values('1','1111111111');

1 row inserted

SQL
> commit;

Commit complete

SQL
> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 1 1
insert into test values('1','1111111111') 1 2 2
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 2 2

SQL
>

SQL
> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%';

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 42906
parse
count (hard) 40520
parse
count (failures) 2

SQL
> insert into test values('1','1111111111');

1 row inserted

SQL
> commit;

Commit complete

SQL
> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
begin dbsnmp.mgmt_response.get_latest_curs(:a); end; 1 1 1
BEGIN IF (:1 = 'READ WRITE') THEN ---- -- For a read- 1 1 1
DECLARE instance_number NUMBER; latest_task_id NUMBER; 1 1 1
update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 4 4
insert into test values('1','1111111111') 1 3 3
DELETE FROM MGMT_DB_LATEST_HDM_FINDINGS WHERE TARGET_GUID = :B2 AND COLLECTION_T 2 3 3
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 3 3

8 rows selected

SQL
>
SQL
> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%';

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 42922
parse
count (hard) 40520
parse
count (failures) 2

SQL
> insert into test values('1','1111111111');

1 row inserted

SQL
> commit;

Commit complete

SQL
> insert into test values('1','1111111111');

1 row inserted

SQL
> commit;

Commit complete

SQL
> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
begin dbsnmp.mgmt_response.get_latest_curs(:a); end; 1 1 1
BEGIN IF (:1 = 'READ WRITE') THEN ---- -- For a read- 1 1 1
DECLARE instance_number NUMBER; latest_task_id NUMBER; 1 1 1
update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 5 5
insert into test values('1','1111111111') 1 5 5
DELETE FROM MGMT_DB_LATEST_HDM_FINDINGS WHERE TARGET_GUID = :B2 AND COLLECTION_T 2 3 3
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 4 4

8 rows selected

SQL
>
SQL
> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%';

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 42946
parse
count (hard) 40520
parse
count (failures) 2

SQL
>

通过这个例子我们也就知道V$SQLAREA中的解析包含什么内容了

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29065182/viewspace-1123607/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Ethtool命令详解
下一篇: RAR安装与使用
请登录后发表评论 登录
全部评论

注册时间:2014-01-14

  • 博文量
    143
  • 访问量
    1202405