I want to verify the parallel execution in my User-Defined Aggregate Function. So I put some DBMS_OUTPUT inside the code - but it seems not to work correctly...
You can reproduce the behaviour by simple create the example user-defined aggregate function "secondmax" (
https://docs.oracle.com/cd/B28359_01/appdev.111/b28425/aggr_functions.htm#BEJBFBHH ) and put a dbms_output.put_line in the odciaggregateinitialize ("init") and odciaggregatemerge ("merge") function.
Here are my testdata:
create table secondmaxtestdata
as
select * from (
select 1 as id,
dbms_random.value(100,2000) as value
from dual connect by level<=30000
union
select 2 as id,
dbms_random.value(200,1500) as value
from dual connect by level<=8000
);Now check the following examples...
1)
-- we see only two "init" in dbms_output, because we have only two groups,
-- no parallel, because we don't use it
select /*+ NO_PARALLEL */
id,
secondmax(value)
from secondmaxtestdata
group by id;
2)
-- if we filter to one id, parallel is working fine, we see many "merge" outputs
select
/*+PARALLEL(3) */
secondmax(value)
from
secondmaxtestdata
where id=2;
3)
-- but without filter and parallel enabled - we see nothing (no "init" and
-- no "merge") - but why?
select
/*+PARALLEL(3) */
id,
secondmax(value)
from
secondmaxtestdata
group by id;
Do you have any idea why my last example did not output anything???
That's because parallel processes can run in a different session to the caller. And you only see the result of calls to dbms_output.put_line in your session.
In the following example, the parallel call to f shows no dbms_output. But it still logs ten rows: just in session 131 instead of the parent session 119:
create table log (
x varchar2(100) not null
);
create or replace procedure p as
pragma autonomous_transaction;
begin
insert into log values (sys_context('USERENV', 'SID'));
commit;
end p;
/
create or replace function f ( val int )
return int parallel_enable as
retval int;
begin
p();
dbms_output.put_line(sys_context('USERENV', 'SID'));
return val;
end f;
/
drop table t purge;
create table t as
select level x from dual
connect by level <= 10;
select /*+ parallel (2) */sum(f(x)), sys_context('USERENV', 'SID') from t;
SUM(F(X)) SYS_CONTEXT('USERENV','SID')
55 119
select x, count(*) from log group by x;
X COUNT(*)
131 10
truncate table log;
select /*+ no_parallel */sum(f(x)), sys_context('USERENV', 'SID') from t;
SUM(F(X)) SYS_CONTEXT('USERENV','SID')
55 119
119
119
119
119
119
119
119
119
119
119
select x, count(*) from log group by x;
X COUNT(*)
119 10 The same principle applies in your code. If you want to see details of the executions, you need to log to a table.