RE: Best Practice: pinning objects
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 5 Mar 2007 14:30:03 -0500
A very real tangible benefit of pinning objects in the shared pool on
startup the effect in reducing ORA-04031 errors due to the reduction in
fragmentation caused by the reloading of large objects (packages and
stored procedures) and from the constant reloading heavily used smaller
stored objects into the shared pool. If you do not have memory to burn
this can be an important step in reducing the frequency of hitting the
ORA-0-4031.
-- Mark D Powell --
Phone (313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
ryan_gaffuri@xxxxxxxxxxx
Sent: Monday, March 05, 2007 12:59 PM
To: ajay_1ajay@xxxxxxxxx; sacrophyte@xxxxxxxxx; oracle-l
Cc: Ajay
Subject: Re: Best Practice: pinning objects
I have yet to see a true tangible reason to pin anything in
memory. If you are going to use it alot it will be in memory. If you,
don't worry about it. I have also not seen any performance improvements
from the use of pinning at all. It just seems like extra work and extra
maintenance for something that does not improve performance in any
noticeable way.
Reducing the small amount of physical IO you will get from a
pinned object that is not staying in memory because it is not used
enough is insignificant.
If you are going to go through the effort of coding a pinning
scheme test it to see if it makes any type of difference.
-------------- Original message --------------
From: Ajay <ajay_1ajay@xxxxxxxxx>
What are the valid points against pinning?
What do you mean by managing the trigger?
You can put the objects to pin in a database table and
join it with dba_objects and pin the appropriate ones. You only change
the table to add/remove objects and leave the trigger code alone. Thanks
Charles Schultz <sacrophyte@xxxxxxxxx> wrote:
Good day, list,
My colleagues and I were debating the merits of
having a startup trigger pin objects. I am in favor of this method, but
the others bring some valid points to the table. It basically comes down
to managing the trigger and the objects that are pinned. We experimented
with a shutdown trigger in the past, which was responsible for gathering
stats and collecting info about "current' objects, but that broke rather
quickly. So we are thinking of providing a list of objects from an
external source (ie, flat file), which would be consistent and extremely
visible. The alternative to using a trigger would be a cron job that
checks every-so-often.
I tried to find supporting documentation online,
but I did not get any useful hits for "best practice[s]" and "pin
object[s]". Anyone want to weigh in on either side? One way or another,
we want various appl icatio n objects to be pinned right after the
database is started, with minimal amount of hassle and overhead. From my
point of view, the whole purpose of a startup trigger is to run
something when the database starts. Yes, one could use a glorified
startup script, but we want to steer away from such conventions. One key
question is "Is there ever a time when you do not want to automatically
pin objects?"
I have also been looking for information about
whether or not a startup trigger would fire for "STARTUP UPGRADE" when
patching.
PS - I have an example I am using from
M.Gralike, 2003-10-10
--
Charles Schultz
________________________________
Be a PS3 game guru.
Get your game face on with the latest PS3 news and
previews at Yahoo! Games.
<http://us.rd.yahoo.com/evt=49936/*http://videogames.yahoo.com>
Other related posts:
