A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database.
Database Instance Structure
When an instance is started, Oracle Database allocates a memory area called the system global area (SGA) and starts one or more background processes. The SGA serves various purposes, including the following:
- Maintaining internal data structures that are accessed by many processes and threads concurrently
- Caching data blocks read from disk
- Buffering redo data before writing it to the online redo log files
- Storing SQL execution plans
The SGA is shared by the Oracle processes, which include server processes and background processes, running on a single computer. The way in which Oracle processes are associated with the SGA varies according to operating system.
A database instance includes background processes. Server processes, and the process memory allocated in these processes, also exist in the instance. The instance continues to function when server processes terminate.
Instance and Database Startup Sequence
SQL> STARTUP ORACLE instance started. Total System Global Area 468729856 bytes Fixed Size 1333556 bytes Variable Size 440403660 bytes Database Buffers 16777216 bytes Redo Buffers 10215424 bytes Database mounted. Database opened.
https://docs.oracle.com/database/121/NTDBI/intro.htm#NTDBI2641
Oracle Instance:
Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database’s data efficiently and serve the one or multiple users of the database.
A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance’s SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.
An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.
The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance’s SGA, and several processes write to the SGA during execution of Oracle.
The SGA contains the following data structures:
- Database buffer cache
- Redo log buffer
- Shared pool
- Java pool
- Large pool (optional)
- Streams pool
- Data dictionary cache
- Other miscellaneous information
Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.
If the system uses shared server architecture, then the request and response queues and some contents of the PGA are in the SGA
In general, the SGA consists of the following sub-components, as can be verified by querying the V$SGAINFO:
SELECT * FROM v$sgainfo;
The common components are:
* Data buffer cache – cache data and index blocks for faster access.
* Shared pool – cache parsed SQL and PL/SQL statements.
* Dictionary Cache – information about data dictionary objects.
* Redo Log Buffer – committed transactions that are not yet written to the redo log files.
* JAVA pool – caching parsed Java programs.
* Streams pool – cache Oracle Streams objects.
* Large pool – used for backups, UGAs, etc.
SQL> SHOW SGA
Total System Global Area 638670568 bytes
Fixed Size 456424 bytes
Variable Size 503316480 bytes
Database Buffers 134217728 bytes
Redo Buffers 679936 bytes
SQL> SELECT * FROM v$sga;
NAME VALUE
——————– ———-
Fixed Size 456424
Variable Size 503316480
Database Buffers 134217728
Redo Buffers 679936
The size of the SGA is controlled by the DB_CACHE_SIZE parameter.
PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc.
[edit] Auto tuning
PGA areas can be sized manually by setting parameters like hash_area_size, sort_area_size etc.
To allow Oracle to auto tune the PGA areas, set the WORKAREA_SIZE_POLICY parameter to AUTO and the PGA_AGGREGATE_TARGET to the size of memory that can be used for PGA. This feature was introduced in Oracle 9i.
PGA usage statistics:
select * from v$pgastat;
Determine a good setting for pga_aggregate_target:
select * from v$pga_target_advice order by pga_target_for_estimate;
Show the maximum PGA usage per process:
select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;
Sources:
https://docs.oracle.com/cd/B19306_01/server.102/b14220/startup.htm
https://docs.oracle.com/cd/B19306_01/server.102/b14220/memory.htm
devopsbyoli.wordpress.com 