I am having a problem and not able to proceed further.
Can you please help me .
I am having 10 procedure from different packages and these procedures internally call some other procedures and function.
this needs to be executed nearly 300 times daily.currently this is happening in sequence like 1,2 3,...300.
I want to run this in 10 parallel jobs,so that we can get some performance improvement.
Can anybody please help me.how can i achive this.
You requirement is not clear(10 procs, need to run 300 times then altogether need to run 10*300=3000 daily?). please post your clear requirement with DB version also
However.If there is no impact on running them in parallel, then why not you schedule them to run on same time.
Sorry, if i am not clear.
My db version is 10g version 2.
we have around 300 stores and every night we have day end stuff that run for all the 300 stores.
there is a cron job which runs at 9:00 PM. which picks all the store numbers in cursor and does pre_dayend, dayend and post day end stuff for each store in sequence.
as this is taking long time, i want to tune it and make it run faster.
Edited by: Sudhir on Nov 19, 2012 10:40 AM
If you are opening a single cursor for all stores then you cannot perform operation in parallel. Either use bulk collect from cursor to improve performance OR make a procedure with store_name as parameter which in turn open and process cursor for that perticular store_name only.
Then schedule this procedure for all different stores as parameter.
Just make a generic procedure that process data for store that passed as a parameter. Ex. if you pass store1 as parameter it process only store1 data as per your requirement. Now schedule this procedure with different parameter as a different job (per job per parameter)
That means you need to schedule 300 different Jobs for 300 different stores.
Ofcourse it make your cron entry large but you must achieve much better performance.
If you go with your existing code then you must go through(if applicable) below to improve some performance.
1. BULK COLLECT clause
2. FORALL Clause
3. PIPELINE Function
thanks for the reply.
I checked with my DBA and he doesn't liked the idea of 300 crons.
Is there any other way or any more ideas we can use to tune the procedure.As we implemented the below mentioned ideas already in the code.
Thier is no direct way to achieve performance, you need to dig more into your code to achieve the same. Without seeing your code, logic, design, business logic I cannot help you much.
However you can check for below point too.
. Use Parallel Hints for your DML operations,
. Can Compile PLSQL procedure to native,
. Program inlining
. Pipeline Function
. Bulk SQL and Bulk Binding
. Tune Subprogram Invocations
. Tune Loops
. Tune Computation-Intensive PL/SQL Code
. Use SQL Character Functions
. Put Least Expensive Conditional Tests First
. Tune SQL Statements
. Tune Function Invocations in Queries
Available from 11 g
. The DBMS_PARALLEL_EXECUTE package enables the user to incrementally update table data in parallel
. Function result catching