RE: Pinning/keeping plans in shared pool
- From: Boris Dali <boris_dali@xxxxxxxx>
- To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>, rjamya@xxxxxxxxx, tanel.poder.003@xxxxxxx
- Date: Sat, 8 Jul 2006 12:54:58 -0400 (EDT)
Brandon,
What?s your cursor_space_for_time setting? Does
setting it true make any difference?
--- "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
wrote:
> 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
>
(//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
//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.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
