Oracle的硬解析和软解析

提到软解析(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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29065182/viewspace-1123607/