Oracle Parallel Execution Deep Dive Session

Here is a recording available on Youtube of a session I did a while ago, covering how to understand the essentials of Oracle Parallel Execution and how to read the corresponding execution plans.

Video Tutorial: XPLAN_ASH Active Session History - Part 12

The final part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

This is the final part of this tutorial, but there are more tutorials coming - about configuring the script, script internals and also the Rowsource Statistics mode of the script.

Video Tutorial: XPLAN_ASH Active Session History - Part 11

The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

More parts to follow.

Video Tutorial: XPLAN_ASH Active Session History - Part 10

The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

More parts to follow.

Video Tutorial: XPLAN_ASH Active Session History - Part 9

The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

More parts to follow.

Video Tutorial: XPLAN_ASH Active Session History - Part 8

The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

More parts to follow.

Video Tutorial: XPLAN_ASH Active Session History - Part 7

The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

More parts to follow.

Video Tutorial: XPLAN_ASH Active Session History - Part 6

The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

More parts to follow.

Video Tutorial: XPLAN_ASH Active Session History - Part 5

The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

More parts to follow.

Video Tutorial: XPLAN_ASH Active Session History - Part 4

The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

More parts to follow.

Video Tutorial: XPLAN_ASH Active Session History - Part 3

The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

More parts to follow.

Video Tutorial: XPLAN_ASH Active Session History - Part 2

The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality has been published. In this part I begin the actual walk-through of the script output.

More parts to follow.

Video Tutorial: XPLAN_ASH Active Session History - Introduction

I finally got around preparing another part of the XPLAN_ASH video tutorial.

This part is about the main funcationality of XPLAN_ASH: SQL statement execution analysis using Active Session History and Real-Time SQL Monitoring.

In this video tutorial I'll explain what the output of XPLAN_ASH is supposed to mean when using the Active Session History functionality of the script. Before diving into the details of the script output using sample reports I provide some overview and introduction in this part that hopefully makes it simpler to understand how the output is organized and what it is supposed to mean.

This is the initial, general introduction part. More parts to follow.

Parallel Execution Skew - Summary

I've published the final part of my video tutorial and the final part of my mini series "Parallel Execution Skew" at AllThingsOracle.com concluding what I planned to publish on the topic of Parallel Execution Skew.

Anyone regularly using Parallel Execution and/or relying on Parallel Execution for important, time critical processing should know this stuff. In my experience however almost no-one does, and therefore misses possibly huge opportunities for optimizing Parallel Execution performance.

Since all this was published over a longer period of time this post therefore is a summary with pointers to the material.

If you want to get an idea what the material is about, the following video summarizes the content:

Parallel Execution Skew in less than four minutes

Video Tutorial "Analysing Parallel Execution Skew":

Part 1: Introduction
Part 2: DFOs and DFO Trees
Part 3: Without Diagnostics / Tuning Pack license
Part 4: Using Diagnostics / Tuning Pack license

"Parallel Execution Skew" series at AllThingsOracle.com:

Part 1: Introduction
Part 2: Demonstrating Skew
Part 3: 12c Hybrid Hash Distribution With Skew Detection
Part 4: Addressing Skew Using Manual Rewrites
Part 5: Skew Caused By Outer Joins

Analysing Parallel Execution Skew - Without Diagnostics / Tuning Pack License

This is the third part of the video tutorial "Analysing Parallel Execution Skew". In this part I show how to analyse a parallel SQL execution regarding Parallel Execution Skew.

If you don't have a Diagnostics / Tuning Pack license the options you have for doing that are quite limited, and the approach, as demonstrated in the tutorial, has several limitations and shortcomings.

Here is a link to the video on my Youtube channel.

If you want to reproduce or play around with the examples shown in the tutorial here is the script for creating the tables and running the queries / DML commands used in the tutorial. A shout goes out to Christo Kutrovsky at Pythian who I think was the one who inspired the beautified version on V$PQ_TQSTAT.

---------------------
-- Links for S-ASH --
---------------------
--
-- http://www.perfvision.com/ash.php
-- http://www.pythian.com/blog/trying-out-s-ash/
-- http://sourceforge.net/projects/orasash/files/v2.3/
-- http://sourceforge.net/projects/ashv/
---------------------

-- Table creation
set echo on timing on time on

drop table t_1;

purge table t_1;

drop table t_2;

purge table t_2;

drop table t_1_part;

purge table t_1_part;

drop table t_2_part;

purge table t_2_part;

drop table t1;

purge table t1;

drop table t2;

purge table t2;

drop table t3;

purge table t3;

drop table t4;

purge table t4;

drop table t5;

purge table t5;

drop table x;

purge table x;

create table t1
as
select  /*+ use_nl(a b) */
        (rownum * 2) as id
      , rownum as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1000) */ * from dual
connect by
        level <= 1000) a, (select /*+ cardinality(2) */ * from dual connect by level <= 2) b
;

exec dbms_stats.gather_table_stats(null, 't1')

alter table t1 cache;

create table t2
compress
as
select
        (rownum * 2) + 1 as id
      , mod(rownum, 2000) + 1 as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1000000) */ * from dual
connect by
        level <= 1000000) a, (select /*+ cardinality(2) */ * from dual connect by level <= 2) b
;

exec dbms_stats.gather_table_stats(null, 't2')

alter table t2 cache;

create table t3
as
select  /*+ use_nl(a b) */
        (rownum * 2) as id
      , rownum as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1000) */ * from dual
connect by
        level <= 1000) a, (select /*+ cardinality(2) */ * from dual connect by level <= 2) b
;

exec dbms_stats.gather_table_stats(null, 't3')

alter table t3 cache;

create table t4
compress
as
select
        (rownum * 2) + 1 as id
      , mod(rownum, 2000) + 1 as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1000000) */ * from dual
connect by
        level <= 1000000) a, (select /*+ cardinality(2) */ * from dual connect by level <= 2) b
;

exec dbms_stats.gather_table_stats(null, 't4')

alter table t4 cache;

create table t5
as
select  /*+ use_nl(a b) */
        (rownum * 2) as id
      , rownum as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1000) */ * from dual
connect by
        level <= 1000) a, (select /*+ cardinality(2) */ * from dual connect by level <= 2) b
;

exec dbms_stats.gather_table_stats(null, 't5')

alter table t5 cache;

create table x
compress
as
select * from t2
where 1 = 2;

create unique index x_idx1 on x (id);

alter table t1 parallel 2;

alter table t2 parallel 2;

alter table t3 parallel 15;

alter table t4 parallel 15;

alter table t5 parallel 15;

create table t_1
compress
as
select  /*+ use_nl(a b) */
        rownum as id
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1e5) */ * from dual
connect by
        level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't_1')

create table t_2
compress
as
select
        rownum as id
      , case when rownum <= 5e5 then mod(rownum, 2e6) + 1 else 1 end as fk_id_skew
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1e5) */ * from dual
connect by
        level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't_2', method_opt=>'for all columns size 1', no_invalidate=>false)

alter table t_1 parallel 8 cache;

alter table t_2 parallel 8 cache;

create table t_1_part
partition by hash(id) partitions 8
compress
as
select  /*+ use_nl(a b) */
        rownum as id
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1e5) */ * from dual
connect by
        level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't_1_part')

create table t_2_part
partition by hash(fk_id_skew) partitions 8
compress
as
select
        rownum as id
      , case when rownum <= 5e5 then mod(rownum, 2e6) + 1 else 1 end as fk_id_skew
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1e5) */ * from dual
connect by
        level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't_2_part', method_opt=>'for all columns size 1', no_invalidate=>false)

alter table t_1_part parallel 8 cache;

alter table t_2_part parallel 8 cache;

---------------------------------------------------------------
-- Single DFO tree (with Parallel Execution Skew), many DFOs --
---------------------------------------------------------------

set echo on timing on time on verify on

define num_cpu = "14"

alter session set workarea_size_policy = manual;

alter session set sort_area_size = 200000000;

alter session set sort_area_size = 200000000;

alter session set hash_area_size = 200000000;

alter session set hash_area_size = 200000000;

select
        max(t1_id)
      , max(t1_filler)
      , max(t2_id)
      , max(t2_filler)
      , max(t3_id)
      , max(t3_filler)
from    (
          select  /*+ monitor
                     no_merge
                     no_merge(v_1)
                     no_merge(v_5)
                     parallel(t1 &num_cpu)
                     PQ_DISTRIBUTE(T1 HASH HASH)
                     PQ_DISTRIBUTE(V_5 HASH HASH)
                     leading (v_1 v_5 t1)
                     use_hash(v_1 v_5 t1)
                     swap_join_inputs(t1)
                  */
                  t1.id     as t1_id
                , regexp_replace(v_5.t3_filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as t1_filler
                , v_5.*
          from    (
                    select  /*+ parallel(t2 &num_cpu)
                                parallel(t3 &num_cpu)
                                leading(t3 t2)
                                use_hash(t3 t2)
                                swap_join_inputs(t2)
                                PQ_DISTRIBUTE(T2 HASH HASH)
                            */
                            t2.id     as t2_id
                          , t2.filler as t2_filler
                          , t2.id2    as t2_id2
                          , t3.id     as t3_id
                          , t3.filler as t3_filler
                    from
                            t1 t2
                          , t2 t3
                    where
                            t3.id2 = t2.id2 (+)
                    and     regexp_replace(t3.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler (+), '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
                    and     mod(t3.id2, 3) = 0
                  ) v_1
                , (
                    select  /*+ parallel(t2 &num_cpu)
                                parallel(t3 &num_cpu)
                                leading(t3 t2)
                                use_hash(t3 t2)
                                swap_join_inputs(t2)
                                PQ_DISTRIBUTE(T2 HASH HASH)
                            */
                            t2.id     as t2_id
                          , t2.filler as t2_filler
                          , t2.id2    as t2_id2
                          , t3.id     as t3_id
                          , t3.filler as t3_filler
                    from
                            t1 t2
                          , t2 t3
                    where
                            t3.id = t2.id (+)
                    and     regexp_replace(t3.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler (+), '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
                    and     mod(t3.id2, 3) = 0
                  ) v_5
                , t1
          where
                  v_1.t3_id = v_5.t3_id
          and     v_5.t2_id2 = t1.id2 (+) + 2001
          and     regexp_replace(v_5.t3_filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t1.filler (+), '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
        )
;

break on dfo_number nodup on tq_id nodup on server_type skip 1 nodup on instance nodup

-- compute sum label Total of num_rows on server_type

select
        /*dfo_number
      , */tq_id
      , cast(server_type as varchar2(10)) as server_type
      , instance
      , cast(process as varchar2(8)) as process
      , num_rows
      , round(ratio_to_report(num_rows) over (partition by dfo_number, tq_id, server_type) * 100) as "%"
      , cast(rpad('#', round(num_rows * 10 / nullif(max(num_rows) over (partition by dfo_number, tq_id, server_type), 0)), '#') as varchar2(10)) as graph
      , round(bytes / 1024 / 1024) as mb
      , round(bytes / nullif(num_rows, 0)) as "bytes/row"
from
        v$pq_tqstat
order by
        dfo_number
      , tq_id
      , server_type desc
      , instance
      , process
;

---------------------------------------------------------------------------------------------------
-- Same statement with Parallel TEMP TABLE TRANSFORMATION, V$PQ_TQSTAT shows useless information --
---------------------------------------------------------------------------------------------------

set echo on timing on time on verify on

define num_cpu = "14"

alter session set workarea_size_policy = manual;

alter session set sort_area_size = 200000000;

alter session set sort_area_size = 200000000;

alter session set hash_area_size = 200000000;

alter session set hash_area_size = 200000000;

with result as
(
select /*+ materialize
           monitor
           no_merge
           no_merge(v_1)
           no_merge(v_5)
           parallel(t1 &num_cpu)
           PQ_DISTRIBUTE(T1 HASH HASH)
           PQ_DISTRIBUTE(V_1 HASH HASH)
           PQ_DISTRIBUTE(V_5 HASH HASH)
           leading (v_1 v_5 t1)
           use_hash(v_1 v_5 t1)
           swap_join_inputs(t1)
       */
       t1.id     as t1_id
     , regexp_replace(v_5.t3_filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as t1_filler
     , v_5.*
from   (
  select /*+ parallel(t2 &num_cpu) parallel(t3 &num_cpu) leading(t3 t2) use_hash(t3 t2) swap_join_inputs(t2) PQ_DISTRIBUTE(T2 HASH HASH) */
        t2.id     as t2_id
      , t2.filler as t2_filler
      , t2.id2    as t2_id2
      , t3.id     as t3_id
      , t3.filler as t3_filler
  from
        t1 t2
      , t2 t3
  where
        t3.id2 = t2.id2 (+)
  and   regexp_replace(t3.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler (+), '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
  and   mod(t3.id2, 3) = 0
)
 v_1
     , (
  select /*+ parallel(t2 &num_cpu) parallel(t3 &num_cpu) leading(t3 t2) use_hash(t3 t2) swap_join_inputs(t2) PQ_DISTRIBUTE(T2 HASH HASH) */
        t2.id     as t2_id
      , t2.filler as t2_filler
      , t2.id2    as t2_id2
      , t3.id     as t3_id
      , t3.filler as t3_filler
  from
        t1 t2
      , t2 t3
  where
        t3.id = t2.id (+)
  and   regexp_replace(t3.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler (+), '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
  and   mod(t3.id2, 3) = 0
) v_5
     , t1
where
       v_1.t3_id = v_5.t3_id
and    v_5.t2_id2 = t1.id2 (+) + 2001
and    regexp_replace(v_5.t3_filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t1.filler (+), '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
)
select max(t1_id), max(t1_filler), max(t2_id), max(t2_filler), max(t3_id), max(t3_filler) from
result;

break on dfo_number nodup on tq_id nodup on server_type skip 1 nodup on instance nodup

-- compute sum label Total of num_rows on server_type

select
        /*dfo_number
      , */tq_id
      , cast(server_type as varchar2(10)) as server_type
      , instance
      , cast(process as varchar2(8)) as process
      , num_rows
      , round(ratio_to_report(num_rows) over (partition by dfo_number, tq_id, server_type) * 100) as "%"
      , cast(rpad('#', round(num_rows * 10 / nullif(max(num_rows) over (partition by dfo_number, tq_id, server_type), 0)), '#') as varchar2(10)) as graph
      , round(bytes / 1024 / 1024) as mb
      , round(bytes / nullif(num_rows, 0)) as "bytes/row"
from
        v$pq_tqstat
order by
        dfo_number
      , tq_id
      , server_type desc
      , instance
      , process
;

--------------------------------------------------------------------------------------------------
-- This construct results in misleading information from V$PQ_TQSTAT (actually a complete mess) --
--------------------------------------------------------------------------------------------------

set echo on timing on time on

alter session enable parallel dml;

truncate table x;

insert  /*+ append parallel(x 4) */ into x
select  /*+ leading(v1 v2) optimizer_features_enable('11.2.0.1') */
        v_1.id
      , v_1.id2
      , v_1.filler
from    (
           select
                   id
                 , id2
                 , filler
           from    (
                      select  /*+ parallel(t2 4) no_merge */
                              rownum as id
                            , t2.id2
                            , t2.filler
                      from
                              t2
                      where
                              mod(t2.id2, 3) = 0
                      and     regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
                   ) v1
        ) v_1
      , (
          select
                   id
                 , id2
                 , filler
          from     (
                     select  /*+ parallel(t2 8) no_merge */
                             rownum as id
                           , t2.id2
                           , t2.filler
                     from
                             t2
                     where
                             mod(t2.id2, 3) = 0
                     and     regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
                   ) v2
        ) v_2
where
        v_1.id = v_2.id
and     v_1.filler = v_2.filler
;

-- Parallel DML requires a COMMIT before querying V$PQ_TQSTAT
commit;

break on dfo_number nodup on tq_id nodup on server_type skip 1 nodup on instance nodup

compute sum label Total of num_rows on server_type

select
        dfo_number
      , tq_id
      , cast(server_type as varchar2(10)) as server_type
      , instance
      , cast(process as varchar2(8)) as process
      , num_rows
      , round(ratio_to_report(num_rows) over (partition by dfo_number, tq_id, server_type) * 100) as "%"
      , cast(rpad('#', round(num_rows * 10 / nullif(max(num_rows) over (partition by dfo_number, tq_id, server_type), 0)), '#') as varchar2(10)) as graph
      , round(bytes / 1024 / 1024) as mb
      , round(bytes / nullif(num_rows, 0)) as "bytes/row"
from
        v$pq_tqstat
order by
        dfo_number
      , tq_id
      , server_type desc
      , instance
      , process
;

----------------------------------------------------------------------
-- Single DFO tree (with Parallel Execution Skew, almost no impact) --
----------------------------------------------------------------------

set echo on timing on time on

alter session set workarea_size_policy = manual;

alter session set sort_area_size = 500000000;

alter session set sort_area_size = 500000000;

alter session set hash_area_size = 500000000;

alter session set hash_area_size = 500000000;

select  /*+ leading(v1)
            use_hash(t_1)
            no_swap_join_inputs(t_1)
            pq_distribute(t_1 hash hash)
        */
        max(t_1.filler)
      , max(v1.t_1_filler)
      , max(v1.t_2_filler)
from
        t_1
      , (
          select  /*+ no_merge
                      leading(t_1 t_2)
                      use_hash(t_2)
                      no_swap_join_inputs(t_2)
                      pq_distribute(t_2 hash hash) */
                  t_1.id as t_1_id
                , t_1.filler as t_1_filler
                , t_2.id as t_2_id
                , t_2.filler as t_2_filler
          from    t_1
                , t_2
          where
                  t_2.fk_id_skew = t_1.id
        ) v1
where
        v1.t_2_id = t_1.id
and     regexp_replace(v1.t_2_filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t_1.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
and     regexp_replace(v1.t_2_filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i') >= regexp_replace(t_1.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
;

break on dfo_number nodup on tq_id nodup on server_type skip 1 nodup on instance nodup

-- compute sum label Total of num_rows on server_type

select
        /*dfo_number
      , */tq_id
      , cast(server_type as varchar2(10)) as server_type
      , instance
      , cast(process as varchar2(8)) as process
      , num_rows
      , round(ratio_to_report(num_rows) over (partition by dfo_number, tq_id, server_type) * 100) as "%"
      , cast(rpad('#', round(num_rows * 10 / nullif(max(num_rows) over (partition by dfo_number, tq_id, server_type), 0)), '#') as varchar2(10)) as graph
      , round(bytes / 1024 / 1024) as mb
      , round(bytes / nullif(num_rows, 0)) as "bytes/row"
from
        v$pq_tqstat
order by
        dfo_number
      , tq_id
      , server_type desc
      , instance
      , process
;

--------------------------------------------------------------------------------------------------------------------------------
-- Full Partition Wise Join with partition skew - V$PQ_TQSTAT is of no help, since no redistribution takes place (single DFO) --
--------------------------------------------------------------------------------------------------------------------------------

set echo on timing on time on

alter session set workarea_size_policy = manual;

alter session set sort_area_size = 500000000;

alter session set sort_area_size = 500000000;

alter session set hash_area_size = 500000000;

alter session set hash_area_size = 500000000;

select count(t_2_filler) from (
select  /*+ monitor
            leading(t_1 t_2)
            use_hash(t_2)
            no_swap_join_inputs(t_2)
            pq_distribute(t_2 none none)
        */
        t_1.id as t_1_id
      , t_1.filler as t_1_filler
      , t_2.id as t_2_id
      , t_2.filler as t_2_filler
from    t_1_part t_1
      , t_2_part t_2
where
        t_2.fk_id_skew = t_1.id
and     regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t_1.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
);

break on dfo_number nodup on tq_id nodup on server_type skip 1 nodup on instance nodup

-- compute sum label Total of num_rows on server_type

select
        /*dfo_number
      , */tq_id
      , cast(server_type as varchar2(10)) as server_type
      , instance
      , cast(process as varchar2(8)) as process
      , num_rows
      , round(ratio_to_report(num_rows) over (partition by dfo_number, tq_id, server_type) * 100) as "%"
      , cast(rpad('#', round(num_rows * 10 / nullif(max(num_rows) over (partition by dfo_number, tq_id, server_type), 0)), '#') as varchar2(10)) as graph
      , round(bytes / 1024 / 1024) as mb
      , round(bytes / nullif(num_rows, 0)) as "bytes/row"
from
        v$pq_tqstat
order by
        dfo_number
      , tq_id
      , server_type desc
      , instance
      , process
;

Analysing Parallel Execution Skew - Data Flow Operations (DFOs) And DFO Trees

This is the second part of the video tutorial "Analysing Parallel Execution Skew". In this part I introduce the concept of "Data Flow Operations (DFOs)" and "DFO Trees", which is what a Parallel Execution plan is made of. DFOs / DFO Trees are specific to Parallel Execution and don't have any counterpart in a serial execution plan.

Understanding the implications of DFOs / DFO Trees is important as prerequisite for understanding some of the effects shown in the later parts of the video tutorial, hence I covered this as a separate topic.

Note that this tutorial also demonstrates some new 12c features regarding Parallel Execution, in particular how Oracle 12c now lifts many of the previous limitations that lead to the generation of multiple DFO Trees.

Here is a link to the video on my Youtube channel.

If you want to reproduce and play around with the DFO Tree variations shown in the tutorial here is the script for creating the tables and running the queries / DML commands used in the tutorial:

-- Table creation
set echo on timing on time on

drop table t1;

purge table t1;

drop table t2;

purge table t2;

drop table t3;

purge table t3;

drop table t4;

purge table t4;

drop table t5;

purge table t5;

drop table x;

purge table x;

create table t1
as
select  /*+ use_nl(a b) */
        (rownum * 2) as id
      , rownum as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1000) */ * from dual
connect by
        level <= 1000) a, (select /*+ cardinality(2) */ * from dual connect by level <= 2) b
;

exec dbms_stats.gather_table_stats(null, 't1')

alter table t1 cache;

create table t2
compress
as
select
        (rownum * 2) + 1 as id
      , mod(rownum, 2000) + 1 as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1000000) */ * from dual
connect by
        level <= 1000000) a, (select /*+ cardinality(2) */ * from dual connect by level <= 2) b
;

exec dbms_stats.gather_table_stats(null, 't2')

alter table t2 cache;

create table t3
as
select  /*+ use_nl(a b) */
        (rownum * 2) as id
      , rownum as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1000) */ * from dual
connect by
        level <= 1000) a, (select /*+ cardinality(2) */ * from dual connect by level <= 2) b
;

exec dbms_stats.gather_table_stats(null, 't3')

alter table t3 cache;

create table t4
compress
as
select
        (rownum * 2) + 1 as id
      , mod(rownum, 2000) + 1 as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1000000) */ * from dual
connect by
        level <= 1000000) a, (select /*+ cardinality(2) */ * from dual connect by level <= 2) b
;

exec dbms_stats.gather_table_stats(null, 't4')

alter table t4 cache;

create table t5
as
select  /*+ use_nl(a b) */
        (rownum * 2) as id
      , rownum as id2
      , rpad('x', 100) as filler
from
        (select /*+ cardinality(1000) */ * from dual
connect by
        level <= 1000) a, (select /*+ cardinality(2) */ * from dual connect by level <= 2) b
;

exec dbms_stats.gather_table_stats(null, 't5')

alter table t5 cache;

create table x
compress
as
select * from t2
where 1 = 2;

create unique index x_idx1 on x (id);

alter table t1 parallel 2;

alter table t2 parallel 2;

alter table t3 parallel 15;

alter table t4 parallel 15;

alter table t5 parallel 15;

---------------------------------------------------------------
-- Single DFO tree (with Parallel Execution Skew), many DFOs --
---------------------------------------------------------------

set echo on timing on time on verify on

define num_cpu = "15"

select
        max(t1_id)
      , max(t1_filler)
      , max(t2_id)
      , max(t2_filler)
      , max(t3_id)
      , max(t3_filler)
from    (
          select  /*+ monitor
                     no_merge
                     no_merge(v_1)
                     no_merge(v_5)
                     parallel(t1 &num_cpu)
                     PQ_DISTRIBUTE(T1 HASH HASH)
                     PQ_DISTRIBUTE(V_5 HASH HASH)
                     leading (v_1 v_5 t1)
                     use_hash(v_1 v_5 t1)
                     swap_join_inputs(t1)
                  */
                  t1.id     as t1_id
                , regexp_replace(v_5.t3_filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as t1_filler
                , v_5.*
          from    (
                    select  /*+ parallel(t2 &num_cpu)
                                parallel(t3 &num_cpu)
                                leading(t3 t2)
                                use_hash(t3 t2)
                                swap_join_inputs(t2)
                                PQ_DISTRIBUTE(T2 HASH HASH)
                            */
                            t2.id     as t2_id
                          , t2.filler as t2_filler
                          , t2.id2    as t2_id2
                          , t3.id     as t3_id
                          , t3.filler as t3_filler
                    from
                            t1 t2
                          , t2 t3
                    where
                            t3.id2 = t2.id2 (+)
                    and     regexp_replace(t3.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler (+), '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
                    and     mod(t3.id2, 3) = 0
                  ) v_1
                , (
                    select  /*+ parallel(t2 &num_cpu)
                                parallel(t3 &num_cpu)
                                leading(t3 t2)
                                use_hash(t3 t2)
                                swap_join_inputs(t2)
                                PQ_DISTRIBUTE(T2 HASH HASH)
                            */
                            t2.id     as t2_id
                          , t2.filler as t2_filler
                          , t2.id2    as t2_id2
                          , t3.id     as t3_id
                          , t3.filler as t3_filler
                    from
                            t1 t2
                          , t2 t3
                    where
                            t3.id = t2.id (+)
                    and     regexp_replace(t3.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler (+), '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
                    and     mod(t3.id2, 3) = 0
                  ) v_5
                , t1
          where
                  v_1.t3_id = v_5.t3_id
          and     v_5.t2_id2 = t1.id2 (+) + 2001
          and     regexp_replace(v_5.t3_filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t1.filler (+), '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
        )
;

---------------------------------------------------------------------------------------------------------------------------------------------------
-- Multiple DFO trees no parent / child (with different DOPs), separate slave sets, one active after the other (12.1: Still multiple DFO trees)  --
---------------------------------------------------------------------------------------------------------------------------------------------------

set echo on timing on time on verify on

with a as (
select /*+ materialize monitor no_merge */
       t1.id     as t1_id
     , regexp_replace(t3_filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as t1_filler
     , v1.*
from   (
        select /*+ no_merge pq_distribute(t3 hash hash) */
              t2.id     as t2_id
            , t2.filler as t2_filler
            , t2.id2    as t2_id2
            , t3.id     as t3_id
            , t3.filler as t3_filler
        from
              t1 t2
            , t2 t3
        where
              t3.id2 = t2.id
        and   regexp_replace(t3.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
        and   mod(t3.id2, 2) = 0
       ) v1
     , t1
where
       v1.t2_id2 = t1.id2
),
b as (
select /*+ materialize monitor no_merge */
       t1.id     as t1_id
     , regexp_replace(t3_filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as t1_filler
     , v1.*
from   (
        select /*+ no_merge pq_distribute(t3 hash hash) */
              t2.id     as t2_id
            , t2.filler as t2_filler
            , t2.id2    as t2_id2
            , t3.id     as t3_id
            , t3.filler as t3_filler
        from
              t3 t2
            , t4 t3
        where
              t3.id2 = t2.id
        and   regexp_replace(t3.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
        and   mod(t3.id2, 2) = 0
       ) v1
     , t5 t1
where
       v1.t2_id2 = t1.id2
)
select max(t1_id), max(t1_filler), max(t2_id), max(t2_filler), max(t3_id), max(t3_filler) from (
select  /*+ no_merge */
        a.t1_id
      , a.t1_filler
      , a.t2_id
      , a.t2_filler
      , a.t3_id
      , regexp_replace(a.t3_filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as t3_filler
from
        a
      , b
where
        a.t3_id = b.t3_id
and     regexp_replace(a.t3_filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(b.t3_filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
and     mod(a.t1_id, 4) = 0
and     mod(b.t1_id, 4) = 0
)
;

-------------------------------------------------------------------------------------------------------------------------------
-- Multiple DFO trees parent / child (with different DOPs), separate slave sets, concurrently active (12.1: Single DFO tree) --
-------------------------------------------------------------------------------------------------------------------------------

set echo on timing on time on

alter session enable parallel dml;

truncate table x;

insert  /*+ append parallel(x 4) */ into x
select  /*+ leading(v1 v2) */
        v_1.id
      , v_1.id2
      , v_1.filler
from    (
           select
                   id
                 , id2
                 , filler
           from    (
                      select  /*+ parallel(t2 4) no_merge */
                              rownum as id
                            , t2.id2
                            , t2.filler
                      from
                              t2
                      where
                              mod(t2.id2, 3) = 0
                      and     regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
                   ) v1
        ) v_1
      , (
          select
                   id
                 , id2
                 , filler
          from     (
                     select  /*+ parallel(t2 8) no_merge */
                             rownum as id
                           , t2.id2
                           , t2.filler
                     from
                             t2
                     where
                             mod(t2.id2, 3) = 0
                     and     regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
                   ) v2
        ) v_2
where
        v_1.id = v_2.id
and     v_1.filler = v_2.filler
;

commit;

--------------------------------------------------------------------------------------------------------------------------------------
-- Multiple DFO trees parent / child (with different DOPs), separate slave sets, *not* concurrently active  (12.1: Single DFO tree) --
--------------------------------------------------------------------------------------------------------------------------------------

set echo on timing on time on

alter session enable parallel dml;

truncate table x;

insert  /*+ append parallel(x 4) */ into x
select
        v1.*
from    (
          select  /*+ parallel(t2 4) */
                  lag(t2.id) over (order by regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')) as id
                , t2.id2
                , regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as filler
          from
                  t2
          where
                  mod(t2.id2, 3) = 0
        ) v1
      , (
          select  /*+ parallel(t2 8) */
                  lag(id) over (order by regexp_replace(filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')) as id
                , id2
                , regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as filler
          from
                  t2
          where
                  mod(t2.id2, 3) = 0
        ) v2
where
        v1.id = v2.id
and     v1.filler = v2.filler
;

commit;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Multiple DFO trees, no parent/child, multiple DFO tree starts, no separate slave sets, concurrently active (12.1: Single DFO tree, new parallel FILTER/SUBQUERY) --
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

set echo on timing on time on

select  /*+ no_merge(x) */
        *
from    (
          select
                  v1.filler
                , (select /*+ parallel(x 2) */ id from t2 x where x.id = v1.id) as id
                , (select /*+ parallel(x 2) */ id2 from t2 x where x.id = v1.id) as id2
          from    (
                    select  /*+ parallel(t2 4) */
                            t2.id
                          , t2.id2
                          , t2.filler
                    from
                            t2
                  ) v1
                , (
                    select  /*+ parallel(t2 8) */
                            t2.id
                          , t2.id2
                          , t2.filler
                    from
                            t2
                  ) v2
          where
                  v1.id = v2.id
          and     v1.filler = v2.filler
        ) x
where
        rownum <= 100
;

----------------------------------------------------------------------------------------------------------------------------------------------------------
-- Multiple DFO trees, no parent/child, multiple DFO tree starts, separate slave sets, concurrently active (12.1: Single DFO tree, new parallel FILTER) --
----------------------------------------------------------------------------------------------------------------------------------------------------------

set echo on timing on time on

select  /*+ parallel(t2 8)
            parallel(t3 8)
            leading(t3 t2)
            use_hash(t3 t2)
            swap_join_inputs(t2)
            PQ_DISTRIBUTE(T2 HASH HASH)
            --PQ_FILTER(@"SEL$1" NONE)
        */
        t2.id     as t2_id
      , t2.filler as t2_filler
      , t2.id2    as t2_id2
      , t3.id     as t3_id
      , t3.filler as t3_filler
from
        t1 t2
      , t2 t3
where
        t3.id2 = t2.id2 (+)
and     regexp_replace(t3.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler (+), '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
and     mod(t3.id2, 3) = 0
and     not exists (select /*+ no_unnest parallel(t2 2) */ null from t2 x where x.id2 = t2.id2)
;

-------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Multiple DFO trees, no parent/child, multiple DFO tree starts, separate slave sets, concurrently active (12.1: Still multiple DFO trees, serial FILTER) --
-------------------------------------------------------------------------------------------------------------------------------------------------------------

set echo on timing on time on

alter session enable parallel dml;

truncate table x;

insert  /*+ append parallel(x 4) */ into x
select  /*+ leading(v1 v2) */
        v_1.id
      , v_1.id2
      , v_1.filler
from    (
           select
                   id
                 , id2
                 , filler
           from    (
                      select  /*+ parallel(t2 4) no_merge */
                              rownum as id
                            , t2.id2
                            , t2.filler
                      from
                              t2
                      where
                              mod(t2.id2, 3) = 0
                      and     regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
                   ) v1
        ) v_1
      , (
          select
                   id
                 , id2
                 , filler
          from     (
                     select  /*+ parallel(t2 8) no_merge */
                             rownum as id
                           , t2.id2
                           , t2.filler
                     from
                             t2
                     where
                             mod(t2.id2, 3) = 0
                     and     regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') = regexp_replace(t2.filler, '^([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
                   ) v2
        ) v_2
where
        v_1.id = v_2.id
and     v_1.filler = v_2.filler
and     not exists (select /*+ no_unnest parallel(y 2) */ null from t2 y where y.id2 = v_1.id2)
;

commit;

Analysing Parallel Execution Skew - Video Tutorial

Along the new mini series "Parallel Execution Skew" at "AllThingsOracle.com" that provides some information what you can do if you happen to have a parallel SQL execution that is affected by work distribution problems I'm publishing a series of video tutorials that explain how you can actually detect and measure whether a SQL execution is affected by skew or not.

Originally this tutorial was planned as one part (Part 5 actually) of the XPLAN_ASH video tutorial series, however so far I've only managed to publish just the inital two parts of that series, and these are already a bit outdated as I've released new versions of the XPLAN_ASH tool with significant changes and enhancements since then.

Since this tutorial covers a lot of information and I don't want to bother you watching a single multi-hour video, I'm splitting this tutorial into four parts:

• Brief introduction
• Explaining Data Flow Operations (DFOs)
• Analysing Parallel Execution Skew without Diagnostic / Tunink Pack license
• Analysing Parallel Execution Skew with Diagnostic / Tunink Pack license

So here is the initial part on my Youtube channel, a brief introduction what the "Parallel Execution Skew" problem is about.

The links mentioned at the beginning of the video are:
Webinar recording "Analysing and Troubleshooting Parallel Execution"
OTN mini series "Understanding Parallel Execution"

New Version Of XPLAN_ASH Utility

A new version of the XPLAN_ASH tool (detailed analysis of a single SQL statement execution) is available for download. The previous post includes links to video tutorials explaining what the tool is about.

As usual the latest version can be downloaded here.

The new version comes with numerous improvements and new features. The most important ones are:

• Real-Time SQL Monitoring info included
• Complete coverage including recursive SQL
• Improved performance
• 12c compatible
• Simplified usage

Although I unfortunately haven't managed yet to publish the remaining parts of the video tutorial based on the previous version, I hope now that the new version is out to come up with the most interesting parts soon.

You can watch this short video on my Youtube channel that demonstrates the most important improvements.

New Version Of XPLAN_ASH Tool - Video Tutorial

A new major release (version 3.0) of my XPLAN_ASH tool is available for download.

You can download the latest version here.

In addition to many changes to the way the information is presented and many other smaller changes to functionality there is one major new feature: XPLAN_ASH now also supports S-ASH, the free ASH implementation.

If you run XPLAN_ASH in a S-ASH repository owner schema, it will automatically detect that and adjust accordingly.

XPLAN_ASH was tested against the latest stable version of S-ASH (2.3). There are some minor changes required to that S-ASH release in order to function properly with XPLAN_ASH. Most of them will be included in the next S-ASH release as they really are only minor and don't influence the general S-ASH functionality at all.

If you're interested in using XPLAN_ASH with an existing S-ASH installation get in touch with me so I can provide the necessary scripts that apply the necessary changes.

Rather than writing another lengthy blog post about the changes and new features introduced I thought I start a multi-part video tutorial where I explain the purpose of the tool and how to use it based on the new version - some parts of the tutorial will focus on specific functionality of the tool and are therefore probably also quite useful as some kind of general tutorial on that Oracle feature and SQL execution troubleshooting guide in general.

The tutorial will consist of six parts initially, the first two are already available on my Youtube channel - the next ones to follow over time.

Part 1: Introduction, Overview

Part 2: Usage, Parameters, Invocation

Part 3: Rowsource Statistics: TBD

Part 4: Active Session History: TBD

Part 5: Systematic Parallel Execution Skew Analysis & Troubleshooting: Coming Soon

Part 6: Experimental Stuff, Script Configuration And Internals: TBD

Feel free to post questions/requests for clarification that are not covered in the tutorials in the comments section - if there are topics of general interest I might publish a seventh part addressing those questions.

In future I might use that video style more often since it's a nicer way of conveying certain kind of information.