Re: 10053 trace for sql fired from pl/sql (stored code)

Wolfgang, Dimitre,

I believe version of Oracle is relevant. I don't have any problems to get the cbo trace for sql from pl/sql in 10g (at least in 10gR2 on Linux), but it doesn't work in 9i for me on any platform.

Flushing shared pool can certainly be my friend, but it doesn't seem to be "friendly enough". Consider:

create table t1( i int);

create or replace procedure p is begin
execute immediate 'alter session set events ''10053 trace name context forever''';
insert into t1 select /* from pl/sql */ * from t1; end;
/

exec p
alter system flush shared_pool;
exec p

... and I get in the trace is :

QUERY
alter system flush shared_pool
*** 2005-12-22 18:23:24.875
QUERY
BEGIN p; END;

Even bouncing a DB doesn't help

---

As somebody else mentioned, Wolfgang's papers are
excellent (probably one of the best piece of technical
documentation I've ever seen) and I think I am well
aware that a cbo trace is emitted only upon a hard
parse, but I've never had a need to trace cbo's
decisions of sql coming from pl/sql and it just
doesn't seem to work in 9i.

And yes, I actually need to do it for another session
(no control over the source code), but if I can figure
it out for my own session in 9i, the rest would be
simple

Any ideas (in 9i please)?

Thanks,
Boris Dali.



--- Wolfgang Breitling  wrote:



> Dimitre and all,

> 

> now that you mention it, I recall that I had

> problems getting an 10053 

> trace out of a (in my case anonymous) PL/SQL block.

> I'll have to look 

> how I eventually got what I was looking for (the

> 10053 trace). It is 

> quite possible that I went the path of least

> resistance and used perl.

> In my case the tool didn't matter since all I was

> after was the 10053 

> trace and a way to run the - more or less same - sql

> with slightly 

> changed parameters.

> 

> Radoulov, Dimitre wrote:

> >  > It works with 9i.

> >  

> >  

> > I tested it on 9.2.0.4 and 9.2.0.7 on Solaris. The

> execute immediate 

> > 'alter session set events ''10053 trace name

> context forever, level 1''' 

> > within a plsql block doesn't seem to provide the

> expected 

> > information. The same code works with event 10046.

> 

> >  

> > You could try to trace with 10046 to get the sql

> and the values of the 

> > bind variables and then trace with 10053 in

> sqlplus, but in this way you 

> > could miss an important session environment

> information (if the 

> > application sets some parameters before calling

> the plsql code for example).

> >  

> > So you can create a logon trigger that executes

> "alter session set 

> > events '10046 ... " for that particular user to

> see the entire 

> > application code.

> >  

> >  

> >  

> > Regards,

> > Dimitre Radoulov

> >  

> >  

> 

> -- 

> Regards

> 

> Wolfgang Breitling

> Centrex Consulting Corporation

> http://www.centrexcc.com

> --

> http://www.freelists.org/webpage/oracle-l

> 

> 

> 




	

	
		
__________________________________________________________ 
Find your next car at http://autos.yahoo.ca
--
http://www.freelists.org/webpage/oracle-l