Execution plan
The influence of PL/SQL functions that are enabled for parallel executions on the
execution plan is demonstrated below.
First, we need a table to select from. The table has the parallel attribute:
create table tq84_px_t (
id number,
val varchar2(10)
) parallel 4;
The following package specification has two functions: non_px_f is an ordinary function, px_f is enabled for parallel execution:
create or replace package tq84_px_p
authid definer
as
function non_px_f(v varchar2) return number;
function px_f(v varchar2) return number parallel_enable;
end tq84_px_p;
/
The package's body:
create or replace package body tq84_px_p as
function non_px_f(v varchar2) return number is begin
return case when v = 'xyz' then 1 else 0 end;
end non_px_f;
function px_f(v varchar2) return number is begin
return case when v = 'xyz' then 1 else 0 end;
end px_f;
end tq84_px_p;
/
Show the execution plan when using the non parallel function:
explain plan for
select * from tq84_px_t
where
tq84_px_p.non_px_f(val) = 1;
select * from table(dbms_xplan.display(format => 'basic'));
--
-- ---------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | TABLE ACCESS FULL| TQ84_PX_T |
-- ---------------------------------------
With the parallel enabled function, the statement will be executed in parallel:
explain plan for
select * from tq84_px_t
where
tq84_px_p.px_f(val) = 1;
select * from table(dbms_xplan.display(format => 'basic'));
--
-- -----------------------------------------
-- | Id | Operation | Name |
-- ------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | PX COORDINATOR | |
-- | 2 | PX SEND QC (RANDOM)| :TQ10000 |
-- | 3 | PX BLOCK ITERATOR | |
-- | 4 | TABLE ACCESS FULL| TQ84_PX_T |
-- -----------------------------------------
Cleaning up:
drop table tq84_px_t;
drop package tq84_px_p;