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

From: dx <seaelephant_at_hotmail.com>
Date: 22 Dec 2003 13:01:28 -0800
Message-ID: <7f28ac37.0312221301.664a36e2@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.

Here are two scripts I used to test.

test1.sql
set serveroutput on
variable n number
exec :n := dbms_utility.get_time;
set termout off
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;
set termout on
exec dbms_output.put_line( (dbms_utility.get_time-:n)/100 || ' seconds....' );

test2.sql
set serveroutput on
variable n number
exec :n := dbms_utility.get_time;
set termout off
alter session set sql_trace=true;
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;
alter session set sql_trace=false;
set termout on
exec dbms_output.put_line( (dbms_utility.get_time-:n)/100 || ' seconds....' );

I tested on 9.2.0.1 windows 2000 platform. SQL> @c:\temp\test2.sql

PL/SQL procedure successfully completed.

1.78 seconds....

PL/SQL procedure successfully completed.

SQL> @c:\temp\test1.sql

PL/SQL procedure successfully completed.

.53 seconds....

PL/SQL procedure successfully completed.

SQL> insert into t1

  2  select 1000+rownum, 'xxxxxxxxxxxxxxxxxxxxx'

  3 from all_objects
  4 where rownum <= 9000;

9000 rows created.

SQL> select count(*) from t1;

  COUNT(*)

---

     10000

SQL> @c:\temp\test2.sql

PL/SQL procedure successfully completed.

55.7 seconds....

PL/SQL procedure successfully completed.

SQL> @c:\temp\test1.sql

PL/SQL procedure successfully completed.

44.28 seconds....

PL/SQL procedure successfully completed.

So sql trace definitely has overhead, but it shouldn't be that huge, you might want to run my scripts to see what happens. Received on Mon Dec 22 2003 - 15:01:28 CST