When to pin objects in memory

[babe9c]

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!

[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.

Maybe someone else has a reason to pin.

[slimdave]

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

[Mr.Hanky]

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

[jmodic]

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? 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"?

[cruser3]

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

[abhaysk]

Originally posted by jmodic
Pinning tables? 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..

[OracleDoc]

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.

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.

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?

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.

[DaPi]

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?

[OracleDoc]

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.