Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Manoj.

Asked: June 19, 2018 - 3:00 pm UTC

Last updated: June 20, 2018 - 3:11 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

I was trying to create a dashboard comparing historical executions and current executions of multiple SQL statements. I have noticed some differences between stats in GV$SQL and DBA_HIST_SQLSTAT. Could you please help us to understand below point.

For eg: For one SQL_ID i have 37 rows in DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOT (has same PHV and same child number for all). Sum of executions_delta column gives me 91. However, as per GV$SQL, EXECUTIONS is only 40.

Min begin interval time from DBA_HIST_SNAPSHOT is 23/May/2018 and max end interval time is 5/June/2018. First_load_time and last_load_time in v$sql is 31 Jan 2018 and 18 June 2018 respectively.

Please help me to understand why DBA_HIST_SNAPSHOT could have more number of executions within a shorter period of time comparing to gv$sql values.




and Chris said...

If the statement drops out of the cache then the number of executions is reset:

select /* FLUSH_ME */* from dual;
select /* FLUSH_ME */* from dual;
select /* FLUSH_ME */* from dual;
select /* FLUSH_ME */* from dual;

exec dbms_lock.sleep(15);

select sql_id, executions, address, hash_value, first_load_time
from   v$sql
where  sql_text = 'select /* FLUSH_ME */* from dual';

SQL_ID          EXECUTIONS   ADDRESS            HASH_VALUE   FIRST_LOAD_TIME       
afjuux59dc7ty              4 0000000097BF3F70     1389764414 2018-06-20/07:39:23

exec sys.dbms_shared_pool.purge('0000000097BF3F70,1389764414', 'C'); 

select sql_id, executions, address, hash_value, first_load_time
from   v$sql
where  sql_text = 'select /* FLUSH_ME */* from dual';

no rows selected

select /* FLUSH_ME */* from dual;

select sql_id, executions, address, hash_value, first_load_time
from   v$sql
where  sql_text = 'select /* FLUSH_ME */* from dual';

SQL_ID          EXECUTIONS   ADDRESS            HASH_VALUE   FIRST_LOAD_TIME       
afjuux59dc7ty              1 0000000097BF3F70     1389764414 2018-06-20/07:39:23 


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database