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

Thread: When to pin objects in memory

  1. #21
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I believe the KEPT column is for backward compatibility.

    Thanks
    Tamil

  2. #22
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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 ...
    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?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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

    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.

  4. #24
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by gandolf989
    I agree with SlimDave. Which is usually a safe bet.
    * warm glow *
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #25
    Join Date
    Jan 2001
    Posts
    3,131
    Just not politically.
    I remember when this place was cool.

  6. #26
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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 *
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  7. #27
    Join Date
    Jul 2004
    Posts
    7
    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!

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