"I have an entire presentation (that can take from 1 hour to 3 days depending on
how long you are willing to listen) "

Thanks

Tom
I liked this
"So, I propose there is 0% overhead, since overhead is something you can get rid of and not lose any material, immediate benefit. "

I think instrumentation is a bit like seat belts in a car.
There is some extra requirement of having to put it on
before driving, but you really need to have it on

With the advent of the profiler in Oracle (DBMS_PROFILER), I wouldn't think instrumentation in the code is as necessary as it used to be. The question then becomes of how to best utilize the profiler for one's needs.

Kashif

Tom,

Is that hourlong presentation available anywhere? I am interested in evaluating different options on the techniques. I have some experience in implementing instrumented code using dbms_pipe. But a full presentation from you would be good for my learning, and useful to convince the whole department to consider it.

Thanks
Raj

Granted instrumentation is invaluable, but poor usage of instrumentation can lead to poorly performing code. At a previous company I worked for I found that almost 20% of the code executed was string concatenations in debug statements. By adding a simple "if" check to each log statement, there was a significant speed up:

// Excuse blatent psuedo-code here
if (Log.level(Log.DEBUG)) {
Log.debug("Here's the object: " +
someBeanWithALotOfProperties.toString());
}

Are there any gotchas like this within Oracle's tools?

Anybody else having trouble running WhatsWrong.ppt. Power point tries to open it and then bombs without any errors.

Thanks.

Try saving the target by right clicking on the link and selecting "Save Target As". Once saved, you should be able to open the saved file... HTH.

Kashif

Tom

PowerZip seems to be the problem. When copy to unix, gunzip, tar extract, copy back and all is fine. Thanks.

> it's the "whats wrong" one on the home page (6/17/2003)
> or asktom.oracle.com/~tkyte sometime after that date.

I do not find this one... Is it missing or is some cache/proxy server making fun on me :-(

I was looking at </code> http://asktom.oracle.com/~tkyte/ <code>page...

"if (Log.level(Log.DEBUG)) {
Log.debug("Here's the object: " +
someBeanWithALotOfProperties.toString());
}
"

Tom
That example is very relevant for Java certainly since
in Java strings are immutable and so string concatenation
is costly. How does this compare to plsql? In your instrumentation code - do you generate the string to
be displayed conditionally? (It makes the code kinda
cumbersome - you have to have an if wherever
you are having a debug statement being used. Of course
if it is required there is no alternative.)

Also, how could I easilly measure this? (figure out
increase in memory - uga/pga wherever) with and
without an if - is that a reliable test?)

"the strings would be built each time.
v$sesstat measures all things.
"
Q1. You mean it is like Java where strings are immutable
and so a = a + b => does not change a but creates a new object (with other objects being created along the way)?

Q2. In your code, do you put an if around your debug.f's?

"correct, there would be a new temporary"

Not to question your knowledge or anything - but how could I verify that (unless it is documented or not)?

And i will try to benchmark the impact of plsql string concatenation sometime in the near future.

Thanx!!!

"umm, how else could it be.
p( x || y );
there must be a temporary "foo" that is x||y. it cannot modify X, it cannot
modify Y yet it must send a variable "foo" that is the result of x||y"

Right:) I guess i was thinking more of the case

x := x || y.

In java the equivalent does not change the x and add y to
the end - it creates a 3rd, totally new x||y and assigns
it to the x. StringBuffer does it in a different way
(actually appends y to x in memory) (I am sure you know this)

My q was is that how it works in plsql? I do see that
it perhaps is not very relevant to the debug.f() statements though as pointed out by you...

"oh, there -- there wouldn't necessarily be a temporary created, it would just
write to X directly. it would not "new" a temporary in that case if x and y were
of the same types (no implicit temporary conversions and all).
"
thanx - actually - the above would impact the string generation- whenever there is
debug.f ( a || b || c)

in plsql you would create only one temporary.

In java a + b + c would create more than one
intermediate strings.

So the fact that you can change a string in plsql
means that it is not as important to enclose the
debug.f() in quotes as in Java.

This is what I wanted to verify in plsql.
Will a+b+c only create one temporary (I think it would
but how can I verify that)

Thanx!

"cannot think of a good way to "verify it" myself. "
If I find one - I will post it here.

WARNING:

This is not a documented or supported activity.  I would not normally post undocumented or unsupported techniques, but I was intrigued by this question.

I do not offer this is as a valid method for determining the answer to the question at hand.  Nor am I sure my interpretation of the output is correct.  In any case, I used event 10928 to enable pl/sql tracing and here are the results:

SQL> connect testuser
Enter password:
Connected.
SQL> variable a varchar2(1)
SQL> variable b varchar2(1)
SQL> variable c varchar2(1)
SQL> alter session set events '10928 trace name context forever, level 1';

Session altered.

SQL> begin
  2    :a := 'A';
  3    :b := 'B';
  4    :c := 'C';
  5    dbms_output.put_line(:a || :b || :c);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> alter session set events '10928 trace name context off';

Session altered.

SQL> disconnect

TRACE FILE CONTENTS:

[header snip]

*** 2003-08-29 12:39:03.000
*** SESSION ID:(10.256) 2003-08-29 12:39:03.000
Entry #1
00001: ENTER      196, 0
00007: INFR       DS[0]+44
  Frame Desc Version = 2, Size = 35
    # of bind proxies = 3
    TC_VCHARi(OUT): FP+8, d=FP+76, ubn(mxl)=1, (byte) CS_IMPLICIT
    TC_VCHARi(OUT): FP+28, d=FP+116, ubn(mxl)=2, (byte) CS_IMPLICIT
    TC_VCHARi(OUT): FP+48, d=FP+156, ubn(mxl)=3, (byte) CS_IMPLICIT
<source not available>
00012: MOVC       HS+0='A'V=, FP+8
00017: SBVAR      SQLT_CHR(1), 1, FP+8
<source not available>
00024: MOVC       HS+28='B'V=, FP+28
00029: SBVAR      SQLT_CHR(1), 2, FP+28
<source not available>
00036: MOVC       HS+56='C'V=, FP+48
00041: SBVAR      SQLT_CHR(1), 3, FP+48
<source not available>
00048: GBVAR      SQLT_CHR(1), 1, FP+8
00055: GBVAR      SQLT_CHR(1), 2, FP+28
00062: CONC3      FP+8, FP+28, FP+72
00069: GBVAR      SQLT_CHR(1), 3, FP+48
00076: CONC3      FP[72], FP+48, FP+68
00083: MOVA       FP[68], FP+4
00088: XCAL       4, 5

[content snip]

00093: RLSM     
00094: RET


At line 00062 it appears that the concatenation of :a and :b is performed and stored at offset FP+72.

At line 00076 it appears that the concatenation of :c with FP+72 is performed then stored at offset FP+68.

It appears that the final result (FP+68) is then moved to offset FP+4.

Again, this could be a wholly incorrect interpretation, and is one reason why I do not normally engage in undocumented activities.  Caveat Emptor.

- Mark 

In case someone would like to know:

A Java class like this:

public class Test {
public void foo() {
String a="A";
String b="B";
String c="C";
System.out.println(a+b+c);
}
}

Compiled:
$> javac Test.java

And disassembled:
$> javap -c Test

Compiled from Test.java
public class Test extends java.lang.Object {
public Test();
public void foo();
}

Method Test()
0 aload_0
1 invokespecial #1 <Method java.lang.Object()>
4 return

Method void foo()
0 ldc #2 <String "A">
2 astore_1
3 ldc #3 <String "B">
5 astore_2
6 ldc #4 <String "C">
8 astore_3
9 getstatic #5 <Field java.io.PrintStream out>
12 new #6 <Class java.lang.StringBuffer>
15 dup
16 invokespecial #7 <Method java.lang.StringBuffer()>
19 aload_1
20 invokevirtual #8 <Method java.lang.StringBuffer append(java.lang.String)>
23 aload_2
24 invokevirtual #8 <Method java.lang.StringBuffer append(java.lang.String)>
27 aload_3
28 invokevirtual #8 <Method java.lang.StringBuffer append(java.lang.String)>
31 invokevirtual #9 <Method java.lang.String toString()>
34 invokevirtual #10 <Method void println(java.lang.String)>
37 return

------------------------------------------------------------
So it uses one temporary variable but it is not a String instance at all rather StringBuffer (which is mutable). It is constructed via successive append() calls and at the end of expression code creates one String instance via toString() call which ultimately is passed onto System.out.println

It's Interesting to note how the compiler optimized (exchanged) calls to StringBuffer.append() and push bytecode of System.out (class variable) before on the stack, so that when it is ready computing temporary string it already has instance (reference to it) of java.io.PrintStream on the stack. An then all that is left is to call virtual println()

Regards

Mark,
you seem to imply that plsql perhaps creates more than one
intermediate variable (caveat emptor noted)

Piotr - what you say (with proof) perhaps seems to go against the commonly accepted norm that multiple
intermediate strings would be created in this example.
It may also be that the compiler you use has this
optimization built in. Is it standard Sun's compiler?

Thanx guys!

Since javac command is just a binary wrapper to embedded Java shared libs and Sun's compiler is written in Java (just bunch of "normal " java classes found in $JAVA_HONE/lib/tools.jar)

>$ java -version
java version "1.4.1_01"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1_01-b01)
Java HotSpot(TM) Client VM (build 1.4.1_01-b01, mixed mode)

Regards.

And if you have GNU gcj

Compile:

$> gcj -C Test.java

Decompile ditto...

But for this small class it produces EXACTLY the same bytecode (opcode for opcode).

One thing I REALLY like about gcj is that it runs almost 2 (yes!!! 2) orders of magnitude faster then Sun's compiler.

GNU:
$> time gcj -C Test.java
real 0m0.029s
user) 0m0.010s
sys 0m0.010s

Sun's
$> time javac Test.java
real 0m2.247s
user 0m1.130s
sys 0m0.020s

On a 1.7 GHz AMD Athlon with 1 GByte RAM

But it had problems with some complicated classes especially those with embedded local and anonymous classes (but this might be already fixed I have a build from April 2003).

It is basically compatible with Java 1.1 so not an appealing solution for state-of-the-art killer-apps ;)

Regards.

Interesting comments!
Tom, so far the conclusion seems to be opposite of what
I thought.

1. pl/sql seems to be creating more than one temporary
variables for concatenating a, b, c
2. the sun's standard compiler seems to be opimizing
by using StringBuffer underneath.

Any comments?

Thanx and have a great labor day weekend - all of you!

Tom,

how do you switch on/off debugging? Do you programm on/off switch info application and set some package variable? Or do you use some kind "remote" debugger to monitor other session (using pipe,alert,locks,table for on/off switch).

Usually I do not want that debugging code is switched on at prod server and would use it seldom - but sometimes it would be more convinient to monitor remotely the end user session [than to get dumb-user to switch on debugging].

hi tom,

yesterday I've got your new book (had to pay 1.3 times the amount in EURs that one has to pay in USD in America!!!) and started reading chapter 1. Now I wonder whether you didn't mention Log4PLSQL on purpose or not (</code> http://log4plsql.sourceforge.net <code>.

I use Log4Perl and Log4J on client side and did just some small tests with Log4PLSQL. Do you have any reasons to NOT USE that tool for database instrumentation?

(just to be curious)

Piotr,

The SUN java compilers takes some time to "warm-up". Go figure...

/cygdrive/d/prosrm/peter> time javac Test.java

real 0m2.721s
user 0m0.031s
sys 0m0.046s

/cygdrive/d/prosrm/peter> time gcj -C Test.java

real 0m0.285s
user 0m0.060s
sys 0m0.139s

/cygdrive/d/prosrm/peter> time gcj -C Test.java

real 0m0.114s
user 0m0.076s
sys 0m0.091s

/cygdrive/d/prosrm/peter> time javac Test.java

real 0m0.714s
user 0m0.015s
sys 0m0.031s

Yes, the SUN compiler is still slower but not as bad as your test initially shows.

Like you said, it's nice to have a fast compiler but it's not very useful if it doesn't support the latest JDK features and it's always going to fall behind in the bug fixes.

Thanks,
-Peter

I too, have figured that out... (warm-up)

Yes, but I can see you use Cygwin so it must be windoze so it is slower than under native Unix (Linux) where I had done my tests. And your SUN java is most probably native windows while GNU gcj is Cygwin program.

Cygwin is a great software but it uses Posix emulation layer which slows things down. So lets say it is not 2 orders of magnitude but "just" 1.

But there are ways to keep SUN compiler up and running all the time... Various IDEs: Forte, JBuilder, Eclipse (I personally don't use them) or something like a "local compilation server" which runs in the background and accepts "compilation requests" from clients using say Unix domain sockets. Then it may work "really fast" (fast in java parlance of course ;).

Regards,
Piotr

"you seem to imply that plsql perhaps creates more than one
intermediate variable (caveat emptor noted)"

Confirmed that in 10G this problem (of creating intermediate strings) does not exist and
has been optimized at the pl/sql compiler level (which
also has lots of other optimizations.)
(Asked an Oracle pl/sql compiler guy )

Hi Tom,

Just would like to ask if it's possible to enable my debugging my code from another session (just like dbms_system.set_sql_trace_in_session), without enabling the debug using triggers or table parameters.
Is it possible if set a context for that existing session? If not, what could be the proper way to achieve this?

Regards,
NOTNA

I've searched your site regarding the above package and I think someone modified and put's some additional features (such as the ability to on and off the debugging, log thru url, etc.... </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:7446151725537, <code>)
But still you have to turn it on from your current session (execute the debug.enable). I want to modify this code so that I can enable the debugging of my package/function/procedure or anything from a different session (just what like dbms_system.set_sql_trace_in_session (...) is doing).
would that be possible?

Tnx and Rgrds....

NOTNA

Hi Notna
I added the enable flag to modify the code so that there
is minimal overhead when the debugging is turned
off. I wanted to turn it on or off on a session
by session basis.

The url based rendering again assumes that you are
using a single connection while rendering the URL
(which is true in most cases I know of.) Again
there is a need for the session level flag..

hope this helps..
Menon:)

<QUOTE>
You just invoke debugf.init from any session and all others will start debugging
if they are supposed to.
</QUOTE>

This is what I do not want to happen. If I execute the debug.init, my code will generate a debug message for all the sessions. I just want a particular session to start debugging.

:) Cheers
NOTNA

What's a good book to read about instrumenting pl/sql code?

I've looked all over, but I don't think that the "NOUG Cruise" or "What's Wrong" files referenced in this thread are still available on this site. Can you post them again?

I'm asking my next question from a position of a bit of naivety as I only learned of instrumentation at Tom's great VicOUG talk yesterday, but:
If Tom Kyte thinks that instrumenting every line is such a good idea, why doesn't the PL/SQL compiler automatically instrument code? Why rely on many developer to instrument many pieces of code that the compiler could do it once for all of everyone's code?

Tom

We have java based middle tier web app connecting to 9ir2 on hp, using connection pools.

I have been looking at using your debug.f package as it looks like just the sort of thing
we need.
I have installed the package and have liberally sprinkled my code with debug.f calls. What I am unsure about though
is how the to initialise the debug package. I have some questions:


1)

In my packaged procedure I have tried calling debug.init. This turns on the debugging fine and debug.clear at the end of the procedure turns it off correctly. When i try and use

debug.init('my_pkg.my_proc');

or even

debug.init('my_proc');

I get no debugging info in the file. Can you explain how to turn on debugging just for my procedure? Am I doing it right?


2)
I am interested in how you turn on your debugging using your URL setting of changing NO to YES. If you call debug.init; ?? Surely this means that debugging is switched on for all modules for all users ( we have connection pools ) ??
So since we have a common login user that the web app uses to create each connection pool, then turning on debug.init for that user would mean we'd get debugging calls from all pools mixed up in one trace file wouldn't it?? How can i get round this? I would like myabe to use the session id in the file name??


Also how do you ensure that your debugging gets switched off??
If you change the url for ASKTOM from the NO to YES I can see your debugging, so when do you call debug.clear? After the page is rendered??
What would happen if your user closes the browser before the page is rendered or you lose the connection, how do you ensure debug.clear is called to turn off debugging? As far as I can see the only way to do this is to call debug.clear at the end of EVERY procedure.


Many thanks

Tom,
Can you provide a very simple example of how to use the debug package to instrument plsql code?
Thanks.

Hi Tom,
good day to you, I am unable to fine whats wrong presentaion and when i go to </code> http://asktom.oracle.com/~tkyte/ <code>and try to download debug.f db.zip file it gives error message have the links changed, please if you can publish the new links it will be a great help I would like to learn the instrumentation method and apply it in my code.

many thanks

Hi Tom,
For all the pro*c programs, we wrote enhanced Logging functionality, where in which,
all the inputs from the users are logged in into some files every day. This information
is very valuable for us to find out request time, response time and to see which
program is taking more time (or sometimes for timeouts).
For a new project, we wrote a PL/SQL package which is being used by Frontend programs (written in Java).
Now we are thinking of implementing some thing similar to what we did for pro*c programs.
For pro*c programs, we have dedicated Servers taking the requests from Frontends.
Hence we open one file per Server and keep writing the information in predefined format.

If i have to implement similar thing for stored procedures, i hope UTL_FILE can be helpful.
I hope i can do something like this:

Whenever there is a request for my stored procedure, i can open one file per day
in append mode and keep writing the information about the input values, request time
and response time.

My doubt is:

1) If many users use the same functionality, will this approach help? Means will i be
able to log meaningful information without destroying the contents of the log file
2) If the number of requests increase for this functionality, will the usage of UTL_FILE
impact the performance. This procedure caters to users request to view some data in real time.
Hence we need to consider about the performance of this program also.

Thanks tom.

From your home page:

Click here for tidbits, howtos and archived presentations - </code> http://asktom.oracle.com/pls/ask/z?p_url=http%3A%2F%2Fasktom.oracle.com%2F%7Etkyte%2F&p_cat=TILDE_TKYTE&p_company=10

That takes you to 

http://asktom.oracle.com/~tkyte/ <code>

From that page:

Some online presentations
These have moved to the Files tab on the home page of Asktom

From the Files tab:

Link Size Description Created On
browser_type_classifier.zip 16.8 kb Browser types .sql script. To map user_agent (long string) to general class of browser like firefox, MSIE, and so on. Contributed by Alberto Dell'Era 27-Dec-2005 05:10pm
top_10s.zip 330 kb Scripts and slides from OOW 2004 and 2005 - the "top tens" of 10g R1 and R2 18-Dec-2005 10:34am
ctoug.zip 267.9 kb Material presented to the CTOUG in October, 2005. Effective Schema Design and "Five Things" 15-Dec-2005 11:45am
Oracle inside cover 0703.pdf 72.5 kb Inside cover of Effective Oracle by Design showing the documentation roadmap. 10-Oct-2005 09:33am
dhl.zip 745.8 kb DHL in Prague talk 01-Sep-2005 11:18am
5300FMcmp1.pdf 339.7 kb PDF version of the table of contents, most excellent foreword by Ken Jacobs, about the author and technical reviewers, acknowledgements, introduction, chapter by chapter overview, and so on for Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions 19-Aug-2005 06:34pm
ToolsIuse.zip 264.6 kb Tools I use slides and scripts as presented to the Oracle User Group in Washinton DC on August 8th, 2005. 08-Aug-2005 04:44pm
10gr2_oguc.zip 1.8 mb 10g R2 presentation 07-Jul-2005 12:19pm
SQLTechniques.zip 205.4 kb The presenter will describe common SQL techniques he has encountered and utilizes day to day to tune query performance. Features such as scalar subqueries, using rownum (yes, to 'tune'), analytics, some hints and more will be demonstrated. Emphasis will be on when they work (where the 'trick' applies) as well as when they don't work - where they do not apply. Care will be taken to show these not as a "top 10 things to do" but rather techniques to keep in mind when looking at problems in general. 26-Jun-2005 12:30pm
asktom_articles.html 13.6 kb Links to all of the asktom articles published in the print magainze over time... 08-Jun-2005 10:42am
AllAboutBinds.zip 270.3 kb All about binds delivered at IOUG-A May 2005. 03-May-2005 07:08am
FalseKnowledge.htm 60.3 kb False Knowledge Article, why evidence is a good thing. Why advice from anyone needs to be questioned, especially from "experts" 07-Apr-2005 06:01pm
VicOUG.zip 834.5 kb Tools I use and Effective Schemas slides and scripts as presented to the Victoria BC Oracle User Group on Jan 20th, 2005. 21-Jan-2005 03:17pm
acxiom.zip 245.8 kb Effective Schema Presenation and Slides from the Acxiom Database User Group session, January 2005 05-Jan-2005 10:36am
10gHASeminar.zip 552.2 kb Harrisburg PA seminar delivered on December 17th, 2004. All about availability. 20-Dec-2004 08:59am

1 - 15 Next


Link Size Description Created On
OOW.tar.gz 310.5 kb Oracle Open World 2004 Presentation, White Paper, and scripts. It was entitled "top 10 things about 10g". 10 things I thought were really useful after using the database in real life for a year. 05-Dec-2004 06:55pm
BROUG.zip 565.8 kb Slides presented at the Baton Rouge OUG, Sep 23th, 2004. The talk was on the 'tools I use' and 'Efficient Design' 29-Nov-2004 04:04pm
AAOUG.tar.gz 446.6 kb Top 5 things done wrong and 10g bits/bytes presented at the Ann Arbor OUG meeting November 16th 2004. 20-Nov-2004 04:50pm

where is it?

I have little experience with triggers.
This app suite I'm taking over has what seem to me rather "complicated" triggers (eg many of them doing "INSERT INTO SELECT FROM several-table-outer-join", I was like "wholey cow !")

What is your approach on instrumenting triggers ? any special consideration or no different than any pl/sql program ?
thanks

Hi Tom,

I would like to enable session tracing in a package.main_sp if the user runs the procedure with parameter debug_mode true, as :

procedure pkg.main_sp (...p_debug_flag in varchar2 := 'N')
IS ...
if p_debug_mode then
alter session set events '10046 trace name context forever, level 12';
end if;
.....<other debug statements>
When done,
if p_debug_mode then
alter session set event '10046 trace name context off';
end if;

Is it ok to do so and could you please help on how to code this ? I tried it and got a PLS -00103 error..

Thanks for your help.

Hi Tom,

I researched some more on this great site about the issue in my previous update, and found I could do :
sys.dbms_system.set_sql_trace_in_session( 46, 13217, TRUE );

You say "if you get the SID, SERIAL# of a session from v$session you can turn on tracing using dbms_system".
Could you elaborate on how to get this info from v$session for the user's session that the package is executing in.

Also, is using dbms_system.set_sql_trace_in_session the same as alter session set event 10046 ?

How do I set the trace off - is it sys.dbms_system.set_sql_trace_in_session( 46, 13217, false ); ?

Thanks and appreciate your help.

Sorry Tom, I should've mentioned - database is 9.2.0 (11.5.9 Oracle apps).

Couple questions :
1. The user APPS does not have direct selects on sys.v_$session and sys.v_$mystat, but it has select any dictionary, and the proc compiles fine. Would it be because of the select any dictionary ?
2. Is tracing with 10046 the same as set_sql_trace_in_session ?
3. How to set the trace off : sys.dbms_system.set_sql_trace_in_session( sid,serial#,false ); ?

Thanks so much for your help.

Hi Tom, I noticed that I missed the exact version of database - it is 9.2.0.6. Please disregard my question #2 in the previous update.

I ran the proc setting the trace in it with set_sql_trace_session and it worked awesome! Thanks so much. However noticed that 10046 event is not the same as set_sql_trace_in_session because in the tkprof I did not see any waits. How can I do the 10046 trace event for my session in the proc ?

Thanks so much for your help.

Thank you very much Tom!

Hope below tips helpful.

create user utility identified by utility;
grant create session, create procedure, create table, create trigger to utility;
alter user utility default tablespace users quota unlimited on users;
--connect sys/? as sysdba
grant execute on utl_file to utility;
grant create public synonym, create any DIRECTORY to utility;

run install_debug in that schema
create a public synonym for debug
And then you can use it.


connect utility/utility@dev
@install_debug.sql
create public synonym debug for utility.debug;
grant execute on utility.debug to public;


CREATE DIRECTORY debug_dir AS '/data1/debug';
GRANT READ, WRITE ON DIRECTORY debug_dir TO PUBLIC;
--exec debug.clear;
--exec debug.init(p_file=>'DEBUG_DIR/system.dbg');

declare
ls_user varchar2(30) DEFAULT USER;
begin
debug.clear;
debug.init(p_file=>'DEBUG_DIR/'||ls_user||'.dbg');
debug.status;
end;
/

-- Set module name to trace, p_modules=>'<package_name OR stand alone procedure_name>'
-- Set login on user to trace, p_user=>'SYSTEM'
declare
ls_trace_user varchar2(30) := 'SYSTEM';
BEGIN
debug.clear;
debug.init(p_modules=>'LB', p_user=>ls_trace_user,
p_file=>'DEBUG_DIR/'||ls_trace_user||'.dbg');
debug.status;
end;
/


Question:
Can I just trace/debug one sub-procedure in a pacakge?
It help you fine-grained pin point the problem.

Thanks a lot.

To: Christopher Beck,
Still have the problem of multiple sessions with same USER logging to the same file,

change the debug code to query
up SYS_CONTEXT ('USERENV', 'SESSION_USER')
dose not work.

You get same result with SYS_CONTEXT ('USERENV', 'SESSION_USER') and :USER.

Here is my proposal,

1. call debug.init to set the SESSIONID(auditing session identifier)

2.
for c in ( select *
from debugtab
where where sesssion_id = userenv('SESSIONID');)
loop

OR

just capture the 1st logon SESSIONID, exclude following same username sessions,
and write to trace file.

the complete code will be uploaded soon.
But I still prefer to use the fully tested robust code(black box tool) you gave.


BTW,
Thanks to clarify that current debug.f dose not support sub-procedure in a package.

Thanks Tom. I got it, Line Number is good enough.