Selecting the Top n Records
select sql_id, BUFFER_GETS_DELTA
from (select sql_id,
BUFFER_GETS_DELTA,
dense_rank() over(order by BUFFER_GETS_DELTA desc) dr
from DBA_HIST_SQLSTAT) x
where dr <= 5;
Calculate 3 period Moving Average
select snap_id,
sql_id,
BUFFER_GETS_DELTA,
avg(BUFFER_GETS_DELTA) over(order by snap_id rows between 1 preceding and 1 following) MA_3snap_BG
from DBA_HIST_SQLSTAT
where sql_id = '0h6b2sajwb74n';
Calculate 3 Day Moving Average
select BEGIN_INTERVAL_TIME,
sql_id,
BUFFER_GETS_DELTA,
avg(BUFFER_GETS_DELTA) over(order by BEGIN_INTERVAL_TIME RANGE between interval '1' day preceding and interval '1' day following) MA_3day_BG
from DBA_HIST_SQLSTAT a, dba_hist_snapshot b
where a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
and a.SNAP_ID = b.SNAP_ID
and sql_id = '0h6b2sajwb74n';
Sort Set by Most Frequently Occurring Items
select sql_id, CNT, dense_rank() over(order by cnt desc) as rnk
from (select sql_id, count(*) as cnt from DBA_HIST_SQLSTAT group by sql_id) x
Identify Percentage of Total
select distinct sql_id, (sql_bg / total) * 100 as pct
from (select sql_id,
sum(BUFFER_GETS_DELTA) over() total,
sum(BUFFER_GETS_DELTA) over(partition by sql_id) sql_bg
from DBA_HIST_SQLSTAT) x
order by 2;
Reduce Skewing (ignore highest and lowest value within a set)
select sql_id, avg(BUFFER_GETS_DELTA) avg_bg
from (select sql_id,
BUFFER_GETS_DELTA,
min(BUFFER_GETS_DELTA) over(partition by sql_id) min_bg,
max(BUFFER_GETS_DELTA) over(partition by sql_id) max_bg
from DBA_HIST_SQLSTAT) x
where BUFFER_GETS_DELTA not in (min_bg, max_bg)
group by sql_id;
Convert Rows into Columns
select
max(case when object_type='TABLE‘ then object_name else null end) as TAB_NAME,
max(case when object_type='INDEX‘ then object_name else null end) as IDX_NAME
from (
select
object_type
,object_name
,row_number() over (partition by object_type order by object_name) rn
from dba_objects
where object_type in ('TABLE', 'INDEX' )
) x
group by rn
order by 1
;
Create fix size “buckets” of data
select segment_name ceil(row_number() over(order by segment_name) / 50) grp,
from DBA_SEGMENTS;
Create pre-defined number of data “buckets”
select ntile(10) over(order by segment_name) grp, segment_name
from DBA_SEGMENTS;
Mix Aggregates from multiple groups
select sql_id,
module,
sum(BUFFER_GETS_DELTA) over(partition by module) module_cnt,
PARSING_SCHEMA_NAME,
sum(BUFFER_GETS_DELTA) over(partition by PARSING_SCHEMA_NAME) PARSING_SCHEMA_NAME_cnt,
sum(BUFFER_GETS_DELTA) over() total
from DBA_HIST_SQLSTAT;
Aggregating across moving historical ranges
select distinct x.*
from (select trunc(BEGIN_INTERVAL_TIME) SNAP_Date,
sum(BUFFER_GETS_DELTA) over(partition by trunc(BEGIN_INTERVAL_TIME)) snap_bg,
sum(BUFFER_GETS_DELTA) over(order by trunc(BEGIN_INTERVAL_TIME) range between 14 preceding and current row) prior_2week_bg
from DBA_HIST_SQLSTAT a, dba_hist_snapshot b
where a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
and a.SNAP_ID = b.SNAP_ID) x;
Identify Consecutive Ranges of Time
select a.sql_id,
b.BEGIN_INTERVAL_TIME snap_time,
lead(b.BEGIN_INTERVAL_TIME) over(order by a.snap_id) next_snap_time
from DBA_HIST_SQLSTAT a, dba_hist_snapshot b
where a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
and a.SNAP_ID = b.SNAP_ID
and sql_id = '83taa7kaw59c1';
Access Future and Historical rows
select snap_id,
BUFFER_GETS_DELTA,
nvl(lead(BUFFER_GETS_DELTA)
over(order by snap_id, BUFFER_GETS_DELTA),
null) forward,
nvl(lag(BUFFER_GETS_DELTA) over(order by snap_id, BUFFER_GETS_DELTA),
null) backward
from (select snap_id, sum(BUFFER_GETS_DELTA) BUFFER_GETS_DELTA
from DBA_HIST_SQLSTAT
group by snap_id) x
order by 1;
Select every N-th row
select SNAP_ID
from (select snap_id, row_number() over(order by snap_id) rn
from dba_hist_snapshot) x
where mod(rn, 5) = 1;
Remove Duplicate without use of Distinct
select sql_id
from (select sql_id,
row_number() over(partition by sql_id order by sql_id) rn
from DBA_HIST_SQLSTAT) x
where rn = 1;