Automatic PGA Memory Management

What is Automatic PGA Memory Management?
Initialization parameters SORT_AREA_SIZE and HASH_AREA_SIZE are used to specify the maximum amount of memory for each session in Oracle8i or earlier.
In Oracle9i, the following parameters specify the maximum amount of the PGA memory available to all server processes for every instance.
(PGA: Program Global Area)

PGA_AGGREGATE_TARGET
Specify the total amount of PGA memory available to instance. You can dynamically change the parameter at instance level. Set the parameter to any value between 10M and 4096G-1 bytes.

WORKAREA_SIZE_POLICY
Specify if the size of the SQL workarea should be tuned automatically or manually.
AUTO: tune automatically
MANUAL: tune manually

When WORKAREA_SIZE_POLICY is set to MANUAL, the maximum amount of memory is defined by the parameter such as HASH_AREA_SIZE. When WORKAREA_SIZE_POLICY is set to AUTO, the maximum amount of memory is defined automatically and the parameters such as HASH_AREA_SIZE are ignored.
If PGA_AGGREGATE_TARGET is set, WORKAREA_SIZE_POLICY will be set to AUTO by default.

How to determine PGA_AGGREGATE_TARGET.
The following formula is used as a guideline.

OLTP systems:
PGA_AGGREGATE_TARGET=(total actual memory*80%)*20%

DSS systems:
PGA_AGGREGATE_TARGET=(total actual memory*80%)*50%

PGA_AGGREGATE_TARGET of DSS systems is determined larger than that of OLTP systems because DSS systems normally access large amount of data by sorting with ORDER BY or GROUP BY.

There three types of SQL workarea executions as follows:
optimal: workarea execution of which all processes such as sort are executed on memory
onepass: workarea execution that requires the minimum writes to a disk
multipass: workarea execution that requires heavy writes to a disk because SQL workarea is too low

The following are standard tuning goal:
workarea execution - optimal >= 90%
workarea execution - multiplass = 0%

Most of the workarea executions are executed in optimal mode to reduce workarea execution - onepass.
The following query enables you to monitor the status of PGA memory in V$STSSTAT.

select name ,value ,100*(value/ decode((select sum(value) from v$sysstat where name like 'workarea exec%'),0,null,(select sum(value) from v$sysstat where name like 'workarea exec%'))) pct from v$sysstat where name like 'workarea exec%' / NAME VALUE PCT ---------------------------------------------------------------------- workarea executions - optimal 1529 100 workarea executions - onepass 0 0 workarea executions - multipass 0 0

If VALUE of workarea execution - multiplass is not 0% or VALUE of workarea executions - onepass exceeds 10%, you need to increase PGA_AGGREGATE_TARGET. If VALUE of workarea executions - optimal is 100%, you may reduce PGA_AGGREGATE_TARGET.

http://www.insight-tec.com/en/mailmagazine/vol170.html 

관련 한글 문서 : http://openframework.or.kr/Wiki.jsp?page=AssignMemoryAutomaticallyForSQLExecution 

10053 trace

on

 alter session set events '10053 trace name context forever';

off

ALTER SESSION SET EVENTS '10053 trace name context OFF';

file ( oracle 11g )

$ORACLE_HOME/diag/rdbms/orcl/trace

oracle pivot

LISTAGG : oracle 11g r2 에서 새로 추가된 함수. 기존에 존재 하던
pivot 함수( 또는 복잡한 SQL) 보다 간결하고 간단하게 구현하도록 제공하고 있다.

Syntax :
LISTAGG   ( <expr> [ ,  <delimiter> )   WITHIN  GROUP   ( ORDER BY  <oby_expression_list> )

 

그외에 다른 방법으로 사용 가능한 SQL

1. PL /SQL

 create or replace type t_vc as table of varchar2(4000);
/

create or replace function pivot return t_vc pipelined
as
  v_last_deptno emp.deptno%type := null;
  v_line  varchar2(4000);
begin
  for r in (select deptno , ename from emp order by deptno) loop
    if v_last_deptno is null then
      v_line:=rpad(r.deptno,11) || ': ';
      v_last_deptno := r.deptno;
    end if;
    if r.deptno <> v_last_deptno then
      pipe row(v_line);
      v_line:=rpad(r.deptno,11) || ': ';
      v_last_deptno := r.deptno;
    end if;
    v_line := v_line || rpad(r.ename,11);
  end loop;
  pipe row(v_line);
  return;
end pivot;
/

출력결과

SQL> select * from table(pivot);

COLUMN_VALUE
-----------------------------------------------------------------------------------------------------------------------------------
10    : CLARK KING    MILLER
20    : JONES FORD    ADAMS      SMITH  SCOTT
30    : WARD TURNER    ALLEN      JAMES  BLAKE     MARTIN

2. SQL ( DECODE 사용 )

SELECT   a.deptno
    ,        MAX(DECODE(a.r, 1,a.ename))
             || MAX(DECODE(a.r, 2,', ' || a.ename))
             || MAX(DECODE(a.r, 3,', ' || a.ename))
             || MAX(DECODE(a.r, 4,', ' || a.ename))
             || MAX(DECODE(a.r, 5,', ' || a.ename))
             || MAX(DECODE(a.r, 6,', ' || a.ename))
             || MAX(DECODE(a.r, 7,', ' || a.ename))
             || MAX(DECODE(a.r, 8,', ' || a.ename))
             || MAX(DECODE(a.r, 9,', ' || a.ename))
             || MAX(DECODE(a.r,10,', ' || a.ename)) names
   FROM    (SELECT t.deptno
            ,      t.ename
            ,      ROW_NUMBER()
                   OVER (PARTITION BY t.deptno
                         ORDER BY     NULL) r
            FROM   emp t) a
   GROUP BY a.deptno
   ORDER BY a.deptno
   /

출력결과

    DEPTNO NAMES
---------- ----------------------------------------------------------------------------------------------------------------------
 10 CLARK, KING, MILLER
 20 JONES, FORD, ADAMS, SMITH, SCOTT
 30 WARD, TURNER, ALLEN, JAMES, BLAKE, MARTIN

 3. SQL ( sys_connect_by_path )

select deptno,
 substr( max( sys_connect_by_path( ename, '; ') ), 3 ) names
 from ( select deptno, ename, row_number() over ( partition by deptno order by ename ) rn
   from emp )
 start with rn = 1
 connect by prior deptno = deptno and
   prior rn+1 = rn
 group by deptno
 order by deptno
/

출력결과

     DEPTNO NAMES
---------- ----------------------------------------------------------------------------------------------------
 10 CLARK; KING; MILLER
 20 ADAMS; FORD; JONES; SCOTT; SMITH
 30 ALLEN; BLAKE; JAMES; MARTIN; TURNER; WARD

참고자료 :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:766825833740

Magic Number

Magic Number 란??
   -  Oracle 이 정확한 Selectivity 를 계산할 수 없을때 사용되는 Default  값이다.

Oracle 이 Magic Number 를 사용했다는것은 제대로된 실행계획을 산출해내지 못할 가능성이 높기 때문에,

상황에 따라서 통계정보 또는 SQL 튜닝을 해야 할 것이다.

Magic Number 는 주로 바인드 변수에 의하여 선택이 된다. 바인드 변수 사용시 어떤 값이 나올지 예측하기

힘들기 때문에 사용되는것이다.

1. Range : 5%

select * from t1 where c1 >= :b1;
select * from t1 where c1 <= :b1;
select * from t1 where c1 between :b1 and :b2;

2. Function : 1%

select * from t1 where f1(c1) = 1;
select * from t1 where f1(c1) = :b1;
select * from t1 where f1(c1) > :b1; -- 5%

Function 사용시 어떤 값이 return 될지 알수 없기 때문에 magic number 가 사용되며,
Function Based Index를 사용하여 해결 가능하다.

3. Like : 5%

select * from t1 where c1 like :b1;
select * from t1 where c1 like '%A';
select * from t1 where c1 like '%A%';

가급적 Magic Number 를 사용하지 않아야 겠지만 ...
Bind 변수의 경우 Soft Parsing 에 유리하기 때문에 trade off 를 고려하여(패턴에 따라서..) 튜닝해야 한다.

Lock Mode

데몬 프로세스 생성

http://kldp.org/node/62759  참고 ...

[펌] Buffer Cache 관련 Wait

[3] Buffer Cache 관련 Wait

■ Buffer Cache 구조

Buffer Cache의 기본적인 기능은 여러 프로세스에 의해 공통으로 자주 액세스 되는 데이터베이스 블록을 메모리에 캐쉬하여 물리적인 디스크 IO를 최소화함으로써 더 빠른 액세스 속도를 제공하기 위한 것이다. 복잡한 설명은 생략하고, Buffer Cache 의 기본구조를 이해하기 위한 몇 가지 핵심 용어들을 간단히 정리해 보도록 하겠다.

▷ Buffer header

모든 버퍼 블록들은 각자의 buffer header를 통해 액세스되고 관리된다. 즉, 메모리에 캐쉬된 특정 데이터 블록에 대한 액세스는 먼저 해쉬 알고리즘을 통해 cache chain 상의 buffer header를 찾고 해당 buffer header에 기록된 데이터 블록의 메모리상 주소를 찾아가 원하는 정보를 읽는 방식으로 이루어진다. Buffer header에 기록되는 주요정보는 다음과 같으며 Buffer header의 내용은 V$bh 뷰를 통하여 조회해볼 수 있다.

     - 메모리상에서의 해당 버퍼블록의 주소
     - 해당 버퍼 블록(실제로는 버퍼헤더)가 포함되어 있는 hash chain
     - LRU, LRUW, CKPTQ와 같은 리스트상에서의 해당 버퍼블록의 위치
     - 해당 버퍼블록에 대한 User, Waiter와 상태를 나타내는 각종 Flag 

▷ Hash Buckets/ Hash Chains

Buffer Cache의 모든 블록은 해쉬 알고리즘을 통해 관리된다. 곧, 데이터 블록의 DBA, Class 값으로 Hash Function을 적용하여 해당 블록이 속하는 hash buckets을 할당하며, 동일한 hash buckets에 할당되는 데이터 블록의 버퍼헤더들은 linked list형태로 hash chain을 이루게 된다. Hash buckets/hash chains는 특정 데이터 블록을 찾아가기 위한 수단을 제공한다. 각각의 hash buckets에는 자신에 속한 hash chain을 보호하기 위한 latch(cache buffers chains)가 할당된다. 

▷ LRU

LRU는 두개의 리스트, 즉 LRUW와 LRU 리스트의 쌍으로 구성된다. LRUW(LRU Write list)는 dirty list와 같은 말이며, 수정되어 디스크에 반영되어야 할 블록들의 리스트이다. LRU(Least recently used list)는 LRUW에 올라가지 않은 나머지 버퍼 블록들이 등록되어 있다. Buffer cache 상의 버퍼블록은 반드시 LRU나 LRUW 둘 중의 하나에 등록되며, 두 리스트에 동시에 포함되는 경우는 없다. LRU는 Free Buffer를 찾기 위한 수단을 제공한다. 경합을 피하기 위해 버퍼캐쉬 블록들을 여러 개의 LRU쌍으로 나누어 관리할 수 있으며, 각 LRU리스트를 보호하기 위해 Latch(Cache buffers lru chain)가 하나씩 할당된다.

■ Buffer Cache 운영규칙

▷ 메모리상의 특정 버퍼블록을 찾아가거나, 특정 블록이 메모리에 캐쉬 되어 있는지를 확인하기 위해서 오라클은 hash bucket/hash chain 구조를 사용한다. 

▷새로운 데이터블록을 디스크로부터 메모리로 읽어 들이기 위한 free buffer를 확보하기 위해 오라클은 LRU 리스트를 사용한다. 

▷ 버퍼블록은 LRU나 LRUW 둘 가운데 하나에 등록된다.

▷ 하나의 블록에 대해 시간대가 다른 여러 개의 복사본이 존재할 수 있으며, 그 가운데 오직 CURRENT 버퍼만이 변경될 수 있다. 

▷하나의 버퍼블록은 한번에 오직 하나의 프로세스에 의해서만 변경될 수 있다. 

■ Buffer Cache 관련 Waits

버퍼캐쉬와 관련되어 흔히 발생하는 대표적인 Wait 이벤트는 다음과 같다. 

▷ buffer busy waits

여러 세션이 동시에 같은 블록을 읽으려고 하거나 여러 세션이 같은 블록에 대한 변경작업이 완료되기를 기다리고 있는 경우에 발생하며, 특정 블록에 대한 경합을 해소하기 위한 조치는 블록의 유형에 따라 달라진다. Data block에 대한 경합이 많은 경우는 Pct free나 Pct used 값을 사용하여 블록 당 로우수를 줄이거나, 특정 블록에 로우 입력이 몰리는 구조의 인덱스(right-hand-index)일 경우는 reverse key index의 사용을 검토하는 등의 방법이 있으며, segment header의 경합이 많은 경우는 freelist 수를 늘리거나 Extent의 크기를 증가시키는 등의 방법이 있고, undo header나 undo block에 대한 경합은 롤백세그먼트의 개수나 크기를 증가시키는 것이 전형적인 조치 방법이다. v$waitstat과 x$kcbfwait을 이용하며 Class 또는 file별로 wait 발생상황을 판단할 수 있다. 

▷ free buffer waits/write complete waits

DBWR가 dirty buffer를 write하는 동안 서버 프로세스가 대기하고 있는 경우 발생한다. 곧, 너무나 많은 dirty buffer가 생겨나거나 DBWR의 쓰기 속도가 충분히 튜닝 되지 못한 경우에 발생한다. 점검 포인트는 물리적 디스크의 속성(stripe size, layour, cache size) 최적화, Raw device의 활용, Async IO나 multi-DBWR(db_writer_processes) 활용여부 등이다.

위와 같은 버퍼 블록에 대한 경합 역시 비효율적인 실행계획을 통해 수행되는 애플리케이션에 의하여 불필요하게 많은 블록이 메모리로 올라오는 것이 원인일 경우가 많으므로 경합이 빈번한 블록이 속하는 테이블/인덱스 명을 찾아낼 수 있다면 관련 SQL을 찾아내어 보다 효과적인 튜닝작업이 이루어질 수 있을 것이다. v$session_wait의 p1,p2 컬럼에 각각 file#, block#값을 표시하여 주므로 이 값을 이용하여 아래의 SQL문으로 현재 어떤 오브젝트에 대하여 해당 wait가 발생하고 있는지를 추적할 수 있다. ( 1회에 소개한 SQL문에서는 Additional Info 값을 참조. )

     select segment_name, segment_type
     from dba_extents
     where file_id = :file#
     and :block# between block_id and block_id + blocks -1

▷ cache buffers chains latch

SGA내에 캐쉬된 데이터블록을 검색할 때 사용된다. 버퍼캐쉬는 블록들의 chain을 이루고 있으므로 각각의 chain은 이 Latch의 child들에 의해 보호된다. 이 Latch에 대한 경합은 특정 블록에 대한 대량의 동시 액세스가 발생할 때 유발된다. 애플리케이션을 검토해 보아야 한다. 
Ø cache buffers lru chain latch
버퍼캐쉬의 버퍼를 LRU 정책에 따라 이동시켜야 할 필요가 있는 경우 프로세스는 이 Latch 획득하게 된다. 이 Latch에 대한 경합은 Multiple buffer pool을 사용하거나 DB_BLOCK_LRU_LATCHES 를 증가시켜 LRU Latch의 개수를 늘려서 해소할 수 있다. SQL문을 튜닝하면 해당 프로세스에 의해 액세스 될 블록의 수가 줄어들 것이므로 당연히 효과를 거둘 수 있다.

위와 같이 버퍼캐쉬를 관리하는 Latch에 대한 경합은 경합이 집중되는 특정 Child Latch에 의해 관리되는 버퍼블록을 찾아 해당 블록이 속한 세그먼트 정보를 알아낸다면 보다 효과적인 조치가 가능할 것인데, latch free wait일 경우 v$session_wait의 p1raw 값이 해당 Latch address를 의미한다. 이 값을 x$bh의 hladdr 값과 조인하면 관련 오브젝트 이름을 추적해볼 수 있다. 

     select file#, dbarfil, dbablk, obj, o.name
     from x$bh bh, obj$ o 
     where bh.hladdr = :latch_address
     and bh.obj = o.obj#;


http://www.oracle.com/technology/global/kr/pub/columns/dbtuning03.html?_template=/ocom/print