It is recommended to declare Oracle PL/SQL functions as PARALLEL_ENABLE when they can be parallelized, so the DBMS knows it can run the function in parallel.
But when is a function parallelizable? Or rather: when is it not?
Let's look at an example:
CREATE OR REPLACE FUNCTION get_country_name(p_country_code VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE
AS
PRAGMA UDF;
v_country_name country.name%TYPE;
BEGIN
SELECT name INTO v_country_name
FROM country
WHERE code = p_country_code;
RETURN v_country_name;
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END;
The function selects the country name for a given country code, e.g. 'France' for 'FR'. I made it UDF, because I will mainly use it in SQL queries, and I made it parallel enabled, because, well, then it works better in select /*+parallel(4)*/ get_country_name(code) from ... - is this what it means? Or what else?
And why can I declare this function PARALLEL_ENABLE? What exactly makes it parallelizable? Can I just declare all my functions PARALLEL_ENABLE? Then I should, shouldn't I? Or are there circumstances that would prevent that? What would keep a function from being parallelizable?
PARALLEL_ENABLE is somewhat of a mystery. Oracle's documentation is very unclear. It is certain that you don't need it in parallel query (PQ, parallelizing of SELECT blocks) - parallel slaves will happily execute your scalar functions in the SELECT clause without being marked as PARALLEL_ENABLE.
One place it definitely does make a difference is with parallel DML (pdml) when using non-pure functions. Per the docs:
Enables the function for parallel execution, making it safe for use in concurrent sessions of parallel DML evaluations.
It actually only needs this when the function is non-pure, and not at all when it's regular PQ. Here's a demonstration, arbitrarily using a large table I have named "snapshot".
This performs the function call serially and effectively disables pdml from working:
CREATE OR REPLACE FUNCTION myname(in_value IN integer)
RETURN number
AS
PRAGMA UDF;
BEGIN
RETURN SYS_CONTEXT('USERENV','SID'); -- makes it non-pure
END;
create table tmp1 (col1 integer) ;
alter session enable parallel dml;
insert /*+ append parallel(x,32) */ into tmp1 x
select /*+ parallel(32) full(s) */ myname(snapshot_id) from snapshot s
Execution plan:
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 344M| 2302M| 10381 (1)| 00:00:01 | | | |
| 1 | LOAD AS SELECT | TMP1 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 344M| 2302M| 10381 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | OPTIMIZER STATISTICS GATHERING | | 344M| 2302M| 10381 (1)| 00:00:01 | Q1,00 | PCWC | |
| 5 | PX BLOCK ITERATOR | | 344M| 2302M| 10381 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL | SNAPSHOT | 344M| 2302M| 10381 (1)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
0 - INS$1 / X@INS$1
U - parallel(x,8)
Note
-----
- Degree of Parallelism is 32 because of hint
- PDML disabled because function is not pure and not declared parallel enabled
As you can see, the LOAD AS SELECT operation comes above/after PX COORDINATOR, so no PDML. The actual insert step is serialized. The plan comments even give us an example of why. Of course this makes sense: depending on which PX slave executes it, the result will be different (since I'm returning the SID of the executing process). Oracle doesn't want us to get different results depending on whether PX is involved or not or how rows are distributed, but this would cause that.
So, we have to swear on our graves that this is really what we want. Now add PARALLEL_ENABLE:
CREATE OR REPLACE FUNCTION myname(in_value IN integer)
RETURN number PARALLEL_ENABLE
AS
PRAGMA UDF;
BEGIN
RETURN SYS_CONTEXT('USERENV','SID');
END;
insert /*+ append parallel(x,8) */ into tmp1 x select /*+ parallel(32) full(s) */ myname(snapshot_id) from snapshot s
Execution plan:
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 344M| 2302M| 10381 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 344M| 2302M| 10381 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| TMP1 | | | | | Q1,00 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 344M| 2302M| 10381 (1)| 00:00:01 | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 344M| 2302M| 10381 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL | SNAPSHOT | 344M| 2302M| 10381 (1)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 32 because of hint
Now LOAD AS SELECT comes below/before PX COORDINATOR, so we are in full PDML mode here and that insert will go a lot faster. Adding PARALLEL_ENABLE to that scalar function made it possible to use it in a PDML operation.
If the function were pure, then the PARALLEL_ENABLE clause isn't necessary. PDML will work just fine without it, because Oracle knows the result won't change regardless of how rows are distributed:
CREATE OR REPLACE FUNCTION myname(in_value IN integer)
RETURN number
AS
PRAGMA UDF;
BEGIN
RETURN in_value*100;
END;
Plan:
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 344M| 2302M| 10381 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 344M| 2302M| 10381 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| TMP1 | | | | | Q1,00 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 344M| 2302M| 10381 (1)| 00:00:01 | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 344M| 2302M| 10381 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL | SNAPSHOT | 344M| 2302M| 10381 (1)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 32 because of hint
Still in PDML mode, but without PARALLEL_ENABLE.
There are other uses for it. The docs discuss it in connection with pipelined functions, particularly with ref cursors, but I've never definitively seen it working. I routinely add it to my pipelined functions, but my execution always show serialized output which are then distributed to the first PX slave set. I have never seen the output itself parallelized.
Perhaps someone else can throw their experience in here who has managed to get it working with pipelined table functions.