This discussion is archived
12 Replies Latest reply: Jan 22, 2013 4:58 AM by DIVI RSS

Run a oracle query asynchronously

DIVI Newbie
Currently Being Moderated
in my stored procedure code is like

execute immediate 'update table_1 set col1='some new value'';
execute immediate 'update table_2 set col1='some new value'';
execute immediate 'update table_3 set col1='some new value'';
.
.
.
now these tables are very huge in size, and i don't want to wait until those queries are completed, i want to move to the next steps in procedure.
is there any simple way to do this rather creating a job and scheduling it and dropping ? ?
  • 1. Re: Run a oracle query asynchronously
    BluShadow Guru Moderator
    Currently Being Moderated
    DIVI wrote:
    in my stored procedure code is like

    execute immediate 'update table_1 set col1='some new value'';
    execute immediate 'update table_2 set col1='some new value'';
    execute immediate 'update table_3 set col1='some new value'';
    .
    .
    .
    now these tables are very huge in size, and i don't want to wait until those queries are completed, i want to move to the next steps in procedure.
    is there any simple way to do this rather creating a job and scheduling it and dropping ? ?
    Perhaps first have a look at why on Earth you are issuing all those multiple updates using dynamic statements. What is the purpose of them (apart from updating the tables)? i.e. why have they been written using Execute Immediate?

    If you want processes to run concurrently, then you have very few options, one being to schedule them to run as seperate jobs.
  • 2. Re: Run a oracle query asynchronously
    Rahul_India Journeyer
    Currently Being Moderated
    DIVI wrote:
    in my stored procedure code is like

    execute immediate 'update table_1 set col1='some new value'';
    execute immediate 'update table_2 set col1='some new value'';
    execute immediate 'update table_3 set col1='some new value'';
    .
    .
    .
    now these tables are very huge in size, and i don't want to wait until those queries are completed, i want to move to the next steps in procedure.
    is there any simple way to do this rather creating a job and scheduling it and dropping ? ?
    Yes use dbms sleep
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6019886867656
  • 3. Re: Run a oracle query asynchronously
    Solomon Yakobson Guru
    Currently Being Moderated
    Rahul India wrote:
    Yes use dbms sleep
    DBMS_LOCK.SLEEP is synchronous. It simple waits specified number of seconds. What OP can do, as Blu already noted, is use DBMS_JOB/DBMS_SCHEDULER and submit each update as a separate job.

    SY.
  • 4. Re: Run a oracle query asynchronously
    Purvesh K Guru
    Currently Being Moderated
    DIVI wrote:
    in my stored procedure code is like

    execute immediate 'update table_1 set col1='some new value'';
    execute immediate 'update table_2 set col1='some new value'';
    execute immediate 'update table_3 set col1='some new value'';
    .
    .
    .
    now these tables are very huge in size, and i don't want to wait until those queries are completed, i want to move to the next steps in procedure.
    is there any simple way to do this rather creating a job and scheduling it and dropping ? ?
    You can execute them in Parallel. DBMS_PARALLEL_EXECUTE examples available in 11g.
  • 5. Re: Run a oracle query asynchronously
    DIVI Newbie
    Currently Being Moderated
    My actual queries are complicated and they will form at run time so i used execute immediate.
    so to use DBMS_SCHEDULER, i should create job for each query, schedule it for one time and drop correct ? ? ?
    once i submit job, control moves to next lines so when i should drop those created jobs ? ?
  • 6. Re: Run a oracle query asynchronously
    BluShadow Guru Moderator
    Currently Being Moderated
    Read the fine manual:

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm#ARPLS72260
  • 7. Re: Run a oracle query asynchronously
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    DIVI wrote:
    in my stored procedure code is like

    execute immediate 'update table_1 set col1='some new value'';
    execute immediate 'update table_2 set col1='some new value'';
    execute immediate 'update table_3 set col1='some new value'';
    And the very obvious problem is total and complete lack of bind variables.

    Not to mention that dynamic SQL is 99% of the used for the wrong reasons - and there are no reasons given to believe that your use of dynamic SQL is in the 1% category.
    now these tables are very huge in size, and i don't want to wait until those queries are completed, i want to move to the next steps in procedure.
    "Huge" tables have nothing to do with the speed of the update. What determines the performance is the amount of work to be done. And this boils down to the amount of I/O that needs to be done, amount of CPU to spend on triggers and other objects, and the time waited due to serialisation (usually due to poor app concurrency design).
    is there any simple way to do this rather creating a job and scheduling it and dropping ? ?
    Assuming valid reasons for using parallel processing/background processing, I would first look at using DBMS_PARALLEL_EXECUTE. As already mentioned.
  • 8. Re: Run a oracle query asynchronously
    rp0428 Guru
    Currently Being Moderated
    >
    "Huge" tables have nothing to do with the speed of the update.
    >
    Nonsense! As you just said yourself
    >
    What determines the performance is the amount of work to be done. And this boils down to the amount of I/O that needs to be done,
    >
    The amount of I/O that needs to be done can most certainly have to do with the size of the table. Try this simple test. Update one row of a table that contains 10 rows and has no indexes.

    Now update one row of a similar table that has 10 million rows.

    Post the results.

    Did the 'huge' table have anything to do with the speed of the update? I think so.
    >
    Assuming valid reasons for using parallel processing/background processing, I would first look at using DBMS_PARALLEL_EXECUTE. As already mentioned.
    >
    I would think DBMS_SCHEDULER would be the first choice.

    OP wants to run multiple queries in parallel. That package is more commonly, and more appropriately, used to execute a single query in parallel.
  • 9. Re: Run a oracle query asynchronously
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Let me rephrase what I was trying to convey. The size of the table does not determine the speed of the SQL, the amount of I/O does.

    It's about the number of rows being updated - not about the total number of rows in the table. E.g. a single row update of a billion row table (via unique index scan) is faster than a 10 row update in a 1000 row table via a full table scan.

    Obviously there are a number of other factors that drives the I/O workload. Such as number of indexes, average row per blocks, average size of row, etc.

    But the basic premise that the amount of I/O determines performance and not the size of the table, is sound. So instead of blindly staring at the size of the table, look at the I/O workload itself. Determine how to optimise that. Or if a hefty workload is the minimum that needs to be done to get to the answer, parallelise that.

    I have seen a lot of FUD here when it comes to VLT. Silly questions ask whether Oracle is capable of dealing with large tables. Wanting to stuff up the database design by splitting the VLT into smaller physical tables and then slap dynamic SQL on the mess as code logic is now needed to figure out which table contains the data. Etc.

    Performance fundamentals and concepts do not change when a table is large. It remains the same. It is about using the minimal amount of I/O, memory and CPU to achieve the desired result. It is not about how large the table (or database) happens to be.
  • 10. Re: Run a oracle query asynchronously
    rp0428 Guru
    Currently Being Moderated
    >
    It's about the number of rows being updated - not about the total number of rows in the table.
    >
    It can be but not always. If a full table scan has to be done to find the rows being updated any additional overhead to actually update the rows once they are found can be miniscule compared to the io to do the full table scan.
    >
    But the basic premise that the amount of I/O determines performance and not the size of the table, is sound.
    >
    Only partially true. The type of I/O can also be a factor. Multi-block reads to read a given number of blocks can be much faster than reading fewer blocks using single-block reads like some indexes have to do.

    Blanket statements like 'the size of a table does not matter' give the wrong impression if those 'other factors' aren't mentioned.
  • 11. Re: Run a oracle query asynchronously
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    rp0428 wrote:
    >
    It's about the number of rows being updated - not about the total number of rows in the table.
    >
    It can be but not always. If a full table scan has to be done to find the rows being updated any additional overhead to actually update the rows once they are found can be miniscule compared to the io to do the full table scan.
    Are we not saying the exact same thing here - it is about the I/O workload? Your example is simply a FTS to illustrate one of the more severe cases of I/O workloads.
    >
    But the basic premise that the amount of I/O determines performance and not the size of the table, is sound.
    >
    Only partially true. The type of I/O can also be a factor. Multi-block reads to read a given number of blocks can be much faster than reading fewer blocks using single-block reads like some indexes have to do.
    There are many technical factors. Most of them far outside the job description of a developer.

    As a developer, your goal is to create efficient workloads. Where the premise I stated is relevant IMO.

    It is the job of the dba/sysadmin to ensure that that workload is also optimal at storage layer. Such as block size of tablespace, pctfree setting that impacts the available row storage in a block, database instance I/O parameters that enables the CBO to make an informed decision between single and multi-block reads, etc.

    IMO there are times to rather convey basic concepts to a developer - like "+focus on the workload created by your code and not the size of the table+" - than to overwhelm the developer with technical intricacies that, though interesting, do not address the basics that the developer needs to know and apply in his/her daily job of creating application code.
  • 12. Re: Run a oracle query asynchronously
    DIVI Newbie
    Currently Being Moderated
    i used dbms_scheduler with
    job_action => my query,
    start_date => systimestamp,
    repeat_interval => null,

    as repeat_interval is null it run only once
    it worked for me.
    Thank you all for your suggestions.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points