Parallel Execution does not work in Oracle

Question

I was created a package called enc_dec, and there was a function called encrypt which used to encrypt the database information. To encrypt large table faster, I tried to use parallel hint:

/*+ parallel(8) */

But after the change, the encrypt performance was the same as before. It seems like the parallel hint did not work. The query I used like this:

create table A as 
select /*+ parallel(8) */ enc_dec.encrypt(B) as B from C;

The interest thing is when I check, there was 8 sessions running which means it was running as parallel. But the total time for running like this is the same as running this query without parallel hint. Can anyone help me to fix it? I need to let the encryption running parallel in order to get better performance.

The picture for execution plan is attached. execution plan 1 execution plan 2

Does your execution plan show "LOAD TABLE CONVENTIONAL"? If so, CTAS isn't using the direct path load. You aren't pulling data directly out of one table and putting it somewhere new, you are modifying the data along the way, which may make the direct path impossible. — Stilgar, Mar 14 '18 at 21:09
Did you see "LOAD AS SELECT"? If so, Oracle is using (or trying to use) the direct path load which is about as fast as it gets. Do source table and destination table reside in the same TABLESPACE? — Stilgar, Mar 15 '18 at 15:07
@Stilgar I cannot see the 'LOAD AS SELECT' in plan table after I add /*+ parallel(8) */ behind select. I do not know what is 'reside in the same TABLESPACE'. I was trying to encrypt some columns from source table and copy them into the destination table with the rest part unchange. — infi999, Mar 15 '18 at 15:32
I see. Well the CTAS is a good approach to high speed data copy of data from one table into a NEW table. It is only fast because of the direct path load and that only works if you don't touch the row result set it is working with (which you are with your encryption). In your query the PARALLEL is only, potentially, going to help you get data out of the source table quicker. You may need to create the destination table with a DDL statement and assign some degree of parallelism. Then use an INSERT with the APPEND hint in place of the CTAS. Check with your DBA. — Stilgar, Mar 15 '18 at 16:01
@Stilgar So do you mean I need to create the table first then insert_into_select with append hint? Put the parallel hint behind create? — infi999, Mar 15 '18 at 16:13
Have a look at this:EXPLAIN PLAN FOR CREATE TABLE Stilgar_A PARALLEL(DEGREE 08) AS SELECT /*+ PARALLEL */ * FROM B; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); — Stilgar, Mar 15 '18 at 16:43
That last one should have "LOAD AS SELECT" in it. If not, you will need to speak with your onsite DBA. But if that string exists, try again using YOUR query with the package call in it. — Stilgar, Mar 15 '18 at 16:47
@Stilgar I tried with yours, the performance was the same as encrypt without parallel. >. — infi999, Mar 15 '18 at 18:14
Execution plan indicates 3 seconds for 10 million rows. Actual run time was what? — Stilgar, Mar 15 '18 at 18:39
@Stilgar I test with 1 million rows, it will take around 50 sec to finish the encryption — infi999, Mar 15 '18 at 18:47
It would appear we have isolated the encryption as the time consuming step of this process and encrypt/decrypt is by nature an expensive operation. The purely database portion of this seems to be implemented well. Thats all the ideas I have. — Stilgar, Mar 15 '18 at 19:00
Please do not post execution plans as images. See Jon Heller's answer on how to properly post an execution plan — , Mar 25 '18 at 05:57
@a_horse_with_no_name Sorry about that.I solved this problem by using parallel_execution package. — infi999, Mar 26 '18 at 13:21

Jon Heller · Answer 1 · 2018-03-25T01:04:32.533

The operation PX COORDINATOR FORCED SERIAL implies the function was not defined with PARALLEL_ENABLE. Adding that clause to the function should improve parallelism.

Regular function objects

create table c(b number);

create or replace package enc_dec is
    function encrypt(p number) return number;
end;
/

create or replace package body enc_dec is
    function encrypt(p number) return number is
    begin
        return p;
    end;
end;
/

explain plan for
create table A as
select /*+ parallel(8) */ enc_dec.encrypt(B) as B from C;

select * from table(dbms_xplan.display);

Execution plan for regular function

Plan hash value: 1467482239

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT               |          |     1 |    13 |     3   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR FORCED SERIAL        |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)                | :TQ10001 |     1 |    13 |     2   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)  | A        |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE                       |          |     1 |    13 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND ROUND-ROBIN             | :TQ10000 |     1 |    13 |     2   (0)| 00:00:01 |        | S->P | RND-ROBIN  |
|   6 |       OPTIMIZER STATISTICS GATHERING |          |     1 |    13 |     2   (0)| 00:00:01 |        |      |            |
|   7 |        TABLE ACCESS FULL             | C        |     1 |    13 |     2   (0)| 00:00:01 |        |      |            |
------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 8 because of hint

Parallel function objects

This code is almost identical to above. The only differences are the package name and the PARALLEL_ENABLE clause in the package specification and body.

create or replace package enc_dec_p is
    function encrypt(p number) return number parallel_enable;
end;
/

create or replace package body enc_dec_p is
    function encrypt(p number) return number parallel_enable is
    begin
        return p;
    end;
end;
/

explain plan for
create table A as
select /*+ parallel(8) */ enc_dec_p.encrypt(B) as B from C;

select * from table(dbms_xplan.display);

Execution plan for parallel function

Note that the operation PX COORDINATOR FORCED SERIAL was changed to PX COORDINATOR.

Plan hash value: 3485102650

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT             |          |     1 |    13 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| A        |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR             |          |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL            | C        |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 8 because of hint

If you're still having problems with parallelism I recommend you look at a long list of parallel factors in my answer here.

Java Function

There doesn't appear to be any inherent limitation preventing Java functions from running in parallel. I haven't used Java in parallel SQL before but the below example doesn't show the "FORCED SERIAL" operation.

create or replace and compile
java source named "JavaEncrypt"
as
public class JavaEncrypt
{
  public static String create()
  {
    return "asdf";
  }
}
/

create or replace package enc_dec is
    function encrypt return varchar2 as language java
    name 'JavaEncrypt.create() return java.lang.String';
end;
/

explain plan for
create table A as
select /*+ parallel(8) */ enc_dec.encrypt as B from C;

select * from table(dbms_xplan.display);

Execution plan:

Plan hash value: 3485102650

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT             |          |     1 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |     1 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| A        |       |            |          |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |     1 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR             |          |     1 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL            | C        |     1 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of hint

You may need to post the entire DDL for us to figure out what's causing the serial plan.

Thank you very much Sir. I tried your way and it did not work. Maybe the reason is the encrypt package is a java store procedure? I load java file into database and use it to encrypt. — infi999, Mar 22 '18 at 13:59