Home » RDBMS Server » Performance Tuning » about increasing performance speed of slow running package (Oracle 11.2.0.2.0, Oracle Enterprise Linux -5)
about increasing performance speed of slow running package [message #597386] Fri, 04 October 2013 04:57 Go to next message
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
Senior Member
Suppose a package running very slowly in oracle, how to improve it's performance?, generally, if an sql query running very slow, sql tuning method can be adopted, but what actually if a trigger, package or a procedure running very slow, what method is best to fine-tune it?


Re: about increasing performance speed of slow running package [message #597389 is a reply to message #597386] Fri, 04 October 2013 05:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Rewrite the slow code so that it's fast.
In very general terms packages are normally slow either because:
a) they're calling a slow sql statement(s)
b) doing a lot of unnecessary work - eg multiple nested for loops with a dml in the middle when the whole thing could be replaced with a single dml.

You want to improve the performance you need to work out where the time is being spent.
Re: about increasing performance speed of slow running package [message #597392 is a reply to message #597389] Fri, 04 October 2013 05:52 Go to previous messageGo to next message
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
Senior Member
about DBMS_PROFILER package any usability to improve package speed?
Re: about increasing performance speed of slow running package [message #597395 is a reply to message #597392] Fri, 04 October 2013 06:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
8939513598$ wrote on Fri, 04 October 2013 11:52
about DBMS_PROFILER package any usability to improve package speed?
The profiler certainly can't make anything run faster. It may help you determine where the time is being spent. Look at dbms_hprof as well for this.
Apart from tuning the SQL called by the pl/sql, in my experience a lot of issues are caused by programmers using pl/sql, particularly cursors, unnecessarily. Some bad programmers don't seem to appreciate that a relational database is a set oriented environment. They insist on writing procedural code that navigates from row to row, rather than statements that operate on row sets.
icon14.gif  Re: about increasing performance speed of slow running package [message #597425 is a reply to message #597395] Fri, 04 October 2013 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Some bad programmers don't seem to appreciate that a relational database is a set oriented environment. They insist on writing procedural code that navigates from row to row, rather than statements that operate on row sets.


+1

Re: about increasing performance speed of slow running package [message #597596 is a reply to message #597425] Mon, 07 October 2013 01:53 Go to previous messageGo to next message
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
Senior Member
Hi John, it's actually a discussion between my project manager and myself, i explained on steps regarding SQL Tuning Method, and PM wants answer for improving the performance of slow running package, procedure & function, i explained on DBMS_PROFILER ,is that correct? improving the performance of the package,procedure & function is possible based on data collected by DBMS_PROFILER.
Re: about increasing performance speed of slow running package [message #597598 is a reply to message #597596] Mon, 07 October 2013 01:58 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
How about DBMS_HPROF?
Re: about increasing performance speed of slow running package [message #597600 is a reply to message #597598] Mon, 07 October 2013 02:19 Go to previous message
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
Senior Member
i'll need to refer it, thanks for your suggession..,
Previous Topic: MEMORY_TARGET error
Next Topic: performance - index creation on text column
Goto Forum:
  


Current Time: Thu Mar 28 07:00:04 CDT 2024