shared_pool_reserved_size usage

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

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]

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

Reduce shared_pool_size and then shared_pool_reserved_size and see the impact.

Hopefully this will help.

Quote:

---

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.

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.