Re: Sql trace failure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/08/14
Message-ID: <33f724e2.8235902@newshost>#1/1

On Thu, 14 Aug 1997 16:24:01 +0300, MORDI <mordi_at_rts.co.il> wrote:

>Hello,
>
>I run a big program that runs several stored procedures, with alter
>session set sql_trace = true in Oracle 7.3.3 Solaris.
>After i do the tkprof and get the formated trace file, some stored
>procedures can't be found in the trace file.
>Our local Oracle support said that there is no such bug registered in
>the Oracle bug database.
>Have you got a similar problem?

This is the expected behaviour. SQL Trace will only show pure SQL statements and pl/sql statements executed directly by you. Consider the following example:

create table t ( d date )
/  

create or replace procedure bar
as
begin

    insert into t values ( sysdate );
end;
/  

create or replace procedure foo
as
begin

    delete from t;
    for i in 1 .. 100 loop

        bar;
    end loop;
    commit;
end;
/

SQL> alter session set sql_trace=true;
SQL> exec foo;

The TKPROF for the above will show timings for the following:

---

alter session set sql_trace=true

begin foo; end;  

DELETE FROM T   INSERT INTO T
VALUES
 ( SYSDATE )   begin dbms_output.get_lines(:lines, :numlines); end;

---

As you can see, BAR is not in this list at all (only the pure sql statements BAR generates are in the list). This is because I did not call BAR myself, another pl/sql routine did. I can see my call to FOO and even a call to dbms_output.get_lines (i did the trace in sqlplus with set server output on, sqlplus called dbms_output).

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Aug 14 1997 - 00:00:00 CDT