dynamically created cursor doesn't work for parallel pipelined functions

From: Frank Bergemann <FBergemann_at_web.de>
Date: Fri, 3 Dec 2010 13:05:35 -0800 (PST)
Message-ID: <6dc6f1aa-1b1e-4dd3-a982-e2106ddc393b_at_n10g2000yqd.googlegroups.com>

---

please see this code sample:

---

drop table parallel_test;

CREATE TABLE parallel_test (
  id NUMBER(10),
  description VARCHAR2(50)
);

BEGIN
  FOR i IN 1 .. 50000 LOOP
    INSERT INTO parallel_test (id, description)     VALUES (i, 'Description or ' || i);
  END LOOP;
  COMMIT;
END;
/

• Define a strongly typed REF CURSOR type.

CREATE OR REPLACE PACKAGE parallel_ptf_api AS

  TYPE t_parallel_test_row IS RECORD (
    id1 NUMBER(10),
    desc1 VARCHAR2(50),
    id2 NUMBER(10),
    desc2 VARCHAR2(50),
    sid NUMBER
  );

  TYPE t_parallel_test_tab IS TABLE OF t_parallel_test_row;

  TYPE t_parallel_test_ref_cursor IS REF CURSOR RETURN t_parallel_test_row;

  FUNCTION test_ptf (p_cursor IN t_parallel_test_ref_cursor)     RETURN t_parallel_test_tab PIPELINED     PARALLEL_ENABLE(PARTITION p_cursor BY any);

END parallel_ptf_api;
/

SHOW ERRORS CREATE OR REPLACE PACKAGE BODY parallel_ptf_api AS

  FUNCTION test_ptf (p_cursor IN t_parallel_test_ref_cursor)     RETURN t_parallel_test_tab PIPELINED     PARALLEL_ENABLE(PARTITION p_cursor BY any)   IS
    l_row t_parallel_test_row;
  BEGIN
    LOOP

      FETCH p_cursor
      INTO  l_row;
      EXIT WHEN p_cursor%NOTFOUND;

	  select userenv('SID') into l_row.sid from dual;

      PIPE ROW (l_row);

    END LOOP;
    RETURN;
  END test_ptf;

END parallel_ptf_api;
/

SHOW ERRORS PROMPT
PROMPT Serial Execution

PROMPT ================

SELECT sid, count(*)
FROM TABLE(parallel_ptf_api.test_ptf(CURSOR(SELECT t1.id, t1.description, t2.id, t2.description, null

                                              FROM   parallel_test t1,
parallel_test t2
												where t1.id = t2.id
                                             )
                                      )
            ) t2

GROUP BY sid;

PROMPT
PROMPT Parallel Execution

PROMPT ==================

SELECT sid, count(*)
FROM TABLE(parallel_ptf_api.test_ptf(CURSOR(SELECT /*+ parallel(t1,5) */ t1.id, t1.description, t2.id, t2.description, null

                                              FROM   parallel_test t1,
parallel_test t2
												where t1.id = t2.id
                                             )
                                       )
            ) t2

GROUP BY sid;

PROMPT
PROMPT Parallel Execution 2

PROMPT ==================

set serveroutput on;

declare

	v_sids ton := ton();
	v_counts ton := ton();
--	v_cur parallel_ptf_api.t_parallel_test_ref_cursor;
	v_cur sys_refcursor;

	procedure OpenCursor(p_refCursor out sys_refcursor)
	is
	begin
		open p_refCursor  for 'SELECT /*+ parallel(t1,5) */ t1.id,
t1.description, t2.id, t2.description, null
                                              FROM   parallel_test t1,
parallel_test t2
												where t1.id = t2.id';
	end;

begin

        OpenCursor(v_cur);

	SELECT sid, count(*) bulk collect into v_sids, v_counts
	FROM   TABLE(parallel_ptf_api.test_ptf(v_cur)) t2
	GROUP BY sid;

	for i in v_sids.FIRST.. v_sids.LAST loop
		dbms_output.put_line (v_sids(i) || ', ' || v_counts(i));
	end loop;

end;
/

---

The output is:

---

SQL> _at_test

Table dropped.

Table created.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

No errors.

Serial Execution

---

       SID COUNT(*)
---------- ----------

       649 50000

Parallel Execution

---

       SID COUNT(*)
---------- ----------

       435	10012
       440	 9970
       537	 9970
       459	 9924
       371	10124

Parallel Execution 2

---

649, 50000

PL/SQL procedure successfully completed.

---

But why doesn't it use multiple SIDs for 'Parallel Execution 2'? Is there any way to make it work?

• many thanks!

Frank Received on Fri Dec 03 2010 - 15:05:35 CST