Memory Structures - Expert Oracle Database Architecture - page 151

■ This is the SGa information for the Oracle instance started with the initialization parameter file in the previous
example. We specified the SGa and pGa sizes ourselves in that parameter file. Therefore we are using automatic shared
memory management and automatic pGa memory management, but not the Oracle 11 g (and above) “automatic memory
management” setting, which would have sized and resized our pGa/SGa settings for us.

Note

In this example, I used automatic shared memory management and controlled the size of the SGA via the single
parameter SGA_TARGET . When my SGA size is under about 1GB, the granule is 4MB. When the SGA size is increased
to some threshold over 1GB (it will vary slightly from operating system to operating system and even from release to
release), I see an increased granule size. First we convert to using a stored parameter file to make altering the
SGA_TARGET easier:

SYS@ORA12CR1> create spfile from pfile;
File created.

SYS@ORA12CR1> startup force;
ORACLE instance started.

Total System Global Area 267227136 bytes
Fixed Size 2287336 bytes
Variable Size 180357400 bytes
Database Buffers 79691776 bytes
Redo Buffers 4890624 bytes
Database mounted.
Database opened.

■ If your instance is currently running, the STARTUP FORCE command will shut down the instance (abort mode)
and restart it.

Note

Then we modify the SGA_TARGET :

SYS@ORA12CR1> alter system set sga_target = 1512m scope=spfile;
System altered.

SYS@ORA12CR1> startup force
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size 2288824 bytes
Variable Size 402654024 bytes
Database Buffers 1174405120 bytes
Redo Buffers 7360512 bytes
Database mounted.
Database opened.

SYS@ORA12CR1> show parameter sga_target