CODEWITHSUNDEEP
performanceoracle-databaseplsql
SWilk
SWilk

Reputation: 3464

Oracle: Limiting resource consumption by long-running procedure

I have created a procedure which takes much time and resources to complete. My DB Admin had to kill the procedure, as it was slowing down Oracle server to much and all the other users in the company could not work.

My procedure is something like this:

procedure my_proc IS
  cursor c IS (SELECT id FROM very_larg_table);
  row_ c%rowtype;
begin
  open c;
    fetch c into row_;
    exit when c%notfound;

    run_a_task_that_takes_0.2_of_a_second_per_run(row_.id);
    commit;

  end loop;
  close c;
end;

The very_large_table have about 250 thousands of rows. Multiply that by 0.2 seconds and you get 14 hours of execution time.

This task is to be run only once. It should build additional data based on many conditions. After it is run this data will be build live for every new record in acceptable time and it will not be rebuild historically any more.

But this taks is way to have to be run on our database. How can I slow it down, so it executes during say 24 hours, but with less impact on the whole system?

Upvotes: 1

Views: 250

Answers (2)

Kirill Leontev
Kirill Leontev

Reputation: 10931

  1. bulk collect data from very_larg_table into a collection and loop through it, not through a cursor - it will eliminate overhead of 250k plsql/sql context switches and fetches.

  2. commit only once at the end of your transaction

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

To limit access to a resource, you can use the Oracle Database Resource Manager.

With the Resource Manager, you can:

  • Guarantee certain sessions a minimum amount of CPU regardless of the load on the system and the number of users.

  • Distribute available CPU by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage can be given to ROLAP (relational online analytical processing) applications than to batch jobs.

The resource manager is accessed through the DBMS_RESOURCE_MANAGER package.

Upvotes: 1

Related Questions