Oracle instance consists of SGA and oracle processes. Oracle processes can be divided into server processes and background processes. Oracle database uses background processes to perform overall database operations, for example, database writer process writes dirty buffer from database buffer cache to datafiles. Oracle creates one or more Server Processes for users connected to database. Each oracle process is assigned a memory area to contain data and control information. This memory area is called Program Global Area (PGA).
The Program Global Area (PGA) is a private memory region that contains the data and control information for a server process. Only a server process can access the PGA. Oracle Database reads and writes information in the PGA on behalf of the server process. it will allocate depend upon connection type :
Dedicated Server environment – the Private SQL Area is located in the Program Global Area (PGA).
Shared Server environment – the Private SQL Area is located in the System Global Area (SGA).
A PGA is:
Contents of PGA:-
Oracle database creates server processes to mange requests of user process. All user process issues SQL statements, it is the responsibility of Server process to execute these statements and return results to user process. Each server process has one PGA. PGA is memory where server process executes statements and stores information.
PGA consists of:
Private SQL Area: Contains data such as bind information and run-time memory structures. It contains Persistent Area which contains bind information and is freed only when the cursor is closed and Run time Area which is created as the first step of an execute request. This area is freed only when the statement has been executed. The number of Private SQL areas that can be allocated to a user process depends on the OPEN_CURSORS initialization parameter. The location of a private SQL area depends on the type of connection established for a session. If a session is connected through a dedicated server, private SQL areas are located in the server process's PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA.
Session Memory: Consists of memory allocated to hold a session’s variable and other info related to the session information for example session variables and logon information
SQL Work Areas: Used for memory intensive operations such as: Sort-based operators (order by, group-by, rollup, window function) ,Hash-join, Bitmap merge, Bitmap Create
The Program Global Area (PGA) is a private memory region that contains the data and control information for a server process. Only a server process can access the PGA. Oracle Database reads and writes information in the PGA on behalf of the server process. it will allocate depend upon connection type :
Dedicated Server environment – the Private SQL Area is located in the Program Global Area (PGA).
Shared Server environment – the Private SQL Area is located in the System Global Area (SGA).
A PGA is:
- a non-shared memory region that contains data and control information exclusively for use by an Oracle process.
- One PGA exists for each Server Process and each Background Process. It stores data and control information for a single Server Process or a single Background Process.
- PGA is the memory reserved for each user process connecting to an Oracle Database and is allocated when a process is created and deallocated when a process is terminated.
- This is NOT a shared part of memory – one PGA to each process only.
- The collection of individual PGAs is the total instance PGA, or instance PGA.
Contents of PGA:-
Oracle database creates server processes to mange requests of user process. All user process issues SQL statements, it is the responsibility of Server process to execute these statements and return results to user process. Each server process has one PGA. PGA is memory where server process executes statements and stores information.
PGA consists of:
- Private SQL Area
- session memory
- SQL work Areas
Private SQL Area: Contains data such as bind information and run-time memory structures. It contains Persistent Area which contains bind information and is freed only when the cursor is closed and Run time Area which is created as the first step of an execute request. This area is freed only when the statement has been executed. The number of Private SQL areas that can be allocated to a user process depends on the OPEN_CURSORS initialization parameter. The location of a private SQL area depends on the type of connection established for a session. If a session is connected through a dedicated server, private SQL areas are located in the server process's PGA. However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA.
Session Memory: Consists of memory allocated to hold a session’s variable and other info related to the session information for example session variables and logon information
SQL Work Areas: Used for memory intensive operations such as: Sort-based operators (order by, group-by, rollup, window function) ,Hash-join, Bitmap merge, Bitmap Create
No comments:
Post a Comment