| about increasing performance speed of slow running package [message #597386] |
Fri, 04 October 2013 04:57  |
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  |
cookiemonster
Messages: 13966
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 #597395 is a reply to message #597392] |
Fri, 04 October 2013 06:32 |
John Watson
Messages: 8966
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
8939513598$ wrote on Fri, 04 October 2013 11:52about 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.
|
|
|
|
|
|
| Re: about increasing performance speed of slow running package [message #597596 is a reply to message #597425] |
Mon, 07 October 2013 01:53 |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
