Memory management for Oracle 11G R2

Basic points of knowledge

Basic memory structure: Sga+pga+uga+software code area (software region)

Learn about the components of SGA memory and some common features

Learn about Oracle 11G automatic memory management capabilities

Specific introduction

An Oracle DB instance is composed of some columns of the system global zone and the background process.

The way a client accesses a database is the client process---->program Global is----->database buffer cache While rewriting a copy of the redo log buffer

How Oracle Memory Management works: Automatic memory management and manual memory management, this article mainly explains the automatic memory management (Automatic management AMM)

1. Oracle 11G boot automatic memory management function

For Oracle 11G, enabling the memory auto-management feature ensures that the Memory_target parameter is not 0. For creating a database through DBCA, install by default, the automatic memory management feature is started by default, and the Memory_target parameter size is 40% of the total memory size

2. Understanding the SGA Key components

More important SGA Components

2 Database Cache

2 Redo Log Cache

2 shared pools

2 large pools

2 Java Pool

2 Stream Pool

2 fixed SGA

The Database Cache pool , also known as the buffer pool, is a memory area that holds a copy of the backup read from the data file. The buffer pool is the primary memory address of the current or most recently used block of data that is stored by the buffer Manager in the temporary cache. All users currently connected to the DB instance share this buffer pool.

The purpose of using a database buffer pool is to:

? Optimization of physical I/O

? Maintain frequent access to the buffer database, reducing block operations to disk, thus increasing access speed

Some terms in the database cache pool:

Buffer status (Buffer State)

Unused: no use of space

Clean: Previously used space, marked as clean, can be used again, allowing overrides

Dirty: Data that needs to be written to disk

Buffer Mode

Current mode

Consistent Mode

Logical I/O is also called buffer I/O, which is read I/O directly from the cache

physical I/O, read I/O from Physical disk

BufferWrite: Dbwn data is written to disk during the process cycle, and the condition of writing to disk is

The server process cannot find the free cache in order to read a new chunk into the cache

Buffer Reads

Buffer Cache Hit ration (Cache Hit rate): The ratio of the data block read in the cache to the total block of data (including data blocks read from cache to disk)

Buffers and full table scan

Redo Log Cache : The redo log cache is a cyclic cache of the SGA region that holds a redo entity (such as an SQL statement) that alters the database. Rebuilding entities including rebuilds, redo DML/DDL operations

Shared Pool ): Shared pool caches various types of program data, such as PL/SQL statements, system parameters and data dictionary information

Large Pool : It is an optional memory allocation area, which is an auxiliary cache area.

Java Pool: He's a memory area. Holds all sessions on Jave code, that is, a memory area that manages Java connections

Stream Pool : An area of memory for stream connection assignments

Fixed SGA: an internally reserved area that describes the database and instance state information, and so on.

Application examples

1. Check whether the Oracle Memory Auto-management feature is enabled

Memory_target is not 0, enable this memory auto-management feature

sql> Show parameter Memory

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address integer 0

memory_max_target Big integer 1232M

memory_target Big integer 1232M

shared_memory_address integer 0

2. Modify the Memory_target size

For example, if we add more memory, the Oracle database does not automatically increase the memory_target size, so it needs to be modified manually. Understanding Memory_target is an automatically updated parameter that can take effect without a restart, and the Memory_max_target parameter needs to be restarted to take effect. Memory_max_target follows the Memory_target parameter.

******************************************************************************

Sql> alter system set memory_target=1230m #默认scope =both, so no write

2;

System altered.

Sql> Show parameter memory

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Hi_shared_memory_address integer 0

Memory_max_target Big Integer 1248M

Memory_target Big Integer 1232M

Shared_memory_address integer 0

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sql> Startup

ORACLE instance started.

Total System Global area 1286066176 bytes

Fixed Size 2254864 bytes

Variable Size 754976752 bytes

Database buffers 520093696 bytes

Redo buffers 8740864 bytes

Database mounted.

Database opened.

Sql> Show parameter memory;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Hi_shared_memory_address integer 0

Memory_max_target Big Integer 1232M

Memory_target Big Integer 1232M

Shared_memory_address integer 0

Sql>

*******************************************************************************

3. View the SGA size allocation

When starting the database and viewing the V$SGA view, you can view the size of the memory allocated by the database buffers and redo log buffers.

sql> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sql> Startup

ORACLE instance started.

Total System Global area 1286066176 bytes

Fixed Size 2254864 bytes

Variable Size 805308400 bytes

Database buffers 469762048 bytes

Redo buffers 8740864 bytes

Database mounted.

Database opened.

Sql> select * from V$SGA;

NAME VALUE

-------------------- ----------

Fixed Size 2254864

Variable Size 805308400

Database buffers 469762048

Redo buffers 8740864

Sql>

4. View V$sgastat and V$pgastat information

***************************************************************************

sql> Select Pool, sum (Bytes) as pool_size from V$sgastat Group by pool;

POOL pool_size

------------ ----------

531089424

Java Pool 16777216

Shared Pool 201326592

Large Pool 16777216

Sql> select * from V$pgastat;

NAME VALUE UNIT

------------------------------ ---------- ------------

Aggregate PGA Target parameter 520093696 bytes

Aggregate PGA Auto target 436313088 bytes

Global Memory bound 104017920 bytes

Total PGA inuse 35298304 bytes

Total PGA Allocated 53518336 bytes

Maximum PGA allocated 54173696 bytes

Total freeable PGA Memory 12845056 bytes

Process Count 28

Max processes Count 28

PGA memory freed back to OS 9043968 bytes

Total PGA used for auto Workar 0 bytes

NAME VALUE UNIT

------------------------------ ---------- ------------

EAs

Maximum PGA used for auto work 0 bytes

Areas

Total PGA Used-manual work 0 bytes

Areas

Maximum PGA used for manual wo 0 bytes

Rkareas

NAME VALUE UNIT

------------------------------ ---------- ------------

Over allocation Count 0

Bytes processed 12819456 bytes

Extra bytes Read/written 0 bytes

Cache hit Percentage percent

Recompute count (total) 309

Rows selected.

Sql>

*******************************************************************************

Memory management for Oracle 11G R2