Parallelizing Table Functions (instead of paralysing)

One of the many topics in our (AMIS’) Oracle 7Up (..to 10g) workshop is Table Functions. We discuss how PL/SQL functions can be turned into ‘table like’ data sources or row-generators. Table Functions have been around since Oracle 8i. Since Oracle 9i, we have two additional capabilities with Table Functions: pipelining and parallel execution. Pipelining basically means that the Table Function will return rows as soon as they are ready, instead of sitting on all of them until the entire set is ready and then returning them. When multiple table functions are used in a series, this means that processing of the first record by the second table function (‘transformer’) can start as soon as it has been piped out by the first table function. That first record may be returned to the client – e.g. SQL*Plus – even before the first Table Function has managed to produce its second record. It should be clear that this pipelining gives tremendous improvement even of overall throughput time. The first record is returned must faster, but since now the first, second and subsequent table functions can work at the same time – in parallel as it were – the processing is complete much sooner than otherwise would have been the case.

However, this is not what is meant by parallel execution of Table Functions. This is what is: when a Table Function – used as a transformer – accepts a cursor as input variable – such as cursor(select * from emp) – without extra specification, it will process al records fetched from the cursor one by one, serially. However, if the nature of the Table Function is such that the each record is processed independently of the others – at least to some extent – there is nothing that would stop us from processing partitions of the cursor’s result set in separate – parallel – instances of the Table Function, by multiple processes (UNIX) or process-threads (Windows), if possible assigned to multiple CPUs.

It is fairly simple to indicate to the Oracle PL/SQL execution engine that a Table Function can be executed in parallel:

• ensure that the table function accepts a single input-parameter of a strongly defined ref cursor type
• add the clause PARALLEL_ENABLED to the function specification
• specify how the records from the cursor’s result-set should be distributed over multiple instances of the table function
• specify that the table(s) from which the cursor fetches its resultset has been defined as parallel enabled
• you may need to specify in the init.ora or through an alter system statement the number of parallel processes allowed (see Orafaq on Parallel Query)/ul>

  I was wondering whether I could actually see the effect of this parallel execution on my single-CPU windows XP machine, running Oracle 10g (10.1.0.2 Enterprise Edition). It turned out I could! This is what I did:

  Define the Ref Cursor Type

  create or replace package EmpTypePkg is
  type emp_rec is RECORD
  (empno number(12),
  ename varchar2(50))
  ;
  type EmpCursorType is REF CURSOR return emp_rec;
  
  end;
  /

  Define the Table Function.

  Note it does not do an awful lot. I just make it sit idle for 2 seconds for each record processed – with the call to dbms_lock.sleep(2). Also note the way I have instructed the PL/SQL execution engine to distribute the records from the p_src cursor: partition by hash on empno – that amounts to random distribution. It is also possible to partition the cursor’s result set in more strict ways, for example ensuring that all employees from one department are processed by the same function instance or making sure they are processed in a specific sequence (see Input Data Streaming for Table Functions in Oracle® Data Cartridge Developer’s Guide
  10g Release 1 (10.1))

  create or replace function
  scramble( p_src in emptypepkg.EmpCursorType)
     return string_table pipelined
  parallel_enable ( partition p_src by Hash ( empno) )
  is
    retval varchar2(100);
    l_emp emptypepkg.emp_rec;
  begin
     loop
       fetch p_src into l_emp;
       exit when p_src%NOTFOUND;
       dbms_lock.sleep(2);
       retval:= translate
                ('abcdefghijklmnopqrs'
                , 'srqponmlkjihgfedcba'
                , l_emp.ename
                ) ;
        pipe row (retval);
     end loop;
    return;
  end;
  /

  In SQL*Plus enable timing so we can see the difference in elapsed time

  set timing on
  

  Disable parallel query on table EMP

  alter table emp noparallel
  /

  Execute query on table function scramble using table EMP

  select *
  from   table
         ( scramble
           ( cursor ( select empno, ename from emp))
         )
  / 

  The query ran for 29 seconds.

  Enable parallel query on table EMP – with a maximum degree of parallellism of 8 (somewhat high given the number of only 14 records in EMP)

  alter table emp parallel 8
  /

  Execute query on table function scramble using table EMP

  select *
  from   table
         (  scramble
           ( cursor ( select empno, ename from emp))
         )
  / 

  This time the query ran in under 13 seconds.

  That means that allowing Oracle to execute the table function in parallel in this case speeded up execution by 230%. Note that with a larger number of records I would expect this performance improvement to increase, as we would benefit more from parallel processing and suffer less (relatively) from parallel-execution overhead. When I inserted another 224 records into EMP (from 14 to 238) the non-parallel version took approximately 238*2 = 488 seconds while the parallel executed version of the query lasted for 209 seconds. Again this is 230%. Apparently I do not have 8 parallel process threads, three is more like it. Still, the results are impressive I would say.

  Check whether the query was actually executed in parallel

  SELECT * FROM V$PQ_SESSTAT WHERE statistic='Queries Parallelized'
  /
  STATISTIC                      LAST_QUERY SESSION_TOTAL
  ------------------------------ ---------- -------------
  Queries Parallelized                    1             1