This discussion is archived
11 Replies Latest reply: May 23, 2012 7:40 AM by 760735 RSS

Conceptual help needed: Making asynchronous calls to database

760735 Newbie
Currently Being Moderated
Hi,
thanks for reading this.
The problem is as follows:
I (software developer with experience in PL/SQL but only a little in administration) have a PHP based web page which calls the DB (stored Procedure). I have no control over timeout settings of PHP/Web server and the called stored procedure runs for an indeterminate amount of time.
In order to avoid web timeouts my solution would be for the web to call a stored procedure which creates (or enables?) a job, returns and monitors the job progress until it completes.

First question: Is this the right approach or are there preferable alternatives?

There is a seemingly endless number of web pages which list/explain the commands of the DBMS_Scheduler package and the syntax, but I have not been able to find a good tutorial which illuminates the conepts behind job queues, job classes, programs (how is it different from a stored procedure), windows and chains (if indeed these are all concepts connected with jobs).

One particular problem I have is figuring out how to handle the jobs (one-off or reuse, for example), since the frequency of use depends on the web user and it is therefor possible that one process is started before previous ones have finished.

If anyone knows of a good web page for this or can outline the workflow and objects involved, your help is much appreciated.
Thanks again,
Markus Schneider
  • 1. Re: Conceptual help needed: Making asynchronous calls to database
    sybrand_b Guru
    Currently Being Moderated
    First question: Is this the right approach or are there preferable alternatives?
    Surely this is the right approach if your goal is to wreck the performance of the database and to have the DBA in hospital.

    As your database doesn't have a version, and there is no indication of what you did read, little help is possible.
    I have been able to get jobs working by using 'Morgans library' http://psoug.org/reference

    Monitoring Oracle by PHP applications should be rewarded with a capital summarily sentence, without trial!!

    -------------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: Conceptual help needed: Making asynchronous calls to database
    damorgan Oracle ACE Director
    Currently Being Moderated
    Demos here:
    http://www.morganslibrary.org/reference/pkgs/dbms_scheduler.html
    the docs at http://tahiti.oracle.com

    What any of this has to do with PHP and timeouts though escapes me.
  • 3. Re: Conceptual help needed: Making asynchronous calls to database
    sb92075 Guru
    Currently Being Moderated
    MCS wrote:
    Hi,
    thanks for reading this.
    The problem is as follows:
    I (software developer with experience in PL/SQL but only a little in administration) have a PHP based web page which calls the DB (stored Procedure). I have no control over timeout settings of PHP/Web server and the called stored procedure runs for an indeterminate amount of time.
    In order to avoid web timeouts my solution would be for the web to call a stored procedure which creates (or enables?) a job, returns and monitors the job progress until it completes.

    First question: Is this the right approach
    No.
    Spectacularly non-scalable approach.

    How would you keep track of which job is associated with which browser session in multi-user application given that the web is a stateless environment?
  • 4. Re: Conceptual help needed: Making asynchronous calls to database
    damorgan Oracle ACE Director
    Currently Being Moderated
    You might want to walk this one back sb ... I do what the OP is asking quite often and it scales beautifully.

    You can easily use DBMS_SCHEDULER to run asynchronous jobs ... just set USE_CURRENT_SESSION to FALSE.

    And keeping track is similarly easy as databases were designed to persist data.

    The web may or may not be stateless ... look up Cookies and the UTL_HTTP built-in package.
  • 5. Re: Conceptual help needed: Making asynchronous calls to database
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    In order to avoid web timeouts my solution would be for the web to call a stored procedure which creates (or enables?) a job, returns and monitors the job progress until it completes.
    yes, it can be done.
    you should just design "monitors the job progress" properly.
    if it would be Java, I'd say that session can subscribe for database change notification. I do not know if it is possible in PHP.
    May be you will have to update some status table, or set something by DBMS_APPLICATION_INFO.SET_CLIENT_INFO, then PHP will check V$SESSION periodically.

    First question: Is this the right approach or are there preferable alternatives?
    Is there is some job facility in PHP? Or starting a background PHP process with a separate connection?
    If yes, then it could be an alternative.
  • 6. Re: Conceptual help needed: Making asynchronous calls to database
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    MCS wrote:

    In order to avoid web timeouts my solution would be for the web to call a stored procedure which creates (or enables?) a job, returns and monitors the job progress until it completes.

    First question: Is this the right approach or are there preferable alternatives?
    Can work. However, the "monitoring" part does not make sense. If a PHP process can monitor a job that was started, why can't the same process not wait interactively for a response? The PHP monitor process will do what when the Oracle background/async process complete? Read the output of that Oracle process? Not possible as the Oracle process does not output data (return cursors/rows/whatever) to a client process, like PHP, as it is not wired to any client process. It is a standalone server process.
    One particular problem I have is figuring out how to handle the jobs (one-off or reuse, for example), since the frequency of use depends on the web user and it is therefor possible that one process is started before previous ones have finished.
    Let's say the web output to the user is some kind of detailed billing report for a month. This needs some large table to be processed in Oracle and processing time to create the report exceeds the web server timeout (and acceptable response time for the web user).

    An Oracle PL/SQL package or procedure is created to "do" the detailed billing report. This is started via the web browser calling PHP. The PHP page calling the Oracle procedure to do the detailed billing for that user and that month (2 input parameters).

    This PL/SQL code does the following:
    1. Submits a DBMS_JOB to the queue - this executes the actual proc that does the detailed billing.
    2. A job number is returned by the DBMS_JOB.Submit() call.
    3. Insert a new row into PHP Detailed Billing Application Jobs table that contains the date submitted, the job number, the name of the web user, and the month
    4. Commit - this will make the job submission visible to the job queue manager and make the inserted row visible to other database sessions.

    The PHP page now displays something like "+job successfully submitted+".

    Another PHP page provides a SQL report on the PHP Detailed Billing Application Jobs table - listing jobs for the specific web user. It does an outerjoin with the USER_JOBS table (on job id) to determine whether that job is still in the job queue (busy running, or waiting to be run) - or whether that job is no longer in the queue and has thus completed.

    Okay, now where does this job's output go to? The detailed billing report needs to go somewhere.

    There PL/SQL procedure that does the actual billing report, can output the report as CSV, XML or HTML formats using a CLOB. So this is basically what this procedure does:
    1. Opens a cursor for the detailed billing report SQL
    2. Bulk fetch a 100 or so rows at a time.
    3. Convert these rows into CSV/XML/HTML and write append it to a CLOB (using the DBMS_LOB interface)
    4. When all the row output from the cursor has been processed, the CLOB (containing CSV/XML/HTML data) is inserted into a PHP Detailed Billing Report table (e.g. date completed, web user, report month, CLOB)

    Another PHP report page can display this table's content for a specific user - with an option to click on a specific report, where this runs another PHP page that pulls the CLOB from a specific row in the PHP Detailed Billing Report table and displays (and renders) that CLOB contents.

    Of course, maintenance pages will be needed for the web user to delete old reports from the database, that is no longer needed.

    Likewise, the PL/SQL proc that submits the job can first check the PHP Detailed Billing Report table to see whether the requested report has not already been created, and check the PHP Detailed Billing Application Jobs table if there is not an existing job scheduled.

    BTW, there will need to be more logic and structure to this approach than the simplistic approach I've sketched above - but this approach is the basic template of how one will go about using Oracle procedures producing report output, to service web users in async mode.
  • 7. Re: Conceptual help needed: Making asynchronous calls to database
    760735 Newbie
    Currently Being Moderated
    @damorgan:
    Thank you for your reply.
    "You can easily use DBMS_SCHEDULER to run asynchronous jobs ... just set USE_CURRENT_SESSION to FALSE."

    Thanks for the nudge. So I take it I should use DBMS_SCHEDULER.run_job with above flag set to false rather than using a start date.
    Are there differences in the behaviour of the two approaches or is it simply that since I want to start the execution immediately this is the obvious way to do it.

    Please forgive me if this sounds inane but as I said I am trying to get a grasp of "How things are done" since I have zero previous experience with jobs and need to implement this task rather quickly.
    Any pointers of what parameters to use and which traps to avoid are very helpful in getting meaningfull google results.

    If you could spare a thought on how to handle the problem of one job being created while a previous job is still running, I would appreciate it. More specifically, can I simply change the arguments of the job and "resubmit" it or do I use the copy_job and use an increment as a suffix for the job name?
    So, thanks again!
  • 8. Re: Conceptual help needed: Making asynchronous calls to database
    760735 Newbie
    Currently Being Moderated
    @Billy Verreynne:
    Thank you for taking the time and writing a detailed answer. I really appreciate it.

    As for the "monitoring", perhaps that was a bit too much of a claim. I simply had in mind for the job to write its progress into a table, commit the change and have the web page refresh automatically and query the record until the state is "finished". I have since learned (from you) that I can simply query the user_jobs table, if all I need is to check that the job is finished.
    (As I said, no previous experience with jobs at all, but still in a position of having to use them :-))
    As for your question of why the process cannot simply wait for the job to finish, I have no rights to changing the cgi-handler's (PHP intepreter) timeout and therefor the session will time out if the job takes too long and I do not try this asynchronously.

    I do not need any direct output from the job except that I need to now that it ran and if it was successful or not. But I guess that if I needed output I could use the above approach and write it to a designated table and have the web page read it once the job has finished, right?

    Any reason you used the DBMS_JOB package rather than the DBMS_SCHEDULER package?

    What is the way to handle the situation in which I have to create the same job again, with different arguments, while a previous job is still running?
    Copy it and use a suffix for the job_name?

    Thanks again!
  • 9. Re: Conceptual help needed: Making asynchronous calls to database
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    MCS wrote:

    As for the "monitoring", perhaps that was a bit too much of a claim. I simply had in mind for the job to write its progress into a table, commit the change and have the web page refresh automatically and query the record until the state is "finished". I have since learned (from you) that I can simply query the user_jobs table, if all I need is to check that the job is finished.
    Oracle has a very rich and informative data dictionary - USER_JOBS is one of just many useful views. :-)

    There is also virtual performance (so-called V$ views) that are also useful. For example, your PL/SQL proc can use the DBMS_APPLICATION_INFO interface to record what step it is busy with (e.g. let's say your proc needs to sequentially process 4 tables as 4 steps). This is then visible via the v$sessions view - giving you accurate data about what step your job code is currently busy with.
    I do not need any direct output from the job except that I need to now that it ran and if it was successful or not. But I guess that if I needed output I could use the above approach and write it to a designated table and have the web page read it once the job has finished, right?
    Yes.
    Any reason you used the DBMS_JOB package rather than the DBMS_SCHEDULER package?
    DBMS_JOB is the "old" interface and DBMS_SCHEDULER is the "new" interface - and it provides a whole range of very useful features. But also comes with an increase in complexity.

    If the goal is simply to fire up a background process in a fork() or CreateThread() fashion (as you would code on Unix/Linux or Windows for async processing), then DBMS_JOB is quite capable of doing the job easier than using DBMS_SCHEDULER.
    What is the way to handle the situation in which I have to create the same job again, with different arguments, while a previous job is still running?
    Copy it and use a suffix for the job_name?
    Nothing much when using DBMS_JOB. The unique identifier is job id (generated by Oracle). So you can have a 1001 duplicate jobs - each with a unique job id and each showing as running/waiting to be run in the USER_JOBS view and each job hitting different data due to using different parameters.

    What DBMS_JOB does not support is bind variables - which means each job is an anonymous PL/SQL block that needs a hard parse. Creating a 1001 such "duplicate" jobs means that there will be a 1001 hard parses and a 1001 cursors created in the Share Pool.

    DBMS_SCHEDULER support bind variables. This means that a 1001 "duplicate" jobs will result in 1 hard parse, 1 cursor and 1000 soft parses using that same cursor. But of course, doing it like this requires more effort on your side as the interface to use is more sophisticated than the DBMS_JOB interface.
  • 10. Re: Conceptual help needed: Making asynchronous calls to database
    760735 Newbie
    Currently Being Moderated
    @Billy Verreynne:
    Many thanks, you are very helpful.

    As a software developer I do not get to spend nearly enough time to gain a greater insight into database matters.
    Your comments have helped immensly and I will now try to put something together.
    Regards,
    Markus
  • 11. Re: Conceptual help needed: Making asynchronous calls to database
    760735 Newbie
    Currently Being Moderated
    For anyone else reading this thread I have found this doc page to be very helpful in getting to understand the entities involved.

    http://docs.oracle.com/cd/B19306_01/server.102/b14231/schedover.htm