If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hybrid View

When to pin objects in memory

I'm a newbie, so please bear with me!

I've done some searching thru google on when database objects should be pinned in memory, and am coming up with mixed answers. I've read some articles which state that you should pin any large, frequently used objects in memory. Other articles state that pinning was used moreso in the earlier versions of Oracle (7 and below) in response to numerous ORA-04031 errors, which are not as much a factor in the newer versions. (I'm working with Oracle 8i and 9i if this makes any difference.)

To me, pinning large, frequently used objects does not seem to make sense since frequently used objects are more likely to remain in memory b/c they are highly used, but as I said, I'm a newbie and I'm probably overlooking something. Also, is there any rule of thumb to determine the size of a 'large' object?

I guess I'm wondering mostly about what the main benefits of pinning objects are, and how to determine if performance would improve from pinning. Any information is appreciated! Thanks!

I haven't yet found a use for pinning objects. If you keep you code modular and efficient, reduce the number of hard parses, and make sure your sga is sized correctly, you should not need to pin anything. i.e. the less unique sql and pl/sql that hits your database the more likely it is to already be in memory. Think bind variables and bulk binding whenever possible.

If you notce the dbms_pipe is not kept and look how many times Oracle had to load him into memory.
This was taken 30 minutes after the above as executed and dbms_pipe was pinned. Notice how the loads have stopped?

One other thing you can look at if you're having problems with your shared pool is the SQL that's being put in there. As of Oracle9i there's a new parameter for cursor_sharing that's called similar. This is great to use if your app has a problem with not using bind variables.

You can also implement your db_keep_cache_size parameter this is used for when you want to cache your tables.

I hope this helps and good luck.

Oracle it's not just a database it's a lifestyle!
--------------BTW....You need to get a girlfriend who's last name isn't .jpg

I personally use a combination of loads and executions when it comes to packages,functions, and triggers. The thing you have to look out for is the size of your SP. Too big fragmentation... too small poor performance. So, what I do is normally any object with 100k + executions and 20 or more loads I pin. With cursors if they've been loaded once I pin them all. I also have a procedure that I use to pin all that good stuff into memory at start up.

With tables I check the top sessions and see which sql statements have been executed the most, along with the physical reads and see who my biggest problem children are then I cache those tables.

Oracle it's not just a database it's a lifestyle!
--------------BTW....You need to get a girlfriend who's last name isn't .jpg