²âÁ¿sql ½âÎöºÄ·ÑµÄʱ¼ä±ÈÀý

Çå¿Õ¹²Ïí³Ø£¬¹Ø±Õ£¬ÖØÆôÊý¾Ý¿â
SQL> shutdown immediate;
SQL> startup;
¿ªÆô ʱ¼äͳ¼Æ
SQL> set timi on
SQL> select count(*) from dba_objects;
  COUNT(*)
----------
     11308
Elapsed: 00:00:00.19
SQL> /
  COUNT(*)
----------
     11308
Elapsed: 00:00:00.03
SQL> /
  COUNT(*)
----------
     11308
Elapsed: 00:00:00.03
SQL> select 16/19 from dual;
     16/19
----------
.842105263
¿ÉÒÔ¿´µ½ ½âÎöʱ¼ä ËùÕ¼ÓÃÁ˾ø´ó²¿·Öʱ¼ä £¬µ¥Æ¾Õâ¸öʵÑé ²»ÄÜ˵SQL½âÎö»áÕ¼ÓÃ84%µÄʱ¼ä£¬²»¹ý¿ÉÒԿ϶¨µÄÊÇ SQL½âÎö»áÕ¼ÓÃSQLÓï¾äÔËÐеĺܴ󲿷Öʱ¼ä¡£
ÏÂÃæÀ´²âÊÔһϠʹÓð󶨱äÁ¿ ºÍ²»Ê¹Óð󶨱äÁ¿ËùSQL½âÎöËù´øÀ´µÄÐÔÄÜÎÊÌâ
Ö´ÐÐÁ½¸ö½Å±¾ µÚÒ»¸ö ½Å±¾²»Ê¹Óð󶨱äÁ¿
bind1.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 ;
/
bind2.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 ;
/
¿ªÊ¼²âÊÔ
SQL> alter system flush shared_pool;
System altered.
SQL> set timi on
SQL> @bind1
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.51
SQL>select sql_text from v$sqlarea where sql_text like '%dba_objects%';
SQL_TEXT
----------------------------------------------------------------------------------------------------
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
....................
¿ÉÒÔ¿´µ½ ¸ÃÓï¾ä½âÎöÁË1000´Î
SQL> alter system flush shared_pool;
System altered.
SQL> @bind2
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.64