오라클 데이터베이스 11g~21c까지의 PGA 구조 정리

오라클 데이터베이스 메모리 관리의 핵심 PGA 구조에 변화가 생겼다.

18c부터 PGA에 있던 "Hash Area"와 "Bitmap Merge Area", "Stack Space"가 Shared Server Environment가 SGA의 Large POOL로 이동한다.

아래 도표와 설명으로 자세히 살펴보자

 

오라클을 전문적으로 다루는 사람도 잘 알고 있다고 그냥 넘어가지 말고, 이번 기회에 다시 한번 정독하도록 하자. 

의외로 모르고 있던 부분이 있을 수 있다.

 

▶ 오라클 데이터베이스 11g, 12c의 PGA 구조

오라클 데이터베이스 11g, 12C의 PGA 구조

In a dedicated server session, the UGA consists of the following components:

• SQL work areas: These are private allocations of PGA memory used for memory-intensive SQL operations. The sort area is used by functions that order data, such as ORDER BY and GROUP BY. The hash area is used to perform hash joins of tables. The bitmap merge area is used to merge data retrieved from scans of multiple bitmap indexes.
• Session memory: This user session data storage area is allocated for session variables, such as logon information, and other information required by a database session. The OLAP pool manages OLAP data pages, which are equivalent to data blocks.
• Private SQL area: This area holds information about a parsed SQL statement and other session-specific information for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas. Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA. The persistent area contains bind variable values. The run-time area contains query execution state information. A cursor is a name or handle to a specific area in the private SQL area. You can think of a cursor as a pointer on the client side and as a state on the server side. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.

 

▶ 오라클 데이터베이스 18c, 19c, 21c의 PGA 구조

오라클 데이터베이스 18C, 19C, 20C, 21C의 PGA 구조

• In a shared server environment, multiple client users share the server process. The UGA is moved into the large pool, leaving the PGA with only stack space, hash area, and bitmap merge area.

 

• In a dedicated server session, the PGA consists of the following components:
  • SQL work areas: The sort area is used by functions that order data, such as ORDER BY and GROUP BY.
  • Session memory: This user session data storage area is allocated for session variables, such as logon information, and other information required by a database session. The OLAP pool manages OLAP data pages, which are equivalent to data blocks.
  • Private SQL area: This area holds information about a parsed SQL statement and other session-specific information for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas. Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA. The persistent area contains bind variable values. The run-time area contains query execution state information. A cursor is a name or handle to a specific area in the private SQL area. You can think of a cursor as a pointer on the client side and as a state on the server side. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.
  • Stack space: Stack space is memory allocated to hold session variables and arrays.
  • Hash area: This area is used to perform hash joins of tables.
  • Bitmap merge area: This area is used to merge data retrieved from scans of multiple bitmap indexes.

 

끝.