Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: February 10, 2020 - 2:35 pm UTC

Answered by: Chris Saxon - Last updated: March 04, 2020 - 10:46 am UTC

Category: Database Development - Version: 12c

Viewed 1000+ times

You Asked

Hi Connor, Chris,

Could you please have a look at below scenario related to dbms_scheduler program and job setu:

-------------- Start Use Case Setup --------------
---------------------------------------------------------------------------------------------------
-- 1. Create Record and Collection
create or replace TYPE table_rec as object (create_dt DATE
                                           ,dept_id   NUMBER(11,0)
                                           ,emp_id    NUMBER(11,0)
                                           )
/

show errors;
/

create or replace TYPE t_table_coll as table of table_rec
/

show errors;
/
---------------------------------------------------------------------------------------------------
-- 2. Create Procedure 
create or replace procedure populate_coll_into_table(p_job_id in varchar, p_t_table_coll in t_table_coll, p_no_of_reties in number)
is
begin
    dbms_output.put_line('Inside Procedure - p_job_id='||p_job_id||'- p_t_table_coll='||p_t_table_coll.count||'- p_no_of_reties='||p_no_of_reties); 
    for rec in p_t_table_coll.first .. p_t_table_coll.last
 loop
       dbms_output.put_line('Iteration #'||rec); 
       dbms_output.put_line('create_dt='||p_t_table_coll(rec).create_dt||'#emp_id='||p_t_table_coll(rec).emp_id||'#dept_id='||p_t_table_coll(rec).dept_id);
    end loop; 
exception 
when others then
   dbms_output.put_line('Error=>'||SQLERRM); 
end populate_coll_into_table;
/

show errors;
/
---------------------------------------------------------------------------------------------------
-- 3. Create Program
set serveroutput on;
begin
    dbms_scheduler.create_program(program_name        => 'PRG_POPULATE_COLL_INTO_TABLE',
                                  program_action      => 'POPULATE_COLL_INTO_TABLE',
                                  program_type        => 'STORED_PROCEDURE',
                                  number_of_arguments => 3,
                                  enabled             => false
    );
    dbms_output.put_line('Program: PRG_POPULATE_COLL_INTO_TABLE created..');
    dbms_scheduler.define_anydata_argument(program_name      => 'PRG_POPULATE_COLL_INTO_TABLE',
                                           argument_position => 1,
                                           argument_name => 'P_JOB_ID',             
                                           argument_type     => 'VARCHAR2',
                                           default_value     => null
    );
    dbms_output.put_line('Set Program: PRG_POPULATE_COLL_INTO_TABLE | Parameter 1.');
    dbms_scheduler.define_anydata_argument(program_name      => 'PRG_POPULATE_COLL_INTO_TABLE',
                                           argument_position => 2,
                                           argument_name => 'P_T_TABLE_COLL',
                                           argument_type     => 'T_TABLE_COLL',
                                           default_value     => NULL);
    dbms_output.put_line('Set Program: PRG_POPULATE_COLL_INTO_TABLE | Parameter 2.');
    dbms_scheduler.define_anydata_argument(program_name      => 'PRG_POPULATE_COLL_INTO_TABLE',
                                           argument_position => 3,
                                           argument_name => 'P_NO_OF_RETIES',
                                           argument_type     => 'NUMBER',
                                           default_value     => null
    );
    dbms_output.put_line('Set Program: PRG_POPULATE_COLL_INTO_TABLE | Parameter 3.');
    
 dbms_scheduler.enable('PRG_POPULATE_COLL_INTO_TABLE');
 
 dbms_output.put_line('Enable Program: PRG_POPULATE_COLL_INTO_TABLE.');
end;
/
-------------- End Use Case Setup --------------


My requirement is to call a procedure with collection as an input (for some application processing logic).
This procedure should be running independently (i.e. in different session) other than running one.
Hence i have used dbms_scheduler (below is the setup for the same).

I have used dbms_scheduler.run_job (job_name => l_job_name, use_current_session => false); to run the procedure in differnt session,
but with this approach jobs is not getting auto dropped even if its gets succesfully completed.

Could you please help me with below concerns:
1. How can I achieve auto drop in this scenario
2. Since set_job_anydata_value is used to input collection to scheduler:
a. Where these values gets stored like in SGA, PGA ?
b. Is thre any USER_* views to find input values to each job?
c. Since my collection count could be around 500 records and expected concurrent jobs can run simultaneously (around 100)
Any suggestion whether below scheduling approach can be implemented using better approach?

------------ create and execute scheduler ------
----------
declare
    l_job_name        varchar2(30 char) := 'SCOTT_1234';
    gv_t_table_coll  t_table_coll := t_table_coll();
    gv_table_rec   table_rec := table_rec(NULL,NULL,NULL); 
 gv_no_of_reties   number := 2;
begin
    gv_table_rec.create_dt := trunc(sysdate-1);
 gv_table_rec.emp_id := 1001;
 gv_table_rec.dept_id := 100;
    gv_t_table_coll.extend;
    gv_t_table_coll(1) := gv_table_rec;

    gv_table_rec.create_dt := trunc(sysdate-2);
 gv_table_rec.emp_id := 1002;
 gv_table_rec.dept_id := 100;
    gv_t_table_coll.extend;
    gv_t_table_coll(2) := gv_table_rec;
 
    dbms_scheduler.create_job(l_job_name, program_name => 'PRG_POPULATE_COLL_INTO_TABLE', start_date => systimestamp, enabled => false);
    
 dbms_scheduler.set_job_anydata_value(l_job_name, 1, sys.anydata.convertvarchar2(l_job_name));  
    dbms_scheduler.set_job_anydata_value(l_job_name, 2, sys.anydata.convertcollection(gv_t_table_coll));
    dbms_scheduler.set_job_anydata_value(l_job_name, 3, sys.anydata.convertnumber(gv_no_of_reties));
    dbms_scheduler.run_job (job_name => l_job_name, use_current_session => false);

 gv_t_table_coll.delete;
exception 
when others then
    dbms_output.put_line('Error=>'||SQLERRM);
end;

and we said...

1. Just enable the job and it'll run in a background session. Then drop:

declare
    l_job_name        varchar2(30 char) := 'SCOTT_1234';
    gv_t_table_coll  t_table_coll := t_table_coll();
    gv_table_rec   table_rec := table_rec(NULL,NULL,NULL); 
 gv_no_of_reties   number := 2;
begin
    gv_table_rec.create_dt := trunc(sysdate-1);
    gv_table_rec.emp_id := 1001;
    gv_table_rec.dept_id := 100;
    gv_t_table_coll.extend;
    gv_t_table_coll(1) := gv_table_rec;

    gv_table_rec.create_dt := trunc(sysdate-2);
    gv_table_rec.emp_id := 1002;
     gv_table_rec.dept_id := 100;
    gv_t_table_coll.extend;
    gv_t_table_coll(2) := gv_table_rec;
 
    dbms_scheduler.create_job(l_job_name, program_name => 'PRG_POPULATE_COLL_INTO_TABLE', start_date => systimestamp, enabled => false, auto_drop => true);
    
    dbms_scheduler.set_job_anydata_value(l_job_name, 1, sys.anydata.convertvarchar2(l_job_name));  
    dbms_scheduler.set_job_anydata_value(l_job_name, 2, sys.anydata.convertcollection(gv_t_table_coll));
    dbms_scheduler.set_job_anydata_value(l_job_name, 3, sys.anydata.convertnumber(gv_no_of_reties));
    
    gv_t_table_coll.delete;
exception 
when others then
    dbms_output.put_line('Error=>'||SQLERRM);
end;
/

select start_date from user_scheduler_jobs
where  job_name = 'SCOTT_1234';

START_DATE                 
11-FEB-2020 13.42.28 +00  

exec dbms_scheduler.enable ( 'SCOTT_1234' );

select start_date from user_scheduler_jobs
where  job_name = 'SCOTT_1234';

no rows selected


2.
a. Variables are stored in PGA

b. I'm not aware of one. They don't appear in user_scheduler_job_run_details/job_log

c. I don't know enough about what you're trying to do. What's the goal here? Why are you submitting so many concurrent jobs?

and you rated our response

  (6 ratings)

Reviews

It worked!!

February 11, 2020 - 2:43 pm UTC

Reviewer: A reader

Hi Chris,
I tried enabling job instead run_job and it worked for me.. Thanks a lot for this quick help.
Just want to get more knowledge on input collection to these jobs - will it release PGA once its SUCCEEDED, even if job log entries exists in user_scheduler_job_run_details/job_log ?

For point #c : we have concurrent existing application processing jobs (which populates certain tables)
Now we came across a new requirement where we want to load new table lets say table_x with the values of each concurrent application sessions.
DML on new table_x may take some time, hence we want existing application flow without any delay, hence looking for running separate job in other sessions.

Chris Saxon

Followup  

February 11, 2020 - 6:10 pm UTC

will it release PGA once its SUCCEEDED

Once a session ends it releases the memory it used. But why is this important to you?

February 11, 2020 - 2:49 pm UTC

Reviewer: Iudith Mentzel from Haifa, Israel

Hello All,

For point b#:
You can see the arguments for each job in USER_SCHEDULER_JOB_ARGS.

Best Regards,
Iudith Mentzel

Chris Saxon

Followup  

February 11, 2020 - 6:16 pm UTC

Ah yes, those these will be lost once the job is dropped!

store_output

February 11, 2020 - 3:18 pm UTC

Reviewer: Mikhail Velikikh from Dublin, Ireland

Hi,

>> b. Is thre any USER_* views to find input values to each job?

Provided that we are talking about getting input values of a dropped job, I would rather use DBMS_OUTPUT to log any required parameters, and then utilize %_SCHEDULER_JOB_RUN_DETAILS.OUTPUT/BINARY_OUTPUT to obtain them.

Oracle actually created an out argument table: scheduler$_job_out_args but I do not think it is used:
1. DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT.OUT_ARGUMENT accepts only FALSE.
2. there is no dependency on this table in DBA_DEPENDENCIES
3. that table is used only in one script in @?/rdbms/admin - in the one that creates it.

Regards,
Mikhail.
Chris Saxon

Followup  

February 11, 2020 - 6:16 pm UTC

I'd rather write them to a logging table!

First time Using scheduling option

February 11, 2020 - 6:13 pm UTC

Reviewer: A reader

Hi Chris,
This is the first time I am using collection input to dbms scheduler and not sure if running 100 odd concurrent sessions of collection input job may impact memory usage.
Chris Saxon

Followup  

February 11, 2020 - 6:20 pm UTC

Yes, but...

This is something that should come out in your stress testing. Unless you're loading colossal arrays or are very tight on memory I wouldn't worry about it too much.

500 records consisting of a date and two numbers is "fairly small".

Job dropped if it fails

February 14, 2020 - 2:53 pm UTC

Reviewer: A reader

Hi Chris,
I am not sure whether this query was posted earlier (I got error while submitting), hence writing it again.
Kindly ignore duplicate (if any)

Setting auto_drop => true and enabling job : I was able to achieve running job in different session.
However, jobs are getting dropped eve if they are marked as FAILED in user_job_run_details.
Could you please help, how can I restrict dropping of job if it is FAILED?

Chris Saxon

Followup  

February 18, 2020 - 10:49 am UTC

You could set auto_drop to false ;)

auto_drop worked.. queries related to scheduler timestamps

March 04, 2020 - 8:34 am UTC

Reviewer: A reader

Hi Chris,
auto_drop = > false worked for me :) Thanks for that.

Could you please tell me what timezone does dba_scheduler_job_run_details contains in columns: LOG_DATE, REQ_START_DATE, ACTUAL_START_DATE.

e.g. I have a job entry in dba_scheduler_job_run_details with below data:
LOG_DATE = 3/3/2020 11:52:14.163029 PM +00:00
REQ_START_DATE = 3/3/2020 11:52:12.375957 PM +00:00
ACTUAL_START_DATE = 3/3/2020 11:52:14.132671 PM

when i execute below SQL, ideally it should not return the above jobs right? correct me if I am wrong
select *
from dba_scheduler_job_run_details
and ACTUAL_START_DATE between to_date('04/03/2020 05:22:00','dd/mm/yyyy hh12:mi:ss') and to_date('04/03/2020 05:22:59','dd/mm/yyyy hh12:mi:ss');

I am bit confused with the timestamp values stored in these columns and the values in SELECT. Could you please help.
Chris Saxon

Followup  

March 04, 2020 - 10:46 am UTC

Well those values are showing +00:00, so UTC (or maybe GMT/London in winter).

You can find the default time zone with:

select dbms_scheduler.stime from dual;

STIME                      
04-MAR-2020 10.44.22 +00 


Tim Hall discusses this further at https://oracle-base.com/articles/10g/scheduler-10g#time-zones

when i execute below SQL, ideally it should not return the above jobs right?


I would assume so, yes. Remember there could be other executions of this job though!

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database