1

Topic: the cursor transferred by function parallely from the first

Found out that if  sys_refcursor that for some reason at the first start it is always fulfilled not parallely (i.e. despite the parallel plan, it is fulfilled in one flow). From the second time normally. In what there can be a business?
Simple script to play back at itself (oracle 11.2.0.4):

create table t1 (x number, y varchar2 (4000));
create type t_number as table of number;
-- To fill a label
declare
i number;
begin
for i in 1. 5 loop
insert into t1
select i * 100000 + rownum, rpad (owner || object_name, 4000, ' ') from dba_objects;
commit;
end loop;
end;
-- At the first start (a hard. ) it is fulfilled always it is serial, at subsequent - it is parallel.
declare
v_res sys_refcursor;
v_arr t_number: = t_number ();
type t_arr is table of t1%rowtype;
v_val t_arr: = t_arr ();
procedure func_returning_cursor (p_ids in t_number,
p_ret out sys_refcursor) is
begin
open p_ret for select / * + parallel (8) */t.* from table (p_ids) p, t1 t where p.column_value = t.x;
end;
begin
v_arr.extend (1000000);
for i in 1. 1000000
loop
v_arr (i): = i;
end loop;
func_returning_cursor (v_arr, v_res);
fetch v_res bulk collect into v_val;
end;

2

Re: the cursor transferred by function parallely from the first

Valergrad;
The first performance happens  and generates two

select sql_id;
executions;
child_number
--,rows_processed
from v$sql
where sql_text like ' %parallel (8) % ' and sql_text not like ' %v$sql % ' and sql_text not like ' %declare % ';
SQL_ID EXECUTIONS CHILD_NUMBER
------------- ---------- ------------
dknw7mqyg5789 1 0
dknw7mqyg5789 0 1
2 rows selected.
select child_number, bind_mismatch
from v$sql_shared_cursor
where sql_id = ' dknw7mqyg5789 ';
CHILD_NUMBER BIND_MISMATCH
------------ -------------
0 N
1 Y
2 rows selected.

Samples of the main session  under child 0, and samples  under child 1.

select distinct decode (session_id, qc_session_id, ' main ', ' slave ') sess_type, sql_child_number - sql_exec_start
from v$active_session_history
where sql_id = ' dknw7mqyg5789 ';
SESS_TYPE SQL_CHILD_NUMBER
--------- ----------------
main 0
slave 1
2 rows selected.

More in detail who that did shows dbms_sqltune.report_sql_monitor.
PS. Irrespective of parallelism collection reading always will be in one flow.

3

Re: the cursor transferred by function parallely from the first

dbms_photoshop;
You are absolutely right, thanks!
I.e. the error turns out in my script for the analysis - I admit, I did not expect that one performance of one request can create some cursors. Much explains this.
For the sake of justice, we mark that TOAD makes the same mistake and too shows it somehow strange.