| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: 10053 trace for sql fired from pl/sql (stored code)
Dimitre,
That is correct. I never had a problem with sql trace of a stored code, it's the cbo trace that doesn't seem to work in 9i for me
It's very desirable though to get a cbo trace, because this stubborn piece of stored code uses a sql that seems to change execution plans "at no apparent reason". So I was hoping to gain some insight into the WHY by catching the cbo calculations "as it happens". I don't have a problem to get a hard parse either via "natural reasons" (this shared pool is small and it ages less often sed sql fast) or via something like re-collecting (or seeding) the stats on one of the tables involved.
The only problem I have it to get the cbo to emit its calculations for me :-(
Thanks,
Boris.
> > 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
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 23 2005 - 00:50:39 CST
 |
 |