oracle中几种游标使用方式的性能测试

硬解析（不使用绑定变量）

create or replace procedure p1 as
  v_cursor number;
  v_sql    varchar2(100);
  v_stat   number;
begin
  for x in 1 .. 10000 loop
    v_sql    := 'insert into t9 values (' || x || ')';
    v_cursor := dbms_sql.open_cursor; --????????
    dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);
    v_stat := dbms_sql.execute(v_cursor);
    dbms_sql.close_cursor(v_cursor);
  end loop;
  commit;
end;

执行时间大概在33.79s

软解析（使用绑定变量，设置session_cached_cursors=0以禁用游标缓存）

create or replace procedure p2 as
  v_cursor number;
  v_sql    varchar2(100);
  v_stat   number;
begin
  execute immediate 'alter session set session_cached_cursors=0';
  v_sql := 'insert into t9 values (:a)';
  for x in 1 .. 10000 loop
    v_cursor := dbms_sql.open_cursor; --????????
    dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);
    dbms_sql.bind_variable(v_cursor, ':a', x);
    v_stat := dbms_sql.execute(v_cursor);
    dbms_sql.close_cursor(v_cursor);
  end loop;
  commit;
end;

　　这两种方案性能测试对比,使用tom的runStats_pkg包收集

游标缓存（使用绑定变量，设置session_cached_cursors>0）

create or replace procedure p3 as
  v_cursor number;
  v_sql    varchar2(100);
  v_stat   number;
begin
  v_sql := 'insert into t9 values (:a)';
  for x in 1 .. 10000 loop
    v_cursor := dbms_sql.open_cursor; --打开游标 
    dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);
    dbms_sql.bind_variable(v_cursor, ':a', x);
    v_stat := dbms_sql.execute(v_cursor);
    dbms_sql.close_cursor(v_cursor);
  end loop;
  commit;
end;

p2 测试于p3测试对比,使用tom的runStats_pkg包收集

　　

可见p2 缓存命中率为0,p3 为99999 这说明游标缓存确实有生效.

持有游标

create or replace procedure p4 as
  v_cursor number;
  v_sql    varchar2(100);
  v_stat   number;
begin
  v_sql    := 'insert into t9 values (:a)';
  v_cursor := dbms_sql.open_cursor; --打开游标 
  dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);
  for x in 1 .. 10000 loop
    --dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);
    dbms_sql.bind_variable(v_cursor, ':a', x);
    v_stat := dbms_sql.execute(v_cursor);
  end loop;
  dbms_sql.close_cursor(v_cursor);
end;

　单独执行p2

　

游标缓存解析时间比不使用游标缓存的软解析要短,但是cpu 时间更长了.

   

p4 持有游标的效果十分明显,解析数只有一次