Oracle Memory Component Theory Chapter One

Goal

1.SGA structure

2.PGA structure

1.SGA

Shared Pool1), shared pool is an area of memory for parsing, compiling, and executing SQL, PL/s programs.
When executing a SELECT * from EMP statement, the SQL statement parsing, compile, build execution plan, run execution plan, etc., are completed in the shared pool;
If the SELECT * FROM EMP statement is executed again, the same SQL is found in the shared pool and, if present, the execution plan is run without compiling, generating an execution plan action step.
Therefore, it is very helpful to develop good coding habits to improve Oracle execution efficiency.    2), the shared pool consists of a library cache and a data dictionary cache. 3), the size of the shared pool directly affects the performance of the database.

Database Buffer Cach1), used to store data read from the disk data file, all users share.    2), the server process will read the data stored in the data buffer, when the subsequent requests need this data can be found in memory, no need to read from the disk, improve the read speed.    3), the size of the data buffer has a direct impact on the reading speed of the database.    4), the data in the data file on the hard disk, how to return to the user?    I, removed from the hard disk, directly back to the user, making it very inefficient to obtain data. II, remove from the hard disk, put in the data buffer, from the data buffer to return data to the user. The next time the same data is read, it is fetched directly from the data buffer and does not need to be fetched from the hard disk.

Redo Log Buffer1), all the modification information of the logging database, the log information is first generated in the log buffer.    2) log data is written to the log file by the background process when the logging data reaches a certain number of records.    3), relatively speaking, the log buffer has less impact on the performance of the database. eg, UPDATE emp SET empno=7935 WHERE empno=7934;
is first recorded in the journal Buffer,
Next some time (three cases; one, the log buffer record reaches 1M, every 3 seconds, the log buffer has been used one-third, to satisfy any one of them) write these records in the log buffer to the online log.

Large PoolThe memory space allocated for large background process operations mainly refers to backup recovery, large IO operations, parallel queries, and so on.

Stream PoolAn advanced replication technology is part of the memory space allocated for stream applications.

JAVA PoolThe memory space allocated for the Java application.

Buffer pool:
Keep Buffer PoolThis pool is used to preserve objects that may be reused in memory and to keep them in memory to reduce I/O operations.
By making the pool larger than the total size of the individual segments allocated to the pool, you can leave the buffer in this pool, which means that the buffer does not have to perform an expired processing. Can
Configuration by specifying the value of the Db_keep_cache_size parameter
Recycle Buffer PoolThis pool is used for blocks with very little chance of reuse in memory, and the size of the recycle pool is smaller than the total size of the individual segments allocated to the pool, which means that chunks that are read into the pool often need to perform outdated processing within the buffer.
Can be configured by specifying the value of the Db_recycle_cache_size parameterNK Buffer PoolThis pool is always present.    It is equivalent to a buffer cache that does not have an instance of the pool and the recycle pool, and can be configured with the Db_cache_size parameter.    Note: Memory in the retention pool or recycle pool is not a subset of the default buffer pool CREATE INDEX cust_idx ...        STORAGE (BUFFER _pool KEEP);        ALTER TABLE oe.customers STORAGE (Buffer_pool RECYCLE);        ALTER INDEX oe.cust_lname_ix STORAGE (BUFFER _pool KEEP); The Buffer_pool clause is used to define the default buffer pool for an object. It is part of the storage clause and is valid for create and alter tables, clusters, and index statements.        Blocks in objects that do not explicitly set the buffer pool will enter the default buffer pool. The syntax is: buffer_pool [KEEP | RECYCLE | DEFAULT].

2.PGA

The PGA is managed differently with shared connection mode and private connection mode. In most cases, private connection mode is used

  dedicated SQL zone  issues SQL Each session of the statement has a dedicated SQL area. 
 Each user who submits the same SQL statement also has its own private SQL area, which uses a shared SQL zone. In this way, many dedicated SQL zones can be associated with the same shared SQL zone. The location of the 
 Private SQL zone depends on the type of connection established for the session. 
 If the session is connected through a dedicated server, the dedicated SQL area is located in the PGA of the server process. 
 If the session is connected through a shared server, a portion of the dedicated SQL area will remain in the SGA  
 
  cursor and SQL zone  Oracle pro*c Application developers of programs or Oracle Call Interface (OCI) programs can explicitly open cursors or handles for specific private SQL areas and use them as named resources throughout the execution of the program. The 
 database also uses a shared SQL zone for recursive cursors that are implicitly issued for some SQL statements.  
 
  workspace  for complex queries (for example, Decision support queries), the majority of the PGA is dedicated to the workspaces allocated by the memory-intensive operators. 
 For example: sorted operators (order BY, GROUP by), Hash joins, bitmap merges, bitmap creation.     
 
  session memory  Session memory is the memory used to hold the session's variables (login information) and other information related to the conversation.    
 for a shared server, session memory is shared, not private.