052.【转：硬解析的例子】

create table t (x int);
SQL> select * from v$sysstat where name='parse count (hard)';

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------------ ---------- ---------- ----------
550 parse count (hard) 64 12801 143509059

SQL> create or replace procedure proc1
2 as
3 begin
4 for i in 1..10000
5 loop
6 execute immediate 'insert into t values(:x)' using i;
7 end loop;
8 commit;
9 end;
10 /

Procedure created.

SQL> exec proc1;

PL/SQL procedure successfully completed.

SQL> select * from v$sysstat where name='parse count (hard)';--因为使用了绑定变量，硬解析次数没有增加很多

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------------ ---------- ---------- ----------
550 parse count (hard) 64 12861 143509059

SQL> create or replace procedure proc2
2 as
3 begin
4 for i in 1..10000
5 loop
6 execute immediate 'insert into t values('||i||')';
7 end loop;
8 commit;
9 end;
10 /

Procedure created.

SQL> exec proc2;

PL/SQL procedure successfully completed.

SQL> select * from v$sysstat where name='parse count (hard)';--因为没有使用绑定变量，差不多增加了10000次的硬解析次数

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------------ ---------- ---------- ----------
550 parse count (hard) 64 22939 143509059

SQL> create or replace procedure proc3
2 as
3 begin
4 for i in 1..10000
5 loop
6 insert into t values(i);
7 end loop;
8 commit;
9 end;
10 /

Procedure created.

SQL> exec proc3;

PL/SQL procedure successfully completed.

SQL> select * from v$sysstat where name='parse count (hard)';--硬解析次数没有增加很多

STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------------ ---------- ---------- ----------
550 parse count (hard) 64 23031 143509059






dingjun123的回复：
proc2是典型的动态语句还没有使用using的，每条语句都会硬解析
proc3静态SQL使用变量，在PLSQL里是默认使用绑定变量的，会将变量自动绑定，你看下v$sql里存的SQL语句或做个10046看看就知道了

vage的回复：
PROC3中，循环中执行insert into t values(i);这样的SQL，Oracle会进行优化。
PROC2中，execute immediate 'insert into t values('||i||')' 这样的语句，全动态游标，Oracle无法进行优化。

查看执行计划的实验：
truncate table t;
alter system flush shared_pool;
alter system flush buffer_cache;
exec proc1;

SQL> select * from (select executions, sql_text from v$sqlarea order by 2 ) where rownum<100;--使用了绑定变量

EXECUTIONS SQL_TEXT
---------- --------------------------------------------------------------------------------
1 BEGIN proc1; END;
10000 insert into t values(:x)

truncate table t;
alter system flush shared_pool;
alter system flush buffer_cache;
exec proc2;

SQL> select * from (select executions, sql_text from v$sqlarea order by 2 ) where rownum<100;--可以看到很多的insert语句，可以知道没有使用绑定变量

EXECUTIONS SQL_TEXT
---------- --------------------------------------------------------------------------------
1 BEGIN proc2; END;
1 insert into t values(10000)
1 insert into t values(6639)
1 insert into t values(6640)
1 insert into t values(6641)
1 insert into t values(6642)
1 insert into t values(6643)
1 insert into t values(6644)

truncate table t;
alter system flush shared_pool;
alter system flush buffer_cache;
exec proc3;

SQL> select * from (select executions, sql_text from v$sqlarea order by 2 ) where rownum<100;--使用了绑定变量

EXECUTIONS SQL_TEXT
---------- --------------------------------------------------------------------------------
1 BEGIN proc3; END;
10000 INSERT INTO T VALUES(:B1 )

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7901922/viewspace-1060036/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7901922/viewspace-1060036/