"PGA 관리"의 두 판 사이의 차이

V$PGASTAT This view gives instance-level statistics on the PGA memory usage and the automatic PGA memory manager. For example:

SELECT * FROM V$PGASTAT;
The output of this query might look like the following:

NAME                                                          VALUE UNIT
-------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                             41156608 bytes
aggregate PGA auto target                                  21823488 bytes
global memory bound                                         2057216 bytes
total PGA inuse                                            16899072 bytes
total PGA allocated                                        35014656 bytes
maximum PGA allocated                                     136795136 bytes
total freeable PGA memory                                    524288 bytes
PGA memory freed back to OS                              1713242112 bytes
total PGA used for auto workareas                                 0 bytes
maximum PGA used for auto workareas                         2383872 bytes
total PGA used for manual workareas                               0 bytes
maximum PGA used for manual workareas                       8470528 bytes
over allocation count                                           291
bytes processed                                          2124600320 bytes
extra bytes read/written                                   39949312 bytes
cache hit percentage                                          98.15 percent

V$PGASTAT:

기본값은 SGA 크기의 20% 입니다. 이 파라미터를 0으로 설정하면 PGA 메모리의 자동 관리가 비활성화됨.

PGA 자동 대상 집계 : Oracle이 자동 모드에서 실행중인 작업 영역에 사용할 수있는 PGA 메모리 양을 제공합니다. 이 총계는 PGA_AGGREGATE_TARGET 파라메터 값과 현재 work area workload 에서 동적으로 파생됩니다. 따라서 Oracle에 의해 지속적으로 조정됩니다. 이 값이 PGA_AGGREGATE_TARGET의 값과 비교하여 작으면 시스템의 다른 구성 요소 (예 : PL/SQL 또는 Java 메모리)에서 많은 PGA 메모리가 사용되며 SORT 작업 영역에는 거의 남지 않습니다. 자동 모드로 실행중인 작업 영역에 충분한 PGA 메모리가 남아 있는지 확인해야합니다.

global memory bound: This gives the maximum size of a work area executed in AUTO mode. This value is continuously adjusted by Oracle Database to reflect the current state of the work area workload. The global memory bound generally decreases when the number of active work areas is increasing in the system. As a rule of thumb, the value of the global bound should not decrease to less than one megabyte. If it does, then the value of PGA_AGGREGATE_TARGET should probably be increased.

total PGA allocated: This gives the current amount of PGA memory allocated by the instance. Oracle tries to keep this number less than the value of PGA_AGGREGATE_TARGET. However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time, when the work area workload is increasing very rapidly or when the initialization parameter PGA_AGGREGATE_TARGET is set to a too small value.

total freeable PGA memory: This indicates how much allocated PGA memory which can be freed.

total PGA used for auto workareas: This indicates how much PGA memory is currently consumed by work areas running under automatic memory management mode. This number can be used to determine how much memory is consumed by other consumers of the PGA memory (for example, PL/SQL or Java):

PGA other = total PGA allocated - total PGA used for auto workareas over allocation count: This statistic is cumulative from instance start-up. Over-allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET is too small to accommodate the PGA other component in the previous equation plus the minimum memory required to execute the work area workload. When this happens, Oracle cannot honor the initialization parameter PGA_AGGREGATE_TARGET, and extra PGA memory needs to be allocated. If over-allocation occurs, you should increase the value of PGA_AGGREGATE_TARGET using the information provided by the advice view V$PGA_TARGET_ADVICE.

total bytes processed: This is the number of bytes processed by memory-intensive SQL operators since instance start-up. For example, the number of byte processed is the input size for a sort operation. This number is used to compute the cache hit percentage metric.

extra bytes read/written: When a work area cannot run optimally, one or more extra passes is performed over the input data. extra bytes read/written represents the number of bytes processed during these extra passes since instance start-up. This number is also used to compute the cache hit percentage. Ideally, it should be small compared to total bytes processed.

cache hit percentage: This metric is computed by Oracle Database to reflect the performance of the PGA memory component. It is cumulative from instance start-up. A value of 100% means that all work areas executed by the system since instance start-up have used an optimal amount of PGA memory. This is, of course, ideal but rarely happens except maybe for pure OLTP systems. In reality, some work areas run one-pass or even multi-pass, depending on the overall size of the PGA memory. When a work area cannot run optimally, one or more extra passes is performed over the input data. This reduces the cache hit percentage in proportion to the size of the input data and the number of extra passes performed. Example 7-3 shows how cache hit percentage is affected by extra passes.

Example 7-3 Calculating Cache Hit Percentage

Consider a simple example: Four sort operations have been executed, three were small (1 MB of input data) and one was bigger (100 MB of input data). The total number of bytes processed (BP) by the four operations is 103 MB. If one of the small sorts runs one-pass, an extra pass over 1 MB of input data is performed. This 1 MB value is the number of extra bytes read/written, or EBP. The cache hit percentage is calculated by the following formula:

BP x 100 / (BP + EBP) The cache hit percentage in this case is 99.03%, almost 100%. This value reflects the fact that only one of the small sorts had to perform an extra pass while all other sorts were able to run optimally. Hence, the cache hit percentage is almost 100%, because this extra pass over 1 MB represents a tiny overhead. On the other hand, if the big sort is the one to run one-pass, then EBP is 100 MB instead of 1 MB, and the cache hit percentage falls to 50.73%, because the extra pass has a much bigger impact.

"workarea_size_policy=manual"로 변경

SELECT a.ksppinm name, b.ksppstvl VALUE 
  FROM sys.x$ksppi a, sys.x$ksppcv b 
 WHERE a.indx = b.indx AND a.ksppinm = '_smm_max_size' ; 

NAME VALUE(KB) 
-------------- -------------------- 
_smm_max_size 15974

alter session set workarea_size_policy = manual; 
alter session set sort_area_size = 2147483647; 

SELECT /*+ full(t) parallel(t 64) */ 
* 
from t 
ORDER BY object_name;

SELECT ROUND( MIN( decode( n.name , 'session pga memory' , s.value ) ) /1024 ) "PGA(KB)" , 
ROUND( MIN( decode( n.name , 'session pga memory max' , s.value ) ) /1024 ) "PGA_MAX(KB)" , 
ROUND( MIN( decode( n.name , 'session uga memory' , s.value ) ) /1024 ) "UGA(KB)" , 
ROUND( MIN( decode( n.name , 'session uga memory max' , s.value ) ) /1024 ) "UGA_MAX(KB)" 
FROM v$statname n 
   , v$sesstat s 
WHERE ( name LIKE '%uga%' 
OR name LIKE '%pga%' ) 
AND n.statistic# = s.statistic# 
AND s.sid = :sid

alter system set pga_aggregate_target = 24M; 
System altered. 
CREATE TABLE t_emp AS 
SELECT * 
FROM emp , 
( 
SELECT ROWNUM no 
FROM dual 
CONNECT BY LEVEL <= 100000 
) ; 
Table created.

SELECT * 
FROM t_emp 
ORDER BY empno ;

alter session set workarea_size_policy = MANUAL; 
alter session set sort_area_size = 52428800; 
alter session set sort_area_retained_size = 52428800; 

SELECT * 
FROM t_emp 
ORDER BY empno ; 


### 결과 
||단계||PGA(KB)||PGA_MAX(KB)||UGA(KB)||UGA_MAX(KB)|| 
||최초|636|3004|280|657| 
||수행 도중|44796|44796|44264|44264| 
||완료 후|2812|52988|2393|47205| 
||커서를 닫은 후|572|52988|280|47205|