Skip to Main Content
  • Questions

  • HOW TO DEBUG WHEN THERE ARE 1000'S OF CODE PRESNT

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, satish.

Asked: June 12, 2008 - 9:38 am UTC

Last updated: April 18, 2012 - 2:58 am UTC

Version: 9I

Viewed 1000+ times

You Asked

HI TOM,

I HAVE A DOUBT THAT, I HAVE TO DEBUG THE CODE WHICH IS IN THOUSANDS OF LINES, WHICH IS THE BEST WAY TO DEBUG. AND SIMULTEANOUSLY DATABASE PERFORMANCE SHOULD BE GOOD.

THANKS,

SATISH.

and Tom said...

Well, it all starts by understanding.

What is the code supposed to do.

What were the documented requirements (hah, that was a joke, you probably don't have any...)

How is the code supposed to do it.

You need to gain an overall understanding of how the modules fit together (hopefully with 1000's of lines of code, you have more than one procedure/package/function)

Once you understand what the code is supposed to do and conceptually how it is supposed to do that - you need to identify "where it is going wrong"

This is why I like to have heavily instrumented code.

http://www.google.com/search?q=site%3Atkyte.blogspot.com+instrumentation

that way, you can debug in production without a debugger - you can debug from anywhere (it is after all how Oracle does it - got a bug, turn on tracing, turn on an event, get the trace file - Oracle is HEAVILY instrumented)


You do have a debugger - sqldeveloper has a nice one - but unless you understand what the code is SUPPOSED to do and conceptually HOW it does it - using a debugger on someone else's code is virtually useless. (if you don't know how the code is supposed to work, how will you know when something goes wrong).


So, I rely on heavily instrumented code (if I inherited a bunch of un-instrumented code, that would be my first plan - to instrument - at all costs - first and foremost (after understanding of course)).

As for performance, that is algorithm tuning. For procedural code, that means you have some experience with lots of ways of doing "things". You know there are 100 ways to do anything and sometimes one way is better than the other depending on the circumstance (this is why we have clustered b*tree indexes, b*tree indexes, bitmap indexes, text indexes, spatial indexes, etc ... because one index is not sufficient for all problems). Tools you have at your disposal for that would be SQL_TRACE, TKPROF, DBMS_PROFILER and sqldeveloper (again...)

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What has to be instrumented.

Karthick, June 13, 2008 - 1:00 am UTC

My question would be what needs to be instrumented. This sounds like an abstract question but being at that level of experience in programming I am sure you must have some set of standards in mind which you will follow when instrumenting your code.

What are all the information that needs to be logged. Is it nice to have like in a package if I have say 10 procedures, Log the entry and exit of the procedure so that it can help me in narrow down my search for the bug. And while doing complex computation would you prefer to log the intermediate results.

So to put it simple while writing code how you say ¿hey this place I need some instrumentation¿. Or this information needs to be logged¿. Is there any guide line that you follow?

Tom Kyte
June 13, 2008 - 7:35 am UTC

everything - all of it. My code frequently would have instrumentation every other line.

the way I view it:

The instrumentation is code I write to make my life better, more enjoyable, easier. The other code - that is code I am forced to write to specification. I enjoy the instrumentation code - the other stuff - that is what I have to write.

Log inputs, outputs, intermediate values, bits and pieces of everything. Log with source code file name, source code line number, timestamps.

look at the volume of information available in an Oracle trace file - sort of like that.

A reader, June 13, 2008 - 4:46 am UTC

If I were at a brand new client and on the first day asked to debug a program that calls several other programs, the place I'd start is to do a SQL*TRACE at the session level (as long as the bug can be consistently reproduced).

But hey, what is a BUG?, is it an Oracle server error, in which case the SQL*trace can point the error in its tracefile. If its a client error, there are tools in each client (Forms Trace, for example) which would help.
Tom Kyte
June 13, 2008 - 8:16 am UTC

but, you don't know what this program IS SUPPOSED TO DO.

you cannot even begin to debug unless and until you understand what the heck something is supposed to do.

That would be step #1, if I called you in and asked you to debug something and your first step was not "ok, find us a white board and start drawing pictures for me" - I would ask you to leave. Sitting down and getting into a sql_trace is so wrong as a first step.


A reader, June 16, 2008 - 4:48 am UTC

Then again Tom, I bet you haven't worked with a client for a couple of decades and by your own admission you don't expect documentation to lead the way to the bug.

I assumed the questioner already knew a bit about the application, he/she actually knew to define the problem as a Bug, but did not state whether it was an Oracle error or an application error.

The point I am making is, he/she could be with a client where the introduction to an application was a demo from a business user, something to forget in 30 minutes.

Also, not a lot of developers know about sql_Trace or tkprof, which is a tool to explore what SQL is being executed and in which order and if properly used info about how the SQL is performing.

I think in an exploration situation its an ideal tool to have a go and try and analyze the results.

Tom Kyte
June 16, 2008 - 1:06 pm UTC

ummm, you would sort of be wrong on all points. First, I've only been working for "a couple of decades" (two) so that would be physically impossible and I work with them all of the time.

You "assumed", you know what - you cannot do that. Re-read what the poster SAID, IN UPPER CASE (that my friend should tell you something)



...

I HAVE A DOUBT THAT, I HAVE TO DEBUG THE CODE WHICH IS IN THOUSANDS OF LINES, WHICH IS THE BEST WAY TO DEBUG. AND SIMULTEANOUSLY DATABASE PERFORMANCE SHOULD BE GOOD.
...

You assumed this poster already knows the application??!?!?

You assumed this poster already knows what to look for???!?!??!

You, you assume A LOT. And you would mostly be wrong in your assumptions.


and you know what, using sql_trace for looking for a program BUG (a logic bug, a bug in an algorithm) would be like using a disassembler on object code to find a bug in a C program - of great scientific interest, it looks cool, but it is fairly useless - to find a BUG IN A PROGRAM.


You can do what you want, but it is the wrongest piece of advice to give someone who asks:


I HAVE A DOUBT THAT, I HAVE TO DEBUG THE CODE WHICH IS IN THOUSANDS OF LINES, WHICH IS THE BEST WAY TO DEBUG. AND SIMULTEANOUSLY DATABASE PERFORMANCE SHOULD BE GOOD.


I thought about this some more, and came back to comment more....

First, if you think about it, you reinforce what I said - first step is to understand. You ASSUMED

... I assumed the questioner already knew a bit about the application, he/she
actually knew to define the problem as a Bug, but did not state whether it was
an Oracle error or an application error.
....

I simply did not make such an assumption, but assumed nothing. So, once you get your assumptions satisfied by my first steps (because you know, when I walk into a customer site, I do not have the assumptions completed yet - I don't know about their application, I don't know what their bug is as yet - I'm lucky sometimes to have seen an iTAR - maybe).


So, we get your assumptions satisfied and probably still the last took I would start with to "debug" would be sql_trace and tkprof. It is still like looking at assembler to fix a bug in C.


Even if it is an oracle error message - you know what? Most all of them come from the application doing something wrong - so, you have an error message "ora-1555", how will a trace help you? Unless it is query syntax - I don't see how a tkprof would be the place you'd want to start. If it were some ora-xxxx error like a "unique index violation" and they cannot understand "how", you'd need to SEE the code, understand the code, so you can get to the point of "Oh, I see, you generate your primary keys via to_char(sysdate,'yyyymmddsssss') - now I can tell you why you hit dup val on index"

Oh you are blocking, let me understand your logic, program flow (information I cannot get from a tkprof)...


Understanding the ordering of an application instances sql (read that carefully, understanding the unique ordering of a single application instance - run - invocation - sql) is not in general useful

Maybe instead of trying an ad hominem attack, you present your case - technically, logically and soundly. You describe

a) a problem you faced regarding a bug
b) how you discovered the root cause with a sql_trace
c) how you extrapolate that to work in the general case.


I use sql_trace and tkprof a lot - for performance and understanding how a single sql statement is performing.


to use it to understand program flow, find a bug - no. And I debug dozens of times a day, right here if you think about it. And for a program bug, I've not ever asked for a tkprof - a reproducible test case - absolutely (code). trace - no.

Karthick, June 17, 2008 - 1:03 am UTC

Understand the problem. Once we do that we can easily identify if it¿s actually a problem or not. Most of the time in the past when client report to me that some stuff is not happening the way it¿s supposed to be, it would be some setting in the application which is not set properly. So after hours of discussion (lot of noise involved) with client we will be able to identify the problem (which is not actually a problem). We don¿t even have to open the code.

I have always used SQL Trace only for performance testing. It has never come handy for me to solve a problem which does not involve performance issues. I am not sure if it can be used in such situations.

Once I get the feel it¿s time to check the code all I do is run through the log files that is generated by the program. Lot of time I have been in situation where the log's where not sufficient. The information that I thought are unnecessary, the information that I thought is time consuming to log would have helped me in a great way if I have had them in place. In the current program that I am working on I have logged every bit of the work I am doing. I am sure this will definitely save me some day or other.

The way I see it when you get an issue in production, replicating it in test environment and debugging it is going to be a time consuming process. So have lot of log information in your code. They can help a lot to narrow down the problem.

Even thought you are an expert in a application in which you are debugging you are not going to find the problem until you sit and talk with the client. Conduct meeting; ask him to explain the issue. Ask him what he did, what he expected, why he expected that and so on. Unless you do that searching for a bug with SQL trace is like searching for a black paper in a dark room. You will never find it.

debugging

A reader, January 17, 2009 - 12:19 pm UTC

Tom:

What do you usually use to debug Pl/SQL code when you use sql*plus or SQl navigator or TOAD.

I have a program that runs slow. I am trying to figure out what is causing that slowiness. I added many sttements like

dbms_output.put_line(sysdate||'I started doing stage 1...');
....code
dbms_output.put_line(sysdate||'I finished doing stage 1...)'

......

is this how you do it or you use a table to insert values?

2. When i ran the above in sql*plus, it did not do what i expected.

It sort of ran the whole program and then gave me the output in one list. I thought it would run line by line and show me what it is doing.

How can i see it line by line.
Tom Kyte
January 17, 2009 - 12:52 pm UTC

1)
why not just use the profiler?

http://asktom.oracle.com/pls/ask/search?p_string=dbms_profiler

2) you do understand the architecture of how things work don't you? That a plsql stored procedure runs on the server - from start, to finish (exception to this: a pipelined function). It is not on the client, it cannot interact with the client in any way shape or form. Client starts it, it runs, and then client and display output from it.

dbms_output is just a way to write lines of text into an array, after the procedure runs, sqlplus calls another procedure - dbms-output.get_lines to retrieve this array and print it on screen.

If you want to watch your procedure 'real time', use dbms_application_info.set_session_longops and monitor its progress from another window.

DEBUG.F

A reader, April 17, 2012 - 7:04 pm UTC

Tom

So you still use DEBUG.F that you mention
in your book Effective Oracle By Design?
Tom Kyte
April 18, 2012 - 2:58 am UTC

http://tylermuth.wordpress.com/2011/11/09/logger-project-moved-temporarilly/

debug.f has grown over the years and rewritten, reimplemented, renamed, re-everythinged...

thankyou

A reader, April 18, 2012 - 5:58 pm UTC

I look forward to playing with the new logger thankyou
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library