Oracle 8i eats memory !!!

From: Barry <odbc_at_mailexcite.com>
Date: 21 May 1999 12:02:23 +1000
Message-ID: <01bea32d$cfb739e0$ee2bf283@dba>

The scene 8.0.5 and 8.1.5 (8i ) on Solaris 2.6

Check this out

Oracle SID1

Oracle Server Manager Release 3.0.5.0.0 - Production

Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.0.0 - Production

SVRMGR> connect thedba
Connected.
SVRMGR> show sga

Total System Global Area                        5185040 bytes
Fixed Size                                        48656 bytes
Variable Size                                   4235264 bytes
Database Buffers                                 819200 bytes
Redo Buffers                                      81920 bytes

SVRMGR> ORacle SID2 clone of SID1 but running 8i / 8.1.5

Oracle Server Manager Release 3.1.5.0.0 - Production

Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SVRMGR> connect thedba
Connected.
SVRMGR> show sga

Total System Global Area                        5496208 bytes
Fixed Size                                        64912 bytes
Variable Size                                   4530176 bytes
Database Buffers                                 819200 bytes
Redo Buffers                                      81920 bytes

Now compare the shared memory segments obtained

IPC status from <running system> as of Fri May 21 08:58:56 1999 T ID KEY MODE OWNER GROUP QBYTES Message Queues:
T ID KEY MODE OWNER GROUP SEGSZ Shared Memory:
m 1600 0xd67f76c8 --rw-r--- oracle dba 26238976 m 1501 0x7060fa08 --rw-r--- oracle dba 6217728

But wait ! Compare the number of pages used by the background processes

8 S oracle 20906 1 0 41 20 625128a0 9118 620a456c 16:04:24 ?   0:01 ora_smon_SID1
8 S oracle 20898 1 0 41 20 657c74f0 9186 620a452c 16:04:23 ?    0:00 ora_pmon_SID1
8 S oracle 21118 20352 0 51 20 62bcb4a0 108 6225efd8 16:24:16 pts/1    0:00 grep SID
8 S oracle 4274 1 0 41 20 65744090 3327 620a408c May 19 ?    0:00 ora_reco_SID2
8 S oracle 4272 1 0 41 20 62a4af98 3323 620a407c May 19 ?    0:00 ora_smon_SID2
8 S oracle 4266 1 0 40 20 62bccfa0 3475 620a404c May 19 ?    0:00 ora_dbw0_SID2
8 S oracle 4270 1 0 41 20 657fbbb8 3464 620a406c May 19 ?   19:40 ora_ckpt_SID2
8 S oracle 4268 1 0 40 20 6577f4d8 3469 620a405c May 19 ?    0:00 ora_lgwr_SID2
8 S oracle 4264 1 0 40 20 62d3c228 3596 620a403c May 19 ?    0:00 ora_pmon_SID2
8 S oracle 20900 1 0 41 20 657c0fe8 9114 620a453c 16:04:23 ?    0:00 ora_dbw0_SID1
8 S oracle 20908 1 0 41 20 62f5a078 9114 620a457c 16:04:24 ?    0:00 ora_reco_SID1
8 S oracle 20904 1 0 41 20 62bcc220 9108 620a455c 16:04:24 ?    0:01 ora_ckpt_SID1
8 S oracle 20902 1 0 41 20 629bc710 9112 620a454c 16:04:24 ?    0:00 ora_lgwr_SID1

To sum up, a 8.0.5 DB with a 6MB SGA acquires a shared memory segment about the same size. convert it to 8.1.5 and it will grab around 26Mb

Lets see, a small development server, 6 databases with small 5 to 6 MB SGAs now grab an EXTRA 20Mb each - 6 x 20MB = 120MB ! Watch your development box crash and burn !

I know of places with over 10 databases so there goes 200MB !

Anybody else found this ...

Oracle has let me done VERY BADLY .... Support are stumped... Received on Thu May 20 1999 - 21:02:23 CDT