This discussion is archived
5 Replies Latest reply: Dec 10, 2012 8:46 PM by BillyVerreynne RSS

Scheduler job - optimization

jaggyam Newbie
Currently Being Moderated
Hi,

I am using 11g release 2.

I have a procedure triggering 7 queries. I am trying to trigger my 7 queries in multiple thread and each thread will be triggered instantaneously and die after the execution, so that I can gain so performance. Say, each query takes a second to complete. if i triggering all at once will complete in 1 second. Whereas if I run those sequentially will take 7 seconds. So that is the reason to go for multiple threads.

I am using 'oracle scheduler' with light weight option. We are trying to call the same procedure from java in 100 threads. 100*7 is 700 jobs. The job queue process limit in oracle is 1000.

I am dropping the program and jobs at the end of my procedure by using a job again (so it is 8 jobs per call now) . I am doing all these because I want to complete each call of this procedure to complete in less than a second. But I am noticing that finding a slot on the job queue is so inconsistent. Some times it is waiting for more than 25 minutes also.

My window is less than a second for each java call. So all the jobs for each thread should be triggered instantly. Can you please guide me to achieve this, even may be with different option.

Thanks in advance,
jaggyam
  • 1. Re: Scheduler job - optimization
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    jaggyam wrote:

    I am using 11g release 2.
    That is a product name - the 4 digit version number is what is important to add to a problem description.
    I have a procedure triggering 7 queries.
    PL/SQL procedure?
    I am trying to trigger my 7 queries in multiple thread and each thread will be triggered instantaneously and die after the execution, so that I can gain so performance.
    PL/SQL does not support threading in the fashion that other languages does, due to the environment it runs in.
    Say, each query takes a second to complete. if i triggering all at once will complete in 1 second. Whereas if I run those sequentially will take 7 seconds. So that is the reason to go for multiple threads.
    Keep in mind that Oracle is not a real-time database, and PL/SQL is not a real-time programming language.
    I am using 'oracle scheduler' with light weight option. We are trying to call the same procedure from java in 100 threads. 100*7 is 700 jobs. The job queue process limit in oracle is 1000.
    This is a bit confusing... are you use PL/SQL with DBMS_SCHEDULER as threading model? Are you using Java with thin JDBC sessions as threading model?
    I am dropping the program and jobs at the end of my procedure by using a job again (so it is 8 jobs per call now) .
    Why? A scheduled job that has no interval is only executed once - so why are you attempting to do delete it?
    I am doing all these because I want to complete each call of this procedure to complete in less than a second. But I am noticing that finding a slot on the job queue is so inconsistent. Some times it is waiting for more than 25 minutes also.
    Correct. As Oracle jobs interface is the wrong architecture and interface to use when fine-grain threading is needed. It was never designed, nor intended, or recommended, to be used in that fashion.
    My window is less than a second for each java call. So all the jobs for each thread should be triggered instantly. Can you please guide me to achieve this, even may be with different option.
    Create a thread pool in Java - with each thread owning its own (dedicated) Oracle session. Create a thread manager that dishes out work to idle threads, and deals (if needed) with thread outputs. Kind of like the Shared Server (Multi-Thread Server) implementation for database sessions in Oracle. Shared server pool consists of processes that execute the requests. Dedicated servers are responsible for interfacing the caller and its request, with the shared server pool.
  • 2. Re: Scheduler job - optimization
    jaggyam Newbie
    Currently Being Moderated
    I have 7 queris in my procedure. I wat to run each query in seperate thread. I want each query/threas to get executed instantly. I tried 'scheduler job' but unsuccessfu because I want to trigger and ocmplete on same secone.

    when i do that using scheduler/job it is waiting for long time to find a slot.

    Please let me knwo the best approach to acheive this.

    Thank a ton
  • 3. Re: Scheduler job - optimization
    AlbertoFaenza Expert
    Currently Being Moderated
    jaggyam wrote:
    I have 7 queris in my procedure. I wat to run each query in seperate thread. I want each query/threas to get executed instantly. I tried 'scheduler job' but unsuccessfu because I want to trigger and ocmplete on same secone.

    when i do that using scheduler/job it is waiting for long time to find a slot.

    Please let me knwo the best approach to acheive this.

    Thank a ton
    It looks you haven't read in details the suggestions from Billy.
    That's why you maybe have so many unanswered questions:
    Handle:      jaggyam  
    Status Level:      Newbie (35)
    Registered:      Feb 26, 2001
    Total Posts:      422
    Total Questions:      56 (41 unresolved) 
    Actually Billy answered in detail to your question and suggested a method. I don't understand why you seem only focused on the same wrong approach (scheduler/job).

    Regards.
    Al
  • 4. Re: Scheduler job - optimization
    jaggyam Newbie
    Currently Being Moderated
    I always read the responses carefully and close those accordingly. But if some threads are unclosed they are legitimate, i believe. my apologies, if i missed out some.

    Kindly let me know the response of billy you are refering to.

    Thanks,
    Jagadish Kumar A.M
  • 5. Re: Scheduler job - optimization
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    jaggyam wrote:
    I have 7 queris in my procedure. I wat to run each query in seperate thread. I want each query/threas to get executed instantly. I tried 'scheduler job' but unsuccessfu because I want to trigger and ocmplete on same secone.

    when i do that using scheduler/job it is waiting for long time to find a slot.
    We've had this conversation before. I told you then that using Oracle jobs are not the right mechanism to use.
    Please let me knwo the best approach to acheive this.
    Client-side threading. A thread pool with existing established database connections to dedicated server processes are needed. This is the only way to eliminate latency having to deal with the Listener (brand new connection), or having to deal with busy dispatchers and shared servers unable to immediately serve you (shared server connections).

    This thread pool needs a mechanism for work to be handed out. Oracle uses a dispatcher model with their "Multi Thread Server" implementation. Whatever method you choose, semaphores and mutexes are likely required to manage threads's access to the same data structures and synchronise it where needed.

    Not simplistic programming and requires a basic knowledge of threading concepts, and how to use the threading model of that language and o/s kernel.

Legend

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