SQL动态执行问题

fengbao

各位好！
     我有一个SELECT语句，想测试一下物理读、逻辑读。
     我写了一个存储过程，通过游标去循环动态执行select语句，等执行完成后，发现物理读，buffer get竟然基本为0。
     数据库版本：11.2.0.3 rac asm redhat as 5.5 x64

SQL> select  t.executions,t.disk_reads,t.buffer_gets from v$sql t where sql_text like 'SELECT 3%' union all
  2  select  t.executions,t.disk_reads,t.buffer_gets from v$sql t where sql_text like 'SELECT 2%' union all
  3  select  t.executions,t.disk_reads,t.buffer_gets from v$sql t where sql_text like 'SELECT 1%'
EXECUTIONS DISK_READS BUFFER_GETS
---------- ---------- -----------
         1                14        78
   2322006          0          10
         1                5          44
   1202925          0          10
其中executions为1的为直接写的SQL语句，disk_read和buffer get正常，但是executions执行为100万次以上为游标循环动态执行select语句，disk_reads为竟然为0。

是不是oracle判断这样动态执行没意义，所以没有执行呢？

存储过程如下：
declare
  n_sid         number;
begin
  for PRD_INST_ID in (select PRD_INST_ID from CRM.TB_PRD_PRD_INST_562) loop
    execute immediate 'SELECT 2,
       A1.PRD_INST_ID,
       A1.PRD_INST_ID,
       A1.ACCT_ID,
       A1.OWN_CUST_ID,
       A1.OFR_ID,
       A1.IF_PREPAY,
       A1.ACCT_ID,
       A1.ACCT_ID,
       A2.PAY_METHOD,
       A2.ACCT_ID,
       A2.EFF_STATE,
       A3.ACCT_ID,
       A3.PRD_INST_ID,
       A4.OFR_ID,
       A4.OFR_ID,
       A4.OFR_DETAIL_INST_REF_ID,
       A4.OFR_INST_ID,
       A4.OFR_DETAIL_TYPE_ID,
       A5.OFR_INST_ID,
       A5.OFR_INST_STAS_ID
  FROM CRM.TB_PRD_PRD_INST_562  A1,
       CRM.TB_BIL_ACCT_562      A2,
       CRM.TB_PRD_PRD_INST_562  A3,
       CRM.TB_PRD_OFR_DETAIL_INST_562 A4,
       CRM.TB_PRD_OFR_INST_562  A5
WHERE A5.OFR_INST_STAS_ID = 1001
   AND A4.OFR_INST_ID = A5.OFR_INST_ID
   AND A4.OFR_DETAIL_TYPE_ID = ''A1''
   AND A3.PRD_INST_ID = A4.OFR_DETAIL_INST_REF_ID
   AND A1.ACCT_ID = A3.ACCT_ID
   AND A2.EFF_STATE = ''10A''
   AND A1.ACCT_ID = A2.ACCT_ID
   AND A1.PRD_INST_ID = :1' using PRD_INST_ID.PRD_INST_ID;

  end loop;

  --采集SQL消耗信息
  insert into t_sql
    select * from v$sql where sql_text like 'SELECT 2,        A1%';

  --采集会话消耗资源信息
  select distinct sid into n_sid from v$mystat;
  insert into t_index_keep_monitor
    select t.SID, n.NAME, t.VALUE, sysdate cjsj
      from v$sesstat t, v$statname n
     where t.STATISTIC# = n.STATISTIC#
       and n.STATISTIC# in (12, 78, 89, 8, 584, 583, 581, 582, 588)
       and t.SID = n_sid;
  commit;
end;

anbob

用TOM 的runstats_pgk

fengbao

runstats 这个包不太适合我测试的情况。
由于这个SQL的查询条件的重复量很低，虽然走的索引，但是物理读比较高，想测试一下把index storage到keep pool中，看看效果情况

fengbao

改了一下PL/SQL，现在可以正常采集了：

declare
  n_sid  number;
  v_var1 varchar2(4000);
  d_start date;
  d_end date;
begin
  for PRD_INST_ID in (select PRD_INST_ID
                        from CRM.TB_PRD_PRD_INST_562) loop
                       d_start:=sysdate;
    execute immediate 'SELECT decode(count(*),
                  0,
                  to_char(1),
                  max(A1.PRD_INST_ID || A1.PRD_INST_ID || A1.ACCT_ID ||
                      A1.OWN_CUST_ID || A1.OFR_ID || A1.IF_PREPAY ||
                      A1.ACCT_ID || A1.ACCT_ID || A2.PAY_METHOD ||
                      A2.ACCT_ID || A2.EFF_STATE || A3.ACCT_ID ||
                      A3.PRD_INST_ID || A4.OFR_ID || A4.OFR_ID ||
                      A4.OFR_DETAIL_INST_REF_ID || A4.OFR_INST_ID ||
                      A4.OFR_DETAIL_TYPE_ID || A5.OFR_INST_ID ||
                      A5.OFR_INST_STAS_ID))

      FROM CRM.TB_PRD_PRD_INST_562        A1,
           CRM.TB_BIL_ACCT_562            A2,
           CRM.TB_PRD_PRD_INST_562        A3,
           CRM.TB_PRD_OFR_DETAIL_INST_562 A4,
           CRM.TB_PRD_OFR_INST_562        A5
     WHERE A5.OFR_INST_STAS_ID = 1001
       AND A4.OFR_INST_ID = A5.OFR_INST_ID
       AND A4.OFR_DETAIL_TYPE_ID = ''A1''
       AND A3.PRD_INST_ID = A4.OFR_DETAIL_INST_REF_ID
       AND A1.ACCT_ID = A3.ACCT_ID
       AND A2.EFF_STATE = ''10A''
       AND A1.ACCT_ID = A2.ACCT_ID
       AND A1.PRD_INST_ID = :1 '
      into v_var1 using PRD_INST_ID.PRD_INST_ID
      ;
      d_end:=sysdate;
      insert into temp_record(id,d_start,d_end) values(PRD_INST_ID.PRD_INST_ID,d_start,d_end);
      commit;
  --dbms_output.put_line(v_var1);
  end loop;

  --采集SQL消耗信息
  insert into t_sql
    select * from v$sql where sql_text like 'SELECT decode(count(*),                   0,                   to_char(1)';

  --采集会话消耗资源信息
  select distinct sid into n_sid from v$mystat;
  insert into t_index_keep_monitor
    select t.SID, n.NAME, t.VALUE, sysdate cjsj
      from v$sesstat t, v$statname n
     where t.STATISTIC# = n.STATISTIC#
       and n.STATISTIC# in (12, 78, 89, 8, 584, 583, 581, 582, 588)
       and t.SID = n_sid;
  commit;
end;