When to pin objects in memory
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: When to pin objects in memory

  1. #1
    Join Date
    Jul 2004
    Posts
    7

    Question 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!

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    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.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I remember using it in V7, but don't have a use for it now.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    We started pinning tables in memory recently but I am not sure of the results, I'll find out tomorrow.
    I remember when this place was cool.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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"?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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



  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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.
    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

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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?

  10. #10
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width