Re: DBA_HIST_SQLSTAT "oddity" - Executions & Executions_Delta = 0

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Thu, 24 Jan 2013 23:31:01 -0800 (PST)
Message-ID: <1359099061.16536.YahooMailNeo_at_web161302.mail.bf1.yahoo.com>



Chris,
Execute immediate statements within pl/sql which are tracked in v$sqlstats (and v$sql) can make it to AWR. Other than DDL statements, remaining statements should appear in awr unless that sql ages out of shared pool.
23:02:35 SQL> declare
23:02:35   2  a number;
23:02:35   3  begin
23:02:35   4  for i in 1..100 loop
23:02:35   5  execute immediate 'select /* test999 */ 1  from dual' into a;
23:02:35   6  execute immediate 'alter /* test999 */ session set cursor_sharing=force';
23:02:35   7  end loop;
23:02:35   8  end;
23:02:35   9  /

PL/SQL procedure successfully completed.

23:02:35 SQL> 
23:02:35 SQL> select executions, sql_text from v$sql where sql_text like '%test999%' and sql_text not like '%v$sql%';

EXECUTIONS SQL_TEXT

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

       100 select /* test999 */ 1  from dual
         1 declare a number; begin for i in 1..100 loop exec
           ute immediate 'select /* test999 */ 1  from dual'
            into a; execute immediate 'alter /* test999 */ s
           ession set cursor_sharing=force'; end loop; end;

Thanks,
 Sai
http://sai-oracle.blogspot.com

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jan 25 2013 - 08:31:01 CET

Original text of this message