Oracle硬解析和软解析的区别分析(2)-木庄网络博客

可以基于不同的级别来设定该参数，如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.尽可能的使用绑定变量来避免硬解析。

标签：Oracle