测量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

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

SQL_TEXT
----------------------------------------------------------------------------------------------------
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 cu
r1; end loop; end ;

select sql_text from v$sqlarea where sql_text like '%dba_objects$'
select object_name from dba_objects where object_id=:x
select sql_text from v$sqlarea where sql_text like '%dba_objects

可以看到 该语句只解析了1次
SQL> select (751-64)/751 from dual;

(751-64)/751
------------
  .914780293 解析过程耗费了该过程执行的 91%的时间!!!!

由此可知，SQL语句的解析是非常耗费资源的，特别是时间资源