I'm currently encountering a weird problem with one query. Because I
want to look into how CBO is treating this query, I tried using event
10053. Before running a statement I'm issuing:

alter session set events '10053 trace name context forever, level 1';

then run a statement and afterwards:

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

The problem is that in the UDUMP trace file there is only QUERY section
with shown statement, but no other information. Oracle is in version
9.2.0.7. On my testing 9.2.0.6 this event is working flawlessly.
Because I known that the plan is going to be displayed only if the
statement issued was hard parsed, I also tried ALTER SYSTEM FLUSH
SHARED_POOL, but with no success.

Did anyone encouter similar problem?

Thank you

Dusan Bolek

"Dusan Bolek" <spambin@seznam.cz> ha scritto nel messaggio
news:1138615372.401926.193740@f14g2000cwb.googlegr oups.com...
> I'm currently encountering a weird problem with one query. Because I
> want to look into how CBO is treating this query, I tried using event
> 10053. Before running a statement I'm issuing:
>
> alter session set events '10053 trace name context forever, level 1';
>
> then run a statement and afterwards:
>
> alter session set events '10053 trace name context off';
>
> The problem is that in the UDUMP trace file there is only QUERY section
> with shown statement, but no other information. Oracle is in version
> 9.2.0.7. On my testing 9.2.0.6 this event is working flawlessly.
> Because I known that the plan is going to be displayed only if the
> statement issued was hard parsed, I also tried ALTER SYSTEM FLUSH
> SHARED_POOL, but with no success.
>
> Did anyone encouter similar problem?

Is the statement in pl/sql block? If so, you cannot get this info in 9i(you
can in 10g).


Regards,
Dimitre

No it is just a simple select. However, I have a fear that the cause of
the problem could be that the query is distributed and one of the
participating tables in it is from a remote TERADATA database connected
via Open Gateway. That could be a reason, but I hoped that at least
part of the information should available anyway.

--
Dusan Bolek

It looks like the "driving site" for this SQL is the teradata database.
Ask the DBA of this database to trace this SQL.

Daniel

I do not understand it at all now. I have tried to trace a simple
non-distributed query in the same database to get at least used
parameters used by optimizer (the second 10053 trace section) to
compare them side by side, but I do not get any information. The whole
trace file looks like this:

Dump file c:\oraadmin\db89\udump\db89_ora_5440.trc
Tue Jan 31 08:37:59 2006
ORACLE V9.2.0.7.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 1, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Windows 2000 Version 5.2 Service Pack 1, CPU type 586
Instance name: db89

Redo thread mounted by this instance: 1

Oracle process number: 9

Windows thread id: 5440, image: ORACLE.EXE


*** 2006-01-31 08:37:59.830
*** SESSION ID18.2289) 2006-01-31 08:37:59.830
QUERY
select * from trace_test
*** 2006-01-31 08:44:49.116
QUERY
alter session set events '10053 trace name context off'

--
Dusan Bolek

Did you try a reboot?

Stranger things have known to get fixed in oracle land after the reboot
of a windows server.

Dusan Bolek wrote:
> I do not understand it at all now. I have tried to trace a simple
> non-distributed query in the same database to get at least used
> parameters used by optimizer (the second 10053 trace section) to
> compare them side by side, but I do not get any information. The whole
> trace file looks like this:
>
> Dump file c:\oraadmin\db89\udump\db89_ora_5440.trc
> Tue Jan 31 08:37:59 2006
> ORACLE V9.2.0.7.0 - Production vsnsta=0
> vsnsql=12 vsnxtr=3
> Windows 2000 Version 5.2 Service Pack 1, CPU type 586
> Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
> With the OLAP and Oracle Data Mining options
> JServer Release 9.2.0.7.0 - Production
> Windows 2000 Version 5.2 Service Pack 1, CPU type 586
> Instance name: db89
>
> Redo thread mounted by this instance: 1
>
> Oracle process number: 9
>
> Windows thread id: 5440, image: ORACLE.EXE
>
>
> *** 2006-01-31 08:37:59.830
> *** SESSION ID18.2289) 2006-01-31 08:37:59.830
> QUERY
> select * from trace_test
> *** 2006-01-31 08:44:49.116
> QUERY
> alter session set events '10053 trace name context off'
>
> --
> Dusan Bolek

Dusan,

I believe you are not doing this right.

It appears that you are trying to get the 10053 trace using the
following:

alter session set events '10053 trace name context forever, level 1';

select * from trace_test;

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

While you should be doing this:

alter session set events '10053 trace name context forever, level 1';

EXPLAIN PLAN FOR select * from trace_test;

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


.... Also make sure that the table is analyzed (i.e. CBO is being used)
otherwise the trace will not show any trace details.

HTH

Anurag

I was doing it right, the explain plan variant is usually used only
when tested query is too complex to be actually issued against a
database. The only problem was that I (as usual) forgot to analyze my
new testing table just after its creation. That means when using select
* from trace_test event 10053 is doing its job flawlessly. However, the
problem remains with queries accessing remote Teradata database, it
looks like this is not supported by event 10053. :-(

--
Dusan Bolek

ah ok ..
Yes there are two ways to get the 10053 trace.
If not using explain plan for ... method, then one needs to make sure
the
statement is parsed for the trace to show.

Metalink Note:225598.1 does a nice job of clarifying this....

Anurag