RE: Best Practice: pinning objects

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Mon, 5 Mar 2007 14:30:03 -0500
Message-ID: <D1DC33E67722D54A93F05F702C99E2A9905A25@usahm208.amer.corp.eds.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ryan_gaffuri_at_comcast.net

	Sent: Monday, March 05, 2007 12:59 PM
	To: ajay_1ajay_at_yahoo.com; sacrophyte_at_gmail.com; 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_at_yahoo.com> 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_at_gmail.com> 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>

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

Received on Mon Mar 05 2007 - 13:30:03 CST