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.

Maybe someone else has a reason to pin.

I remember using it in V7, but don't have a use for it now.

We started pinning tables in memory recently but I am not sure of the results, I'll find out tomorrow.

Quote:

---

Originally posted by Mr.Hanky
We started pinning tables in memory recently but I am not sure of the results, I'll find out tomorrow.

---

Pinning tables? :confused: In which memory? How do you do that? Or have you confused concept of pinning PL/SQL object with the concept of "keep buffer cache"?

I'm with jmodic - thought frquently used "packages" were pinned in the SP, not tables !!
Some that come to mind:dbms_ =>system,output etc...

:cool:
:)

Quote:

---

Originally posted by jmodic
Pinning tables? :confused: In which memory? How do you do that? Or have you confused concept of pinning PL/SQL object with the concept of "keep buffer cache"?

---

Looks like Mr H meant the Caching option on Table..

Oracle has a great article on this task and makes recommendations as to what should be pinned.

You can also run this script to get an idea of what is going on in your SP.

Code:

---

SELECT owner, name, o.type, sharable_mem,
      loads, executions, kept
  FROM v$db_object_cache o
 WHERE loads > 0  AND o.type in ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'SEQUENCE')
 ORDER BY sharable_mem DESC

---

Of course you can modify it to check the number of executions and such.

Just to give you an example of why pinning objects are a good thing. Lets look at this.

Code:

---

SUBSTR( SUBSTR( TYPE                              LOADS EXECUTIONS KEP
------- ------- ---------------------------- ---------- ---------- ---
SYS    DBMS_PI PACKAGE                            1932    678695 NO
SYS    DBMS_LO PACKAGE                            581    576015 NO
SDE    PINFO_U PACKAGE                              50    1092836 NO
MPI2    TA_SIP_ TRIGGER                              35    366824 NO
MPI2    SPCOL_D TRIGGER                              35    154010 NO
MPI2    TIB_DML TRIGGER                              18    4017861 NO
MPI2    TXP_DEV TRIGGER                              9    105112 NO
MPI2    TTL_DEV TRIGGER                              6    106664 NO
MPI2    TA_SIP_ TRIGGER                              6    106665 NO
MPI2    TSDE_DE TRIGGER                              6    105117 NO
SDE    LOCK_UT PACKAGE                              5    3057220 YES

SUBSTR( SUBSTR( TYPE                              LOADS EXECUTIONS KEP
------- ------- ---------------------------- ---------- ---------- ---
SYS    DBMS_UT PACKAGE                              4    221480 YES
SDE    SDE_UTI PACKAGE                              4    4500938 YES

---

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?

Code:

---

SUBSTR( SUBSTR( TYPE                              LOADS EXECUTIONS KEP
------- ------- ---------------------------- ---------- ---------- ---
SYS    DBMS_PI PACKAGE                            1935    680144 YES

---

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.

Good stuff OracleDoc, thanks.

Now the question arises, how to prioritise the pinning?

Naïvely I would say:
AWD = Avoidable Work Done = sharable_mem * (loads -1)
So would you start with the biggest AWD?

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.

select ksmlrcom,ksmlrsiz,ksmlrnum from x$ksmlru;
Run this, based on ksmlrsiz and ksmlrnum find out which ksmlrcom should be pinned.

Thanks and Regards,
Satheesh Babu.S

Pinning the PKG is necessary only in 7 and 8 to avoid 4031 error.

No need to pin PKG or procedure if you are using 8i or later release.
If the pkg size > 5K , then oracle now stores it in 5K piece in the SP.

Tamil

tamil, can you give me some Doc's on that?
thanks!

Is this sufficient?

http://asktom.oracle.com/pls/ask/f?p...2667245192483,

Pinning, Keep pool, it's all the same crap. You are storing it in memory.

Listen to what I mean, not what I say!!

Isn't the keep pool more of a datawarehouse thing?

Quote:

---

Originally posted by padders
Is this sufficient?

http://asktom.oracle.com/pls/ask/f?p...2667245192483,

---

Thanks Padders that was a good read. Normally my Dad:Dhas awesome advice and is able to filter and put into laymens terms all the rhetoric Oracle spews. However, just because one man says one thing doesn't mean it's the be all truth. In this particular case I'm going to have to disagree with him and the reason I am is because the code doesn't lie.
If you look at the DBMS_PIPE package that I pinned in my database yesterday from the above post and look at it this morning.

Code:

---

SUBSTR(OW SUBSTR(NA SUBSTR(O.      LOADS EXECUTIONS KEP
--------- --------- --------- ---------- ---------- ---
SYS      DBMS_PIPE PACKAGE        1935    723563 YES

---

Ever since I've pinned it, the loads have stayed the same.

By all means post a followup then ;)

http://asktom.oracle.com/pls/ask/f?p...:2667245192483

Quote:

---

Originally posted by padders
By all means post a followup then ;)

http://asktom.oracle.com/pls/ask/f?p...:2667245192483

---

Hmmmm, I got a better idea why don't we get input from our community? Wouldn't you think the input from many people (with experience) would outway what one person says?

I'm not trying to spark a debate here. Like I said Tom is a great advisor. I think one should look at all the options (and opinions)before jumping to conclusions.

Move to Obfuscation Unlimited ??

Quote:

---

Originally posted by OracleDoc
Move to Obfuscation Unlimited ??

---

WHY? The kind of discussion you propose is EXACTLY what should be going on here.

My 2¢: Tom takes the official Oracle line (he has to). The official Oracle line is to have an infinite amount of memory and an infinite number of disks. :D Those of us who have to compromise might need to pin? resize SGA? etc? etc. At least with the query you gave, there is some basis for logical action and some means of measuring the effect of changes.

The big question: how does it reflect on response time or throughput?

Quote:

---

Originally posted by DaPi
The big question: how does it reflect on response time or throughput?

---

It hardly does..

I believe the KEPT column is for backward compatibility.

Thanks
Tamil

Quote:

---

Originally posted by OracleDoc
Ever since I've pinned it, the loads have stayed the same.

---

Have you monitored the total number of loads though? If DBMS_PIPE is now pinned then I expect that other code is being aged out faster and suffering more loads. I'd try monitoring total loads, not just this one package.

From the Tom Kyte thread, this is the most interesting comment for me ...

Quote:

---

If you use it -- it'll stay.
If you don't -- it'll go.

If you pin it -- it'll stay -- meaning, if you use it, it would stay anyway. If
you DON'T use it, why did you pin it?

pinning was "useful" for big packages before the 4k pagesize stuff.
after that, not very useful

---

It makes sense ... and if you pin a package then it has to be at the expense of others, right?

Quote:

---

Originally posted by slimdave
Have you monitored the total number of loads though? If DBMS_PIPE is now pinned then I expect that other code is being aged out faster and suffering more loads. I'd try monitoring total loads, not just this one package.

From the Tom Kyte thread, this is the most interesting comment for me ...


It makes sense ... and if you pin a package then it has to be at the expense of others, right?

---

I agree with SlimDave. Which is usually a safe bet.

Quote:

---

Originally posted by gandolf989
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.

---

Quote:

---

Originally posted by gandolf989
I agree with SlimDave. Which is usually a safe bet.

---

* warm glow *

Just not politically.

Quote:

---

Originally posted by Mr.Hanky
Just not politically.

---

I'm realistic, and don't expect everyone to be as perfect as me.

* 'nother warm glow *

Quote:

---

Originally posted by OracleDoc
Oracle has a great article on this task and makes recommendations as to what should be pinned.

Code:

---

SELECT owner, name, o.type, sharable_mem,
      loads, executions, kept
  FROM v$db_object_cache o
 WHERE loads > 0  AND o.type in ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'SEQUENCE')
 ORDER BY sharable_mem DESC

---

Of course you can modify it to check the number of executions and such.

---

Both the 8i and 9i reference manuals I've looked at indicate that the executions column of v$db_object_cache is not used. They suggest using the executions column of v$sqlarea instead. Anyone know why?

Also just wanted to say thanks to everyone for all the input! :D