• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

Parallel Pipelined function

Parallel pipelined function  not working in productionQuestion:
I have created a parallel pipelined function which i am using to read the data from the source table and load into target table. I am able to see parallel pipelined function in action in TEST. But the same code in prod is NOT working..I cannot see the records being processed in parallel slaves

This is in TEST
select * from v$pq_sesstat;

STATISTIC      LAST_QUERY      SESSION_TOTAL
Queries Parallelized      0      6
DML Parallelized      1      6
DDL Parallelized      0      0
DFO Trees      1      12
Server Threads      0      0
Allocation Height      8      0
Allocation Width      1      0
Local Msgs Sent      224      11255
Distr Msgs Sent      0      0
Local Msgs Recv'd      224      14043
Distr Msgs Recv'd      0      0

You can see that the rows have beein inserted in multiple parallel sessions:

SESSION_ID      COUNT(*)
5423      9805
5455      7460
5412      6192
5419      9630
5409      7444
5445      9085
5439      10788
5427      6052

I dont see the above results in PROD. Instead all the rows are inserted in one session..

Can you please answer why this is happening .... i am trying to implement an ETL code using parallel pipelined technique and I need to ensure the code runs in parallel sessions in production

Here is the complete sequence of code:



create table t_src (id number not null, text varchar2(30) not null) parallel;

Table created.

 insert into t_src select object_id,substr(object_name,1,30) from dba_objects where object_id is not null and object_name is not null;

70269 rows created.

commit;

 exec dbms_stats.set_table_stats(user,'t_src',numrows=>1000000, numblks=>100000 );

PL/SQL procedure successfully completed.

create or replace type t_tgt_type as object
  (
   id number,text varchar2(30),session_id number
   );
   /

Type created.

create or replace type t_tgt_tab_type as table of t_tgt_type;
  2  /

Type created.


 create table t_tgt
   (
    id number not null,
     text        varchar2(30) not null,
     session_id  number
   ) parallel;

Table created.

create or replace function pp(i_cur in sys_refcursor)
  return t_tgt_tab_type
  parallel_enable(partition i_cur by any)
   pipelined
  is
  v_sess_id number;
  v_rec t_src%rowtype;
  begin
 
 
     select sid into v_sess_id from v$mystat where rownum = 1;
 
     loop
 
       fetch i_cur into v_rec;
      exit when i_cur%notfound;
 
     pipe row(t_tgt_type(v_rec.id*2,v_rec.text,v_sess_id));
 
     end loop;
 
     close i_cur;
 
    return;
 
   end;
  /

Function created.

alter session enable parallel dml;

Session altered.

insert /*+ append */ into t_tgt (id,text,session_id) select * from
    table(pp(cursor(select /*+ parallel(t_src) */ * from t_src)));

70269 rows created.

select * from v$pq_sesstat;

In TEST

you can see that it has executed in parallel as highlighted in BOLD


STATISTIC                   LAST_QUERY       SESSION_TOTAL
Queries Parallelized      0      8
DML Parallelized      1      8
DDL Parallelized      0      0
DFO Trees      1      16
Server Threads      0      0
Allocation Height      8      0
Allocation Width      1      0
Local Msgs Sent      224      15567
Distr Msgs Sent      0      0
Local Msgs Recv'd      224      18483
Distr Msgs Recv'd      0      0


In PROD:

STATISTIC      LAST_QUERY      SESSION_TOTAL
Queries Parallelized      0      0
DML Parallelized      0      1
DDL Parallelized      0      0
DFO Trees      0      1
Server Threads      0      0
Allocation Height      0      0
Allocation Width      0      0
Local Msgs Sent      0      932
Distr Msgs Sent      0      0
Local Msgs Recv'd      0      932
Distr Msgs Recv'd      0      0

commit

select session_id,count(*) from t_tgt group by session_id;

In TEST:

SESSION_ID      COUNT(*)
5409      8954
5420      8947
5419      6351
5473      8333
5472      7815
5439      8800
5479      6923
5427      10333

IN PROD:

SESSION_ID      COUNT(*)
5291                        66114


As you can see from the last query, that in test the records are processed in 8 parallel session while in PROD its processed only in one parallel session..

Not sure any settings have to be changed...

Please advise..

Thanks,





0
gs79
Asked:
gs79
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Check the parallel init parameters are the same between the systems:

http://download.oracle.com/docs/cd/E11882_01/server.112/e25523/parallel004.htm#i1007196
0
 
gs79Author Commented:
'show parallel parameters'

in both environment shows similar settings..

I also see that the query being processed in parallel in prod as well. But it is not consistent as in test. While almost all the time it executes in parallel in test enviroment, I observe this behaviour occasionally in prod.

Could it be that the other running queries running is using up the Slave processes as reports run all the time on this report?

Does the massive running queries running eat up parallel processors even though parallel hint is not specified?

I need to bring an ETL query running for 4 hours to 30 min and hence I am trying this technique?

Is there a way for my query to check automatically that no other query is running automatically before this query kicks in?

Thanks
0
 
slightwv (䄆 Netminder) Commented:
>>in both environment shows similar settings..

What about cpu and the other related parameters?


>>query running for 4 hours to 30 min

That might not be a realistic goal.

>>Slave processes as reports run all the time on this report?

I suppose this is possible but you should be able to look at the processes to verify this.

>>Does the massive running queries running eat up parallel processors

Depends.  You can set parallelism on the objects.

>>check automatically that no other query is running automatically

I see you opened this a another question so it cannot be talked about here or it is a duplicate.



I suggest you work directly with Oracle Support on this one.  Since you are seeing sporadic behavior they can explain why.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now