top of page
  • Writer's picturekyle Hailey

SQL timings for ASH II

Here is a max, min, avg  execution time  for SQL executions in ASH data along with count of execution and histogram of their execution times

(tomorrow I’ll look at showing the execution date time of the longest elapsed times)

col 1 for 99999
col 2 for 99999
col 3 for 9999
col 4 for 9999
col 5 for 9999
col av for 99999.9
with pivot_data as (
select
      sql_id,
      ct,
      mxdelta mx,
      mndelta mn,
      avdelta av,
      width_bucket(delta,0,mxdelta+.1,5) as bucket
from (
select
   sql_id,
   delta,
   count(*) OVER (PARTITION BY sql_id) ct,
   max(delta) OVER (PARTITION BY sql_id) mxdelta,
   min(delta) OVER (PARTITION BY sql_id) mndelta,
   avg(delta) OVER (PARTITION BY sql_id) avdelta
from (
   select
        sql_id,
        sql_exec_id,
        max(delta) delta
     from ( select
                 sql_id,
                 sql_exec_id,
              ((cast(sample_time    as date)) -
               (cast(sql_exec_start as date))) * (3600*24) delta
           from
              dba_hist_active_sess_history
           where sql_exec_id is not null
	)
     group by sql_id,sql_exec_id
)
)
)
select * from pivot_data
PIVOT ( count(*) FOR bucket IN (1,2,3,4,5))
order by mx,av
/
SQL_ID                CT         MX         MN       AV      1      2     3     4     5
------------- ---------- ---------- ---------- -------- ------ ------ ----- ----- -----
5k7vccwjr5ahd       2653       1963          0     33.4   2623     15     8     4     3
ds8cz0fb8w147        161       2531         13    273.8    136     18     5     1     1
bzyny95313u12        114       2599          0     46.5    113      0     0     0     1
0hbv80w9ypy0n        161       4089          0   1183.9     27    116     9     6     3
71fwb4n6a92fv         49       4481         30    676.9     38      6     2     2     1
0bujgc94rg3fj        604       4929          0     24.7    601      1     1     0     1
64dqhdkkw63fd       1083       7147          0      7.2   1082      0     0     0     1
990m08w8xav7s        591       7681          0     51.8    587      0     0     2     2
2spgk3k0f7quz        251      29607          0    546.1    247      2     0     0     2
497wh6n7hu14f         49      69438          0   5498.2     44      1     0     1     3
0 views0 comments

Recent Posts

See All

Comments


bottom of page