Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    46

    Unanswered: Pinning the objects and the impact

    Hi,

    I am working in ORacle 9i and have pinned one object which is a stored procedure.

    Can anyone explain me the advantages and disadvantages of pinning the objects.

    Will it have impact on the performance.

    Pl. advice.

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Non-pinned objects are flushed off the shared pool using the LRU algorithm (Least Recently Used) when there's no place for new SQL or PL/SQL objects. A pinned object stays in shared pool during the life of Oracle instance. As shared pool size isn't unlimited, you should pin only frequently used objects.

    It is expected that pinning should improve performance as system would not have to spend precious time to load those objects in the shared pool and create necessary space for it.

  3. #3
    Join Date
    May 2006
    Posts
    46
    Thanks.

    Can anyone give me the standard scripts to increase the sga and to monitor the performance of the database.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Isn't an object supposed to move _ahead_ in the LRU once you request it ? Say, you execute procedure A, it goes into the SGA, after some time, you execute it again, I expect procedure A to move ahead in the LRU, since I requested it again, and thus making it less proning to be moved off the SGA. I believe this _pinning_ stuff must be used carefully.. (having said that, I can't find a use for it.. unless of course you haven't set your SGA properly)..

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    yes, pinning might only be useful if you have a package that is not used
    regularly. Normally, if items get flushed out of the pool then an increase in
    pool size is done.

    If your package gets called a lot then I wouldn't bother pinning it.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jul 2006
    Posts
    1

    pinning objects..

    the pinning was done as a consequence of ora-04031 while accessing a package. the apllication is OLTP and reports are used EOD.now will the pinned package occupy more space in sga when it is called,ie in my case when the report is used. any quick review on the impact.
    to my knowledge ora-04031 is independent of the number of records returned .
    can some body calrify...
    Pls let me know if i have to add more details.

    i have a dedicated 9.2.0.7 on hp.
    my sga is 800M
    sharedpool is 208 M
    and the size of package pinned is
    262490 + 44970 of shrable memory (v$db_object_cache)

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    basically your shared pool is too small.

    if you pin that package then another object will be kicked out of the pool at some point.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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