清空共享池，关闭/重启DB

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  419430400 bytes

Fixed Size                  1267836 bytes

Variable Size             364906372 bytes

Database Buffers           50331648 bytes

Redo Buffers                2924544 bytes

Database mounted.

Database opened.

不使用绑定变量

SQL>set timing on

SQL> declare

type cur is ref cursor;

cur1 cur;

name varchar2(1000);

begin

for i in 1..1000 loop

open cur1 for 'select object_name from dba_objects where object_id=' || i;

fetch cur1 into name;

close cur1;

end loop;

end ; 

/

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.72

SQL>select sql_text from v$sqlarea where sql_text like '%dba_objects%';

select object_name from dba_objects where object_id=70

select object_name from dba_objects where object_id=410

select object_name from dba_objects where object_id=884

select object_name from dba_objects where object_id=595

select object_name from dba_objects where object_id=687

select object_name from dba_objects where object_id=999

select object_name from dba_objects where object_id=831

select object_name from dba_objects where object_id=626

select object_name from dba_objects where object_id=596

select object_name from dba_objects where object_id=993

select object_name from dba_objects where object_id=907

SQL_TEXT

--------------------------------------------------------------------------------

select object_name from dba_objects where object_id=895

select object_name from dba_objects where object_id=408

select object_name from dba_objects where object_id=576

select object_name from dba_objects where object_id=506

select object_name from dba_objects where object_id=716

select object_name from dba_objects where object_id=129

select object_name from dba_objects where object_id=218

select object_name from dba_objects where object_id=319

select object_name from dba_objects where object_id=140

select object_name from dba_objects where object_id=196

select object_name from dba_objects where object_id=776

SQL_TEXT

--------------------------------------------------------------------------------

select object_name from dba_objects where object_id=84

select object_name from dba_objects where object_id=621

select object_name from dba_objects where object_id=368

select object_name from dba_objects where object_id=726

select object_name from dba_objects where object_id=424

select object_name from dba_objects where object_id=466

select object_name from dba_objects where object_id=444

select object_name from dba_objects where object_id=9

select object_name from dba_objects where object_id=284

select object_name from dba_objects where object_id=854

select object_name from dba_objects where object_id=387

SQL_TEXT

--------------------------------------------------------------------------------

select object_name from dba_objects where object_id=484

select object_name from dba_objects where object_id=1000

select object_name from dba_objects where object_id=138

select object_name from dba_objects where object_id=25

select object_name from dba_objects where object_id=416

select object_name from dba_objects where object_id=897

select object_name from dba_objects where object_id=162

select object_name from dba_objects where object_id=728

select object_name from dba_objects where object_id=102

select object_name from dba_objects where object_id=554

select object_name from dba_objects where object_id=508

SQL_TEXT

--------------------------------------------------------------------------------

select object_name from dba_objects where object_id=658

select object_name from dba_objects where object_id=480

select object_name from dba_objects where object_id=922

select object_name from dba_objects where object_id=951

select object_name from dba_objects where object_id=283

1003 rows selected.

Elapsed: 00:00:01.16

可以看到 该语句解析了1000次

使用绑定变量

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.52

SQL>set timing on

SQL> declare

type cur is ref cursor;

cur1 cur;

name varchar2(1000);

begin

for i in 1..1000 loop

open cur1 for 'select object_name from dba_objects where object_id=:x'using i;

fetch cur1 into name;

close cur1;

end loop;

end ;

 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15

SQL> select sql_text from v$sqlarea where sql_text like '%dba_objects%';

SQL_TEXT

--------------------------------------------------------------------------------

select object_name from dba_objects where object_id=:x

select sql_text from v$sqlarea where sql_text like '%dba_objects%'

declare type cur is ref cursor; cur1 cur; name varchar2(1000); begin for i in 1.

.1000 loop open cur1 for 'select object_name from dba_objects where object_id=:x

'using i; fetch cur1 into name; close cur1; end loop; end ;

Elapsed: 00:00:00.02

可以看到 该语句只解析了1次

计算解析时间：

SQL> SELECT (572-15)/572 FROM DUAL;

(572-15)/572

------------

  .973776224

Elapsed: 00:00:00.00

看到了吧，时间全都花在了语句解析上