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: 29
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.

Report message to a moderator

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: 2882
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?

Report message to a moderator

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: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
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

Report message to a moderator

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: 2882
Registered: May 2007
Location: Scotland
Senior Member
Yes, but how will that make my car go faster?

Report message to a moderator

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: 21825
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).

Report message to a moderator

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: 7901
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

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jul 13 13:04:39 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.