Re: 'alter session set sql_trace=true' bug or feature ?

From: VC <boston103_at_hotmail.com>
Date: Tue, 23 Dec 2003 12:46:41 GMT
Message-ID: <ROWFb.434117$Dw6.1333516@attbi_s02>

Hello there,

"bonminh lam" <hansmayer1962_at_hotmail.com> wrote in message news:3c6b1bcf.0312230303.17ad8032_at_posting.google.com...
> Hello,
>
> I carried out your test on a HP superdome running 9.2.4. The impact of
> SQL Trace is ignorable:
>
> 1 select count(*) from (
> 2 select a.id, count(*)
> 3 from (select id from t1) a
> 4 join (select id from t1) b
> 5 on a.id<=b.id
> 6 group by a.id
> 7* )
> SQL> /
>
> COUNT(*)
> ----------
> 1000
>
> Elapsed: 00:00:00.59
> SQL> alter session set sql_trace=true;
>
> Session altered.
>
> Elapsed: 00:00:00.15
> SQL> select count(*) from (
> 2 select a.id, count(*)
> 3 from (select id from t1) a
> 4 join (select id from t1) b
> 5 on a.id<=b.id
> 6 group by a.id
> 7 );
>
> COUNT(*)
> ----------
> 1000
>
> Elapsed: 00:00:01.09
>
>

Lose the outer count(*) and execute the original query. You are comparing apples to oranges.

Better run this:

set serverout on
 declare
   l_start number;
 begin
   execute immediate 'alter session set sql_trace=true';    l_start := dbms_utility.get_time;
   for l_record in

      (select a.id, count(*)
      from
       (select id from t1) a
       join
       (select id from t1) b
       on a.id<=b.id group by a.id)  loop
       null;

   end loop;
   dbms_output.put_line( (dbms_utility.get_time - l_start)/100 || 'seconds....' );

   execute immediate 'alter session set sql_trace=false';  end;
/

... with and without 'execute immediate 'alter session set sql_trace=true';

VC Received on Tue Dec 23 2003 - 06:46:41 CST