Solved

Debugging a Oracle PLSQL stored procedure

Posted on 2011-03-22
10
917 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

726 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