Home » SQL & PL/SQL » SQL & PL/SQL » Time spent for the statements inside a procedure (Oracle 10.2.0.3)
Time spent for the statements inside a procedure [message #397696] Mon, 13 April 2009 07:15 Go to next message
user2004
Messages: 33
Registered: April 2009
Member
Is there a way to know where most time spent in a procedure? The procedure contains dml operations.My procedure contains more than 3000 lines of code

Thanks
Re: Time spent for the statements inside a procedure [message #397697 is a reply to message #397696] Mon, 13 April 2009 07:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use trace & tkprof or awr reports.
Re: Time spent for the statements inside a procedure [message #397700 is a reply to message #397697] Mon, 13 April 2009 07:33 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member
Thanks Frank Sir. The peocedure while executed is taking 1 minute to execute. But as per the request it needs to be tuned.
What approach should I take to make the time less. The procedure itself contains many insert operations. The data is being retrived through cursor. As it contains more than 3000 lines of codes its quite difficult to trace.


Is the tkproof/trace reports only option? Thanks for your help
Re: Time spent for the statements inside a procedure [message #397702 is a reply to message #397700] Mon, 13 April 2009 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't cursor loop, use only SQL.

Regards
Michel
Re: Time spent for the statements inside a procedure [message #397719 is a reply to message #397700] Mon, 13 April 2009 09:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What has the number of lines in your procedure to do with how difficult it is to trace?
Do you actually know how trace & tkprof work?
Re: Time spent for the statements inside a procedure [message #397757 is a reply to message #397719] Mon, 13 April 2009 15:30 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member
Sorry Sir, I just wanted to mean that its difficult on my part to analyze through the entire bunch of codes.Instead I want to know where the most time spent for the procedure. The procedure calls so many sub procedures/functions and dml operations
Re: Time spent for the statements inside a procedure [message #397761 is a reply to message #397757] Mon, 13 April 2009 16:13 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Creating a trace file is quite easy. You may need your DBA's help to get access to the trace file on the DB server to be able to analyze it using TKPROF.

Other easy approaches are to include some DBMS_OUTPUT lines in your code at the main stages of execution is possible. This will help you narrow the search down to certain areas of the code (e.g. query vs insert). Wallclock seconds only tells part of the story and so isn't suiable for more than basic tuning.

SQL> set serveroutput on
SQL> DECLARE
  2     t0   PLS_INTEGER;
  3     x    PLS_INTEGER;
  4  BEGIN
  5     t0 := DBMS_UTILITY.get_time;
  6
  7     FOR i IN 1 .. 100000000
  8     LOOP
  9        x := i;
 10     END LOOP;
 11
 12     DBMS_OUTPUT.put_line
 13          ('t0-->t1 = ' || TO_CHAR (DBMS_UTILITY.get_time - t0)||' sec/100' );
 14
 15     FOR j IN 1 .. 50000000
 16     LOOP
 17        x := j;
 18     END LOOP;
 19
 20     DBMS_OUTPUT.put_line
 21          ('t0-->t2 = ' || TO_CHAR (DBMS_UTILITY.get_time - t0)||' sec/100'  );
 22  END;
 23  /
t0-->t1 = 166 sec/100
t0-->t2 = 250 sec/100

PL/SQL procedure successfully completed.

SQL>


Re: Time spent for the statements inside a procedure [message #397783 is a reply to message #397757] Mon, 13 April 2009 23:03 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also use dbms_profiler.

Regards
Michel
Previous Topic: Session browser access
Next Topic: Logging DMLs
Goto Forum:
  


Current Time: Fri Dec 09 01:44:06 CST 2016

Total time taken to generate the page: 0.18995 seconds