Comments below.
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?
More below...
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?
The plan_hash_value is a quasi-unique (subject to hash collisions) signature of a plan. You can get the same plan - and therefore the same plan_hash_value - not only for only slightly different, but even for rather different sql statements. Just think of the simplest plan, a full table scan. Any number of sql could result in a full table scan (of the same table) and will therefore have the same plan_hash_value.
Before 9i when Oracle came out with the plan_hash_value I used to extract sql statements from v$sql, run them through explain and assign a hash value to each plan. Then repeat the process after changing some tuning parameters (re-analyze tables, add/drop an index, init.ora parameters) and look for plan changes which I then could scrutinize for their performance impact. Sort of Laredo on a shoestring. With the plan_hash_value that has become much simpler.
But, to answer your question, since so many different sql statements can result in the same plan, you can hardly draw conclusions from the cbo trace of one sql to the cbo trace of another, even if they eventually result in the same plan (and plan_hash_value).
Tanel.
-- //www.freelists.org/webpage/oracle-l
