oracle 存储过程中，使用execute immediate 来执行动态SQL，每次...

ALLSTARS_ORACLE

oracle 存储过程中，使用execute immediate 来执行动态SQL，每次都会发生硬解析吗？



使用execute immediate sql， oracle每次都会发生硬解析吗？大批量数据操作或者频繁使用 execute immediate 是否会影响性能？

declare
   v_sql varchar2(2000);
   v_type varchar2(8);
begin
   v_type := '100';
   v_sql := 'insert into t_emp(id, name)'
     || 'select id, name from tmp_tab where calc_type = ''' || v_type || '''';
   ---(1)
   execute immediate v_sql;
   ---(2)
   execute immediate '
     insert into t_emp(id, name)
     select id, name from tmp_tab where calc_type = :calc_type
     '
     using v_type;
end;

上面两部分execute immediate，一个对应sql未使用变量绑定，一个使用变量绑定，两者性能上是否有明显区别，使用绑定变量的是否对应再次执行就不用硬解析了呢？


直接使用程序调用SQL执行（使用变量绑定），与execute immediate使用变量绑定（上面（2）部分的execute immediate 用法）性能有明显差别吗？主要差别在哪里？
insert into t_emp(id, name)
     select id, name from tmp_tab where calc_type = ?

有一个功能，会频繁调用SQL，想判断或分析一下execute immediate 对性能是否有影响，影响多大，然后决定是否使用此方案。

谢谢！







1)是否硬解析取决于执行的SQL是否在shared pool中存在，并且已经解析过，不取决于执行SQL的方式。execute immediate只是提供了一种动态执行SQL的方式。

2)第一种方法不使用绑定变量，每个SQL语句在shared pool中都是不同的：

您可以查看select * from v$sqlarea,发现：

insert into t_emp(id, name)
     select id, name from tmp_tab where calc_type =1

insert into t_emp(id, name)
     select id, name from tmp_tab where calc_type =2

insert into t_emp(id, name)
     select id, name from tmp_tab where calc_type =3

因为每一个SQL都不一样，所以每次执行都需要硬解析。

第二种方法可以使每个SQL共用一个cursor：

insert into t_emp(id, name)
     select id, name from tmp_tab where calc_type =:B1

从而可能使用软解析。

3)性能差距：

硬解析会消耗较高CPU资源，不过一般测试可能测不出来，因为在SQL语句简单时差别不大，在SQL语句复杂时差别很大。

在有多个进程同时做parse时，性能差距可能也很大，因为有shared pool latch争用等，会引发别的问题。

而且您可以看到，不使用绑定变量的SQL会占用大量shared pool 空间。

所以一般情况下，我们推荐使用绑定变量，并且尽量避免硬解析。

4)如果SQL语句在shared pool中存在，则可以实现软解析(判断并直接使用其执行计划)，但是有更好的方法可以实现不解析（只解析一次）。在数据库有严重parse性能问题时可以作为一种解决方案。实例如下：

CREATE OR REPLACE PROCEDURE demo AS
   type recstartyp is table of emp%rowtype index by BINARY_INTEGER;
   rec_tab recstartyp;
   cursor temp is select wo_no,id from emp;
   cursor_name INTEGER;
   rows_processed INTEGER;
begin
   cursor_name := dbms_sql.open_cursor;
   DBMS_SQL.PARSE(cursor_name, 'UPDATE DMPDB2.LOCK_WIP_DETAIL   SET WO_NO =:x WHERE ID =:y',
                  DBMS_SQL.NATIVE);

<=========Parse一次
                 
   open temp;
   fetch temp bulk collect into rec_tab;
   FORALL i in rec_tab.first..rec_tab.last
      DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', rec_tab(i).wo_no);
      DBMS_SQL.BIND_VARIABLE(cursor_name, ':y', rec_tab(i).id);
      rows_processed := DBMS_SQL.EXECUTE(cursor_name);

<=========执行多次

      commit;
   close temp;
   DBMS_SQL.CLOSE_CURSOR(cursor_name);
WHEN OTHERS THEN
   close temp;
   DBMS_SQL.CLOSE_CURSOR(cursor_name);
end;
/




sys@R11203> create table t1 (id number);

Table created.

sys@R11203> create index t1_id on t1(id);

Index created.

sys@R11203> begin
  2           for i in 1..1000 loop
  3               insert into t1 values(i);
  4           end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

sys@R11203> commit;

Commit complete.

sys@R11203>
sys@R11203> create or replace procedure p1(pid number) is
  2  begin
  3           update /*static SQL*/ t1 set id = id+1 where id = pid;
  4  end;
  5  /

Procedure created.

sys@R11203>
sys@R11203> create or replace procedure p2(pid number) is
  2  begin
  3           execute immediate 'update /*dynamic SQL literal*/ t1 set id = id+1 where id = '||pid;
  4  end;
  5  /

Procedure created.

sys@R11203>
sys@R11203> create or replace procedure p3(pid number) is
  2  begin
  3           execute immediate 'update /*dynamic SQL bind*/ t1 set id = id+1 where id = :1' using pid;
  4  end;
  5  /

Procedure created.

sys@R11203>
sys@R11203>
sys@R11203> ---1. Static SQL
sys@R11203> alter system flush shared_pool;

System altered.

sys@R11203> alter system flush shared_pool;

System altered.

sys@R11203> alter system flush shared_pool;

System altered.

sys@R11203>
sys@R11203> select a.value,b.name from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.name like '%parse%';

     VALUE NAME                                                                                                                                                                                         
---------- ----------------------------------------------------------------                                                                                                                             
        12 parse time cpu                                                                                                                                                                              
        29 parse time elapsed                                                                                                                                                                           
       304 parse count (total)                                                                                                                                                                          
       245 parse count (hard)                                                                                                                                                                           
         0 parse count (failures)                                                                                                                                                                       
         0 parse count (describe)                                                                                                                                                                       

6 rows selected.

sys@R11203>
sys@R11203> begin
  2           for i in 1..100 loop
  3               p1(i);
  4           end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

sys@R11203> select a.value,b.name from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.name like '%parse%';

     VALUE NAME                                                                                                                                                                                         
---------- ----------------------------------------------------------------                                                                                                                             
        15 parse time cpu                                                                                                                                                                              
        33 parse time elapsed                                                                                                                                                                           
       314 parse count (total)                                                                                                                                                                          
       280 parse count (hard)                                                                                                                                                                           
         0 parse count (failures)                                                                                                                                                                       
         0 parse count (describe)                                                                                                                                                                       

6 rows selected.


sys@R11203> rollback;

Rollback complete.

sys@R11203>
sys@R11203> ---2. dynamic SQL literal
sys@R11203> alter system flush shared_pool;

System altered.

sys@R11203> alter system flush shared_pool;

System altered.

sys@R11203> alter system flush shared_pool;

System altered.

sys@R11203>
sys@R11203> select a.value,b.name from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.name like '%parse%';

     VALUE NAME                                                                                                                                                                                         
---------- ----------------------------------------------------------------                                                                                                                             
        18 parse time cpu                                                                                                                                                                              
        34 parse time elapsed                                                                                                                                                                           
       324 parse count (total)                                                                                                                                                                          
       300 parse count (hard)                                                                                                                                                                           
         0 parse count (failures)                                                                                                                                                                       
         0 parse count (describe)                                                                                                                                                                       

6 rows selected.

sys@R11203>
sys@R11203> begin
  2           for i in 1..100 loop
  3               p2(i);
  4           end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

sys@R11203> select a.value,b.name from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.name like '%parse%';

     VALUE NAME                                                                                                                                                                                         
---------- ----------------------------------------------------------------                                                                                                                             
        49 parse time cpu                                                                                                                                                                              
       127 parse time elapsed                                                                                                                                                                           
       627 parse count (total)                                                                                                                                                                          
       623 parse count (hard)                                                                                                                                                                           
         0 parse count (failures)                                                                                                                                                                       
         0 parse count (describe)                                                                                                                                                                       

6 rows selected.


sys@R11203> rollback;

Rollback complete.

sys@R11203>
sys@R11203> ---3. dynamic SQL bind
sys@R11203> alter system flush shared_pool;

System altered.

sys@R11203> alter system flush shared_pool;

System altered.

sys@R11203> alter system flush shared_pool;

System altered.

sys@R11203>
sys@R11203> select a.value,b.name from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.name like '%parse%';

     VALUE NAME                                                                                                                                                                                         
---------- ----------------------------------------------------------------                                                                                                                             
        52 parse time cpu                                                                                                                                                                              
       128 parse time elapsed                                                                                                                                                                           
       652 parse count (total)                                                                                                                                                                          
       652 parse count (hard)                                                                                                                                                                           
         0 parse count (failures)                                                                                                                                                                       
         0 parse count (describe)                                                                                                                                                                       

6 rows selected.

sys@R11203>
sys@R11203> begin
  2           for i in 1..100 loop
  3               p3(i);
  4           end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

sys@R11203> select a.value,b.name from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.name like '%parse%';

     VALUE NAME                                                                                                                                                                                         
---------- ----------------------------------------------------------------                                                                                                                             
        59 parse time cpu
       143 parse time elapsed
       705 parse count (total)
       706 parse count (hard)
         0 parse count (failures)
         0 parse count (describe)
        
**********************************************
总结：静态绑定变量
parse: 314 - 304 = 10次  
Hard Parse: 280 - 245 = 35次(这个值不太正常，目前没找到原因)
**********************************************

**************************************
总结：动态不绑定变量
Parse count: 303次
Hard Parse: 323次
**************************************

**************************************
总结：动态绑定变量
Parse count: 53次
Hard Parse: 54次
**************************************

动态，静态，绑定变量与字面值，主要是对parse有影响。

建议： 尽量使用绑定变量的静态SQL。


补充，关于您的问题2：

直接使用程序调用SQL执行（使用变量绑定），与execute immediate使用变量绑定（上面（2）部分的execute immediate 用法）性能有明显差别吗？主要差别在哪里？
insert into t_emp(id, name)
     select id, name from tmp_tab where calc_type = ?

直接使用SQL语句执行带绑定变量和execute immediate使用变量绑定，从这个SQL语句的执行上来讲基本上是一样的。

差别是execute immediate是另外起了一个recursive的session来执行指定的语句，而不是在当前session中执行。

这是为什么PL/SQL中不允许写DDL，但是我们可以在PL/SQL中利用execute immediate来执行一个execute immediate 'create table...'。理论上来说execute immediate会消耗更多的资源，但是实际应用中感觉不出来太大差异。


是否硬解析取决于执行的SQL是否在shared pool中存在，并且已经解析过，不取决于执行SQL的方式。execute immediate只是提供了一种动态执行SQL的方式。

主要差别在soft parse, 与cursor pin: S相关.