OraFAQ Forum: SQL & PL/SQL » Time spent for the statements inside a procedure

Home » SQL & PL/SQL » SQL & PL/SQL » Time spent for the statements inside a procedure (Oracle 10.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Time spent for the statements inside a procedure [message #397696]

Mon, 13 April 2009 07:15

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

Report message to a moderator

Re: Time spent for the statements inside a procedure [message #397697 is a reply to message #397696]

Mon, 13 April 2009 07:17

Frank
Messages: 7901
Registered: March 2000

Senior Member

Use trace & tkprof or awr reports.

Report message to a moderator

Re: Time spent for the statements inside a procedure [message #397700 is a reply to message #397697]

Mon, 13 April 2009 07:33

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

Report message to a moderator

Re: Time spent for the statements inside a procedure [message #397702 is a reply to message #397700]

Mon, 13 April 2009 07:42

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Don't cursor loop, use only SQL.

Regards
Michel

Report message to a moderator

Re: Time spent for the statements inside a procedure [message #397719 is a reply to message #397700]

Mon, 13 April 2009 09:11

Frank
Messages: 7901
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?

Report message to a moderator

Re: Time spent for the statements inside a procedure [message #397757 is a reply to message #397719]

Mon, 13 April 2009 15:30

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

Report message to a moderator

Re: Time spent for the statements inside a procedure [message #397761 is a reply to message #397757]

Mon, 13 April 2009 16:13

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>

Report message to a moderator

Re: Time spent for the statements inside a procedure [message #397783 is a reply to message #397757]

Mon, 13 April 2009 23:03

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can also use dbms_profiler.

Regards
Michel

Report message to a moderator

Previous Topic:	Session browser access
Next Topic:	Logging DMLs

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 05:03:59 CST 2024