Thanks for the question, poorni.

Asked: December 26, 2011 - 7:51 pm UTC

Last updated: April 22, 2013 - 1:47 pm UTC

Version: 10.1.0.2.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I am working on a project , where it needs parallel jobs for the performance.Each of our packages run for nearly an hour .Some of them are handled through unix jobs .I am trying this apprach , pls suggest me .

CREATE TABLE MAX.TEST_TIME
(
DESCR VARCHAR2(50),
RUNDATE DATE,
RUNTIME VARCHAR2(30),
RUN NUMBER
);

CREATE OR REPLACE procedure MAX.test_22 (run in number)
is
begin
dbms_output.put_line (' procedure 22 started ');
insert into test_time values(' procedure 22 started ',sysdate,localtimestamp,run);
dbms_output.put_line ('procedure 22 going to sleep');
dbms_lock.sleep(3);
dbms_output.put_line (' procedure 22 ended ');
insert into test_time values(' procedure 22 ended',sysdate,localtimestamp,run);
commit;
end;
/

declare
jobid integer;
begin
dbms_job.submit(jobid,'begin test_22(1); end;',sysdate);
commit;
dbms_job.submit(jobid,'begin test_22(2); end;',sysdate);
dbms_job.submit(jobid,'begin test_22(3); end;',sysdate);
commit;
end;

Q: According to docs,when a job is submited after the commit , the job automatically runs. But the job is not running,can see it in user_jobs though.

declare
jobid1 integer:=101;
jobid2 integer := 102
begin
dbms_job.submit(jobid1,'begin test_22(1); end;',sysdate);
commit;
dbms_job.submit(jobid2,'begin test_22(2); end;',sysdate);
commit;
dbms_job.run(jobid1,false); --run in background
dbms_job.run(jobid2);
end;

Q: Still serial run of jobs.

select * from test_time;

DESCR RUNDATE RUNTIME RUN
-------------------------------------------------- --------- ------------------------------ --------
procedure 22 started 26-DEC-11 26-DEC-11 05.39.30.524780 PM 1
procedure 22 ended 26-DEC-11 26-DEC-11 05.39.33.528442 PM 1
procedure 22 started 26-DEC-11 26-DEC-11 05.39.33.556651 PM 2
procedure 22 ended 26-DEC-11 26-DEC-11 05.39.36.557033 PM 2

The job_queue_processes is set to 10, and Parallel_dml is enables.
What am i missing? How exactly does the SNP slave processes work?

I tried searching for the answer , couldn't find it.Point me in the right direction if you already answered this type of Q.

Thnak you.

and Tom said...

dbms_job.run does not ever run them in the background, you are confused about the 'force' parameter. It applies in RAC (real application clusters) and causes the job to run on a given node (instance) in the cluster.. so - you ran those jobs sequentially.

If you submit multiple jobs and hang out for a minute, you'll see up to 10 of them running at the same time (based on your settings). For example:


ops$tkyte%ORA11GR2> create table t ( start_time timestamp, end_time timestamp, inputs varchar2(30) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace procedure p( p_inputs varchar2 )
  2  as
  3          l_rowid rowid;
  4  begin
  5          insert into t (start_time, inputs)
  6          values ( systimestamp, 'job # ' || p_inputs )
  7          returning rowid into l_rowid;
  8  
  9          sys.dbms_lock.sleep(20);
 10  
 11          update t set end_time = systimestamp where rowid = l_rowid;
 12  end;
 13  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          l_job number;
  3  begin
  4          dbms_job.submit( l_job, 'p(JOB);' );
  5          dbms_job.submit( l_job, 'p(JOB);' );
  6          dbms_job.submit( l_job, 'p(JOB);' );
  7          dbms_job.submit( l_job, 'p(JOB);' );
  8          commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_lock.sleep(45);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from t order by start_time;

START_TIME
---------------------------------------------------------------------------
END_TIME
---------------------------------------------------------------------------
INPUTS
------------------------------
27-DEC-11 11.39.50.768242 AM
27-DEC-11 11.40.12.055726 AM
job # 225

27-DEC-11 11.39.50.769928 AM
27-DEC-11 11.40.12.068341 AM
job # 223

27-DEC-11 11.39.50.770194 AM
27-DEC-11 11.40.12.069167 AM
job # 224

27-DEC-11 11.39.50.789304 AM
27-DEC-11 11.40.12.067426 AM
job # 226


ops$tkyte%ORA11GR2>

run that example for us and post the results, should be similar.

Rating

(9 ratings)

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

Comments

Populate

A reader, December 29, 2011 - 4:06 am UTC

Hi Tom,

lets say we have 2 tables XYZ and XYZ_dummy.
In every batch process we truncate table XYZ_dummy and insert from table XYZ.The row count of table XYZ is 39500014 .This run in multiple streams.Can you tell what is fastest way we can do this ??

If we drop table and create XYZ_dummy as select * from XYZ will be faster than insert ???

December 29, 2011 - 11:19 am UTC

how can a truncate and insert take place in multiple streams?

The fastest way to populate this table would probably be:

a) truncate it
b) insert /*+ append */ into it using PDML (parallel DML)

in a single stream, we do the parallel stuff for you.

that can be done without generating any redo for the table if the dummy table is nologging. It will also bypass undo generation for the table as well.

A reader, December 29, 2011 - 12:03 pm UTC

Hi Tom,

Sorry.Truncate table is not in multiple streams.Only insert in multiple streams.AS we are already running in multiple streams do we really need PDML ??
we can use "truncate table XYZ drop storage" instead of truncate table XYZ and then insert with /*+append*/.
Does it impact to select query if we use append hint after drop storage ???

December 29, 2011 - 1:22 pm UTC

Why are you running in multiple streams something which is done trivially in a single statement.

Your procedural code that splits up this insert will almost be definition include alot of overhead a single sql statement will not.

If you decide to change the degree of parallelism we can do it in a millisecond using pdml ... how about your code?

We can do direct path loading... can you? You cannot do it in parallel unless you are writing in oci. And if you are in oci .... well you've done it wrong since you wrote a ton of code when one line would do...

Can you skip the undo redo generation?

Why did you write code that would be slower, use more runtime resources, required debugging, and requires maintenance over time?

A reader, December 29, 2011 - 10:43 pm UTC

Hi Tom,

Only Below is the query in code under a function which run in multiple stream.It takes 76.65 minute to complete.Total record under the view is 2735849.
Here acct_hoff is a view of 4 tables.Almost all the record are flg_mnt_status = 'A'
mis_acct_hoff is a table.

INSERT INTO mis_acct_hoff
SELECT amt_sched_princ_bal, amt_instal_arrears, dat_acct_close,
flg_mnt_status, cod_cc_brn, cod_acct_no, cod_prod,
cod_cust_id, dat_of_maturity, cod_ccy, rat_int_current,
amt_arrears_princ, amt_arrears_interest, amt_arrears_chg,
cod_acct_stat, flg_past_due_status, amt_face_value,
amt_princ_balance, flg_accr_status, dat_last_renew,
dat_last_due, amt_disbursed, amt_arrears_charges,
cod_user_stat, amt_net_disbursed, amt_arrears_nonacc, amt_accr_computed,
cod_remitter_acct, dat_acct_open, bal_book, rat_penalty_int, nam_cust_shrt
FROM acct_hoff b
WHERE b.flg_mnt_status = 'A'
AND EXISTS ( SELECT 1
FROM mis_ctrl z
WHERE z.cod_cc_brn = b.cod_cc_brn
AND z.cod_stream_id = var_actual_cod_stream_id
AND z.cod_prog_id = 'MISC');

so we can replace with below and remove streaming :

INSERT /*+append*/INTO mis_acct_hoff
SELECT /*+parallel(b,40)*/
amt_sched_princ_bal, amt_instal_arrears, dat_acct_close,
flg_mnt_status, cod_cc_brn, cod_acct_no, cod_prod,
cod_cust_id, dat_of_maturity, cod_ccy, rat_int_current,
amt_arrears_princ, amt_arrears_interest, amt_arrears_chg,
cod_acct_stat, flg_past_due_status, amt_face_value,
amt_princ_balance, flg_accr_status, dat_last_renew,
dat_last_due, amt_disbursed, amt_arrears_charges,
cod_user_stat, amt_net_disbursed, amt_arrears_nonacc, amt_accr_computed,
cod_remitter_acct, dat_acct_open, bal_book, rat_penalty_int, nam_cust_shrt
FROM acct_hoff b
WHERE b.flg_mnt_status = 'A'
AND EXISTS ( SELECT 1
FROM mis_ctrl z
WHERE z.cod_cc_brn = b.cod_cc_brn
AND z.cod_stream_id = var_actual_cod_stream_id
AND z.cod_prog_id = 'MISC');

Yes,we can skip the undo redo generation.

How can we do direct path loading ?? Please explain.

This code was written earlier.Due to performance issue i am looking into this.Please help.

December 30, 2011 - 10:54 am UTC

... Yes,we can skip the undo redo generation.
...

IF and ONLY IF you use a single sql statement with append, you cannot use append in your multiple streams. I know for a fact that right now YOU CANNOT skip undo and redo.

/*+ APPEND */ is a direct path load.

http://www.oracle.com/pls/db112/search?word=parallel+direct+path+insert&partno=

I do not think you will be using the sql you said you would use. It still seems to have a reference to some stream_id which you WILL NOT be using anymore. You will have a single session, using parallel DML, to achieve in a single line of code what is now encoded into a big program.

A reader, December 29, 2011 - 10:51 pm UTC

Hi Tom,

Please ignore above comment

Only Below is the query in code under a function which run in multiple stream.It takes 76.65
minute to complete.Total record under the view is 2735849.
Here acct_hoff is a view of 4 tables.Almost all the record are flg_mnt_status = 'A'
mis_acct_hoff is a table.

INSERT INTO mis_acct_hoff
SELECT amt_sched_princ_bal, amt_instal_arrears, dat_acct_close,
flg_mnt_status, cod_cc_brn, cod_acct_no, cod_prod,
cod_cust_id, dat_of_maturity, cod_ccy, rat_int_current,
amt_arrears_princ, amt_arrears_interest, amt_arrears_chg,
cod_acct_stat, flg_past_due_status, amt_face_value,
amt_princ_balance, flg_accr_status, dat_last_renew,
dat_last_due, amt_disbursed, amt_arrears_charges,
cod_user_stat, amt_net_disbursed, amt_arrears_nonacc, amt_accr_computed,
cod_remitter_acct, dat_acct_open, bal_book, rat_penalty_int, nam_cust_shrt
FROM acct_hoff b
WHERE b.flg_mnt_status = 'A'
AND EXISTS ( SELECT 1
FROM mis_ctrl z
WHERE z.cod_cc_brn = b.cod_cc_brn
AND z.cod_stream_id = var_actual_cod_stream_id
AND z.cod_prog_id = 'MISC');

so we can replace with below and remove streaming :

INSERT /*+append*/INTO mis_acct_hoff
SELECT /*+parallel(b,40)*/
amt_sched_princ_bal, amt_instal_arrears, dat_acct_close,
flg_mnt_status, cod_cc_brn, cod_acct_no, cod_prod,
cod_cust_id, dat_of_maturity, cod_ccy, rat_int_current,
amt_arrears_princ, amt_arrears_interest, amt_arrears_chg,
cod_acct_stat, flg_past_due_status, amt_face_value,
amt_princ_balance, flg_accr_status, dat_last_renew,
dat_last_due, amt_disbursed, amt_arrears_charges,
cod_user_stat, amt_net_disbursed, amt_arrears_nonacc, amt_accr_computed,
cod_remitter_acct, dat_acct_open, bal_book, rat_penalty_int, nam_cust_shrt
FROM acct_hoff b
WHERE b.flg_mnt_status = 'A'
AND EXISTS ( SELECT 1
FROM mis_ctrl z
WHERE z.cod_cc_brn = b.cod_cc_brn
AND z.cod_stream_id = var_actual_cod_stream_id
AND z.cod_prog_id = 'MISC');

Yes,we can skip the undo redo generation.

Yes can we do direct path loading by using hint append .But is it going to impact select query in performance wise ???

This code was written earlier.Due to performance issue i am looking into this.Please help.

December 30, 2011 - 10:55 am UTC

you cannot use append in multiple sessions - the append will lock the table, you can only use that append hint if you do it right - a single session...

append

A reader, December 30, 2011 - 11:23 am UTC

Hi Tom,

Yes will remove the multiple streams.
if we do following things it will have better than multiple streams :

1. use /*+append*/ on insert query
2./*+parallel(40)*/ on select query
3.alter table mis_acct_hoff nologging

December 30, 2011 - 11:41 am UTC

read the link i sent to you above, everything you want/need to do is fully explained in the documentation, read it all before you proceed.

Oracle Sessions and pga

vinod, July 13, 2012 - 8:54 am UTC

Hello Tom,

Please let me know how much pga_agg_target i should set for 500 sessions

Or in other way if have 2 gb pga how much sessions it will support without any issue

thanks

July 16, 2012 - 3:30 pm UTC

so, do you have 50 cpu cores? (500 sessions is probably way too many...)

http://www.youtube.com/watch?v=xNDnVOCdvQ0

anyway - the only answer anyone can give you is:

it depends.

do you sort a lot of rows frequently?
do you do tons of big hash joins?
do you do large bitmap index merges?
do you have an average of 2 active sessions?
do you have an average of 200 active sessions?
do you mostly use indexes to access 10 or so rows per query?
do you mostly use full scans to process tons of rows per query?

and so on - think about it, no one could answer this. No one.

What you can do is start with something you feel is reasonable - you know what your queries do, you know how many should be active at the same exact time approximately, you can take a first good guess and then watch the pga aggregate advisor (statspack or AWR report) and see what it says over time.

Oracle Sessions and pga

vinod, July 19, 2012 - 3:13 am UTC

Thanks Tom,

To add at some time maximum concurrent sessions can be 450
on average say have 300 concurrent sessions
its 10g rel 2 on rhel,20 gb ram,8 gb sga,1.8 gb pga
pga statistics show pga hit 53% and it indicate it will
be same until 8gb pga,is that optimum

digdeep, April 03, 2013 - 3:51 am UTC

Hello, I'm a fresh to Oracle.
I'm don't know that in the statement:
"dbms_job.submit( l_job, 'p(JOB);' );"

where is the parameter 'JOB' comes from ????

Thank you so much.

April 22, 2013 - 1:46 pm UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9088073430469#9099572884547

digdeep, April 03, 2013 - 4:42 am UTC

Hello, I'm already know that in the statement:

dbms_job.submit( l_job, 'p(JOB);' );

the parameter JOB equals to l_job.

Am I right? Thank you.

April 22, 2013 - 1:47 pm UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9088073430469#9099572884547

not really. but sort of.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Populate

append

Oracle Sessions and pga

Oracle Sessions and pga

More to Explore

PL/SQL demos

PL/SQL docs

Run parallel jobs.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Populate

append

Oracle Sessions and pga

Oracle Sessions and pga

More to Explore

PL/SQL demos

PL/SQL docs