You Asked

Hello Tom,

I gave the below link a try and applied the method on 12c.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4248554900346593542

But it takes same time as serial processing. Could you please light me up what I'm missing?
Here is the what I have done so far.

A little part of schema and procedure are on livesql:
https://livesql.oracle.com/apex/livesql/s/f38g1fftxhc6fmilx6fzfnze9

DECLARE
  l_task     VARCHAR2(50) := 'TASK_SP_ENR_ENT_ORACLE_BY_PRIVTYPE';
  l_sql_stmt VARCHAR2(200);
  l_try      NUMBER;
BEGIN

    DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);

   DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   => l_task,
                                               TABLE_OWNER => 'AVUSER',
                                               table_name  => 'T_ENTITLEMENTS',
                                               by_row      => TRUE,
                                               chunk_size  => 100000);
  
l_sql_stmt := 'begin SP_ENR_ENT_ORACLE_BY_PRIVTYPE( :start_id, :end_id ); end;';

  DBMS_PARALLEL_EXECUTE.run_task(task_name      => l_task,
                                 sql_stmt       => l_sql_stmt,
                                 language_flag  => DBMS_SQL.NATIVE,
                                 parallel_level => 4);

Hope, that's enough for understanding issue.

Thanks,
Emre

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/f38g1fftxhc6fmilx6fzfnze9

and Chris said...

How long does it take when you run it serially? And then how long using DIY parallel?

When I run the example, I see:

DECLARE
  l_task     VARCHAR2(50) := 'TASK_SP_ENR_ENT_ORACLE_BY_PRIVTYPE';
  l_sql_stmt VARCHAR2(200);
  l_try      NUMBER;
BEGIN

  DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);

  DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   => l_task,
                                               TABLE_OWNER => user,
                                               table_name  => 'T_ENTITLEMENTS',
                                               by_row      => TRUE,
                                               chunk_size  => 100000);
                                               
  l_sql_stmt := 'begin SP_ENR_ENT_ORACLE_BY_PRIVTYPE( :start_id, :end_id ); end;';

  DBMS_PARALLEL_EXECUTE.run_task(task_name      => l_task,
                                 sql_stmt       => l_sql_stmt,
                                 language_flag  => DBMS_SQL.NATIVE,
                                 parallel_level => 4); 

end;
/

select chunk_id, (end_ts - start_ts) runtime
from   user_parallel_execute_chunks
where  task_name = 'TASK_SP_ENR_ENT_ORACLE_BY_PRIVTYPE';

CHUNK_ID   RUNTIME               
      2707 +00 00:00:00.012770   
      2708 +00 00:00:00.013488   
      2709 +00 00:00:00.004485

So we have three chunks, which each take less than a tenth of a second to execute!

Unless your real tables have significantly more rows than the example, the data are just too small and the procedure too quick for you to get any meaningful benefit from using DIY parallel. You may as well stick with serial processing.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Yunus Emre Guloglu, January 15, 2018 - 11:53 am UTC

My real table has more than 10 million rows.

Running it serially and using DIY parallel finish at the same time.

What else can I try? Any permission, configuration, etc.
OR should I change the parallel method?

January 15, 2018 - 1:59 pm UTC

And how long does it take when you run it serially? And how long using DIY parallel? How are you getting these times?

If the current process is taking too long, rather than going for DIY parallel and hoping it'll make things faster figure out why it's slow serially.

Do this by tracing the application while it runs:

exec DBMS_monitor.session_trace_enable ( null, null, true, true );
***your code here***
exec DBMS_monitor.session_trace_disable;

You can read more about this at:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof

It's likely using this you can find where to make gains on serial execution. For starters, there's nested cursor for loops. I'm sure moving these to set processing will help.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Coding Parallel Processing on 12c

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs