[ORACLE]从两个简单的存储过程看绑定变量的重要

CREATE TABLE TTT1 (X INT);

CREATE OR REPLACE PROCEDURE PROC1
AS
BEGIN
            FOR i IN 1 .. 100000
            LOOP
                        EXECUTE IMMEDIATE 'INSERT INTO TTT1 VALUES(:X)' USING i;
            END LOOP;
END;
/


CREATE OR REPLACE PROCEDURE PROC2
AS
BEGIN
            FOR i IN 1 .. 100000
            LOOP
                        EXECUTE IMMEDIATE 'INSERT INTO TTT1 VALUES('||i||')';
            END LOOP;
END;
/

两个存储过程都很简单，都是向表TTT1中插入1-100000条 记录，唯一的不同是proc1使用了绑定变量:x，proc2没有使用绑定变量，我们来简单的看一下执行时间 ：
ETL@DWTEST> EXEC PROC1;

PL/SQL procedure successfully completed.

El apsed: 00:00:02.80
ETL@DWTEST> EXEC PROC2;

PL/SQL procedure successfully completed .

Elapsed:  00:00:06.27

可以看到PROC2的执行时间几乎是PROC1执行时间的3倍！
TOM曾说过： Oracle  将已解析、已编译的SQL 连同其他内容存储在共享池（share d pool）中，这是系统全局区（System Global Area ，SGA）中一个非常重要的共享内存结构。如果你确实想让Oracle 缓慢地运 行，甚至几近停顿，只要根本不使用绑定变量就可以办到。
如果使用绑定变量，无论是谁，只要提交引用同一对象的同一个查询 ，都会使用共享池中已编译的查询计划。这样你的子例程只编译一次就可以反复使用。这样做效率很高，这也正是数据库期望你采用的做法。你使用的资源会更少（软解析耗费的资源相当少），不仅如此，占用闩的时间也更短，而且不再那么频繁地需要闩。这些都会改善应用的性能和可扩缩性。
实际上，proc2 需要的时间几乎是proc1 的3 倍，这说明，在这种情况下，对于每个“无绑定变量”的INSERT，执行语句所需时间中有2 /3 仅用于解析语句！因此，请检查一下我们曾经写过的程序，看一看是否可以使用绑定变量，如果真的可以使用绑定变量，那我确信程序经过修改可以提高一大截性能！