Home » RDBMS Server » Performance Tuning » Improve procedure's performance
Improve procedure's performance [message #566443] Fri, 14 September 2012 11:38 Go to next message
suryay22
Messages: 5
Registered: September 2012
Location: Bangalore
Junior Member
I have one procedure i.e it's performance is very low. So to improve performance of procedure,
what steps do i follow?
please help me...
Re: Running a report from PL/SQL code [message #566444 is a reply to message #566443] Fri, 14 September 2012 11:57 Go to previous messageGo to next message
tigsav
Messages: 49
Registered: April 2012
Member
Hi,

You should run that procedure using DBMS_PROFILER package.This will help you to identify the bottlenecks in the code .Based on that we should be able to find out the places where the procedure can be improved.
If you are using Oracle 11g DBMS_HPROF will be much more useful.
Re: Running a report from PL/SQL code [message #566446 is a reply to message #566444] Fri, 14 September 2012 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Running a report from PL/SQL code [message #566465 is a reply to message #566446] Sat, 15 September 2012 00:28 Go to previous messageGo to next message
suryay22
Messages: 5
Registered: September 2012
Location: Bangalore
Junior Member
please can you just explain steps?
Re: Running a report from PL/SQL code [message #566466 is a reply to message #566465] Sat, 15 September 2012 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Step 1: Read the documentation (about DBMS_HPROF and DBMS_PROFILER)

Regards
Michel
Re: Improve procedure's performance [message #566492 is a reply to message #566443] Sat, 15 September 2012 20:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
?how to tune? is a very wide open question. Such a vague question can only be handled with a vague answer of sorts. So lets start with a simple reasoning and work from there.

If you want to reduce the time taken by a process, then you need to know where inside the process your time is going. A process (like your procedure), is made up of parts. You need to find out how long each part takes. Once you know this you can make some reasonable guesses about what to do next.

Consider for example, that you have a process that is made up of two parts:

1) A login step with takes 10 seconds
2) A reporting step that takes 10 hours


You are told you need to take at least 2 hours off the process. So... how successful will you be in taking 2 hours of the process if you spend your time tuning the login step?

So, you need to start your effort by doing the following:

1) break your procedure into parts.  This can be pretty much anything you want, 
but use some common sense.  For example, each SQL statement inside your process could 
each be one part.  Break it up in whatever way makes sense to you.

2) get runtime stats for each part.  This can be as simple as monitoring the "WALL CLOCK" 
for each step.  How many eplapsed seconds does each part take.


Once you know how long each part takes, you will know which parts you need to concentrate on in order to achieve a speed up. If you want to make money, you need to go where the money is. Similarly, if you want to save time, you have to go where the time is being spent.

Here is an very simple example:

begin
dbms_output.put_line(to_char(sysdate,'dd-mon-rrrr hh24:mi:ss'));
   do_sql1;
dbms_output.put_line(to_char(sysdate,'dd-mon-rrrr hh24:mi:ss'));
   do_sql2;
dbms_output.put_line(to_char(sysdate,'dd-mon-rrrr hh24:mi:ss'));
   do_sql3;
dbms_output.put_line(to_char(sysdate,'dd-mon-rrrr hh24:mi:ss'));
end;
/


With this info, you can figure out, using simple date math, how many seconds do_sql1 took, do_sql2 took, do_sql3 took. Yes, this means you have to modify your code, but you will find this way easier to do that using plsql profiling.

Do this and show us how long the entire procedure runs, and how long the different parts run.

Good luck. Kevin

[Updated on: Sun, 16 September 2012 14:40]

Report message to a moderator

Re: Improve procedure's performance [message #566497 is a reply to message #566492] Sun, 16 September 2012 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but you will find this way easier to do that using plsql profiling


There is almost nothing to do to use the 2 mentioned packages.
Add START_PROFILER call at the beginning of the code you want to watch and END_PROFILER at the end (and possibly SUSPENP and RESUME on parts you don't want to follow), execute the code and query the profiler tables.
There is a demo in %ORACLE_HOME%\plsql\DEMO\profdemo.sql

Regards
Michel

[Updated on: Sun, 16 September 2012 01:24]

Report message to a moderator

Re: Improve procedure's performance [message #566518 is a reply to message #566497] Sun, 16 September 2012 14:43 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Well, I will look again at it. Maybe in 11g it is better.

But my experiences with these tools is very poor. I found them to have mistakes in their results, and require training time I did not want to spend on them, and provide too much detail for my needs.

Kevin
Previous Topic: Help needed to tune the SQL (merged 2)
Next Topic: inbound connection timed out (ORA-3136)
Goto Forum:
  


Current Time: Thu Mar 28 08:26:22 CDT 2024