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

From: VC <boston103_at_hotmail.com>
Date: Mon, 22 Dec 2003 23:53:01 GMT
Message-ID: <xtLFb.625746$Tr4.1620004@attbi_s03>

Hello dx,

Please see below:
"dx" <seaelephant_at_hotmail.com> wrote in message news:7f28ac37.0312221301.664a36e2_at_posting.google.com...
> sql*plus set timing is broken some time, you'd better not use it to
> time sql running, set autotrace on also introduce overhead.
>

Firstly, I must apologize for my forgetting to mention that Oracle 9..2.0.1does not exhibit this kind of behaviour. I did mention in my original message that I was using was 9.2.0.4.

>>"dx" Here are two scripts I used to test.

Secondly, just for the fun of it, I ran your scripts, slightly modified to avoid sqlplus influence entirely. Here are the results:

---

SQL> drop table t1;

Table dropped.

SQL> create table t1(id int, data varchar2(30));

Table created.

SQL>
SQL> begin
  2 for i in 1..1000 loop

  3      insert into t1 values(i, 'xxxxxxxxxxxxxxxxxxxxx');

  4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> set serverout on
SQL> declare
  2 l_start number;
  3 begin
  4 execute immediate 'alter session set sql_trace=true';   5 l_start := dbms_utility.get_time;   6 for l_record in

  7       (select a.id, count(*)
  8       from
  9        (select id from t1) a
 10        join
 11        (select id from t1) b
 12        on a.id<=b.id group by a.id)  loop
 13        null;

 14 end loop;
 15 dbms_output.put_line( (dbms_utility.get_time - l_start)/100 || 'seconds..
..' );
 16 execute immediate 'alter session set sql_trace=false';  17 end;
 18 /
2.45seconds....

PL/SQL procedure successfully completed.

SQL> declare
  2 l_start number;
  3 begin
  4 --execute immediate 'alter session set sql_trace=true';   5 l_start := dbms_utility.get_time;   6 for l_record in

  7       (select a.id, count(*)
  8       from
  9        (select id from t1) a
 10        join
 11        (select id from t1) b
 12        on a.id<=b.id group by a.id)  loop
 13        null;

 14 end loop;
 15 dbms_output.put_line( (dbms_utility.get_time - l_start)/100 || 'seconds..
..' );
 16 execute immediate 'alter session set sql_trace=false';  17 end;
 18 /
0.36seconds....

PL/SQL procedure successfully completed.

---

For ten thousand rows, the results are:

244.58seconds... (trace = true)
vs
34.58seconds.... (trace =false)

So, as you can see, the results are not much different from those I got by using SET TIMING ON. The query runs about seven times slower with 'sql_trace=true'.

Lastly, I would not call this kind of behaviour 'overhead'. Would'nt you agree that the tool, in its present state, is practically useless for performance tuning ?

Rgds.

VC Received on Mon Dec 22 2003 - 17:53:01 CST