Solved

Debugging a Oracle PLSQL stored procedure

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
nextBoolean(double p) for Random class 3 35
table example 4 19
Oracle SQL Select unique values from two columns 4 17
Oracle Listener Not Starting 11 13
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

939 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

4 Experts available now in Live!

Get 1:1 Help Now