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

From: VC <boston103_at_hotmail.com>
Date: Tue, 23 Dec 2003 15:22:24 GMT
Message-ID: <Q4ZFb.637443$Fm2.571436@attbi_s04>

Please see below:

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3fe85712$0$9386$ed9e5944_at_reading.news.pipex.net...
>
> gives on my system no timing differences. You might object that this gives
a
> nested loops join but stick a use_merge hint in and I still see no
> difference.
>

Ah, but that's because you've used a wrong tool for the job. As someone quite correctly said, 'set timing on' is just too crude. Let's use some PL/SQL instead:

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 t1.object_name
  8         from
  9         t1,t2
 10         where t1.object_id = t2.object_id
 11         and t2.object_id between 2000 and 3000) loop
 12         null;
 13     end loop;
 14     dbms_output.put_line( (dbms_utility.get_time - l_start) || '

centisecond
s....' );
 15 execute immediate 'alter session set sql_trace=false';  16 end;
 17 /
2 centiseconds....

PL/SQL procedure successfully completed.

SQL> /
2 centiseconds....

PL/SQL procedure successfully completed.

SQL> /
2 centiseconds....

PL/SQL procedure successfully completed.

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 t1.object_name
  8         from
  9         t1,t2
 10         where t1.object_id = t2.object_id
 11         and t2.object_id between 2000 and 3000) loop
 12         null;
 13     end loop;
 14     dbms_output.put_line( (dbms_utility.get_time - l_start) || '

centisecond
s....' );
 15 execute immediate 'alter session set sql_trace=false';  16 end;
 17 /
5 centiseconds....

PL/SQL procedure successfully completed.

SQL> As you can see, the 'overhead' is 150%. Quite significant, would'nt you agree ?
Also, you should try on a more realistic result set, say, more than 1000 rows since we are already down to centiseconds with our 600 rows.

Rgds.

VC Received on Tue Dec 23 2003 - 09:22:24 CST