| 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: Pinning/keeping plans in shared pool
Brandon,
What’s your cursor_space_for_time setting? Does setting it true make any difference?
> Boris, Raj & Tanel,
>
> Please forgive my imposing on you, but I am facing a
> problem that I see
> the 3 of you discussed on the oracle-l freelist a
> few months ago
>
(http://www.freelists.org/archives/oracle-l/12-2005/msg00588.html)
> so I'm hoping I might be able to benefit from your
> prior experience &
> knowledge in this area if you have a few minutes to
> help me out. My
> question is regarding whether or not a sql plan is
> kept in the shared pool when the cursor is kept via
> dbms_shared_pool.keep.
> Tanel's explanation, and the Oracle white paper he
> referenced both seem
> to indicate that the sql_plan/heap6/sqlarea is also
> kept and that it can
> be unpinned when the cursor is invalidated, e.g. due
> to DDL, updating
> stats, grants, etc., but when the plan is loaded
> into the pool again, it
> will be automatically repinned/kept. I'm not saying
> they're wrong, but
> my findings aren't in line with these statements so
> I'm trying to understand why.
Maybe I misunderstood it, but I thought it works
differently. Here’s Tanel’s explanation relevant for
this bit (taken from the same post
http://www.freelists.org/archives/oracle-l/12-2005/msg00658.html):
> > 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).
My observations were fully aligned with yours and I think are explained by Tanel’s post. Metadata (heap 0) might be pinned, while the plan (heap 6) might be aged out. Keeping a cursor ensures that the metadata stays around, not necessarily the plan. Tanel suggested that the cursor_space_for_time should ensure that both stay – and hence my question at the beginning of this email (but I haven’t tested this)
In my case I also wanted to keep the plan but for a different reason. It wasn’t the parsing time that took a long, but the execution plan that the CBO (rightfully) used to come up with for the original user/parser that was vastly inappropriate for the others.
It was hilarious. One of the sales agents used to come in really early – around 7:30am. She happened to work in the part of the application that absolute majority of the users worked, except “the selected few”. So she used to come in early and caused the parse and a good plan (after the nightly stats collection job). The problem struck when she didn’t. If she was delayed or took a day off, we were in trouble, because somebody from another department (marketing I think it was) happened to be this “one of the selected few” and he would come in around 8am (normally later than the sales lady) and happened to use the historical data, which for this just-rolled-out in prod app was next to zero. Naturally he hits almost empty partitions and the CBO (rightfully again) comes up with the full partition scans, which worked great for him and made application miserable for just about everybody else (including sales, generating revenue, so bean counters were not particularly thrilled when this happened).
Now, when the “good” (for majority of users) plan was used I observed quite often the same picture as you do – metadata is there, plan isn’t. Since absolute majority of the users used to hit the “right” partitions causing the good plan, it wasn’t a problem most of the time. (The only exception was when plan was aged out and the “bad guy” from marketing came in, which didn’t happen often). One of the “magic” solutions was... simply to shutdown the app, flush the pool and let the sales people in first :-)
When marketing people came in the morning first however, their plan caused queries of everybody else to execute a loooong time and the plan would stay around whole day long (because queries had been running, so Oracle couldn’t unpin it, making it eligible for aging out) until nightly stats collected would flush it. And in this case I could see both metadata and the plan in the library cache.
>> If it doesn't keep the plan in the pool, then
>> what is the point of keeping a cursor at all?
I might easily be wrong here, but my understanding is that keeping was designed for a shared pool space management, not as a performance tool (for keeping executions plans around, although it would certainly be a nice side effect). Not sure if my previous sentence makes any sense though as space management of heap 0 is probably rather simple (metadata is small) compared to the heap 6 as plans can be pages long...
Thanks,
Boris Dali.
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jul 08 2006 - 11:54:58 CDT
 |
 |