shared_pool_reserved_size usage
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: shared_pool_reserved_size usage

  1. #1
    Join Date
    Jul 2001
    Posts
    108
    Hello,

    Is there a way to get the hwm and maximum used and usage percentage for shared_pool_reserved_size usage?

    How to find out the base x$ tables for V$shared_pool_reserved?

    Thanks,
    Nikee



  2. #2
    Join Date
    Oct 2001
    Posts
    122
    Can't find underneath tables but if you want to know
    how to use it...............

    Using SHARED_POOL_RESERVED_SIZE
    The size of the reserved list, and the minimum size of the objects that can be allocated from the reserved list, can be controlled by the initialization parameter SHARED_POOL_RESERVED_SIZE. Begin this tuning only after performing all other shared pool tuning.

    The default value for SHARED_POOL_RESERVED_SIZE is 5% of the SHARED_POOL_SIZE. This means that, by default, the reserved list is always configured.

    If SHARED_POOL_RESERVED_SIZE > 1/2 SHARED_POOL_SIZE, then Oracle signals an error. Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. The amount of operating system memory, however, may constrain the size of the shared pool. In general, set SHARED_POOL_RESERVED_SIZE to 10% of SHARED_POOL_SIZE. For most systems, this value is sufficient if you have already tuned the shared pool. If you increase this value, then the database allows fewer allocations from the reserved list and requests more memory from the shared pool list.

    Statistics from the V$SHARED_POOL_RESERVED view help you tune these parameters. On a system with ample free memory to increase the size of the SGA, the goal is to have REQUEST_MISSES = 0. If the system is constrained for operating system memory, then the goal is to not have REQUEST_FAILURES or at least prevent this value from increasing.

    If you cannot achieve this, then increase the value for SHARED_POOL_RESERVED_SIZE. Also, increase the value for SHARED_POOL_SIZE by the same amount, because the reserved list is taken from the shared pool.



    SHARED_POOL_ RESERVED_SIZE Too Small
    The reserved pool is too small when the value for REQUEST_FAILURES is more than zero and increasing. To resolve this, increase the value for the SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE accordingly. The settings you select for these depend on your system's SGA size constraints.

    This option increases the amount of memory available on the reserved list without having an effect on users who do not allocate memory from the reserved list. As a second option, reduce the number of allocations allowed to use memory from the reserved list; however, doing so increases the normal shared pool, which may have an effect on other users on the system.

    SHARED_POOL_ RESERVED_SIZE Too Large
    Too much memory may have been allocated to the reserved list if:

    REQUEST_MISS = 0 or not increasing

    FREE_MEMORY = > 50% of SHARED_POOL_RESERVED_SIZE minimum

    If either of these is true, then decrease the value for SHARED_POOL_RESERVED_SIZE.



    [Edited by prafful on 10-18-2001 at 12:04 AM]

  3. #3
    Join Date
    Jul 2001
    Posts
    108
    Hello Prafful,

    In our system the SHARED_POOL_RESERVED_SIZE is 5% of the SHARED_POOL_SIZE.

    shared_pool_reserved_size ---> 31457280
    shared_pool_size ---> 629145600

    Select Free_space,avg_free_size,max_free_size,
    used_space,avg_used_size,max_used_size,request_misses,request_failures from V$SHARED_POOL_RESERVED;

    FREE_SPACE -------> 28,870,680
    AVG_FREE_SIZE ----> 45,681
    MAX_FREE_SIZE ---> 25,818,224
    USED_SPACE ------->
    AVG_USED_SIZE ---> 2,586,600
    MAX_USED_SIZE ---> 4,093
    REQUEST_MISSES --> 0
    REQUEST_FAILURES-> 0

    By looking at the above values approximately 28MB (FREE_SPACE) of the SGA is getting wasted.

    How can we avoid this?

    Thanks,
    Nikee


  4. #4
    Join Date
    Oct 2001
    Posts
    122

    Cool

    Reduce shared_pool_size and then shared_pool_reserved_size and see the impact.

    Hopefully this will help.

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by prafful
    Reduce shared_pool_size and then shared_pool_reserved_size and see the impact.

    Hopefully this will help.
    There should be correlation between these two values: shared_pool_size and then shared_pool_reserved_size.

    Oracle 6 and most Oracle 7 DBAs still might be not very clear about the meaning of shared_pool_reserved_size. In release 7.3, Oracle introduced paged PL/SQL. Since then, most of the memory chunks in the shared pool are less then (about) 5K. Thus it would not be very wise to search the shared pool free list and LRU lists for chunks more than 5K. Oracle does not do that any more, Oracle uses namely the shared_pool_reserved_size for those lrage chunks.

    There is a way to encrease 5000 bytes to a bit more (with _SHARED_POOL_RESERVED_MIN_ALLOC) but you should not do that.

    I personally keep a reatio of about 1:10 for shared_pool_size and then shared_pool_reserved_size. 1:20 is the default.


  6. #6
    Join Date
    Oct 2000
    Posts
    467
    Hi,
    If you have the privileges you can see the base tables.

    set autotrace on
    select * from v$shared_pool_reserve;

    This will give you the details of all /any of the base tables used for quering the v$ objects.
    Vinit

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