date:time of execution

[big]

Hi,
I'm looking for a query to find date/time of execution, during last 10 days, of a query that I know its sql_id .
Can you help me?

I querried v$sql_plan but surprisingli that sql_id is not there.

Thanks.

[ErmanArslansOracleBlog]

you can get that info, I mean you can get execution statistics and history for a SQL using sql_id using Data Dictionary queries for both queries in memory and those in the AWR.

See -> How to get execution statistics and history for a SQL (Doc ID 1371778.1)

[big]

Hi,

I made this:
select STARTUP_TIME from DBA_HIST_SNAPSHOT where snap_id in (select  snap_id
from dba_hist_sqlstat
where sql_id = 'sql_id' );

But the rows returned are only in december of 23.
 But in our AWR report for 16/02/2024 and 19/02/2024 we can see that sql_id executed!!!!

By the way

select max(STARTUP_TIME), min ( STARTUP_TIME)from DBA_HIST_SNAPSHOT;

MAX(STARTUP_TIME)               MIN(STARTUP_TIME)              
------------------------------- -------------------------------
03-DEC-23 03.39.19.000000000 PM 03-DEC-23 03.39.19.000000000 PM

Any suggestion?

Thank you.

[ErmanArslansOracleBlog]

What about using -> dba_hist_sqlstat directly? If you want to check the snapshot, then check it using dba_hist_sqlstat.snap_id.

See the definition of the dictionary table ->

DBA_HIST_SQLSTAT displays historical information about SQL statistics. This view captures the top SQL statements based on a set of criteria and captures the statistics information from V$SQL.
"The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view."

I already shared you a MOS note for this, and there you ll find the following query ->

select sql_id, snap_id, plan_hash_value, sql_profile, executions_total,
trunc(decode(executions_total, 0, 0, rows_processed_total/executions_total)) rows_avg,
trunc(decode(executions_total, 0, 0, fetches_total/executions_total)) fetches_avg,
trunc(decode(executions_total, 0, 0, disk_reads_total/executions_total)) disk_reads_avg,
trunc(decode(executions_total, 0, 0, buffer_gets_total/executions_total)) buffer_gets_avg,
trunc(decode(executions_total, 0, 0, cpu_time_total/executions_total)) cpu_time_avg,
trunc(decode(executions_total, 0, 0, elapsed_time_total/executions_total)) elapsed_time_avg,
trunc(decode(executions_total, 0, 0, iowait_total/executions_total)) iowait_time_avg,
trunc(decode(executions_total, 0, 0, clwait_total/executions_total)) clwait_time_avg,
trunc(decode(executions_total, 0, 0, apwait_total/executions_total)) apwait_time_avg,
trunc(decode(executions_total, 0, 0, ccwait_total/executions_total)) ccwait_time_avg,
trunc(decode(executions_total, 0, 0, plsexec_time_total/executions_total)) plsexec_time_avg,
trunc(decode(executions_total, 0, 0, javexec_time_total/executions_total)) javexec_time_avg
from dba_hist_sqlstat
where sql_id = '&sql_id'
order by sql_id, snap_id;

[big]

Yes I read document 1371778.1 and was inspired from
And I ran:
select STARTUP_TIME from DBA_HIST_SNAPSHOT where snap_id in (select  snap_id
from dba_hist_sqlstat
where sql_id = 'sql_id' );

But I was wrong and I should use BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME as it is mentionned instead of STARTUP_TIME.


Thanks and regards.

[ErmanArslansOracleBlog]

Good. So you are okay now then..