Oracle 硬解析与软解析

benxiong 发表于 2011-12-22 08:54

<DIV>
--=======================
-- Oracle 硬解析与软解析
--=======================
 
    Oracle 硬解析与软解析是我们经常遇到的问题，什么情况会产生硬解析，什么情况产生软解析，又当如何避免硬解析？下面的描述将给出
软硬解析的产生，以及硬解析的弊端和如何避免硬解析的产生。
    
一、SQL语句的执行过程
    当发布一条SQL或PL/SQL命令时，Oracle会自动寻找该命令是否存在于共享池中来决定对当前的语句使用硬解析或软解析。
    通常情况下，SQL语句的执行过程如下：
    a.SQL代码的语法(语法的正确性)及语义检查(对象的存在性与权限)。
    b.将SQL代码的文本进行哈希得到哈希值。
    c.如果共享池中存在相同的哈希值，则对这个命令进一步判断是否进行软解析，否则到e步骤。
    d.对于存在相同哈希值的新命令行，其文本将与已存在的命令行的文本逐个进行比较。这些比较包括大小写，字符串是否一致，空格，注释
        等，如果一致，则对其进行软解析，转到步骤f。否则到d步骤。
    e.硬解析，生成执行计划。
    f.执行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不同，则需要使用硬解析,生成不同的执行计划
<DIV style="BORDER-RIGHT: windowtext 1pt dotted; PADDING-RIGHT: 4pt; BORDER-TOP: windowtext 1pt dotted; PADDING-LEFT: 4pt; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 1pt; BORDER-LEFT: windowtext 1pt dotted; PADDING-TOP: 1pt; BORDER-BOTTOM: windowtext 1pt dotted; mso-element: para-border-div; mso-border-alt: dotted windowtext .5pt">
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        sys@ASMDB> select owner,table_name from dba_tables where table_name like 'TB_OBJ%';
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        OWNER                          TABLE_NAME
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        ------------------------------ ------------------------------
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        USR1                           TB_OBJ               --两个对象的名字相同，当所有者不同
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        SCOTT                          TB_OBJ
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        usr1@ASMDB> select * from tb_obj;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        scott@ASMDB> select * from tb_obj;      --此时两者都需要使用硬解析以及走不同的执行计划</DIV>
 
三、硬解析的弊端
        硬解析即整个SQL语句的执行需要完完全全的解析，生成执行计划。而硬解析，生成执行计划需要耗用CPU资源，以及SGA资源。在此不
    得不提的是对库缓存中闩的使用。闩是锁的细化，可以理解为是一种轻量级的串行化设备。当进程申请到闩后，则这些闩用于保护共享内存
    的数在同一时刻不会被两个以上的进程修改。在硬解析时，需要申请闩的使用，而闩的数量在有限的情况下需要等待。大量的闩的使用由此
    造成需要使用闩的进程排队越频繁，性能则逾低下。
        
四、硬解析的演示        
    下面对上面的两种情形进行演示
    在两个不同的session中完成，一个为sys帐户的session，一个为scott账户的session，不同的session，其SQL命令行以不同的帐户名开头
    如" sys@ASMDB> "  表示使用时sys帐户的session，" scott@ASMDB> "表示scott帐户的session
 
<DIV style="BORDER-RIGHT: windowtext 1pt dotted; PADDING-RIGHT: 4pt; BORDER-TOP: windowtext 1pt dotted; PADDING-LEFT: 4pt; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 1pt; BORDER-LEFT: windowtext 1pt dotted; PADDING-TOP: 1pt; BORDER-BOTTOM: windowtext 1pt dotted; mso-element: para-border-div; mso-border-alt: dotted windowtext .5pt">
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;         
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        NAME                      CLASS      VALUE
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        -------------------- ---------- ----------           --当前的硬解析值为569
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        parse count (hard)           64        569
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        scott@ASMDB> select * from emp;    
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;      
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        NAME                      CLASS      VALUE
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        -------------------- ---------- ----------           --执行上一个查询后硬解析值为570，解析次数增加了一次
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        parse count (hard)           64        570
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        scott@ASMDB> select * from Emp;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;        
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        NAME                      CLASS      VALUE
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        -------------------- ---------- ----------           --执行上一个查询后硬解析值为571
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        parse count (hard)           64        571
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        scott@ASMDB> select * from EMP;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;        
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        NAME                      CLASS      VALUE
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        -------------------- ---------- ----------           --执行上一个查询后硬解析值为572
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        parse count (hard)           64        572   
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        scott@ASMDB> select * from emp where empno=7369;       
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        NAME                      CLASS      VALUE
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        -------------------- ---------- ----------           --执行上一个查询后硬解析值为573
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        parse count (hard)           64        573
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        scott@ASMDB> select * from emp where empno=7369;    
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        NAME                      CLASS      VALUE
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        -------------------- ---------- ----------          --执行上一个查询后硬解析值为574
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>        parse count (hard)           64        574</DIV>
 
    从上面的示例中可以看出，尽管执行的语句存在细微的差别，但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
 
<DIV style="BORDER-RIGHT: windowtext 1pt dotted; PADDING-RIGHT: 4pt; BORDER-TOP: windowtext 1pt dotted; PADDING-LEFT: 4pt; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 1pt; BORDER-LEFT: windowtext 1pt dotted; PADDING-TOP: 1pt; BORDER-BOTTOM: windowtext 1pt dotted; mso-element: para-border-div; mso-border-alt: dotted windowtext .5pt">
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            sys@ASMDB> show parameter cursor_shar             --查看参数cursor_sharing
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            NAME                                 TYPE        VALUE
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            ------------------------------------ ----------- ------------------------------
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            cursor_sharing                       string      EXACT
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            sys@ASMDB> alter system set cursor_sharing='similar';    --将参数cursor_sharing的值更改为similar
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;    
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            NAME                      CLASS      VALUE
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            -------------------- ---------- ----------        --当前硬解析的值为865
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            parse count (hard)           64        865
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            scott@ASMDB> select * from dept where deptno=10;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;  
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            NAME                      CLASS      VALUE
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            -------------------- ---------- ----------        --执行上一条SQL查询后，硬解析的值变为866
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            parse count (hard)           64        866
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            scott@ASMDB> select * from dept where deptno=20;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            NAME                      CLASS      VALUE
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            -------------------- ---------- ----------        --执行上一条SQL查询后，硬解析的值没有发生变化还是866
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            parse count (hard)           64        866
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            sys@ASMDB> select sql_text,child_number from v$sql   -- 在下面的结果中可以看到SQL_TEXT列中使用了绑定变量:"SYS_B_0" 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              2  where sql_text like 'select * from dept where deptno%';
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            SQL_TEXT                                           CHILD_NUMBER
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            -------------------------------------------------- ------------   
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            select * from dept where deptno=:"SYS_B_0"                    0
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            sys@ASMDB> alter system set cursor_sharing='exact';       --将cursor_sharing改回为exact
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            --接下来在scott的session 中执行deptno=40 和的查询后再查看sql_text，当cursor_sharing改为exact后，每执行那个一次
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            --也会在v$sql中增加一条语句
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            sys@ASMDB> select sql_text,child_number from v$sql                
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              2  where sql_text like 'select * from dept where deptno%';
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            SQL_TEXT                                           CHILD_NUMBER
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            -------------------------------------------------- ------------
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            select * from dept where deptno=50                            0      
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            select * from dept where deptno=40                            0
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            select * from dept where deptno=:"SYS_B_0"                    0</DIV>
 
        注意当该参数设置为similar，会产生不利的影响，可以参考这里：<A href="http://www.eygle.com/archives/2011/01/cursor_sharing_expdp.html">cursor_sharing参数对于expdp的性能影响</A>
 
    2.使用绑定变量
        绑定变量要求变量名称，数据类型以及长度是一致，否则无法使用软解析
        绑定变量（bind variable）是指在DML语句中使用一个占位符，即使用冒号后面紧跟变量名的形式，如下
            select * from emp where empno=7788    --未使用绑定变量
            select * from emp where empono=:eno   --:eno即为绑定变量
        在第二个查询中，变量值在查询执行时被提供。该查询只编译一次，随后会把查询计划存储在一个共享池（库缓存）中，以便以后获取
            和重用这个查询计划。
    
        下面使用了绑定变量，但两个变量其实质是不相同的，对这种情形，同样使用硬解析
            select * from emp where empno=:eno;
            select * from emp where empno=:emp_no
 
        使用绑定变量时要求不同的会话中使用了相同的回话环境，以及优化器的规则等。
 
        使用绑定变量的例子(参照了TOM大师的Oracle 9i&10g编程艺术)
<DIV style="BORDER-RIGHT: windowtext 1pt dotted; PADDING-RIGHT: 4pt; BORDER-TOP: windowtext 1pt dotted; PADDING-LEFT: 4pt; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 1pt; BORDER-LEFT: windowtext 1pt dotted; PADDING-TOP: 1pt; BORDER-BOTTOM: windowtext 1pt dotted; mso-element: para-border-div; mso-border-alt: dotted windowtext .5pt">
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            scott@ASMDB> create table tb_test(col int);     --创建表tb_test
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            scott@ASMDB> create or replace procedure proc1  --创建存储过程proc1使用绑定变量来插入新记录
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              2  as
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              3  begin
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              4      for i in 1..10000
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              5      loop
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              6          execute immediate 'insert into tb_test values(:n)' using i;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              7      end loop;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              8  end;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              9  /
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            Procedure created.
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            scott@ASMDB> create or replace procedure proc2 --创建存储过程proc2，未使用绑定变量，因此每一个SQL插入语句都会硬解析
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              2  as
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              3  begin
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              4      for i in 1..10000
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              5      loop
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              6          execute immediate 'insert into tb_test values('||i||')';
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              7      end loop;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              8  end;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>              9  /
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            Procedure created.
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>  
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            scott@ASMDB> exec runstats_pkg.rs_start
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            PL/SQL procedure successfully completed.
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            scott@ASMDB> exec proc1;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            PL/SQL procedure successfully completed.
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            scott@ASMDB> exec runstats_pkg.rs_middle;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            PL/SQL procedure successfully completed.
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            scott@ASMDB> exec proc2;
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            PL/SQL procedure successfully completed.
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            scott@ASMDB> exec runstats_pkg.rs_stop(1000);
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            Run1 ran in 1769 hsecs
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            Run2 ran in 12243 hsecs             --run2运行的时间是run1的/1769≈倍
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            run 1 ran in 14.45% of the time   
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            Name                                Run1      Run2      Diff
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.SQL memory manager worka       410     2,694     2,284
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.session allocation             532     8,912     8,380
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.simulator lru latch             33     9,371     9,338
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.simulator hash latch            51     9,398     9,347
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            STAT...enqueue requests               31    10,030     9,999
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            STAT...enqueue releases               29    10,030    10,001
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            STAT...parse count (hard)              4    10,011    10,007    --硬解析的次数，前者只有四次
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            STAT...calls to get snapshot s        55    10,087    10,032
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            STAT...parse count (total)            33    10,067    10,034
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            STAT...consistent gets               247    10,353    10,106
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            STAT...consistent gets from ca       247    10,353    10,106
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            STAT...recursive calls            10,474    20,885    10,411
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            STAT...db block gets from cach    10,408    30,371    19,963
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            STAT...db block gets              10,408    30,371    19,963
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.enqueues                       322    21,820    21,498    --闩的队列数比较
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.enqueue hash chains            351    21,904    21,553
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            STAT...session logical reads      10,655    40,724    30,069
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.library cache pin           40,348    72,410    32,062    --库缓存pin
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.kks stats                        8    40,061    40,053
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.library cache lock             318    61,294    60,976
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.cache buffers chains        51,851   118,340    66,489
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.row cache objects              351   123,512   123,161
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.library cache               40,710   234,653   193,943
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            LATCH.shared pool                 20,357   243,376   223,019
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            Run1 latches total versus runs -- difference and pct
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            Run1      Run2      Diff     Pct
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            157,159   974,086   816,927  16.13%          --proc2使用闩的数量也远远多于proc1，其比值是.13%  
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left> 
<P class=MsoNormal style="PADDING-RIGHT: 0cm; PADDING-LEFT: 0cm; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 0cm; MARGIN: 0cm 0cm 0pt; PADDING-TOP: 0cm; TEXT-ALIGN: left; mso-layout-grid-align: none; mso-border-alt: dotted windowtext .5pt; mso-padding-alt: 1.0pt 4.0pt 1.0pt 4.0pt" align=left>            PL/SQL procedure successfully completed.</DIV>
            
        由上面的示例可知，在未使用绑定变量的情形下，不论是解析次数，闩使用的数量，队列，分配的内存，库缓存，行缓存远远高于绑定
        变量的情况。因此尽可能的使用绑定变量避免硬解析产生所需的额外的系统资源。
        
        绑定变量的优点
            减少SQL语句的硬解析，从而减少因硬解析产生的额外开销(CPU,Shared pool,latch)。其次提高编程效率，减少数据库的访问次数。
        绑定变量的缺点
            优化器就会忽略直方图的信息，在生成执行计划的时候可能不够优化。SQL优化相对比较困难
 
六、总结
    1.尽可能的避免硬解析，因为硬解析需要更多的CPU资源，闩等。
    2.cursor_sharing参数应权衡利弊，需要考虑使用similar与force带来的影响。
    3.尽可能的使用绑定变量来避免硬解析。
    
 
转自：<A href="http://blog.csdn.net/robinson_0612/article/details/6195483">http://blog.csdn.net/robinson_0612/article/details/6195483</A></DIV>

页: [1]

Chinaunix's Archiver

Oracle 硬解析与软解析