11 Replies Latest reply: Sep 16, 2010 5:43 PM by user521233 RSS

    Scheduler parameters

    564285
      I have a time sensitive procedure that the client is requiring me to push application logging to a separate thread in order to offload the I/O over head of creating and commit the data.

      In order to do this we are looking at using dbms_scheduler to push the necessary data over to a one time job. I have about a dozen items that need to be passed for the various messages to be logged. This works fine when I create a job and create an object to hold the parameters. I use "anydata" to pass the object. It works fine, but I need to pass three collections and I can't seem to figure out how to do that.

      It seems to be populating the collection okay and converting it to anydata using convertcollection, but when I try to set_job_anydata_value to the converted collection I get "incorrect usage of method". If I comment out the collection it works.

      I tried moving the collection to a separate object and the first object works, but the collection fails.

      Anybody have any examples of passing collections as parameters?
        • 1. Re: Scheduler parameters
          Rnr-Oracle
          Looks like noone's ever tried this before. As you pointed out it just doesn't work. We have filed a bug a bug to track this issue and hopefully get it fixed (and possibly backported).

          One thing you can try is creating a program and passing the collection in as a default argument value for one of the arguments. This may work although I suspect it doesn't.

          Another way is to simply store all arguments to a table and then pass the table name or an identifying column value into the job which can then do the bulk select from the table.

          -Ravi
          • 2. Re: Scheduler parameters
            564285
            Okay, thanks for the confirmation. I've been pulling my hair out trying to figure it out.

            The table route may be a requirement, but the project requirement is to avoid I/O overhead which is the whole point of moving the logging out to a separate procedure via the scheduler. The application is a claims processing data lookup procedure which must have and end-to-end turnaround of 3/10 of a second and the I/O overhead is the only left to shave. I may not have any choice now though.
            • 3. Re: Scheduler parameters
              Rnr-Oracle
              Hi,

              I know this is a bit hackish. But if your values are short enough, have you considered concatenating them into a single varchar2/clob separated by a special separator string and then reparsing them inside the job. It isn't the most elegant but it will replace I/O by CPU .

              -Ravi
              • 4. Re: Scheduler parameters
                564285
                Yes, I've been experimenting with table_to_comma and comma_to_table to effect that same sort of logic, but I have a multi-dimensional array, so it would get far too complicated and kludgey.
                • 5. Re: Scheduler parameters
                  564285
                  I did some additional experimentation and confirmed that using a program and defining program parameters has the exact same issue, plus the additional overhead of creating a program just to be able to set a job by job parameter to pass the run by run information.

                  I did however manage to get it working with varrays as follows:

                  CREATE OR REPLACE TYPE ELEMENTS_RCVD_NAME_TAB_T IS TABLE OF VARCHAR2(20);
                  CREATE OR REPLACE TYPE ELEMENTS_RCVD_VALUE_TAB_T IS TABLE OF VARCHAR2(127);

                  CREATE OR REPLACE TYPE MSG_FRAGMENT_REC_T IS OBJECT
                  (GROUP_NAME VARCHAR2 (100),
                  NPI INTEGER,
                  ELEMENTS_RCVD_NAMES ELEMENTS_RCVD_NAME_TAB_T,
                  ELEMENTS_RCVD_VALUES ELEMENTS_RCVD_VALUE_TAB_T,
                  ELEMENTS_USED_NAMES ELEMENTS_RCVD_NAME_TAB_T
                  );

                  CREATE OR REPLACE TYPE MSG_FRAGMENT_TABLE_T IS varray(10000) OF MSG_FRAGMENT_REC_T;

                  CREATE OR REPLACE TYPE NPI_LOG_OBJECT_T IS OBJECT
                  (
                  REQUEST VARCHAR2(32767),
                  RESPONSE VARCHAR2(32767),
                  REFERENCE VARCHAR2(40),
                  TYPE VARCHAR2(100),
                  ITEM_AGN NUMBER,
                  ALT_IDENTIFIER VARCHAR2(20),
                  ACTIVE_STATUS CHAR(1),
                  CORN VARCHAR2(32767),
                  TAX_ID VARCHAR2(15),
                  SUBMITTER_ID VARCHAR2(40),
                  ERROR_CODE VARCHAR2(10),
                  ERROR_MESSAGE VARCHAR2(500),
                  CCN_NOTE VARCHAR2(32767),
                  MSG_ID VARCHAR2(32767),
                  PROC_START TIMESTAMP(9),
                  PROC_COMPLETE TIMESTAMP(9)
                  );

                  DECLARE
                  v_job_name varchar2(100);

                  v_object npi_log_object_t;

                  v_msg_frag_tab msg_fragment_table_t := msg_fragment_table_t();

                  v_ELEMENTS_RCVD_NAMES ELEMENTS_RCVD_NAME_TAB_T := ELEMENTS_RCVD_NAME_TAB_T();
                  v_ELEMENTS_RCVD_VALUES ELEMENTS_RCVD_VALUE_TAB_T := ELEMENTS_RCVD_VALUE_TAB_T();
                  v_ELEMENTS_USED_NAMES ELEMENTS_RCVD_NAME_TAB_T := ELEMENTS_RCVD_NAME_TAB_T();

                  BEGIN
                  v_ELEMENTS_RCVD_NAMES.extend;
                  v_ELEMENTS_RCVD_NAMES(1) := 'elmnt 1';
                  v_ELEMENTS_RCVD_NAMES.extend;
                  v_ELEMENTS_RCVD_NAMES(2) := 'elmnt 2';
                  v_ELEMENTS_RCVD_NAMES.extend;
                  v_ELEMENTS_RCVD_NAMES(3) := 'elmnt 3';

                  v_ELEMENTS_RCVD_VALUES.extend;
                  v_ELEMENTS_RCVD_VALUES(1) := 'VALUE 1';
                  v_ELEMENTS_RCVD_VALUES.extend;
                  v_ELEMENTS_RCVD_VALUES(2) := 'VALUE 2';
                  v_ELEMENTS_RCVD_VALUES.extend;
                  v_ELEMENTS_RCVD_VALUES(3) := 'VALUE 3';

                  v_ELEMENTS_USED_NAMES.extend;
                  v_ELEMENTS_USED_NAMES(1) := 'elmnt 1';
                  v_ELEMENTS_USED_NAMES.extend;
                  v_ELEMENTS_USED_NAMES(2) := 'elmnt 2';
                  v_ELEMENTS_USED_NAMES.extend;
                  v_ELEMENTS_USED_NAMES(3) := 'elmnt 3';

                  v_msg_frag_tab.extend;
                  v_msg_frag_tab(1) := MSG_FRAGMENT_REC_T('test', 123,
                  v_ELEMENTS_RCVD_NAMES,
                  v_ELEMENTS_RCVD_VALUES,
                  v_ELEMENTS_USED_NAMES);

                  v_object := npi_log_object_t('request',
                  'response',
                  'reference',
                  'type',
                  123,
                  'ALT_IDENTIFIER',
                  'P',
                  'CORN',
                  'TAX_ID',
                  'SUBMITTER_ID',
                  'ERROR_CODE',
                  'ERROR_MESSAGE',
                  'CCN_NOTE',
                  'MSG_ID',
                  systimestamp - 1/24,
                  systimestamp);

                  v_job_name := dbms_scheduler.generate_job_name ('NPI_LOG_JOB_');

                  dbms_scheduler.create_job (
                  job_name => v_job_name,
                  job_type => 'STORED_PROCEDURE',
                  job_action => 'scp.chris.scheduler_test',
                  number_of_arguments => 2,
                  enabled => false,
                  comments => 'This job is spawned automatically by the NPI ' ||
                  'Crosswalk process.');

                  -- set up parameters to be retrieved when the job runs
                  dbms_scheduler.set_job_anydata_value
                  (
                  job_name => v_job_name,
                  argument_position => 1,
                  argument_value => sys.anydata.convertobject(v_object)
                  );

                  dbms_scheduler.set_job_anydata_value
                  (
                  job_name => v_job_name,
                  argument_position => 2,
                  argument_value => sys.anydata.convertcollection(v_msg_frag_tab)
                  );

                  dbms_scheduler.enable(v_job_name);
                  end;

                  This will schedule and run successfully. The procedure being scheduled is just a dummy procedure to read the passed parameters and it works correctly.
                  • 6. Re: Scheduler parameters
                    Rnr-Oracle
                    That is a very creative workaround !

                    And this will be very useful to anyone else who is trying to pass a collection into a job.

                    Thanks for posting it !

                    -Ravi
                    • 7. Re: Scheduler parameters
                      564285
                      Thanks for the compliment!

                      We have run into a snag however -- is there a limit on how many jobs can be scheduled? Not concurrently running, but physically scheduled?

                      My question stems from the fact that when our process runs it spawns jobs to log information about the process. This work fine for a while, but when multiple threads are executing the procedure spawning multiple jobs per second, at some point everything comes to a crawl. My unconfirmed suspicion is that with all of those threaded processes spawning jobs, that at some point it hits a threshold beyond which it can't schedule more jobs and the process requesting the schedule has to wait. I suspect that number to be 10,000, but this is just a guess.

                      Does my theory hold any water? Is there a limit to how many jobs can be scheduled? I wouldn't think so, but then again the scheduler wasn't really designed to be used this way (spawning tens of thousands of jobs).

                      I consider advanced queueing, but the I/O overhead was too much for this time sensitive process.
                      • 8. Re: Scheduler parameters
                        Rnr-Oracle
                        Hi,

                        The number of jobs that can be scheduled (i.e. exist in an enabled state) should be much higher than 10,000. But lots of jobs being run at the same time or being created at the same time could run into some bottleneck somewhere.

                        We have done some internal stress testing to remove bottlenecks from running jobs so there shouldn't be any obvious ones at lower rates.

                        But this rate of job creation is pretty high so you could be running into a couple things.

                        - The scheduler maintains an internal queue of jobs that are supposed to run in the next half an hour. If there are thousands of jobs supposed to run in the next half an hour processing this queue may be taking up too much time. Also if there are lots of processes scheduling jobs I believe that each job creation locks the internal queue briefly so this could be a bottleneck.

                        - by default a job logs an entry for every run. If you are running this many jobs, you should have your own job_class with logging turned off. This should reduce the disk activity of running many very short jobs.

                        - every job has a small overhead because it does a full login. Running lots of very short jobs may cause this overhead to be taking up more time/CPU than the jobs themselves.

                        In 11g there are some solutions to these problems but this won't help you very much.

                        The only thing I can think is somehow batching several jobs into a single one , perhaps by inserting rows into a table and having a single repeating jobs process rows from this table.

                        I was going to suggest AQ and using messages instead but I notice you have already tried this. I do find it a bit surprising that creating a job is less I/O expensive than enqueuing a message but perhaps AQ has issues of its own.

                        Yet another possibility is that if you are scheduling jobs in extreme bursts then have them start at a random time in the future so they will be spaced out instead of all trying to run at the same time during the burst.

                        Performance optimization is tricky and very specific to the situation so I can't think of much else.

                        The database does have tools for monitoring bad performance and you may be able to use these to figure out whether any particular SQL statements are consuming too much cpu or any locks are being waited on for too much time. Perhaps you could look into using AWR (workload repository) to check for obvious bottlenecks ?

                        Hope this helps,
                        Ravi.
                        • 9. Re: Scheduler parameters
                          564285
                          This project has been an interesting challenge. Business requirements for sub-300 millisecond response time has really tested our technical skills. We reached a point where we were trying to shave nanoseconds and the only overhead we had control over was message logging (writing the incoming data and outgoing response to tables).

                          Writing the data was taking as much as a third of the total process time, so we looked for alternatives. The original thought was writing just the incoming request and having a separate non-time sensitive process reprocess. Obviously a bad idea for many reasons. Then I looked into AQ, but the time needed to queue was about the same as the time to insert, which makes sense since it's just inserting into different tables maintained by the system.

                          Then we tried scheduling with the theory that the scheduler could multithread through our multiprocessor, multinode RAC and be able to keep up with the demand.

                          It worked well until we ran into bottlenecks when multiple threads were sending requests which spawns schedules for each request. The scheduled process runs in probably less than a second, but with 40 incoming threads processing in 300 milliseconds each spawning jobs we could be scheduling 100+ jobs per second. Even on a multiprocessor 3 node RAC the scheduler was hard pressed to keep up when it was executing a procedure which took most of a second to run.

                          We resorted to just inserting the incoming request into a table and having a running procedure reprocess the data for logging purposes just to meet the project deadline, but now we are trying to go back and do it "better", which could be either AQ or scheduling.
                          • 10. Re: Scheduler parameters
                            Rnr-Oracle
                            Actually there is an internal bug (#4531589) filed for this which is fixed in the upcoming 11g and the fix has been backported to 10.2.0.4 .

                            One-off patches for this issue are also available through support if requested.

                            -Ravi
                            • 11. Re: Scheduler parameters
                              user521233
                              i'm finding little information about this bug... i am running a 10.2.0.4.0 database.

                              i'm finding little information with an entire working example that includes the signature of the called logic. i've tried various things including below but only get "ORA-06553: PLS-ORA-06553: PLS-306: wrong number or types of arguments in call to 'TEST_SCHEDULER'" at any attempt to pass a sys.anydata type through the dbms_scheduler!

                              -- target type to be passed
                              create or replace type t_replacements_job_options is varray(10000) of varchar(20);

                              -- simple proc that does nothing at the moment except report that it was called
                              create or replace procedure test_scheduler (ip_replacements_job_options sys.anydata) as
                              -- procedure name for debug information
                              C_PROCEDURE_NAME constant varchar2(50) := '(test_scheduler)';
                              begin
                              ta_debug.set_debug_on;
                              ta_debug.writeDebug('DEBUG: procedure entry '||C_PROCEDURE_NAME||'.');
                              ta_debug.writeDebug('DEBUG: procedure exit '||C_PROCEDURE_NAME||'.');
                              exception
                              when others then
                              raise;
                              end;
                              /

                              declare
                              -- procedure name for debug information
                              C_PROCEDURE_NAME constant varchar2(50) := '(anonymous block)';

                              v_replacements_job_options t_replacements_job_options := t_replacements_job_options();
                              egin
                              ta_debug.set_debug_on;
                              ta_debug.writeDebug('DEBUG: procedure entry '||C_PROCEDURE_NAME||'.');

                              for i in 1..1000 loop
                              v_replacements_job_options.extend;
                              v_replacements_job_options(i) := 'R';
                              end loop;

                              DBMS_SCHEDULER.CREATE_JOB (
                              job_name => v_job_name,
                              job_type => 'STORED_PROCEDURE',
                              job_action => 'test_scheduler',
                              start_date => null,
                              repeat_interval => null,
                              number_of_arguments => 1);

                              dbms_scheduler.set_job_anydata_value(
                              job_name => v_job_name
                              , argument_position => 1
                              , argument_value => sys.anydata.convertcollection(v_replacements_job_options));

                              DBMS_SCHEDULER.enable(v_job_name);

                              ta_debug.writeDebug('DEBUG: procedure exit '||C_PROCEDURE_NAME||'.');
                              exception
                              when others then
                              raise;
                              end;
                              /

                              i need to pass data to a batch stored procedure and have had nothing but trouble attempting to send maybe 100 characters and 100 numbers... building these collections and passing as an anonymous block to dbms_job.submit blows up as the 'what' parameter can only take 2000 characters...

                              was hoping that i could use pass anydata via the dbms_scheduler to lift this kind of restriction.