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

Boris, Dimitre,

I did a bit of further checking and
a) as you both already stated, there does not seem to be a way to have pl/sql code create a cbo trace prior to Oracle 10. b) and when I ran into that "problem" I resorted to Perl to get around it.

Boris
In the absence of a cbo trace one suggestion is to continuously monitor the hash_value and plan_hash_value, print out the plans from v$sql_plan, and try to draw some conclusion from the changed plan and possibly change row source estimates.
Can the problem be due to bind variable peeking? A pl/sql stored procedure is likely using bind variables (it's harder to avoid it than not) and as you said, your shared pool is fairly small, so it is possible for the sql to age out (or is the procedure "kept") and therefore be reparsed, possibly with a different bind value. Are there histograms on any of the involved tables?

At 04:50 PM 12/22/2005, Boris Dali wrote:
>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 :-(

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

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