This content has been marked as final. Show 8 replies
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
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