Shared_Pool_Reserved_Size* parameter questions.

Hi all,

We are running Oracle 7.3 on NT 4.0.
In response to intermittent ORA-4031 errors I set the following parameters in our initORCL.ora file:

  shared_pool_size         = 40000000
  shared_pool_reserved_size= 5000000
  shared_pool_reserved_min_alloc= 10000

After running a few days with these parameters I then queried the v$shared_pool_reserved table with the following
(partial) results:

  FREE_SPACE = 3413892
  AVG_FREE_SIZE = 2864.0034
  FREE_COUNT = 304
  USED_SPACE = 1586108
  AVG_USED_SIZE = 1330.6275
  USED_COUNT = 888 Question 1)
FREE_COUNT(304) * AVG_FREE_SIZE(2864.0034) = 870657.0336 Why is this figure so much less than FREE_SPACE(3413892)?

Question 2)
USED_COUNT(888) * AVG_USED_SIZE(1330.6275) = 1181597.22 Why is this figure less than USED_SPACE(1586108) although not nearly as far off as the free space figure?

Question 3)
Why in the world is AVG_USED_SIZE(1330.6275) so much less than the value of the shared_pool_reserved_min_alloc parameter(10000)? To my way of thinking, the average used size would have to always be 10000 or larger since only objects of this size get allocated in the reserved area.
The whole point in setting the shared_pool_reserved* parameters was have larger objects allocated in the reserved area, but this doesn't seem to be the case. What gives?

I thought I had read enough about these parameters that I understood their use, but now I wonder. Can someone enlighten an OF like me?

TIA,
Dana Stockler
(with my DBA hat on)
Received on Mon Nov 02 1998 - 01:59:18 CST