Home » SQL & PL/SQL » SQL & PL/SQL » PL SQL procedure optimization
PL SQL procedure optimization [message #271469] Mon, 01 October 2007 13:28 Go to next message
pa1sas3
Messages: 27
Registered: June 2005
Junior Member
I have a procedure that uses a pl/sql collection - having roughly 8000 rows. There are many validations (around 25 queries) on tables with 100,000 rows.
It takes 55-70 minutes for the procedure to run.

Any leads as to if we can make it faster. All the queries are tuned up. Any suggestions on the DB server parameters that could be set/changed (if at all) will be helpful.

Thanks.
Re: PL SQL procedure optimization [message #271473 is a reply to message #271469] Mon, 01 October 2007 13:37 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
My car has 4 wheels (and a steering wheel) it runs on petrol. How can I make it go faster?
Re: PL SQL procedure optimization [message #271475 is a reply to message #271469] Mon, 01 October 2007 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No database parameter to change.
Application code to change.
Follow the prime directive:

Application must only do what is absolutly mandatory

Regards
Michel
Re: PL SQL procedure optimization [message #271477 is a reply to message #271475] Mon, 01 October 2007 13:40 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Yes, but how will that make my car go faster?
Re: PL SQL procedure optimization [message #271497 is a reply to message #271477] Mon, 01 October 2007 14:19 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Drop it from an airplane.
Quote:
There are many validations (around 25 queries) on tables with 100,000 rows. It takes 55-70 minutes for the procedure to run.

Do you *must* run all the validations, or do you consider the complete process failed as soon as the first one fails? If so, it *might* considerably consume time required to run the procedure. Also, you might do some statistics and run validation code which is likely to fail first (so that other procedures do not have to be run at all).
Re: PL SQL procedure optimization [message #271551 is a reply to message #271497] Tue, 02 October 2007 00:46 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In addition to earlier suggestions:
Do you need to do these validations using pl/sql? Can't (at least) some of them be done using a single sql?
Do you use bulk fetches to get each set of 8000?

Lots of things to consider
Previous Topic: Insert from cursor
Next Topic: problem with procedure not using defaults when called via dbms job or manually
Goto Forum:
  


Current Time: Thu Dec 08 10:42:57 CST 2016

Total time taken to generate the page: 0.18120 seconds