ASH Report – sql_id

SELECT
  trunc(begin_interval_time, 'WW'),
  ROUND(sum(elapsed_time_delta)/sum(executions_delta)/1000)
FROM dba_hist_sqlstat a
JOIN dba_hist_snapshot b
ON a.snap_id = b.snap_id
WHERE sql_id        = '&sql_id'
AND executions_delta>0
GROUP BY trunc(begin_interval_time, 'WW')
ORDER by trunc(begin_interval_time, 'WW') DESC

http://portrix-systems.de/blog/brost/tracking-historic-sql-execution-time-with-ash/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s