| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic SGA and pinned
Hi Howard
> There may be subtleties about the way Solaris uses memory that I am
unaware
> of, but as far as I know, if you set SGA_MAX_SIZE on any platform to a
given
> value, then that amount of RAM is immediately 'stolen' from the operating
> system and allocated to Oracle's own exclusive use. That large chunks of
> that shared memory segment are not *actually* used, because your
> shared_pool_size or db_cache_size are set to low amounts is irrelevant:
the
> large shared memory segment has nevertheless been allocated to Oracle's
use
> from the total pool of available physical RAM, and hence that RAM is not
> available for any other programs running on that server to make use of.
It really depends on the OS. A good example is Linux.... here an example taken from my SLES8-SP3 laptop (notice that in the output I just added some NL for easier reading, otherwise is a 1:1 copy/paste from my terminal):
linux:oracle:A1010 /u00/app/oracle> free -m
total used free shared buffers cached
Mem: 1008 995 13 0 10 646
-/+ buffers/cache: 338 669
Swap: 501 186 315
linux:oracle:A1010 /u00/app/oracle> ps -ef | grep dbw
oracle 7532 1 0 Aug06 ? 00:00:43 ora_dbw0_A1010 oracle 7180 1 0 10:02 ? 00:00:00 asm_dbw0_+ASM oracle 11874 1 0 11:53 ? 00:00:00 ora_dbw0_A901 oracle 11988 1 0 11:54 ? 00:00:01 ora_dbw0_A920 oracle 12114 1 0 11:58 ? 00:00:00 ora_dbw0_A817
linux:oracle:A1010 /u00/app/oracle> ipcs -m
linux:oracle:A1010 /u00/app/oracle> a817 linux:oracle:A817 /u00/app/oracle> sq
SQL*Plus: Release 8.1.7.0.0 - Production on Sun Aug 8 12:03:16 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> show sga
Total System Global Area 68944032 bytes
Fixed Size 73888 bytes Variable Size 51036160 bytes Database Buffers 16777216 bytes Redo Buffers 1056768 bytes
SQL> select nvl(pool,name), sum(bytes) from v$sgastat group by rollup(nvl(pool,name));
NVL(POOL,NAME) SUM(BYTES)
-------------------------- ----------
db_block_buffers 16777216
fixed_sga 73888
java pool 16003072
log_buffer 1048576
shared pool 35015408
68918160
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
linux:oracle:A817 /u00/app/oracle> a901
linux:oracle:A901 /u00/app/oracle> sq
SQL*Plus: Release 9.0.1.4.0 - Production on Sun Aug 8 12:03:31 2004
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.4.0 - Production
With the Partitioning option
JServer Release 9.0.1.4.0 - Production
SQL> show sga
Total System Global Area 538209912 bytes
Fixed Size 282232 bytes Variable Size 520093696 bytes Database Buffers 16777216 bytes Redo Buffers 1056768 bytes
SQL> show parameter sga_max_size
NAME TYPE VALUE ------------------------------------ ----------- --------------------------- --- sga_max_size big integer 538209912
SQL> select nvl(pool,name), sum(bytes) from v$sgastat group by rollup(nvl(pool,name));
NVL(POOL,NAME) SUM(BYTES)
-------------------------- ----------
buffer_cache 16777216
fixed_sga 282232
java pool 16777216
log_buffer 1048576
shared pool 62914560
97799800
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.4.0 - Production
With the Partitioning option
JServer Release 9.0.1.4.0 - Production
linux:oracle:A901 /u00/app/oracle> a920
linux:oracle:A920 /u00/app/oracle> sq
SQL*Plus: Release 9.2.0.5.0 - Production on Sun Aug 8 12:03:47 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.5.0 - Production
SQL> show sga
Total System Global Area 538516888 bytes
Fixed Size 454040 bytes Variable Size 461373440 bytes Database Buffers 75497472 bytes Redo Buffers 1191936 bytes
SQL> show parameter sga_max_size
NAME TYPE VALUE ------------------------------------ ----------- --------------------------- --- sga_max_size big integer 538516888
SQL> select nvl(pool,name), sum(bytes) from v$sgastat group by rollup(nvl(pool,name));
NVL(POOL,NAME) SUM(BYTES)
-------------------------- ----------
buffer_cache 75497472
fixed_sga 454040
java pool 33554432
large pool 4194304
log_buffer 1180672
shared pool 83886080
198767000
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining
options
JServer Release 9.2.0.5.0 - Production
linux:oracle:A920 /u00/app/oracle> a1010 linux:oracle:A1010 /u00/app/oracle> sq
SQL*Plus: Release 10.1.0.2.0 - Production on Sun Aug 8 12:04:08 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring
Engine options
SQL> show sga
Total System Global Area 536870912 bytes
Fixed Size 779724 bytes Variable Size 488905268 bytes Database Buffers 46137344 bytes Redo Buffers 1048576 bytesSQL> show parameter sga_max_size
NAME TYPE VALUE ------------------------------------ ----------- --------------------------- --- sga_max_size big integer 512M
SQL> select nvl(pool,name), sum(bytes) from v$sgastat group by rollup(nvl(pool,name));
NVL(POOL,NAME) SUM(BYTES)
-------------------------- ----------
buffer_cache 46137344
fixed_sga 779724
java pool 25165824
large pool 4194304
log_buffer 1048576
shared pool 88080852
165406624
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.1.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring
Engine options
linux:oracle:A1010 /u00/app/oracle> +asm linux:oracle:+ASM /u00/app/oracle> sq
SQL*Plus: Release 10.1.0.2.0 - Production on Sun Aug 8 12:04:23 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring
Engine options
SQL> show sga
Total System Global Area 100663296 bytes
Fixed Size 777616 bytes Variable Size 99885680 bytes Database Buffers 0 bytes Redo Buffers 0 bytesSQL> show parameter sga_max_size
NAME TYPE VALUE ------------------------------------ ----------- --------------------------- --- sga_max_size big integer 96M
SQL> select nvl(pool,name), sum(bytes) from v$sgastat group by rollup(nvl(pool,name));
NVL(POOL,NAME) SUM(BYTES)
-------------------------- ----------
fixed_sga 777616
java pool 25165824
large pool 12582912
shared pool 33554432
72080784
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.1.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring
Engine options
Chris
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 |
 |