Gather stats on the table

 col TABLE_NAME for a30

col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from DBA_TAB_STATISTICS where STALE_STATS='YES';

OR

select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where OWNER='&OWNER' AND STALE_STATS='YES';

set linesize 200 trimspool on
set pagesize 100
set pause off echo off verify off
set head on term on 

col begin_interval_time format a30
col min_sess_per_day format 99,999 head 'MIN|SESS|PER|DAY'
col max_sess_per_day format 99,999 head 'MAX|SESS|PER|DAY'
col avg_sess_per_day format 99,999 head 'AVG|SESS|PER|DAY'
col min_proc_per_day format 99,999 head 'MIN|PROC|PER|DAY'
col max_proc_per_day format 99,999 head 'MAX|PROC|PER|DAY'
col avg_proc_per_day format 99,999 head 'AVG|PROC|PER|DAY'

with processes as (
	select snap_id, CURRENT_UTILIZATION process_count
	from dba_hist_RESOURCE_LIMIT
	where resource_name = 'processes'
),
sessions as (
	select snap_id, CURRENT_UTILIZATION session_count
	from dba_hist_RESOURCE_LIMIT
	where resource_name = 'sessions'
)
select distinct
	sn.begin_interval_time
	, min(s.session_count) over ( partition by trunc(sn.begin_interval_time)) min_sess_per_day
	, max(s.session_count) over ( partition by trunc(sn.begin_interval_time)) max_sess_per_day
	, round(avg(s.session_count) over ( partition by trunc(sn.begin_interval_time)),1) avg_sess_per_day
	, min(p.process_count) over ( partition by trunc(sn.begin_interval_time)) min_proc_per_day
	, max(p.process_count) over ( partition by trunc(sn.begin_interval_time)) max_proc_per_day
	, round(avg(p.process_count) over ( partition by trunc(sn.begin_interval_time)),1) avg_proc_per_day
from dba_hist_snapshot sn
right outer join sessions s on s.snap_id = sn.snap_id
right outer join processes p on p.snap_id = sn.snap_id
order by sn.begin_interval_time
/

SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS WHERE TABLE_NAME='&TNAME';

-- awr-top-sqlid-events.sql

-- get top 5 events per AWR snapshot, per sql_id

-- Jared Still still@pythian.com jkstill@gmail.com

--

-- 2016-11-02 jkstill - added enqueue decode

-- requires https://github.com/jkstill/oracle-script-lib/blob/master/get_date_range.sql

-- prompt for date range

@get_date_range

-- or just specify it here

--@get_date_range '2018-07-01 00:00:00' '2018-07-12 08:00:00'

@clears 

set linesize 200 trimspool on

set pagesize 60

col event format a30

col p1text format a20

col p1 format a25

col p2text format a20

spool aws-top-sqlid-event.log

break on begin_interval skip 1

with snaps as (

select 

min(snap_id) min_snap_id

, max(snap_id) max_snap_id

from dba_hist_snapshot

where begin_interval_time >= to_date(:v_begin_date,'&d_date_format')

and end_interval_time <= to_date(:v_end_date,'&d_date_format')

),

data as (

select 

h.snap_id

, h.dbid

, h.instance_number

, case h.session_state

when 'ON CPU' then 'CPU'

else h.event

end event

, h.p1text

, CASE

WHEN event like 'enq%' THEN

'0x'||trim(to_char(h.p1, 'XXXXXXXXXXXXXXXX'))||': '||

chr(bitand(h.p1, 4278190080)/16777216)||

chr(bitand(h.p1,16711680)/65536)||

' mode '||bitand(h.p1, power(2,14)-1)

ELSE NULL 

END AS p1

, h.sql_id

from dba_hist_active_sess_history h

where h.sql_id is not null

),

agg_data as (

select  distinct

to_char(hs.begin_interval_time,'&d_date_format') begin_interval

, d.instance_number

, d.sql_id

, d.event

, d.p1text

, d.p1

, count(d.event) over (partition by d.snap_id, d.sql_id, d.event, d.instance_number) event_count

from data d

join dba_hist_snapshot hs on hs.snap_id = d.snap_id

and hs.instance_number = d.instance_number

and hs.dbid = d.dbid

and hs.snap_id between (select min_snap_id from snaps)

and (select max_snap_id from snaps)

--where rownum <= 200

order by begin_interval, event_count desc, instance_number

), 

rank_output as (

select

begin_interval

, instance_number

, sql_id

, event

, p1text

, p1

, event_count

, row_number() over (partition by begin_interval, instance_number order by event_count desc) event_rank

from agg_data

)

select 

begin_interval

, instance_number

, sql_id

, event

, p1text

, p1

, event_count

, event_rank

from rank_output

where event_rank <= 5

order by begin_interval, event_count desc

/

spool off

ed aws-top-sqlid-event.log