Detailed description of Oracle memory Parameter Optimization Technology

Preface
Recently, the company's technology and R & D have been asking me about how to set memory parameters to optimize oracle performance. So I took the time to sort out this document for reference.
 
Purpose
 
We hope to organize this document to give you a comprehensive understanding of the oracle memory structure and use it flexibly in actual work, so that the oracle memory performance can reach the optimal configuration, improve application response speed and make reasonable memory usage.
 
Content
 
Instance Structure
 
Oracle instance = memory structure + Process Structure
 
When an oracle instance is started, the oracle memory parameter value is loaded into the memory, and the corresponding background process is started for the relevant service process.
 
Process Structure
 
Oracle process = 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, which is generated by DBA forcibly and the tablespace is offline)
 
LCKn (0-9): blocks the process.
 
Dnnn: scheduling process.
 
Memory Structure (which we will focus on)
 
Memory Structure = SGA (system global zone) + PGA (Program global zone)
 
SGA is the memory area used to store database information, which is shared by database processes. It contains data and control information of the Oracle server. It is allocated in the actual memory of the computer where the Oracle server resides. If the actual memory is insufficient, it can be written to the virtual memory.
 
We focus on SGA. In theory, SGA can account for 1/2 of the physical memory of the OS system-1/3.
 
Principle: memory usage by SGA + PGA + OS <total physical RAM
 
SGA = (db_block_buffers * blocksize) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1 MB
 
1. Global zone of the SGA system (including the following five zones)
 
A. Data Buffer: (db_block_buffers) stores data read from disk data files.
 
Size: db_block_buffers * db_block_size
 
Oracle9i sets the data buffer to Db_cache_size.
 
Principle: The main setting objects in SGA are generally 40% available memory.
 
B. Shared Pool: (shared_pool_size): Data Dictionary, SQL Buffer, and pl/SQL syntax analysis. This increases the availability.
 
Principle: The main setting object in SGA is generally available memory 10%
 
C. Log Buffer: (log_buffer) Stores database modification information.
 
Principle: 128 K-1 M, should not be too large
 
D. JAVA pool (Java_pool_size) is mainly used for JAVA development.
 
Principle: If java is not used, in principle, it cannot be less than 20 M. It is usually enough to give 30 M.
E. If MTS is not set for a large pool (Large_pool_size), it is mainly used for the database backup and recovery manager RMAN.
 
Principle: If MTS is not used, the value range is 5-10 MB.
 
SGA =. db_block_buffers * db_block_size + shared_pool_size + log_buffer + Java_pool + size + large_pool_size
 
Principle: reach 55-58% of available memory.
 
2. PGA program global Zone
 
PGA: contains the data and control information of a single server process or a single background process. The SGA shared with several processes is the opposite. PGA is only used by one process, PGA is allocated during Process Creation and recycled upon Process Termination.
 
A. Sort_area_size memory occupied by sorting
 
B. Hash_area_size is used for hash join and bitmap index.
 
These two parameters belong to PGA rather than SGA in non-MTS, and are allocated separately for each session. In addition to OS + SGA on our servers, we must consider these two parameters.
Principle: OS Memory + SGA + number of concurrent execution 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
 
5. Are applications of the OLTP or OLAP type?
 
The basic principle is that db_block_buffer can be as large as possible, shared_pool_size should be moderate, and log_buffer is usually as large as several hundred K to 1 m.
 
A. If the single CPU db_block_size of 512 m ram is 8192 bytes
SGA = 0.55*512 M = around 280M
 
Shared_pool_size = 50 M, db_block_buffer * db_block_size = 200 M
Details: shared_pool_size = 52428800 #50 M
 
Db_block_buffer = 25600 #200 M
 
Log_buffer = 131072 #128 k (128 K * Number of CPUs)
 
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 # sort_area_retained_size = sort_area_size during MTS
 
B. If 1G RAM has a single CPU db_block_size of 8192 bytes
SGA = 0.55*1024 M = around 563M
Shared_pool_size = 100 M, db_block_buffer * db_block_size = 400 M
Details: shared_pool_size = 104857600 #100 M
 
Db_block_buffer = 51200 #400 M
 
Log_buffer = 131072 #128 k (128 K * Number of CPUs)
 
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 # sort_area_retained_size = sort_area_size during MTS
 
 
C. If the size of a single 2g cpu db_block_size is 8192 bytes
 
SGA = 0.55*2048 M = around 1126.4M
Shared_pool_size = 200 M, db_block_buffer * db_block_size = 800 M
 
Details: shared_pool_size = 209715200 #200 M
 
Db_block_buffer = 103192 #800 M
 
Log_buffer = 131072 #128 k (128 K * Number of CPUs)
 
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 # sort_area_retained_size = sort_area_size during MTS
 
 
 
Assume that 64-bit ORACLE
 
4 GB memory
 
Shared_pool_size = 200 M, data buffer = 2.5G
 
 
8 GB memory
 
Shared_pool_size = 300 M, data buffer = 5G
 
 
12 GB memory
 
Shared_pool_size = 300M-----800M, data buffer = 8G
 
Parameter Change Method
 
Oracle8i:
 
You can modify the oracle startup parameter file to perform relevant configuration.
 
Parameter file location:
 
D: \ oracle \ admin \ DB_Name \ pfile \ init. ora
 
Modify the preceding parameter values.
 
Oracle9i:
 
Two methods: the first method is to modify the oracle startup parameter file and then create a server parameter file through this parameter file.
 
The second is to directly run the oracle modification command for modification.
 
SQL> alter system set db_cache_size = 200 M scope = spfile;
 
SQL> alter system set shared_pool_size = 50 M scope = spfile;

From fmhui879