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

"VC" <boston103_at_hotmail.com> wrote in message news:DoWFb.624239$HS4.4571770_at_attbi_s01...
> Hello Niall,
>
> ["Niall Litchfield" ]
> > for l_record in (select sysdate from all_objects) loop
> >
> > null;
> > end loop;
> > dbms_output.put_line( (dbms_utility.get_time - l_start)/100
> > ||'seconds....' );
> > end;
> > /
>
> It it a joke ? What has measuring 'select sysdate from all_objects'
> performance got to do with my original test ? The original query performs
a
> merge join on a real table and the trace(sql_trace) was supposed to show
at
> what stage in the join Oracle spends most time.

It is a different query which exhibits different behaviour.

Another case which is a little more real world like

drop table t1;

create table t1 as select * from all_objects;

alter table t1 add constraint pk_t1 primary key (object_id);

drop table t2;

create table t2 as select * from all_objects;

alter table t2 add constraint pk_t2 primary key (object_id);

exec dbms_stats.gather_schema_stats('NIALL');

alter session set events '10046 trace name context forever, level 8';

set timing on

set autotrace on explain

select count(*) from

(select t1.object_name

from

t1,t2

where t1.object_id = t2.object_id

and t2.object_id between 2000 and 3000);

alter session set events '10046 trace name context off';

select count(*) from

(select t1.object_name

from

t1,t2

where t1.object_id = t2.object_id

and t2.object_id between 2000 and 3000);

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.

>
> I am sure that the test would perform beautifully if you removed the query
> entirely.
>
>
> [VC]> > 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 ?
> >
>
> ["Niall Litchfield" ]
> > So I think that I disagree with your statement.
>
> I wonder how one can disagree with facts. A simple merge join runs 7
times
> slower with tracing enabled. Of what use is the performance measurement
> tool causing this kind of 'overhead' ?
>
> ["Niall Litchfield" ]
> > Where the impact is
> > significant then the tool is unhelpful, however where the overhead is
> > insignificant then it isn't.
>
> Please elaborate on the cases where the tool can be helpful. You cannot
> seriously claim that the tool can be used only for simple full table
scans.
> Even in this case, the difference is significant, although not as
striking:

I think I stated originally that where the elapsed time from the problem process corresponds roughly to the elapsed time from the traced process then sql_trace ought to be useful.

> We have 1.25 vs. 0.67 seconds. The 'overhead' is 'only' 100%.
>
> Even for full table scans, the tool is hardly useful.
>
> ["Niall Litchfield" ]
> > Having said all that some further digging on metalink suggests that
there
> is
> > a bug 3009359 which has been filed against this issue against a forum
> > thread you raised in June. Have you tried pushing Oracle for a status
> update
> > on this bug?
>
> I have but to no avail.

Ah. Not good. Especially as it looks as though the bug was raised in response to your query.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK