Solved

Debugging a Oracle PLSQL stored procedure

Posted on 2011-03-22
10
884 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 73

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 73

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
 
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 73

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 73

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 73

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 20

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now