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.
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;
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-lReceived on Fri Jan 25 2013 - 08:31:01 CET