Oracle System and server performance learning note

 

 

This week I learned about the Oracle architecture.

 

 

 

The Oracle system consists of two major parts: SGA (system global zone) and PGA (Program global zone ).

 

SGA mainly includes database buffer cache, shared pool, redo log buffer, large pool, and java pool. When an instance is started, the system allocates an SGA for it. The Database buffer cache mainly stores accessed or data to be stored in the disk. The shared pool stores compiled and parsed SQL statements, data dictionaries, and other information. The Redo log buffer stores information about the recovered files. The Large pool and java pool are optional. The Large pool is responsible for backup, parallel processing, and I/O operations, java pool can process stored procedures written in java.

 

PGA includes user process, server process, and background process, which are short processes of the user, processes that process the connection between the client and the server, and processes that run the core database. A user is allocated with a PGA connection.

 

The main indicators for evaluating server performance include response time, throughput, concurrency, transaction processing capability, File Cache hit rate, cpu load, and I/O read/write capability. Different server settings should be configured for different server processing conditions to make the server run better. The server can allocate all the memory remaining for running the basic software to the Oracle system, so as to make more reasonable use of existing resources.

 

When online transaction analysis (OLAP) is large on the server, a large amount of memory can be allocated to PGA. OLAP is mainly used to process one-time transactions with large data volumes, full table analysis, and sorting, A large amount of memory is required to process these transactions.

 

When there is a large number of online transaction processing (OLTP), a large amount of memory can be allocated to SGA. OLTP mainly processes a large number of small transactions and has high performance requirements. In addition to allocating a large amount of memory to the shared pool, the memory allocation in SGA can allocate most of the remaining memory to the db cache. The basic knowledge of SGA is as follows, storing recently used data in the db cache can improve the data access hit rate without reading it to the disk. The appropriate shared pool stores compiled SQL statements. When the same statements come in, repeated SQL parsing can be reduced. You only need to adjust the memory size of the remaining blocks and allocate them as needed.

 

From: astring