求助存储过程中硬解析问题

Question

create or replace procedure proc1
               as
                v_cou        pls_integer;
               begin
                for i in 1..10000
                 loop
                     execute immediate 'insert into tb_test values(:n)' using i;
 execute immediate 'select count(1) into v_cou from equipstate where state>0  and lineid = :n ' using  i;        
           insert into tb_test values(v_cou);
                  end loop;
             end;以上是测试的存储过程，select count(1) into v_cou from equipstate where state>0  and lineid =：n在原来的存储过程中该语句每秒执行数次，硬解析值非常高，目前想改为软解析，找了一些资料，引用了现在的写法execute immediate 'select count(1) into v_cou from equipstate where state>0  and lineid = :n ' using  i;   编译通过但是执行时语法错误，MS不能这样写，向各位大神求助，这里应该如何改写为硬解析，其实就是很简单的一个查询赋值给v_cou ，先谢了存储

Answer 1

你这完全不需要动态sql的啊，照样不需要硬解析的create or replace procedure proc1 as
  v_cou pls_integer;
begin
  for i in 1 .. 10000 loop
    insert into tb_test values (i);
    
    select count(1)
      into v_cou
      from equipstate
     where state > 0
       and lineid = i;
  
    insert into tb_test values (v_cou);
  end loop;
end;

Answer 2

但是看这篇文章写的 应该是差别蛮大的http://blog.csdn.net/duanning397/article/details/7586609再创建一个不适用绑定变量往T表查数据的存储过程：
SQL> create or replace procedure pro2
  2 as
  3 begin
  4 for i in 1..10000
  5     loop
  6      execute immediate
  7     'insert into t values('||i||')';
  8   end loop;
  9 end;
下面开始执行这两个存储过程，查看下运行时间：
SQL> set timing on
SQL> exec pro1;
PL/SQL 过程已成功完成。
已用时间:  00: 00: 00.42
SQL> truncate table t; --此目的只是与执行存储过程pro1前T表都为空
表被截断。
已用时间:  00: 00: 00.20
SQL> exec pro2;
PL/SQL 过程已成功完成。
已用时间:  00: 00: 11.53
SQL> set timing off
结果非常明显，使用绑定变量节省的时间可不是一点。这只是个简单的例子，如果在实际应用中那节省的时间将会更多，可能就会造成2个不同性能级别的应用系统。在一个已成型的应用系统中再重新使用绑定变量时费时费力的，你要寻找每个SQL语句，查看是否能使用绑定变量，最重要的是你可能已经提交给客户，这直接影响到客户的满意度。
绑定变量的好处是减少了硬解析，降低了CPU的竞争，节省了shared_pool，但缺点是不能使用柱状图（histogram），SQL优化比较困难。
在系统的研发阶段，应该为数据库设计投入更多的时间，因为所有的数据都是在后台运行的，后台的性能在很大程度上决定了一个系统的性能。
不要以为数据库只有SELECT、INSERT、UPDATE、DELETE，这些只是数据库的基础操作，只是这些简单的操作就需要开发人员增加一些HINT来加快数据的操作，由这些操作还能延伸到数据库的redo日志、undo段的管理等等。数据库并不是一门简单的“语言”，数据库调优与维护更是数据库的精华所在，需要我们学而时习之！

Answer 3

我自己也建了俩个测了一下 一个是0.468秒 一个是4.6秒 10倍啊亲create table tb_test (x int);
 create or replace procedure pro1
   as
   begin
    for i in 1..10000
     loop
     execute immediate
     'insert into tb_test values(:x)' using i;
   end loop;
 end;
 
 
 
 create or replace procedure pro2
   as
   begin
  for i in 1..10000
   loop
    execute immediate
    'insert into tb_test values('||i||')';
 end loop;
 end;

Answer 4

execute immediate  'insert into tb_test values('||i||')';这样每次执行的语句都是不同的，当然是硬解析。但是如果不用动态语句，直接insert into tb_test values(i);这就可以了