"Go" Oracle Infrastructure cognitive--oracle Memory Architecture eight mesh 2017-12-15 20:31:27

Oracle's DB instance is a set of background processes and memory structures, and the memory structure consists of the system global area and the program global Zone.

#修改SGA和PGA的配置文件, usually in/. sgz_max_size=64m, sga_target=64m, pga_aggregate_target parameters in/oracle/admin/spfile/init.ora, or alter system set in Sqlplus Sga_max_size=64m Scope=spfile; After modification, restart the database and the service takes effect.

System global Area (SGA): A piece of storage area used by Oracle systems to hold system information, both user processes and Oracle background processes can use the SGA.

Sga:database buffer Cache (Database Cache area), Redolog buffer cache (redo log cache area), large shared pool (Java Pool,large pool), Shared pool (library cache[ Library buffers],data Dictionary cache[data Dictionary buffers]), fixed SGA (can not be set to modify, automatically created at the beginning of Oracle);

1. Data buffer Cache

In the data buffer area, where the most recently used data blocks of the Oracle system (that is, the user's high-speed buffer) are stored, when the data is written to the database, it is read and written in chunks, and when the data buffer is filled, the system automatically removes some data that is infrequently accessed. Oracle automatically reads from the disk if the data that the user is looking for is not in the data buffer zone. Oracle every query that executes must be called from disk to the existence of data DBSGA, and the database used to invoke the access data generally exists DBSGA. Long-term access to a code table exists in the persistent cache pool that persists in memory for a long time and does not release, frequently accessed large tables present in the regeneration cache pool.

The data cache area consists of three types:

(1). Dirty Zone (Dirty buffers): contains a block of data that has been changed and needs to be written back to the data file.

(2). Free buffers: No area containing any data and can be re-written, Oracle can write from the data file read block of storage information to the area.

(3). Reserved area (Pinned buffers): This zone contains areas that are being processed or explicitly reserved for future data access.

2. Redo Log buffers (Rado log buffer)

Any transaction (Transaction) must first be placed in the Redo log buffer (Redo log buffers) before logging to the Redo log (recovery work requires the use of online redo logs). The contents of this buffer are then periodically written by the log write process (LGWR) to the Redo log files (redolog file).

3. Shared Pool

A shared pool is a zone reserved for the SGA that is used to store such as SQL, PL-SQL stored procedures and packages, data dictionaries, locks, character set information, security attributes, and so on. A shared pool contains:

(1). Library cache: Shared SQL Zone (preserves SQL interpretation version), PL/Plsql (functions and procedures and packages that retain the function)

(2). Dictionary cache buffer (Dictionary cache): Data dictionary, check table name, column name, lock, character set information, security attributes, etc.

View sga*****

Through the Sqlplus tool, type: Show parameter SGA or select * from V$SGA;. sga=fixed size+variable size+database Buffers+redo buffers

View the SGA for Oracle

4. Large shared pool: In the SGA, the large pool is an optional buffer. It can be configured with administrative authority as needed. It can provide a large area for the object database backup and recovery operations.

Program Global: (PGA) is an area of memory used by Oracle, which can only be stored by one process at a time for data and control, for storing session variables and internal arrays, etc., in private service mode (dedicated server configuration), Each request that processes only one user process. Under Shared server configuration, a large number of users can share several service processes by reducing the number of service processes to make efficient use of system resources.

Example: A user accesses a session of a process with a service process.

View pga****

Show parameter PGA;

View the PGA for Oracle

"Go" Oracle Infrastructure cognitive--oracle Memory Architecture eight mesh 2017-12-15 20:31:27