当前位置:  数据库>oracle

Oracle硬解析和软解析的区别分析

    来源: 互联网  发布时间:2014-10-04

    本文导语:  一、摘要 Oracle硬解析和软解析是我们经常遇到的问题,所以需要考虑何时产生软解析何时产生硬解析,如何判断 SQL的执行过程 当发布一条SQL或PL/SQL命令时,Oracle会自动寻找该命令是否存在于共享池中来决定对当前的语句使用...

一、摘要

Oracle硬解析和软解析是我们经常遇到的问题,所以需要考虑何时产生软解析何时产生硬解析,如何判断

SQL的执行过程

当发布一条SQL或PL/SQL命令时,Oracle会自动寻找该命令是否存在于共享池中来决定对当前的语句使用硬解析或软解析。

通常情况下,SQL语句的执行过程如下:

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

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

Step3. 如果共享池中存在相同的哈希值,则对这个命令进一步判断是否进行软解析,否则到e步骤。

Step4. 对于存在相同哈希值的新命令行,其文本将与已存在的命令行的文本逐个进行比较。

    这些比较包括大小写,字符串是否一致,空格,注释等,如果一致,则对其进行软解析,转到步骤Step6,无需再次硬解析。

    否则到步骤Step5。

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

Step6. 执行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" 
      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        
      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使用绑定变量来插入新记录
as
begin
for i in 1..10000
loop
execute immediate 'insert into tb_test values(:n)' using i;
end loop;
end;
/
Procedure created.
scott@ASMDB> create or replace procedure proc2 --创建存储过程proc2,未使用绑定变量,因此每一个SQL插入语句都会硬解析
as
begin
for i in 1..10000
loop
execute immediate 'insert into tb_test values('||i||')';
end loop;
end;
/
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.尽可能的使用绑定变量来避免硬解析。


    
 
 

您可能感兴趣的文章:

  • linux下oracle的自启动脚本解析
  • oracle SQL解析步骤小结
  • 解析mysql与Oracle update的区别
  • 细化解析Oracle 10g STATSPACK的新功能
  • 深入解析Oracle参数及参数文件
  • Oracle数据库安装配置流程示例详细解析
  • Oracle中DBMS_SQL解析SQL语句的流程
  • [Oracle] RAC 之 - 负载均衡深入解析
  • Oracle数据库安全性管理基本措施实例解析
  • 解析Oracle查询和删除JOB的SQL
  • 解析jdbc处理oracle的clob字段的详解
  • 职场专家解析Oracle认证门道
  • oracle 索引不能使用深入解析
  • oracle的rownum深入解析
  • 解析如何查看Oracle数据库中某张表的字段个数
  • Oracle row_number() over()解析函数高效实现分页
  • 解析学习Oracle架构所应了解的基础知识
  • Oracle基本查询过滤排序示例解析
  • 解析Oracle中多表级联删除的方法
  • 解析oracle对select加锁的方法以及锁的查询
  • Oracle中关数据库对象的统计分析
  • 深入oracle特定信息排序的分析
  • oracle 数据库连接分析
  • Oracle移动数据文件到新分区步骤分析
  • 基于oracle小数点前零丢失的问题分析
  • Oracle date如何比较大小分析
  • Linux上建立第二个ORACLE实例分析
  • Oracle 数据库容灾复制解决方案分析Shar Plex
  • 计算机名称修改后Oracle不能正常启动问题分析及解决
  • oracle导出sql语句的结果集和保存执行的sql语句(深入分析)
  • linux as3 _weblogic8_oracle系统访问50万/日,常常ping不通,分析原因
  •  
    本站(WWW.169IT.COM)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.169IT.COM)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE,用root用户和用oracle用户安装有什么区别么?
  • Oracle 12c的九大最新技术特性介绍
  • 网间Oracle的连接,远程连接Oracle服务器??


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2017,169IT.COM,E-mail:www_169it_com#163.com(请将#改为@)

    浙ICP备11055608号