PLSQL_性能优化系列06_Oracle Soft Parse / Hard Parse软硬解析

2014-08-11 Createed By
BaoXinjian

一、摘要


Oracle硬解析和软解析是咱们常遇上的题材,所以需要考虑何时起软解析何时起硬解析,如何判断

 

  1. SQL的实施进程

当发布一久SQL或PL/SQL命令时,Oracle会自动检索该令是否存在让联合享池中来支配对眼前之言辞以硬解析或软解析。

常见情况下,SQL语句之实施进程如下:

Step1.
SQL代码的语法(语法的正确性)及语义检查(对象的存在性与权力)。

Step2.
将SQL代码的文件进行哈希得到哈希值。

Step3.
如果同享池中设有一样之哈希值,则针对这令进一步认清是否开展软解析,否则到e步骤。

Step4.
对于在同样哈希值的新命令行,其文件以与已经存在的命令行的文本逐个开展较。

   
这些比包括大小写,字符串是否一致,空格,注释等,如果同,则指向其开展软解析,转至步骤Step6,无需重复硬解析。

    否则交步骤Step5。

Step5. 硬解析,生成执行计划。

Step6. 执行SQL代码,返回结果。

 

2.
Oracle对准斯sql将展开几独步骤的处理过程:

Step1. 语法检查(syntax check)

  检查是sql的拼写是否语法。

Step2. 语义检查(semantic check)

  诸如检查sql语句被的看对象是不是有与拖欠用户是否具有相应的权杖。

Step3、对sql语句进行剖析(parse)

  用内部算法对sql进行辨析,生成解析树(parse
tree)及实施计划(execution plan)。

Step4、执行sql,返回结果(execute and
return)

 

  1. 硬解析的祸:

(1)
占用资源更多,执行款,因为不会见用已解析好的query plan。

(2) 硬解析导致library
cache上之latch竞争,这会降系统的并发性,使oracle无法充分利用系统资源。(此时尽管系统资源看上去不忙,oracle也会好缓慢)。

(3)
一个来好多硬解析的大概利用或引致数据库有应用变慢。

 

  1. 总结

     
其中,软、硬解析就产生在第三只过程里(对sql语句进行解析parse)。

  Oracle用中的hash算法来得到该sql的hash值,然后于library
cache里查找是否是该hash值;

  假设是,则用是sql与cache中的拓较;

  假设“相同”,就拿运用就有的解析树与实践计划,而简约了优化器的系工作。这为即是软解析的进程。

  诚然,如果地方的2独如中管有一个非成立,那么优化器都将拓展创办解析树、生成执行计划的动作。这个历程就是为硬解析。

  创建解析树、生成执行计划对sql的行来说是支付昂贵之动作,所以,应当着力避免硬解析,尽量以软解析。

 

二、软解析


Oracle中SQL语句执行过程遭到,Oracle内部分析原理如下:

1、当一用户率先不行提交一个SQL表达式时,Oracle会将及时SQL进行Hard
parse,这过程有点像程序编译,检查语法、表名、字段名等相关消息(如下图),这过程会花比较丰富的岁月,因为她一旦分析报告句子的语法与语义。然后抱无限优化后的施行计划(sql
plan),并于内存中分红一定的空间保存该语句与相应的尽计划等信息。

 

2、当用户第二不善呼吁或数呼吁时,Oracle会自动找到先前的说话与实践计划,而非见面进行Hard
parse,而是直接开展Soft
parse(把话对应的尽计划调出,然后实施),从而减少数据库的辨析时。

注意的是:Oracle中只能完全相同的讲话,包大小写、空格、换行都要求一律不时,才见面重复使用以前的辨析结果和实施计划。

 

3、对于大气底、频繁造访的SQL语句,如果未采取Bind
变量的法门,哪Oracle会花费大量的Shared latch与CPU在开Hard
parse处理,所以,要硬着头皮提高语句的重用率,减少语句子之辨析时,通过了解Oracle
SQL语句的解析过程可以明白Oracle的中处理逻辑,并于设计与落实上避免。

于用JDBC或外持久化数据(如Hibernate,JDO等)操作时,尽量用占位符(?)

 

4、解析过程图

 

5、总结

ORACLE sql 的处理过程大致如下:

  • 1.以HASH算法,得到一个HASH值,这个价值好通过V$SQLAREA.HASH_VALUE
    查看
  • 2.交shared pool 中之 library cache
    中找是否有一样的HASH值,如果在,则无论需硬解析,进行软解析
  • 3.使shared
    pool不存此HASH值,则进行语法检查,查看是否生语法错误
  • 4.假如没语法错误,就进行语义检查,检查该SQL引用的对象是不是留存,该用户是否持有访问该对象的权杖
  • 5.如果没有语义错误,对该SQL进行解析,生成解析树,执行计划
  • 6.生成ORACLE能运作的亚前行制代码,运行该代码并且返回结果被用户

硬解析和软解析都于第5步进行

硬解析通常是昂贵的操作,大约占用尽SQL执行之70%横之岁月,硬解析会生成执行树,执行计划,等等。

当再度实施同样长条SQL语句之时光,由于发现library
cache中产生雷同之HASH值,这个时段不见面硬解析,而会软解析,

那么软解析究竟是关系了哟为?其实软解析就是越了了颇成解析树,生成执行计划之耗时而耗CPU的操作,直接运用生成的履计划运行该SQL语句。

 

三、软解析


1.下面的老三个查询语句,不可知利用相同之共享SQL区。尽管查询的发明对象下了大大小小写,但Oracle为其生成了不同之履计划

select * from emp;

select * from Emp;

select * from EMP;

2.类似之情景,下面的查询中,尽管该where子句empno的值不同,Oracle同样为该老成了不同的施行计划       

select * from emp where empno=7369

select * from emp where empno=7788

3.当认清是否利用硬解析时,所参照的目标以及schema应该是一样的,如果目标同,而schema不同,则需要使用硬解析,生成不同之执行计划

sys@ASMDB> select owner,table_name from dba_tables where table_name like 'TB_OBJ%';
        OWNER                          TABLE_NAME
        ------------------------------ ------------------------------
        USR1                           TB_OBJ               --两个对象的名字相同,当所有者不同
        SCOTT                          TB_OBJ

usr1@ASMDB> select * from tb_obj;

scott@ASMDB> select * from tb_obj;      --此时两者都需要使用硬解析以及走不同的执行计划

 

四、硬解析



解析即一切SQL语句之推行得了完全都的剖析,生成执行计划。而硬解析,生成执行计划要耗用CPU资源,以及SGA资源。在这不得不提的凡针对库缓存中
闩的运。闩是沿的细化,可以理解也是一模一样种轻量级的差行化设备。当进程申请到闩后,则这些闩用于掩护共享内存的多次在平时刻不会见受简单单以上的经过修改。在
硬解析时,需要申请闩的施用,而闩的数码在简单的情形下要等。大量的闩的用由此导致需要采取闩的历程排队越频繁,性能则更为低下。

1.
脚对端的少种植状态进行现身说法

在个别独不等之session中就,一个啊sys帐户的session,一个为scott账户的session,不同的session,其SQL命令执行以不同的款家名为开始

若果” sys@ASMDB> ” 
表示以时sys帐户的session,” scott@ASMDB>
“表示scott帐户的session

sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;         
NAME                      CLASS      VALUE
-------------------- ---------- ----------           --当前的硬解析值为569
parse count (hard)           64        569

scott@ASMDB> select * from emp;    

        sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;      
        NAME                      CLASS      VALUE
        -------------------- ---------- ----------           --执行上一个查询后硬解析值为570,解析次数增加了一次
        parse count (hard)           64        570

scott@ASMDB> select * from Emp;

        sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;        
        NAME                      CLASS      VALUE
        -------------------- ---------- ----------           --执行上一个查询后硬解析值为571
        parse count (hard)           64        571

scott@ASMDB> select * from EMP;

        sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;        
        NAME                      CLASS      VALUE
        -------------------- ---------- ----------           --执行上一个查询后硬解析值为572
        parse count (hard)           64        572   

scott@ASMDB> select * from emp where empno=7369;       

        sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
        NAME                      CLASS      VALUE
        -------------------- ---------- ----------           --执行上一个查询后硬解析值为573
        parse count (hard)           64        573

scott@ASMDB> select * from emp where empno=7788;   --此处原来empno=7369,复制错误所致,现已更正为7788@20130905   

        sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
        NAME                      CLASS      VALUE
        -------------------- ---------- ----------          --执行上一个查询后硬解析值为574
        parse count (hard)           64        574

自打者的示范中可看出,尽管实施之语句存在细微的差距,但Oracle还是为该进行了硬解析,生成了不同的实行计划。即便是平的SQL语句,而简单长长的语句中空格的小不同等,Oracle同样会开展硬解析。 

 

**五、硬解析改进

  • 使用动态语句**

1.
改成参数cursor_sharing

       
参数cursor_sharing决定了何种类型的SQL能够用相同的SQL area

        CURSOR_SHARING = { SIMILAR |
EXACT | FORCE }    

            EXACT     
–只有当通告之SQL语句与缓存中的言语完全相同时才故已部分实行计划。

            FORCE     
–如果SQL语句是许面量,则迫使Optimizer始终以已部分实行计划,无论已有些实行计划是勿是最佳的。

            SIMILAR  
–如果SQL语句是许面量,则只有当都有些实行计划是超级时才使她,如果就发出实践计划未是顶尖则还对之SQL

                           
–语词进行解析来制定最佳实践计划。

       
可以根据不同的级别来设定该参数,如ALTER SESSION, ALTER SYSTEM

sys@ASMDB> show parameter cursor_shar             --查看参数cursor_sharing
            NAME                                 TYPE        VALUE
            ------------------------------------ ----------- ------------------------------
            cursor_sharing                       string      EXACT

sys@ASMDB> alter system set cursor_sharing='similar';    --将参数cursor_sharing的值更改为similar

sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;    
            NAME                      CLASS      VALUE
            -------------------- ---------- ----------        --当前硬解析的值为865
            parse count (hard)           64        865

scott@ASMDB> select * from dept where deptno=10;

sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;  
            NAME                      CLASS      VALUE
            -------------------- ---------- ----------        --执行上一条SQL查询后,硬解析的值变为866
            parse count (hard)           64        866

scott@ASMDB> select * from dept where deptno=20;

sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
            NAME                      CLASS      VALUE
            -------------------- ---------- ----------        --执行上一条SQL查询后,硬解析的值没有发生变化还是866
            parse count (hard)           64        866

sys@ASMDB> select sql_text,child_number from v$sql   -- 在下面的结果中可以看到SQL_TEXT列中使用了绑定变量:"SYS_B_0" 
         2  where sql_text like 'select * from dept where deptno%';
            SQL_TEXT                                           CHILD_NUMBE
            -------------------------------------------------- ------------  
            select * from dept where deptno=:"SYS_B_0"                    0

sys@ASMDB> alter system set cursor_sharing='exact';       --将cursor_sharing改回为exact

            --接下来在scott的session 中执行deptno=40 和的查询后再查看sql_text,当cursor_sharing改为exact后,每执行那个一次

            --也会在v$sql中增加一条语句

sys@ASMDB> select sql_text,child_number from v$sql                
         2  where sql_text like 'select * from dept where deptno%';
            SQL_TEXT                                           CHILD_NUMBER
            -------------------------------------------------- ------------
            select * from dept where deptno=50                            0      

            select * from dept where deptno=40                            0

            select * from dept where deptno=:"SYS_B_0"                    0

 

2.
利用绑定变量的点子

绑定变量要求变量名称,数据类型以及长是如出一辙,否则无法使软解析

(1). 绑定变量(bind
variable)是靠以DML语句被使用一个占据位符,即采取冒号后面紧跟变量名的花样,如下

            select * from emp where
empno=7788    –未使用绑定变量

            select * from emp where
empono=:eno   –:eno即为绑定变量

           
在次独查询中,变量值在询问执行时受提供。该查询才编译一糟,随后会管询问计划存储于一个共享池(库缓存)中,以便以后获得和录取这个查询计划。

(2).
下面用了绑定变量,但少只变量其本质是未一样的,对这种状态,同样采用硬解析

            select * from emp where
empno=:eno;

            select * from emp where
empno=:emp_no

          
使用绑定变量时要求不同之对话中以了平的应环境,以及优化器的规则等 

scott@ASMDB> create table tb_test(col int);     --创建表tb_test

scott@ASMDB> create or replace procedure proc1  --创建存储过程proc1使用绑定变量来插入新记录
          2  as
          3  begin
          4      for i in 1..10000
          5      loop
          6          execute immediate 'insert into tb_test values(:n)' using i;
          7      end loop;
          8  end;
          9  /

Procedure created.

scott@ASMDB> create or replace procedure proc2 --创建存储过程proc2,未使用绑定变量,因此每一个SQL插入语句都会硬解析
          2  as
          3  begin
          4      for i in 1..10000
          5      loop
          6          execute immediate 'insert into tb_test values('||i||')';
          7      end loop;
          8  end;
          9  /

Procedure created.

scott@ASMDB> exec runstats_pkg.rs_start

PL/SQL procedure successfully completed.

scott@ASMDB> exec proc1;

PL/SQL procedure successfully completed.

scott@ASMDB> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

scott@ASMDB> exec proc2;

PL/SQL procedure successfully completed.

scott@ASMDB> exec runstats_pkg.rs_stop(1000);
            Run1 ran in 1769 hsecs
            Run2 ran in 12243 hsecs             --run2运行的时间是run1的/1769≈倍
            run 1 ran in 14.45% of the time   

            Name                                Run1      Run2      Diff
            LATCH.SQL memory manager worka       410     2,694     2,284
            LATCH.session allocation             532     8,912     8,380
            LATCH.simulator lru latch             33     9,371     9,338
            LATCH.simulator hash latch            51     9,398     9,347
            STAT...enqueue requests               31    10,030     9,999
            STAT...enqueue releases               29    10,030    10,001
            STAT...parse count (hard)              4    10,011    10,007    --硬解析的次数,前者只有四次
            STAT...calls to get snapshot s        55    10,087    10,032
            STAT...parse count (total)            33    10,067    10,034
            STAT...consistent gets               247    10,353    10,106
            STAT...consistent gets from ca       247    10,353    10,106
            STAT...recursive calls            10,474    20,885    10,411
            STAT...db block gets from cach    10,408    30,371    19,963
            STAT...db block gets              10,408    30,371    19,963
            LATCH.enqueues                       322    21,820    21,498    --闩的队列数比较
            LATCH.enqueue hash chains            351    21,904    21,553
            STAT...session logical reads      10,655    40,724    30,069
            LATCH.library cache pin           40,348    72,410    32,062    --库缓存pin
            LATCH.kks stats                        8    40,061    40,053
            LATCH.library cache lock             318    61,294    60,976
            LATCH.cache buffers chains        51,851   118,340    66,489
            LATCH.row cache objects              351   123,512   123,161
            LATCH.library cache               40,710   234,653   193,943
            LATCH.shared pool                 20,357   243,376   223,019

            Run1 latches total versus runs -- difference and pct
            Run1      Run2      Diff     Pct
            157,159   974,086   816,927  16.13%          --proc2使用闩的数量也远远多于proc1,其比值是.13%  PL/SQL procedure successfully completed.

 

 (3). 使用绑定变量的补

  • 出于点的言传身教可知,在不运绑定变量的景象下,不论是分析次数,闩使用的数目,队列,分配的内存,库缓存,行缓存远远盖绑定
  • 变量的状。因此尽可能的采用绑定变量避免硬解析产生所需要的额外的系统资源。
  • 绑定变量的长
  • 削减SQL语句的硬解析,从而减少为硬解析产生的额外开销(CPU,Shared
    pool,latch)。其次提高编程效率,减少数据库的拜访次数。
  • 绑定变量的缺点
  •  优化器就见面忽视直方图的音信,在转移执行计划的下可能不够优化。SQL优化相对比困难

 

六、总结


 1.尽可能的免硬解析,因为硬解析需要重多的CPU资源,闩等。

 2.cursor_sharing参数应权衡利弊,需要考虑以similar与force带来的熏陶。

 3.尽可能Oracle的使用绑定变量来避免硬解析。 

 

Thanks and Regards

参考:乐沙弥大神 –
http://blog.csdn.net/leshami/article/details/6195483

参考:乐沙弥大神 –
http://10.61.208.50:15871/cgi-bin/blockpage.cgi?ws-session=18446744072512592920

参照:乐沙弥大神 –
http://czmmiao.iteye.com/category/143940

相关文章