by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

13. 정렬과 머지 조인




정렬과 머지조인

  • 보통 오라클에서 order by절, group by절, distinct 연산자, connect by 쿼리, b-tree to bitmap conversions 오퍼레이션, 분석함수, 집합연사, 인덱스 생성 등 많은 경우 정렬처리를 필요로한다.
  • 정렬 처리는 아주 일반적인 오퍼레이션이면서 실제 메커니즘과 자원요구량이 잘 알려지지 않았다.

정렬처리 모드(work_area_execution)

정렬처리도 해시 조인과 같이 효율성 기준으로 optimal. onepass, multipass과 같이 3가지로 구분한다

  • optimal정렬은 순전히 메모리에서만 수행된다. 정렬을 시작할때 데이터 읽기를 진행하면서 점차적으로 메모리가 할당되며, 정렬시작 시 sort_area_size로 설정된 크기(또는 pga_aggregate_target의 5%)만큼의 메모리가 할당되는 것은 아니다
  • onepass정렬은 읽은 데이터가 너무 커서 메모리에 더 이상 수용할 수 없을때 나타난다.
    데이터를 읽어 들이면서 정렬을 수행하다가 가용메모리가 차게 되면 정렬된 데이터집합을 디스크로 덤프하게되는데 이러한 과정은 모든 입력 데이터를 처리할 때까지 반복된다.
    위 과정이 끝나면 몇 개의 정렬된 run(집합)이 남게 되고 이것을 단일 데이터 집합으로 머지한다.
    정렬된 모든 run을 전부 읽어 들일 정도로 메모리가 충분히 크다면, 이것이 onepass정렬이 된다.
  • multipass정렬은 onepass정렬과 시작은 같다.
    디스크에 남아 있는 모든 run이 메모리에 한 번에 들어가지 못하고 머지된 결과를 다시 디스크에 쓰는 작업을 정렬이 완료될때까지 반복하는 과정이 추가된다.
    결국 데이터의 정렬을 위해 여러 번 디스크에 쓰고 읽기가 반복될 수 있다.
    이때 정렬된 데이터를 반복해서 읽어야하는 횟수를 merge passes횟수라고 하다.
    (10032트레이스의 max intermediate merge width으로 확인 할 수 있다.)

정렬테스트

테스트에서 보여지는 문제점들..

정렬 중에 디스크로 덤프되는 데이터 포맷을 보면 쓰여지는 전체 양이 생각 보다는 훨씬 더 크다.
본 사례를 보면 디스크로 덤프된 량이 몇 블록인데(몇 mb)인데 실제 정렬된 순수 데이터 부분은 몇 mb크기에 불과하다

  • SORT_AREA_SIZE는 1MB일때 OPTIMAL정렬이 되지 않을 경우 정렬데이터가 1MB까지 도달하고난 후 디스크에 쓰여질거라고 예상했지만 그렇지 않은 이유가 있다.
    • 정렬데이터에 순수데이터 뿐아니라 일정한 포멧데이터도 포함된다.
    • 또한 메모리 구조에 의한 부가적인 공간도 포함된다.
  • SORT_AREA_SIZE의 크기를 변경하였을때 CPU시간이 크게 나오는 이유
    • 일반적으로 sort가 메모리에서만 실행되면 최적이다라고 생각하고 적절한 sort_area를 지정하여 메모리안에서만 정렬하기를 원한다.
    • 그러나 표에서는 예상과는 다르게 sort_area를 키워도 수행속도등 개선사항이 보이지 않느다.

필자의 가설 : 오라클 정렬메커니즘의 의한 결과라는 유추
: 일종의 이진삽입트리에 기초로 한다( 이진 트리는 하위노드가 2두개인 트리구조이다)

  • 인덱스의 B-Tree에서도 트리가 성장할때 많은 비용이 들어가는데 이진트리인 경우 특성상 DEPTH가 깊으므로 더 많은 상위 노드과 거기에 딸려 있는 노드들이 영향을 받을 것이다

  • 수행된 총비교횟수에 차이가 나지 않는다.
    : 메모리 상의 이진트리가 길어지면 탐색시간이 증가한다는 것을 실험적으로 알 수 있다.
    : 병목이 i/o가 아닌 메모리정렬에 의한 것이라면 in-memory보다는 onepass정렬로 유도할 수 있도록 memory를 설정하여 대량정렬 시 성능향상을 유도할 수 있다고 한다.
    : 어째튼 sort_area는 multi-pass정렬이 되지 않을 정도 그리고 가능한 작게 설정한다.
정렬에 영향을 미치는 파라미터
  • sort_area_retained_size의 영향(10.2 default 0)
    : sort_area_retained_size를 0이 아닌 다른 값을 설정하게 되면 오라클은 클라이언트로 결과를 반환하기 전에 모든 데이터를 디스크로 덤프하도록 한다.
    : 정렬데이터를 처리할때 처음은 UGA에서 시작한다. 할당된 메모리가 sort_area_retained_size를 넘어가면 그 다음 할당은PGA에서 이루어 진다. 비록 정렬이 메모리안에서 이루어진다고 해도UGA와 PGA가 분할되어 있다면 전체 정렬데이터 집합을 디스크로 덤프해야 한다.
  • pga_aggregate_target
    : workarea_policy = auto로 설정하면 정렬, 해시조인, 인덱스생성, 비트맵인덱스 처리등과 같은 대량의 메모리요구를 하는 프로세스에서 사용해야 하는 메모리 전체한도의 적정선을 의미한다.
    : 병렬처리가 아닌 정렬처리의 경우 pga_aggregate_target의 5%까지 메모리를 할당 받을 수 있으며, 병렬처리일 경우 salve 프로세스가 사용하는 작업영역의 메모리 합계가 pga_aggregate_target의 30%를 넘을 수 없다.
  • 병렬처리시의 메모리사용량을 변경할 수 있는 hidden파라미터
    _smm_px_max_size : 병렬처리 시 메모리한도를 설정
    _smm_max_size : 직렬처리 시 최대 메모리한도를 설정(디폴트 pga_aggregate_target의 5%)
    -smm_min_size : 직렬처리 시 최소 메모리한도를 설정(디폴트 pga_aggregate_target의 0.1%)
    _smm_auto_min_io_size, _smm_auto_max_io_size : sort run 머지 시 보다 큰 i/o크기를 사용하면 더 커진 메모리 할당의 이점을 누릴 수 있다.

: 여튼 workarea_policy = auto이며 pga_aggregate_target 디폴트 200MB를 사용한 경우 샘플코드를 수행하면서 최적의 메모리를 사용한다.

정렬 비용
  • 4가지 설정 시 정렬비용 비교
    (가설이 너무 많아 pass, 단 cpu costing 방식일때 MBRC값에 따라 비용이 변경될 수 있음 )

머지조인

머지조인을 보통 sort merge join이라고 하는 것은 조인의 양쪽 대상 집합 모두가 조인 컬럼 순으로 정렬되어야 하기 때문이다.
해시 조인 처럼 조인을 두개의 독립된 쿼리로 분해하고 결과집합을 다시 결합하는 것이다.
그러나 해시 조인과는 달리 두 번째 쿼리 부분을 시작하기 전에 첫 번째 쿼리부분을 끝내야할 필요는 없다.(정렬을 한다는 것은 두 집합이 각각 실행완료된것을 의미하지 않을까?)

동작순서

1. 액세스와 필터 조건절을 사용하여 첫 번째 데이터 집합을 획득하고 그것을 조인 컬럼 순으로 정렬한다.
2. 액세스와 필터 조건절을 사용하여 두 번째 데이터 집합을 획득하고 그것을 조인 컬럼 순으로 정렬한다.
3. 첫 번째 데이터 집합의 각 로우에 대해 두 번째 데이터 집합에서 시작점을 찾고 조인에 실패하는 로우를 만날 때까지 스캔한다.(각각 집합이 정렬되어 있으므로 중단지점을 알 수 있다.)

sort merge에 대해 알고 있던 내용과 다른 점

적절한 인덱스가 있으면 두 번째 집합을 획득하기 전에 전체를 처리하지 않아도된다.
머지조인은 첫번째 집합을 정렬할 필요가 없다.
-> : 정렬준비가 완료된 후에라야 조인을 시작할 수 있으므로 원초적으로 부분범위처리를 할 수 없어 항상 전체범위처리를 한다
: 동시적으로 처리된다. 조인 대상 집합은 각자가 상대 집합의 처리결과와 상관없이 자신이 보유한 처리조건만 가지고 액세스하여 정렬해 둔다. 양쪽 집합이 모두 준비가 완료되어야만 머지를 시작할 수 있으므로 순차적인 처리가 불가능하다
http://wiki.gurubee.net/pages/viewpage.action?pageId=1966755

alter session set hash_join_enabled = false;
create table t1 as
 with generator as (
   select --+ materialize
          rownum id
     from all_objects
    where rownum <= 3000
  )
select rownum                id,
       rownum                n1,
       trunc((rownum - 1)/2) n2,
       lpad(rownum,10,'0')   small_vc,
       rpad('x',100,'x')     padding
  from generator v1,
       generator v2
 where rownum <= 10000;

alter table t1 add constraint t1_pk primary key(id);

select count(distinct t1_vc || t2_vc)
  from (select /*+ no_merge ordered use_merge(t2) */
               t1.small_vc t1_vc
               t2.small_vc t2_vc
          from t1, 
               t2,
         where 
               t1.n1 <= 1000 and t2.id = t1.id                     -- 1번
               t1.n1 <= 1000 and t2.n2 = t1.id                         -- 2번
               t1.n1 <= 1000 and t2.id between t1.id -1 and t1.id +1   -- 3번
               t1.n1 <= 1000 and t2.n2 = t1.id                         -- 4번
               t1.n1 <= 1000 and t2.id = t1.id                         -- 5번
         );          

집계

집계query 수행 시 기본적인 실행계획


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |     1 |    26 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     1 |    26 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

각 문장에 따른 변화는 id =1번의 오퍼레이션이 조금씩 바뀌지만 골격은 바뀌지 않는다.
단 10g부터는 Hash Group by(Hash unique)오퍼레이션이 있는 데 대부분의 경우 sort group by보다 빠르게 수행된나 기존 정렬의 효과는 없다.

select col1, col2 from t1 order by col1, col2;
|   1 |  SORT ORDER BY     |      |     1 |    26 |     3  (34)| 00:00:01 |

select distinct col1, col2 from t1;
|   1 |  HASH UNIQUE       |      |     1 |    26 |     3  (34)| 00:00:01 | <- 책은 Sort (unique) 

select col1, col2, count(*) from t1 group by col1, col2;
|   1 |  SORT GROUP BY     |      |     1 |    26 |     3  (34)| 00:00:01 |

select col1, col2, count(*) from t1 group by col1, col2 order by col1, col2;
|   1 |  SORT GROUP BY     |      |     1 |    26 |     3  (34)| 00:00:01 |

select max(col1), max(col2) from t1;
|   1 |  SORT AGGREGATE    |      |     1 |    26 |            |          | 

인덱스

  • 단일 테이블을 읽어 인덱스를 만드는 경우 데이터 정렬이 전체 일량의 대부분을 차지한다. (특정 컬럼을 기준으로 정렬을 해야하기 때문)
  • B-tree 인덱스를 만들기 위한 필요 메모리 양을 계산하고자 하면 추가적인 컬럼 (Rowid)르 고려해야 한다.
  • rowid의 크기
    힙테이블 : 6 byte
    클러스터 인덱스 : 8 byte
    파티션 테이블의 global인덱스 : 10 byte
    파티션 테이블의 local 인덱스 : 6 byte

집합연산자

Union(합집합) : 양측 집합 중 어느 쪽에든 존재하는 로우
Intersect(교집합) : 양측 집합 모두에 존재하는 로우
Minus(차집합) : 첫 번째 집합 중 두 번째 집합에 없는 로우

  • 기술적으로 볼때 집합 연산자는 중복을 허용하지 않으므로 중복을 허용하는 Union all 집합 연산자가 아니다.
     alter session set "_optimizer_cost_model"=io
     
     drop table t1 purge;
     create table t1 as 
      select rownum id, ao.*
        from all_objects ao
      where rownum <= 2500;
    
     drop table t2 purge;
     create table t2 as 
      select rownum id, ao.*
        from all_objects ao
      where rownum <= 2000;
    
    
     - 각각 집합에 대한 유일성을 강제한 후 집합연산을 하는 경우의 쿼리
       select distinct owner, object_type from t1
       intersect
       select distinct owner, object_type from t2;
     - 집합연산자는 unique한 결과를 반환한다는 점을 반영한 쿼리
       select owner, object_type from t1
       intersect
       select owner, object_type from t2;
    
  • 불필요한 오퍼레이션에 의해 부가적인 정렬연산을 일으킬 것이라 생각했지만 옵티마이져는 두사례를 동일한 일량으로 처리하는것을 볼 수 있다.
    cpu 모델은 비용이 같도록 계산되나 i/o 모델은 비용으로 다른 비용 가질 수 있다는데...
    
    --위 쿼리
    -----------------------------------------------------------
    | Id  | Operation           | Name | Rows  | Bytes | Cost  
    -----------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |  2000 |   123K|    45 
    |   1 |  INTERSECTION       |      |       |       |       
    |   2 |   SORT UNIQUE       |      |  2500 | 70000 |    24 
    |   3 |    TABLE ACCESS FULL| T1   |  2500 | 70000 |     5 
    |   4 |   SORT UNIQUE       |      |  2000 | 56000 |    21 
    |   5 |    TABLE ACCESS FULL| T2   |  2000 | 56000 |     4 
    ----------------------------------------------------------------------------
    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |  2000 |   123K|    20  (50)| 00:00:01 |
    |   1 |  INTERSECTION       |      |       |       |            |          |
    |   2 |   SORT UNIQUE       |      |  2500 | 70000 |    11  (10)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL| T1   |  2500 | 70000 |    10   (0)| 00:00:01 |
    |   4 |   SORT UNIQUE       |      |  2000 | 56000 |     9  (12)| 00:00:01 |
    |   5 |    TABLE ACCESS FULL| T2   |  2000 | 56000 |     8   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    --아래 쿼리
    ------------------------------------------------------------
    | Id  | Operation           | Name | Rows  | Bytes | Cost  |
    ------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |  2000 |   123K|    45 |
    |   1 |  INTERSECTION       |      |       |       |       |
    |   2 |   SORT UNIQUE       |      |  2500 | 70000 |    24 |
    |   3 |    TABLE ACCESS FULL| T1   |  2500 | 70000 |     5 |
    |   4 |   SORT UNIQUE       |      |  2000 | 56000 |    21 |
    |   5 |    TABLE ACCESS FULL| T2   |  2000 | 56000 |     4 |
    ------------------------------------------------------------
    ---------------------------------------------------------------------------- 
    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
    ---------------------------------------------------------------------------- 
    |   0 | SELECT STATEMENT    |      |  2000 |   123K|    20  (50)| 00:00:01 | 
    |   1 |  INTERSECTION       |      |       |       |            |          | 
    |   2 |   SORT UNIQUE       |      |  2500 | 70000 |    11  (10)| 00:00:01 | 
    |   3 |    TABLE ACCESS FULL| T1   |  2500 | 70000 |    10   (0)| 00:00:01 | 
    |   4 |   SORT UNIQUE       |      |  2000 | 56000 |     9  (12)| 00:00:01 | 
    |   5 |    TABLE ACCESS FULL| T2   |  2000 | 56000 |     8   (0)| 00:00:01 | 
    ---------------------------------------------------------------------------- 
    
alter session set "_optimizer_cost_model"=cpu;
alter session set statistics_level = all;
alter session set optimizer_features_enable = '9.2.0';

explain plan for 
create table t_intersect as
select distinct *
  from ( select owner, object_type from t1
         intersect
         select owner, object_type from t2
       );

select * from table(dbms_xplan.display());
----------------------------------------------
| Id  | Operation              | Name        |
----------------------------------------------
|   0 | CREATE TABLE STATEMENT |             |
|   1 |  LOAD AS SELECT        | T_INTERSECT |
|   2 |   SORT UNIQUE          |             | 
|   3 |    VIEW                |             |
|   4 |     INTERSECTION       |             |
|   5 |      SORT UNIQUE       |             |
|   6 |       TABLE ACCESS FULL| T1          |
|   7 |      SORT UNIQUE       |             |
|   8 |       TABLE ACCESS FULL| T2          |
----------------------------------------------

alter session set optimizer_features_enable = '10.2.0.1';
explain plan for 
create table t_intersect as
select distinct *
  from ( select owner, object_type from t1
         intersect
         select owner, object_type from t2
       );
select * from table(dbms_xplan.display());
-------------------------------------------------------------------------------------- 
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------- 
|   0 | CREATE TABLE STATEMENT |             |  2000 | 56000 |    30  (10)| 00:00:01 | 
|   1 |  LOAD AS SELECT        | T_INTERSECT |       |       |            |          | 
|   2 |   HASH UNIQUE          |             |  2000 | 56000 |    27  (12)| 00:00:01 | 
|   3 |    VIEW                |             |  2000 | 56000 |    26   (8)| 00:00:01 | 
|   4 |     INTERSECTION       |             |       |       |            |          | 
|   5 |      SORT UNIQUE       |             |  2500 | 70000 |    11  (10)| 00:00:01 | 
|   6 |       TABLE ACCESS FULL| T1          |  2500 | 70000 |    10   (0)| 00:00:01 | 
|   7 |      SORT UNIQUE       |             |  2000 | 56000 |     9  (12)| 00:00:01 | 
|   8 |       TABLE ACCESS FULL| T2          |  2000 | 56000 |     8   (0)| 00:00:01 | 
-------------------------------------------------------------------------------------- 

9i와 10g에서 같은 쿼리를 실행하였을 때 9i에서 불필요한 오퍼레이션을 보여주지 않았지만 10g에서는 불필요한 연산에 대한 부분이 나온다.
그러나 9i실행계획이 잘못된 것이며 실제 10032트레이로 확인 하면 9i도 10g와 같은 중간연산을 하는 것을 확인할 수 있다.(위 테스트에서는 적용되지 않음)

마지막 당부

런타임 시에 엔진이 수행하는 것이 옵티마이저가 비용계산 시에 생각한 그대로일 필요는 없다는 것이다.

10032 항목

: max intermediate merge width multi-pass 정렬을 해야할 때, 오라클이 한 번에 읽고 머지할 수 있는 sort run의 수
: number of merges 스트림머지를 한 수
: Inital runs 오라클이 추산한 디스크로 덤프하게 될 sort runs의 개수(Block to sort * 블록크기 / area size)로 계산된다.\
: Number of direct sync 직접읽기대기회수
: Number of direct writes 직접쓰기대기회수 (오류로 정확하지 않은것 같다.)

10053 항목

: sort_with = max intermediate merge width
: Merge passes 전체 데이터 집합이 디스크로 쓰여졌다가 다시 읽혀지는 횟수이다.
inital runs가 Sort width보다 적으면 한번의 pass로 머지됨을 의미한다.
: Area size 데이터처리목적으로 사용된 메모리양(pga_aggregate_target에 기초한 최소할당양)
: Max Area size 해당 세션에서 받을 수 있는 최대 할당양
: Degree 병렬처리외 직렬처리 시 각각의 정렬비용계산내역을 별도의 섹션으로 보여준다.
: Rows 테이블의 계산된(필터링된) 카디널리티이다.
Area_size를 늘이지 않고도 Row갯수를 떨어뜨릴 수 있기 때문에 병렬처리를 이용하면 정렬비용을 손쉽게 낮출 수 있다.
: Block to sort 정렬될 데이터양으로 "로우크기*로우개수/블록크기"로 산출된다.
: Row size 옵티마이져가 추산한 정렬 데이터의 평균 row길이이다.
: Total IO sort cost '아마도' cost per pass와 pass회수가 결합된 값이다.
: Total CPU sort cost 비용중 CPU에 해당하는 부분으로서 CPU오퍼레이션 수 단위이고, 트레이스파일을 보면 나중에 상응하는 비교적 작은 I/O비용으로 전환한다.
: Total Temp space used 정렬 오퍼레이션이 요구하는 임시 영역의 이론적인 크기

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 9월 30, 2009

    장태길 says:

    PGA_AGGREGATE_TARGET 값을 조정할 필요가 생겨서 간단히 찾아보았음 출처 : 1. http://wiki.ex-em.com/in...

    PGA_AGGREGATE_TARGET
    값을 조정할 필요가 생겨서 간단히 찾아보았음

    출처 :
    1. http://wiki.ex-em.com/index.php/Direct_path_read/write_temp#PGA_AGGREGATE_TAREGET.EA.B3.BC_direct_I.2FO
    2. Prodba / [발표자료]PGA와 UGA - 하루살이님글

    가. PGA_AGGREGATE_TARGET 튜닝

    • EM 의 Advisor 를 통해서 PGA_AGGREGATE_TARGET 에 대한 튜닝이 가능하다.
      ( 안되면 AWR 이나 STATSPACK 에서도 활용 가능하다. )

    나. PGA_AGGREGATE_TARGET 개념
    :*_AREA_SIZE 파라미터를 사용하는 데 있어 기억해야 할 중요한 것들은 다음과 같다:
    o 이 파라미터들은 SORT, HASH, BITMAP MERGE operation에 사용되는 메모리의 최대치를 제어한다.
    o 단일 query는 이 메모리를 사용하는 많은 operation들을 가질 수도 있으며 여러 개의 sort/hash 영역이생성될 수 있다.
    Open 된 많은 cursor를 동시에 가질 수도 있지만 그것들 각자가 자신의 SORT_AREA_RETAINED 필요로 한다는 것을 기억하라.
    그래서, 만일 sort 영역의 크기를 10MB로 설정한다면 세션에서는 10, 100, 1,000 또는 그 이상의 megabyte의 RAM을 사용할 수 있다.
    이 설정들은 세션 단위의 제한이 아니라 단일 operation의 제한이며,
    세션은 단일 query 내에 많은 sort를 가지거나 하나의 sort를 요청하는 많은 query를 가질 수 있다.
    o 이들 영역의 메모리는 "요구 기반"으로 할당된다. 만일 sort 영역의 크기를 1GB로 하였더라도
    그것이 1GB의 RAM을 할당하였음을 의미하지 않는다. 그것은 오직 오라클 프로세스에게
    sort/hash operation을 위해 많은 메모리를 할당할 수 있도록 허가하였음을 의미할 뿐이다.
    ==> 세션 레벨이 아닌 세션에서 수행되는 SQL 내의 Operation 에 할당되는 의미라고 한다.

    다. PGA_AGGREGATE_TARGET 관련 파라미터
    가. _SMM_MAX_SIZE
    : 직렬처리 시 최대 메모리한도의 설정
    PGA_AGGREGATE_TARGET 의 5%
    나. _SMM_PX_MAX_SIZE
    : 병렬처리 시 최대 메모리한도의 설정
    PGA_AGGREGATE_TARGET 의 30%

    라. PGA_AGGREGATE_TARGET 적정값
    참고)
    That was true up to Oracle 10.1. Since 10.2 it is a dynamic parameter.
    In 10.2 it is limited to 200M as long as pga_aggregate_target is smaller as 1GB.
    When pga_aggregate_target is set to a larger value as 1GB then _pga_max_size= 20% of pga_aggregate_target .

    오라클 튜닝 가이드에서는 다음과 같이 PGA_AGGREGATE_TARGET의 값을 설정할 것을 권고하고 있다.

    • OLTP : PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
    • DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

    OS 메모리가 20기가라면
    ( 20G * 0.8 ) * 0.2 = 6.4 G

    마. PGA_AGGREGATE_TARGET 값 체크

    • PGA_AGGREGATE_TARGET 체크
      select sid,name,value,DISPLAY_VALUE
      from v$spparameter
      where name like '%pga%' ;

    PGA_AGGREGATE_TARGET 관련 파라미터 체크 ( 단위는 KBYTES 임 )
    select a.ksppinm, b.ksppstvl, a.ksppdesc
    from x$ksppi a, x$ksppsv b
    where a.indx = b.indx
    and a.inst_id = b.inst_id
    and a.ksppinm in ( '_smm_max_size','_smm_px_max_size','_pga_max_size')
    order by ksppinm ;
    =================================================================================================================================
    PGA Check Script
    – 출처 : http://www.dba-village.com
    set linesize 145
    set pagesize 1000
    set trimout on
    set trimspool on
    Set Feedback off
    set timing off
    set verify off

    prompt

    prompt – ----------------------------------------------------------------------- —

    prompt – PGA —

    prompt – ----------------------------------------------------------------------- —

    prompt

    clear breaks
    break on unit -
    skip 1

    compute Sum of value on unit

    column value format 99999990 heading "Value (Mb)"
    column name format a40 heading "Name"
    column Unit format a10 heading "Unit"

    select
    Unit Unit
    , name name
    , round(value/1024/1024,0) value
    from
    v$pgastat
    Order
    By unit
    ;

    Prompt

    clear breaks

    Prompt The following main statistics are of interest here:

    Prompt 'aggregate PGA target parameter'

    Prompt This displays the value to which PGA_AGGREGATE_TARGET is set

    Prompt

    Prompt 'aggregate PGA auto target'

    Prompt This displays the value that Oracle can use for work areas that run in the

    Prompt automatic PGA mode. It is effective if this value is not small in relation

    Prompt to the total size of the PGA_AGGREGATE_TARGET aggregate PGA target

    Prompt parameter. Therefore, there should still be sufficient space remaining for

    Prompt the work area.

    Prompt

    Prompt 'over allocation count'

    Prompt This value specifies how often the value of PGA_AGGREGATE_TARGET did not

    Prompt meet the minimum memory requests of the server process. As a result, Oracle

    Prompt had to add additional PGA memory internally again over and above the

    Prompt maximum value specified in PGA_AGGREGATE_TARGET. If 'over allocation count'

    Prompt is greater than zero, this means that the value of PGA_AGGREGATE_TARGET is

    Prompt too low. You must then increase PGA_AGGREGATE_TARGET. If

    Prompt PGA_AGGREGATE_TARGET is set optimally, the value of 'over allocation count'

    Prompt should be zero.

    Prompt

    Prompt 'cache hit percentage'

    Prompt This value shows the performance/efficiency of the Oracle automatic PGA

    Prompt memory management since the instance started. The ideal result of 100%,

    Prompt which is not usually achieved (nor does it have to be), means that the work

    Prompt areas of the server processes were the optimum size for all queries and

    Prompt that no one-pass or multi-pass operations occurred.

    prompt

    prompt

    Prompt – Database PGA Advice

    COL ptfe FORMAT 999,999,999,999 HEADING 'PGA Target|For Estimate (MB)'
    COL ptf FORMAT 990.90 HEADING 'PGA|Target|Factor'
    COL adv FORMAT a4 HEADING 'A.|S.'
    COL bp FORMAT 999,999,999 HEADING 'Bytes (Mb)|Processed'
    COL eebr FORMAT 999,999,999 HEADING 'Estimated|Bytes (Mb)|Processed'
    COL epchp FORMAT 999,999,999 HEADING 'Estimated|PGA %'
    COL eoc FORMAT 999,999,999 HEADING 'Estimated|Overalloc|Count'

    SELECT
    round(PGA_TARGET_FOR_ESTIMATE/1024/1024) ptfe
    , PGA_TARGET_FACTOR ptf
    , ADVICE_STATUS adv
    , round(BYTES_PROCESSED/1024/1024) bp
    , round(ESTD_EXTRA_BYTES_RW/1024/1024) eebr
    , ESTD_PGA_CACHE_HIT_PERCENTAGE epchp
    , ESTD_OVERALLOC_COUNT eoc
    FROM
    v$pga_target_advice
    ;

    prompt

    prompt

    Prompt – This view displays currently active SQL queries, that is, SQL queries of an

    Prompt – instance currently being executed with their work area memory allocation

    Prompt – behavior. Depending on the application, the display in this view is very

    Prompt – dynamic.

    column sid format 9999 heading "Id"
    column operation format a20 heading "Operation"
    column Esize format 999,999,999 heading "Expected|Size (Kb)"
    column Mem format 999,999,999 heading "Actual|Mem. Used (Kb)"
    column Max_Mem format 999,999,999 heading "Max.|Mem. Used (Kb)"
    column Pass format 99999 heading "Pass"
    column Tsize format 999,999,999 heading "Temp.|Seg. Size(Kb)"

    Select
    to_number(decode(SID, 65535, NULL, SID)) sid
    , operation_type OPERATION
    , trunc(EXPECTED_SIZE/1024) ESIZE
    , trunc(ACTUAL_MEM_USED/1024) MEM
    , trunc(MAX_MEM_USED/1024) Max_Mem
    , NUMBER_PASSES PASS
    , trunc(TEMPSEG_SIZE/1024) TSIZE
    From
    V$SQL_WORKAREA_ACTIVE
    Order
    by 1
    ,2
    ;

    prompt

    prompt

    Prompt – From a performance point of view, the aim is to configure the work area

    Prompt – large enough that no multi-pass executions are necessary.

    column los format 999,999,999 heading "Low Optimal|Size (Kb)"
    column hos format 999,999,999 heading "High Optimal|Size (Kb)"
    column oe format 999,999,999 heading "Optimal|Execution"
    column ope format 999,999,999 heading "One Pass|Execution"
    column mpe format 999,999,999 heading "Multi Pass|Execution"

    Select
    low_optimal_size/1024 los
    , (high_optimal_size+1)/1024 hos
    , optimal_executions oe
    , onepass_executions ope
    , multipasses_executions mpe
    From
    v$sql_workarea_histogram
    Where
    total_executions != 0
    ;

    – What is the percentage relationship between SQL queries of a
    – specific work area size in each case? How were the differently sized
    – queries processed in each case?

    column optc format 999,999,999 heading "Optimal Count"
    column optp format 9999 heading "Optimal %"
    column opc format 999,999,999 heading "One Pass Count"
    column opp format 9999 heading "One Pass %"
    column mpc format 9999 heading "Multi Pass Count"
    column mpp format 9999 heading "Multi Pass %"

    select
    optimal_count optc
    , round(optimal_count*100/total,2) optp
    , onepass_count opc
    , round(onepass_count*100/total,2) opp
    , multipass_count mpc
    , round(multipass_count*100/total,2) mpp
    from
    (select
    decode(sum(total_executions),0,1,sum(total_executions)) total
    , sum (optimal_executions) optimal_count
    , sum (onepass_executions) onepass_count
    , sum (multipasses_executions) multipass_count
    from
    v$sql_workarea_histogram
    where
    low_optimal_size > 1*1024 – for 1K optimal size
    );

    column profile format a40 heading "Profile"
    column cnt format 999,999,999 heading "Count"
    column percentage format 9999 heading "%"

    Select
    name profile
    , cnt
    , decode(total, 0, 0, round(cnt*100/total)) percentage
    From
    (Select name, value cnt, (sum(value) over ()) total
    From V$SYSSTAT
    Where name like 'workarea exec%')
    ;

    Prompt