Solved

Debugging a Oracle PLSQL stored procedure

Posted on 2011-03-22
10
904 Views
Last Modified: 2012-05-11
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
Comment
Question by:codemonkey2480
10 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 35190426
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 35190454
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
 

Author Comment

by:codemonkey2480
ID: 35190562
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35190658
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35190695
>>> 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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35190702
ashilo - this is pl/sql,  not sql.

There is no "execution plan" for a pl/sql procedure
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35190865
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
 
LVL 4

Expert Comment

by:devind
ID: 35192394
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35192428
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
 
LVL 21

Expert Comment

by:Amitkumar Panchal
ID: 35192962
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question