parallel

Solution: Parallel Activity just drops

As you remember we had a hash join outer with dominant null values. Thus, the parallel process dealing with the null values got most of the work to do.
How would we deal with it? Let’s us back up a bit.
A null value is a special case. We are comparing in our join a null value versus a primary key that is defined not null. The null values will certainly not generate a match when joining. Therefore any of the parallel slaves could do the compare, as long as it is guaranteed not to generate match.
It is therefore beneficial to convert the null values into some other value, as long as we to fulfill two prerequisites:

  1. we have to convert the null values in such way that the get evenly distributed across all parallel slaves
  2. we must make sure that the converted values do not generate a match

It took me a long time to figure this. First I tried something like negative values, but they were still going to the same slave.
The issue is that our newly generated key needs to be roughly in range with the pk of the build table to be spread across all slaves.
I could have tried to figure the range in a with clause reading the data dictionary, but could not get myself to do something I considered uncool.
Instead I tried something else, which is honestly a bit of a hack, but it looks way better. I used the pk of the outer table to generate a not null widespread key.
I also took advantage of the fact that the id’s were integer and changed the join condition to:

FROM T_TAB1 T1
LEFT OUTER JOIN T_TAB2 T2
ON ( NVL(T2.X_ID, T2_id +0.5)    = T1.X_ID)

That is certainly somewhat tricky. The only thing I can say in favor is that is kind of worked.

After I applied my solution the activity tab looked like that:

Compare this to the previous picture. It is quite impressive.
Well, in version 12 the database should be able to deal with skew join distribution. But it still does not work for all cases of the as Randolf Geist states.
If someone does not like my solution he might have a look into _fix_control 6808773, but I do not warrant either solution. 😉

 

Advertisements

Parallel Activity just drops

In one of my DWH POCs for Oracle’s Real World Performance Group we did see an unexpected drop of activity. The customer wanted to know why it happened.
This is how sql monitor’s activity tab looked:

I investigated carefully and found out that the reason was in both cases an outer join that looked liked that

FROM T_TAB1 T1
LEFT OUTER JOIN T_TAB2 T2
ON T2.X_ID    = T1.X_ID

I found that 90% of all values in T1.X_ID were Null values. The distribution for the join was hash. Thus, the parallel slave dealing with null values got most of the work and was for quite some time the only one running. Table T1 was the biggest table, to broadcast it was out of question.
(You may also compare Randolf’s article on skew parallel distribution: Parallel Execution Skew – Demonstrating Skew).

Can you now re-code the join condition such that the join is done by all parallel slaves? (This was version 11G. In version 12 the optimizer should take care of it.)  Further assumption: table T2 has a one column primary key call T2_id.

 

Solution: Navigate many shallow hierachies in parallel

Lets create the indexes we need. There might be better options, but these will do:


create index i1 on tree_emp( empno);
create index i2 on tree_emp( mgr);

Now we define the pipe table package. Of course it is possible to have a more efficient solution, but I want to show the principle.


create or replace package parallel_access
as

TYPE R_REC IS RECORD (empno tree_emp.empno%TYPE,
sum_salaries Number); — result record, change defintion according to your needs
TYPE refcur_t IS REF CURSOR RETURN R_REC;

TYPE result_Tab is TABLE OF R_REC;

FUNCTION passData (p_ref refcur_t) RETURN result_Tab
PIPELINED
PARALLEL_ENABLE(PARTITION p_ref BY ANY); — function will inherit parallelism from ref cursor

END parallel_access;
/

create or replace package body parallel_access
as
FUNCTION passData (p_ref refcur_t) RETURN result_Tab
PIPELINED PARALLEL_ENABLE(PARTITION p_ref BY ANY)
IS
out_rec r_rec;
BEGIN
execute immediate ‘alter session set “_old_connect_by_enabled”=true’;
LOOP — for each parallel process
FETCH p_ref INTO out_rec;
EXIT WHEN p_ref%NOTFOUND;
SELECT sum(sal)
INTO out_rec.sum_salaries
FROM tree_emp
CONNECT BY PRIOR EMPNO = MGR
START WITH mgr = out_rec.empno;

PIPE ROW(out_rec);
END LOOP;
execute immediate ‘alter session set “_old_connect_by_enabled”=false’;
RETURN;
END passData;

END parallel_access;
/

We can use the package as follows. Parallel 4 is just an example, one can choose the parallelism as needed.

SELECT b.* FROM
TABLE(parallel_access.passdata (CURSOR( select /*+ parallel (d 4) */ empno , null from tree_emp where mgr is null))) b;

I tested the example with parallel 16 on my laptop (4 core CPU). The piped table was about twice as fast as the standard plan.

Navigate many shallow hierachies in parallel

Suppose you have a large number of flat hierarchies. As an example we take the emp table from the Scott schema.
Using it is we create a table Tree_emp as shown here:


create table tree_emp as select * from emp;

alter table tree_emp modify (empno number(18));
alter table tree_emp modify (mgr number(18));

INSERT INTO tree_emp
( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
)
;
SELECT EMPNO+(10000*step.n) empno,
ENAME,
JOB,
MGR+(10000*step.n) mgr,
HIREDATE,
TRUNC(DBMS_RANDOM.value(low => 8, high => 50))*100 SAL,
COMM,
DEPTNO
FROM emp,
(SELECT rownum n FROM dual CONNECT BY rownum <= 1000000
) step
;

Show for all presidents (mgr is zero) the sum of the salaries of all subordinate . Write a pipelined table function that can navigate multiple trees simultaneously.
Use Oracle’s connect by Syntax.You can index as you like.