• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1018
  • Last Modified:

Debugging a Oracle PLSQL stored procedure

I have a java code calling several Oracle stored procedures. All but one is taking too long to execute.

How do I find out the troubles some PLSQL lines. How do I debug the Stored Procedure? I have Toad and SQL Plus command line tools.

please help.
0
codemonkey2480
Asked:
codemonkey2480
2 Solutions
 
sdstuberCommented:
you can step through the procedure using Toad much like you would a debugger in other IDE's.

Even better, ask your dba to install the dbms_profiler objects (or hierarchical profiler if 11g)

Toad can create a modified set of profiler tables for you to use and has a profiler viewer that is very nice but you will still need to have the profiler package created
0
 
sdstuberCommented:
if profiling and code stepping arent' viable options then you can always resort to embedded dbms_output comments in your code, run it and then view the output in Toads output tab.

I would use the profiler first though, it gives much better results
0
 
codemonkey2480Author Commented:
sdstuber - Using the Procedure Editor? the SP compiles fine no errors, but when I run it it takes way too long. No idea why, how to figure out which statement is taking time?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Aaron ShiloChief Database ArchitectCommented:
hi

you should look at the execution plan.

you can look at the point where the COST jumps and thats probably the area where the query/proc runns into a havy process.

or just post the exec plan an let up take a look :-)
0
 
sdstuberCommented:
>>> how to figure out which statement is taking time?

make sure you have the debug toolbar enabled, run your procedure with the debugger, as you step through each line you'll be able to see how long it took.

or, as mentioned above, use the profiler,  after execution finishes the profiler report (Toad will do this for you) you will have line by line summary of time


OR, put dbms_output  statements through your code, print a timestamp in each one, run the code, check the output and see for which statements take the longest time
0
 
sdstuberCommented:
ashilo - this is pl/sql,  not sql.

There is no "execution plan" for a pl/sql procedure
0
 
Aaron ShiloChief Database ArchitectCommented:
yes thats right "sdstuber"

codemonkey2480: you can execute the contents of the procedure (the query)
outside the scope of the procedure in plain sql and generate a explain plan OR

You can "wrap" the procedure or function with another procedure / function that performs the following;

-----------
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';

<procedure or function to be traced>

execute immediate 'alter session set events ''10046 trace name context off''';
------------

This will perform a sql trace with information binds and waits (as well as the explain plan for every statement), generating a dump file in user_dump_dest.

0
 
devindCommented:
Connect to the database via Toad
Run your program
In Toad Click on Menu Database->Monitor-Session Browser
In the left frame in Session Browser Click on OsUser to sort the usenames and look for session started by your username.

Ignore the session marked in Red Color that will be Toad's session. Check other sessions and look for session started by Java Code.
In the right hand side Frame click on Current statement Tab to look at which sql is currently running.

Keep clicking Refresh to monitor the changes or set number of sec to refresh automatically

Using above approach you can find the problematic sql taking too long to run.
0
 
sdstuberCommented:
note,  the problem might not be a sql statement that take a long time.

the problem might be pl/sql loops that issue many, many sql statements that are fast individually.


if you use the profiler you'll get that information.  You can also derive it from a trace file but it's less friendly to do so.
0
 
Amitkumar PSr. ConsultantCommented:
Check the following link

http://sanat-pattanaik-debug.blogspot.com/

You need to use SQL Developer (A free IDE from oracle). I'm following the same steps mentioned in the page (above link).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now