| 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)
Wolfgang,
Thanks for your reply.
This is what I suspected, but wanted to get a confirmation from an authoritative source. If Wolfgang says you can't get a cbo trace, I know I can't get a cbo trace.
No histograms on the tables involved, but you are dead on with the BV peeking suggestion. This case is somewhat similar to the one you cracked for me couple of years ago (that also started on this list). It's just here it's on 9i, different application and OS, but also partition stats and different plans developed based on different data distributions in different partitions. In fact one of the ways I simulate application behavior in the test-cases is by forcing the CBO to look at different partitions on a hard parse (the others are cardinality hint and different seeded stats). I can confirm this from a sql trace by verifying that BV values actually point to a partition that would justify cbo's change of plans. But in some cases BV values point to the same partition as before... but the plan is changed. That's the reason I really wanted to see cbo's calculations.
Couple of other questions if you don't mind: 1) Quite often I see this case where the sql is still visible in v$sql (and its aggregate cousins - v$sqlarea, etc) - so it's not (completely) flushed out right?, but it's gone from v$sql_plan. In fact if stats are re-gathered or re-seeded on one of the row sources, this is also what is happening. I suppose this is because the parent sql and its plan are stored in different heaps? Than if a packaged stored code is kept in the shared pool (via dbms_shared_pool) - is the plan also guaranteed to be kept with it? But if stats are re-gathered (with default invalidate option) - plan gets flushed out regardless of the "keeping" correct?
2) Can I get the same plan_hash_value for slightly different (but functionaly the same) statements (with different hash_values of course - I fish it out from v$sql, but than reformat it slightly so it looks less ugly, sometimes adding [additional] hints)? If I do, does it constitute a prove that my test-case and a plan developed for the application are effectively the same, meaning if I **had** an option to get a cbo trace for the app's sql it would've matched a cbo trace I get for my test-case?
3) Another stupid question, just taking advantage of you being here - do you know when EXACTLY v$sql_plan_statistics gets populated? Sometimes I get a result set back sometime ago, and the plan is visible in v$sql_plan, but the stats are still all zeros in v$sql_plan_statistics. Any way to "flush" it (like flushing index monitoring info visible in v$object_usage, which otherwise might not appear for some 15min)?
Thanks for your help. Merry Christmas everybody! Boris Dali.
> 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-lReceived on Fri Dec 23 2005 - 15:17:39 CST
 |
 |