1

I have a pl/sql parallel_enable pipelined function to write files. The select statement with the parallel hint runs fine in parallel if running separately. But the function doesn't run in parallel. Anyone knows how to fix it?

select *
  from table(parallel_x(cursor (select /*+ parallel(c, 5) */
                      c.cid
                       from customers c)));

create or replace type x_t as object
(
  line_count number,
  session_id number
);

create or replace type x_tab as table of x_t;

function parallel_x(s in sys_refcursor) return x_tab
  pipelined
  parallel_enable(partition s by any) is

  session_id number;
  f          utl_file.file_type;
  line       varchar2(32767);
  line_count number := 0;

begin
  select sid into session_id from v$mystat where rownum = 1;

  file_name := 'file_' || session_id;

  f := utl_file.fopen(location     => 'directory',
                  filename     => file_name,
                  open_mode    => 'w',
                  max_linesize => 32767);

  loop
fetch s
  into line;

    utl_file.put_line(file => f, buffer => line);

line_count := line_count + 1;

exit when s%notfound;
  end loop;

utl_file.fclose(f);

 pipe row(x_t(line_count, session_id));

 return;
end;
  • What happens if you put the parallel hint in the top level of the select, rather then(or as well as) in the cursor subquery? – Alex Poole Feb 13 '15 at 19:31
  • @AlexPoole I tried both as below. Both run as single: `select /*+ parallel(t, 5) */ * from table(parallel_x(cursor (select c.cid from customers c))) t; select /*+ parallel(t, 5) */ * from table(parallel_x(cursor (select /*+ parallel(c, 5) */ c.cid from customers c))) t;` – user4564484 Feb 13 '15 at 20:02
  • It works for me using 12.1.0.2. I ran it in one window and looked at `select * from v$px_process;` in another to verify it was running in parallel. Maybe you should go through [my DOP troubleshooting list](http://stackoverflow.com/a/21132027/409172) and look for anything unusual. – Jon Heller Feb 14 '15 at 04:25

0 Answers0