Detailed description of Oracle memory Parameter Optimization Technology

Preface

Recent company Technologies,R & D personnel are asking me how to set memory parameters to be optimized.OraclePerformance,So I took the time to sort out this document.,For reference.

Purpose

You want to organize this document,Make everyoneOracleA comprehensive understanding of the Memory Structure,And flexible application in actual work,EnableOracleThe memory performance reaches the optimal configuration, improving the applicationProgramResponse speed and reasonable memory usage.

Content

Instance Structure

OracleInstance=Memory Structure+Process Structure

OracleThe instance startup process is actuallyOracleThe value of the memory parameter settings is loaded into the memory, and the corresponding background process is started to perform related service processes.

Process Structure

OracleProcess=Server process+User Process

Several important background processes:

Dbwr: Data Writing Process.

Lgwr:Log Writing Process.

Arch:Archiving Process.

Ckpt:Checkpoint Process(Log switching; after the previous checkpoint, it exceeds the specified time; the predefined log block is written to the disk; the routine is closed,DBAForce generate,TablespaceOffline)

Lckn(0-9): Blocks processes..

Dnnn:Scheduling process.

Memory Structure(What we will focus on)

Memory Structure= SGA(System global zone)+ PGA(Program global zone)

SGA: Memory zone used to store database information. This information is shared by database processes. It containsOracleServer data and control information,It is inOracleThe actual memory of the computer where the server resides is allocated. If the actual memory is insufficient, write it to the virtual memory.

 

We focus on settingSGA, TheoreticallySGAAvailableOSSystem physical memory1/2--1/3

Principles:SGA + PGA + OSMemory usage<Total physicalRam

SGA = (db_block_buffers * blocksize) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1 MB

1,SGASystem global Zone.(Including the following five zones)

A, Data Buffer:(Db_block_buffers) Stores the data read from the disk data file.

Size: Db_block_buffers * db_block_size

Oracle9iSet the data buffer:Db_cache_size

  Principles:SGAMainly set the object, generally available memory40%.

B, Sharing pool:(Shared_pool_size):Data dictionary,SQLBuffer,PL/SQLSyntax analysis.Increase the availability.

Principles:SGAMainly set the object, generally available memory10%

C, Log Buffer:(Log_bufferStore the modification information of the database..

Principles:128 K ---- 1 mMust not be too large

D,JavaPool (Java_pool_size) Is mainly usedJavaLanguage Development.

Principle: if you do not useJavaIn principle, it cannot be smaller20 m,30 mUsually enough.
E, Large pool (Large_pool_size)If you do not setMTSUsed for database backup and recovery ManagerRMAN.

Principle: if you do not useMTS, 5 ---- 10 mMust not be too large

SGA =. db_block_buffers * db_block_size + shared_pool_size + log_buffer + java_pool + size + large_pool_size

 Principles: Available memory55-58%You can..

2,PGAProgram global Zone

PGA: Contains data and control information of a single server process or a single background process, shared with several processesSGAOppositePGAIs the region used by only one process,PGAAllocated during Process Creation and recycled upon Process Termination.

A,Sort_area_sizeMemory Used for sorting

B,Hash_area_sizeUsed for hash join and bitmap Index

These two parameters are notMTSAll belongPGA, Does not belongSGA,Is for eachSessionAssigned separately, exceptOS + SGA,Be sure to consider the two parts
Principles:OSMemory usage+ SGA +Number of concurrent processes* (Sort_area_size + hash_ara_size + 2 m) <0.7 *Total memory

Instance Configuration

I. Physical memory size

Ii. Estimated memory used by the operating system

Iii. Whether the database uses a file system or a bare Device

4. Number of concurrent connections

V. application isOLTPType orOLAPType

The basic principle is,Db_block_bufferUsually it can be as big as possible,Shared_pool_sizeModerate,Log_bufferUsually several hundredKTo1 mThat's all.

AIf512 M RamSingleCPUDb_block_sizeYes8192 bytes
SGA = 0.55*512 M = 280 mLeft and right

SuggestionsShared_pool_size = 50 m, db_block_buffer * db_block_size = 200 m
Details: Shared_pool_size = 52428800 #50 m

Db_block_buffering = 25600#200 m

Log_buffer = 131072#128 K (128 K * CPUNumber)

Large_pool_size = 7864320#7.5 m

Java _ pool_size = 20971520#20 m

Sort_area_size = 524288#512 K (65 k -- 2 m)

Sort_area_retained_size = 524288# MTSHourSort_area_retained_size = sort_area_size

BIf1g RAMSingleCPUDb_block_sizeYes8192 bytes
SGA = 0.55*1024 M = 563 mLeft and right
Suggestions Shared_pool_size = 100 m, db_block_buffer * db_block_size = 400 m
Details: Shared_pool_size = 104857600#100 m

Db_block_buffering = 51200#400 m

Log_buffer = 131072#128 K (128 K * CPUNumber)

Large_pool_size = 15728640#15 m

Java _ pool_size = 20971520#20 m

Sort_area_size = 524288#512 K (65 k -- 2 m)

Sort_area_retained_size = 524288# MTSHourSort_area_retained_size = sort_area_size

 

CIf2GSingleCPUDb_block_sizeYes8192 bytes

SGA = 0.55*2048 M = 1126.4 mLeft and right
Suggestions Shared_pool_size = 200 m, db_block_buffer * db_block_size = 800 m

Details: Shared_pool_size = 209715200#200 m

Db_block_buffering = 103192#800 m

Log_buffer = 131072#128 K (128 K * CPUNumber)

Large_pool_size = 31457280#30 m

Java _ pool_size = 20971520#20 m

Sort_area_size = 524288#512 K (65 k -- 2 m)

Sort_area_retained_size = 524288# MTSHourSort_area_retained_size = sort_area_size

 

 

Assume that64-bit Oracle

Memory4G

Shared_pool_size = 200 m, data buffer = 2.5g

 

Memory8 GB

Shared_pool_size = 300 m, data buffer = 5g

 

Memory12g

Shared_pool_size = 300m-----800m, data buffer = 8g

Parameter Change Method

Oracle8i:

Mainly through modificationOracleStart the parameter file for related configuration

Parameter file location:

D: \ oracle \ admin \ db_name \ pfile \ init. ora

Modify the preceding parameter values.

Oracle9i:

Two methods: the first is to modifyOracleAfter the parameter file is started, use this parameter file to create a server parameter file

The second method is to run directly.OracleModify the command.

SQL> alter system set db_cache_size = 200 m scope = spfile;

SQL> alter system set shared_pool_size = 50 m scope = spfile;