Boris Dali wrote:
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)?
Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com -- http://www.freelists.org/webpage/oracle-l