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

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?

The cursor name (SQL statement text in case of plain SQL and anonymous blocks) is only stored in library cache parent for space savings (X$KGLOB.KGLNA). So all child cursors with same name reference to the parent. All child cursors do have a handle, a heap 0 for library cache metadata where general information is held and the execution plan is stored in heap 6. Latter is normally bigger one as it stores a "compiled" representation of execution plan.

The execution plan heap can be aged out independently of heap0 and library cache child handle, if it is so, then we still see all child cursors in library cache, but no execution plans. Having a situation where heap 6 is unpinned and heap 0 is pinned is quite common, heap 0 is usually pinned as long as there are open (or closed but cached) cursors referencing it while heap 6 is only pinned for the duration of actual execution/call (unless cursor_space_for_time = true, which keeps all cursor heaps pinned all the time).

I think when we pin a cursor, then I think a bit is set in cursor handle, which says that whenever heaps are loaded into memory, they should be immediately be pinned. This prevents aging out of these - however, when DDL on dependent objects invalidates the cursos, the unused heaps can be aged out again, but as soon as we reload them (reparse) these are pinned again thanks to this bit in cursor handle.

A very good whitepaper about Oracle's shared pool/library cache memory management is at:
http://download-west.oracle.com/oowsf2005/003wp.pdf

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).

Wasn't there also a problem that when we have a statement select * from emp parsed both under user1 and user2, referencing completely different tables (but with similar execution plan flow), then the plans hash to the same value?

So, the plan_hash_value is good for experimenting, but not completely reliable (as you already said..)

Tanel.

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