Memory-related issues from "Expert Oracle 9i Administration" by Sam Alapati

From: alea <aleatory_at_hotmail.com>
Date: 10 Apr 2006 21:02:25 -0700
Message-ID: <1144728145.502797.232870@g10g2000cwb.googlegroups.com>

Hi all,

I would like to have a few advice on memory-related issues in Oracle. So far some of the figures I find seem to be contracdictory even though I
could be wrong.

According to Sam Alapati, the author of "Expert Oracle 9i Administration," he
says, "If you consistenly see a lot of free memory, obviously your shared pool
allocation is excessive and you can reduce it. On the other hand, if the free
memory is concistently hovering around 1MB to 5MB, it is an indication that
the shared pool memory is too low and you need to raise it." (p 903)

SQL> select bytes
  2 from v$sgastat
  3 where pool = 'shared pool'
  4 and name = 'free memory';

BYTES

---

12769368

Since I consistenly get more than 10MB of free memory, I consider the database
I manage is probably okay.

The contradictory figure, however, is the following.

SQL> l
  1 select to_number ( value ) shared_pool_size,

  2         sum_obj_size,
  3         sum_sql_size,
  4         sum_user_size,
  5         ( sum_obj_size + sum_sql_size + sum_user_size ) * 1.2

min_shared_pool
  6 from ( select sum ( sharable_mem ) sum_obj_size

  7             from v$db_object_cache ),
  8         ( select sum ( sharable_mem ) sum_sql_size
  9             from v$sqlarea ),
 10         ( select sum ( 250 * users_opening ) sum_user_size
 11             from v$sqlarea ),
 12         v$parameter

 13* where name = 'shared_pool_size'
SQL> / SHARED_POOL_SIZE SUM_OBJ_SIZE SUM_SQL_SIZE SUM_USER_SIZE MIN_SHARED_POOL

---------------- ------------ ------------ -------------
---------------
301989888    234997578    221440558        759500       548637163

According to Sam Alapati, the database I manage needs a minimum of about
548637163 (MIN_SHARED_POOL), but it actually has only 301989888
(SHARED_POOL_SIZE) .
(p 904) According to this specific explanation roughly 246MB more
memory is
needed.

I am really puzzled since I don't know which one to believe...

Many thanks in advance,

alea Received on Mon Apr 10 2006 - 23:02:25 CDT