Oracle Analytic SQL Practical Examples

 

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;