'Oracle Admin Guide'에 해당되는 글 16건

  1. [Oracle] SKIP LOCKED for locked tables
  2. [Oracle] Memory Management
  3. [Oracle] Partitioning in Oracle 11g
  4. [Oracle] Database Replay
  5. [Oracle] New features in Performance and Resource Management
  6. [Oracle] New features in Fault Management
  7. [Oracle] Subdivisions of the SGA
  8. [Oracle] Initialization Parameters Affecting Memory Usage
  9. [Oracle] All About Oracle Latches
  10. [Oracle] Oracle Background Process




SKIP LOCKED

Oracle 11g는 데이터베이스의 다른 활성 세션에 고정되지 않은 테이블에서 레코드를 조회할 SKIP LOCKED절을 소개했습니다.

이것은 Exclusive Lock Mode와 매우 비슷합니다.


아래는 SKIP LOCKED에 관련된 예제입니다.

-- 이 테이블의 마스터 테이블 입니다.

select empno, ename, job, sal from emp order by sal;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            800
      7900 JAMES      CLERK            950
      7876 ADAMS      CLERK           1100
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250
      7934 MILLER     CLERK           1300
      7844 TURNER     SALESMAN        1500
      7499 ALLEN      SALESMAN        1600
      7782 CLARK      MANAGER         2450
      7698 BLAKE      MANAGER         2850
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7839 KING       PRESIDENT       5000

-- 1번 세션에서 아래와 같은 명령을 날리게 되면 SAL이 1000보다 높은 5행을 잠그는 것입니다.

SELECT empno, ename, job, sal FROM EMP where sal > 1000 and rownum <= 5 for update;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7499 ALLEN      SALESMAN        1600
      7521 WARD       SALESMAN        1250
      7566 JONES      MANAGER         2975
      7654 MARTIN     SALESMAN        1250
      7698 BLAKE      MANAGER         2850
-- 2번 세션에서 아래 명령어를 수행합니다.

SELECT empno, ename, job, sal FROM EMP where sal > 1000 and rownum <= 5 for update;
보통 세션은 행 세션 1로 부터 commit 또는 rollback 을 수행할 때까지 기다려야합니다. 그 대신, SKIP LOCKED 옵션 사용할 수 있습니다. 이 옵션 지금까지 기다리는 업데이트에 사용할 수 있는 다음의 5 항목을 검색합니다
SELECT empno, ename, job, sal FROM EMP where sal > 1000 and rownum <= 5 for update skip locked;

저작자 표시 비영리 동일 조건 변경 허락
신고

[Oracle] Memory Management




Automatic Memory Management (AMM)

Oracle 인스턴스는 PMON, SMON 그리고 시스템 글로벌 영역(SGA)와 프로그램 글로벌 영역(PGA)와 같은 메모리 영역으로 프로세스들의 모임입니다.

SGA 내에서 Database Cache, Large Pool 등 각각의 영역이 사이즈 결정은 Host의 메모리, OS에 따라 변경될 수 있어 결정이 힘들 수 있습니다.

오라클 데이터베이스 10g에서는 전체적인 SGA 메모리 영역에 대해 SGA_TARGET에 대한 특정값을 할당함으로써 단순화됩니다.

이러한 결정 사이즈가 Cache, Shared Pool등 하위 영역을 연속적으로 자동 조정합니다.


그러나, DB_CACHE_KEEP_SIZE 와 같이 일부 구성요소들은 10G에서 조정되지 않고 수동으로 조정해야 합니다.

그래서, 여전히 SGA와 PGA의 크기 결정에 있어서 몇 가지 사항을 체크 후 결정해야 합니다.


만약 PGA를 너무 많이 할당하고, SGA를 작게 설정한다면, 성능으로 인해 메모리 낭비가 있을 수 있습니다.

하지만, PGA와 SGA의 경계가 있다면 메모리 낭비를 잡을 수 있는 바람직한 기능일 것입니다.


Oracle Database는 Oracle 9i의 PGA_AGGREAGTE_TARGET 매개변수를 도입하여 PGA 관리를 자동화하였고, Oracle 10g의 SGA_TARGET 매개변수를 도입하여 SGA를 자동관리함으로써 효율적 메모리관리를 지향했습니다. Oracle 11g는 오라클 동적 SGA와 PGA를 모두 관리하기 위해서 사용하는 메모리의 청크를 할당 할 수 있도록 한 단계 더 나아갑니다.


자동 메모리 관리는 이 새로운 초기화 매개변수를 사용하여 구성됩니다.

● MEMORY_TARGET : 동적으로 SGA와 PGA를 제어할 때 사용하는 공유 메모리의 양. 이 매개변수는 동적이므로, 오라클에 사용할 수 있는 메모리의 총량은 증가하거나, MEMORY_MAX_TARGET 제한을 초과하지 않는 범위. 디폴트 값은 0 이다.

MEMORY_MAX_TARGET : 이 MEMORY_TARGET가 인스턴스 재 시작 없이 증가될 수 있는 최대 크기를 정의한다. MEMORY_MAX_TARGET가 지정되지 않는 경우, 디폴트 설정을 MEMORY_TARGET로 한다.


만약 UNIX/Linux 를 사용하는 경우 AMM 사용을 고려 하기 전에, Shared Memory File System (/dev/shm) 이 MEMORY_TARGET 및 MEMORY_MAX_TARGET 값을 수용할 수 있는 만큼의 크기를 가져야 합니다. 그렇지 않으면 오류가 발생합니다.


[oracle@oradba3 dbs]$ df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      36316784  19456684  15015264  57% /
/dev/hda1               101086      9632     86235  11% /boot
none                    517448    131448    386000  26% /dev/shm
-- 충분하지 않을 경우 아래와 같이 에러가 발생합니다.
ORA-00845: MEMORY_TARGET not supported on this system

Command Line에서 아래와 같이 설정 가능합니다.

SQL> alter system set memory_max_target = 1G scope=spfile;
SQL> alter system set memory_target = 1G scope = spfile;

이 예제는 어플리케이션에서의 요구로 변경하는 예제입니다.

이 결과는 V$MEMORY_DYNAMIC_COMPONENT 뷰에서 확인 할 수 있습니다.

SET linesize 150
SELECT SUBSTR (component, 1, 20) component, current_size, min_size, max_size,
       user_specified_size user_spec, oper_count, last_oper_type,
       last_oper_time
  FROM v$memory_dynamic_components
 WHERE current_size != 0;
 
-- Here is the Output
COMPONENT            CURRENT_SIZE   MIN_SIZE   MAX_SIZE  USER_SPEC OPER_COUNT LAST_OPER_TYP LAST_OPE
-------------------- ------------ ---------- ---------- ---------- ---------- ------------- --------
shared pool             360710144  327155712  360710144          0          3 GROW          14/01/06
large pool                4194304          0    4194304          0          1 GROW          14/01/05
java pool                 4194304    4194304    4194304          0          0 STATIC                
SGA Target             1073741824 1073741824 1073741824 1073741824          0 STATIC                
DEFAULT buffer cache    662700032  662700032  700448768          0          4 SHRINK        14/01/06
PGA Target              536870912  536870912  536870912  536870912          0 STATIC                

이 방법 외에 크기 조정 작업에 대한 정보를 V$MEMORY_RESIZE_OPS 뷰에서 확인할 수 있습니다.

SELECT   start_time, end_time, status, SUBSTR (component, 1, 20) component,                                                          
         oper_type op, oper_mode, SUBSTR (parameter, 1, 20) parameter,                                                               
         initial_size, target_size, final_size                                                                                       
    FROM v$memory_resize_ops                                                                                                         
   WHERE final_size != 0                                                                                                             
ORDER BY 1, 2;                                                                                                                       
                                                                                                                                     
-- Here is the Output                                                                                                                
START_TIME END_TIME   STATUS    COMPONENT            OP           OPER_MODE   PARAMETER            INITIAL_SIZE TARGET_SIZE FINAL_SIZ
                                                                                                                                     
2014-01-05 2014-01-05 COMPLETE  shared pool          STATIC                   shared_pool_size     0            327155712   327155712
2014-01-05 2014-01-05 COMPLETE  SGA Target           STATIC                   sga_target           0            1073741824  107374182
2014-01-05 2014-01-05 COMPLETE  DEFAULT buffer cache INITIALIZING             db_cache_size        700448768    700448768   700448768
2014-01-05 2014-01-05 COMPLETE  DEFAULT buffer cache STATIC                   db_cache_size        0            700448768   700448768
2014-01-05 2014-01-05 COMPLETE  PGA Target           STATIC                   pga_aggregate_target 0            536870912   536870912
2014-01-05 2014-01-05 COMPLETE  java pool            STATIC                   java_pool_size       0            4194304     4194304  
2014-01-05 2014-01-05 COMPLETE  DEFAULT buffer cache SHRINK       IMMEDIATE   db_cache_size        700448768    696254464   696254464
2014-01-05 2014-01-05 COMPLETE  large pool           GROW         IMMEDIATE   large_pool_size      0            4194304     4194304  
2014-01-05 2014-01-05 COMPLETE  DEFAULT buffer cache SHRINK       DEFERRED    db_cache_size        696254464    683671552   683671552
2014-01-05 2014-01-05 COMPLETE  shared pool          GROW         DEFERRED    shared_pool_size     327155712    339738624   339738624
2014-01-05 2014-01-05 COMPLETE  DEFAULT buffer cache SHRINK       IMMEDIATE   db_cache_size        683671552    679477248   679477248
2014-01-05 2014-01-05 COMPLETE  shared pool          GROW         IMMEDIATE   shared_pool_size     339738624    343932928   343932928
2014-01-06 2014-01-06 COMPLETE  DEFAULT buffer cache SHRINK       DEFERRED    db_cache_size        679477248    662700032   662700032
2014-01-06 2014-01-06 COMPLETE  shared pool          GROW         DEFERRED    shared_pool_size     343932928    360710144   360710144


저작자 표시 비영리 동일 조건 변경 허락
신고




Partitioning Upgrades

Oracle Partitioning은 다양한 애플리케이션의 관리성, 성능, 가용성을 개선하기 위한 Oracle Database 11g Enterprise Edition의 옵션입니다.

파티셔닝은 거의 모든 오라클 데이터웨어 하우스 쿼리의 성능을 향상시키기고, 일상적인 유지보수의 복잡성을 완하시켜 DBA의 부담을 줄여줍니다.

11g의 시작으로 더 많은 파티셔닝 옵션이 제공되어 SQL 구문의 관점에서 완전히 투명하게 구현되어, OLTP에서 데이터 웨어하우징에 이르기까지 어떤 애플리케이션도 파티셔닝을 적용할 수 있습니다.


오라클은 테이블 파티셔닝 기능을 오라클 8.0 버전에서부터 물리적인 파티셔닝을 최초로 지원하는 RDBMS의 벤더였습니다.

직접(파티션 함수 구현)은 아니지만 SQL Server 2005의 물리적 파티셔닝을 지원하는 반면 SQL Server (2000)과 DB2 둘다 (UNION ALL view 사용)만 논리 파티션을 제공합니다.


아래 표는 각 버전/릴리즈별 파티셔닝 기능의 진화 목록입니다.


Oracle Database Version

Partitioning Features

8.0.5

Introduced Range Partitioning

8i

Introduced Hash and composite Range-Hash partitioning.

9i

Introduced List Partitioning, Composite Range-List partitioning.

10G

Introduced Range, List and Hash partitioning of Index Organized tables. Also introduced other composite partitioning options.

11G

Introduced partition extensions:
 -
Interval partitioning
 -
REF partitioning
 -
Virtual Column-based partitioning
 -
Introduced Partition Advisor.



Partition Types

1) Range Partitioning

데이터분할 키의 값의 범위에 따라서 배분됩니다.

예를 들어, order_date를 기준으로 Orders 테이블을 파티셔닝 합니다.


2) Hash Partitioning

해시 알고리즘은 주어진 행에 대한 파티션을 결정하는 파티션 키가 됩니다.

이는 I/O Balancing을 제공하지만 범위 또는 부등식 쿼리에 사용될 수 없습니다.

예를 들어, customer_id를 기준으로 Orders 테이블을 파티셔닝 합니다.


3) List Partitioning

일정한 순서가 없는 값을 기준으로 파티셔닝 합니다.

예를 들어, country를 기준으로 Orders 테이블을 파티셔닝 합니다.


4) Composite Partitioning

데이터 배포 방법의 조합은 복합 파티션을 생성하는데 사용됩니다.

위에서 설명한 Range, Hash, List, Interval Partitioning 중 두 가지 테크닉을 조합하여 사용합니다.

Range_Hash, Range-List, Range-Range, List-Range, List-List, List-Hash 등이 있습니다.

예를 들어, Orders 테이블에 대해 order_date 기준으로 Range 파티셔닝을, Customer_id를 기준으로 Hash 서브파티셔닝을 수행합니다.

또, Orders 테이블에 대해 order_date를 기준으로 Range 파티셔닝을, 다시 shipment_date를 기준으로 Range 서브파티셔닝을 수행합니다.


인덱스 구성 테이블(인덱스와 데이터가 함께 저장되어 있는 테이블) 범위, 목록 또는 10g의 해시 파티셔닝을 지원합니다.

그러나, 복합 파티션은 인덱스 구성 테이블에서 지원하지 않습니다.


5) Interval Partitioning

11g에서부터 도입되었으며, Interval 파티션은 Range 파티션의 확장입니다.

이들은 동등 크기의 Range 파티션에 대한 자동화를 제공합니다. 또, 파티션은 메타 데이터로 만들어집니다만 시작 파티션은 지속됩니다.

데이터가 도착하는대로 추가 세그먼트가 할당됩니다. 추가 파티션 및 지역 인덱스가 자동으로 생성됩니다.

예를 들어, 일정 간격(월, 주, 분기 또는 년)으로 분류되는 날짜를 기준으로 동적으로 분할 할 수 있습니다.


6) Partitioning On Virtual Columns

쿼리할 때 가상 Column은 일반 테이블의 Column처럼 보입니다. 하지만 그 값은 디스크에 저장되지 않고 파생됩니다.

이것은 가상의 열 값을 기준으로 테이블을 분할하는 것이 가능합니다.


7) Partitioning By Reference

또 다른, 파티셔닝 기법은 트랜잭션을 포함하는 다른 파티션 테이블의 항목에 디테일한 트랜잭션 관계를 기반으로 세부 트랜잭션을 포함하는 테이블을 분할 할 수 있는 기능입니다.

하나의 송장(마스터)에 직접 해당 송장 라인 항목(세부 항목)의 집합 사이의 관계는 일반적인 비즈니스 예입니다.

Oracle Database 11g는 자동으로 두 개의 테이블에 마스터 및 세부 행 사이의 관계를 설정하고 Foreign Key 제약조건에 따라 적절한 하위 파티션에 세부 테이블의 데이터를 배치합니다.

이 마스터 테이블의 파티셔닝이 상세 테이블의 파티션에서도 구동하기 때문에 두 테이블에 서로 다른 파티션을 설정할 필요가 없습니다.


저작자 표시 비영리 동일 조건 변경 허락
신고

[Oracle] Database Replay




Database Replay

Database Replay 기술은 프로덕션 시스템에서 Workload를 캡처하고 정확한 타이밍, 동시성, 원래의 워크로드의 트랜잭션 특성을 가진 테스트 시스템에서 이를 재생하는 데이터베이스 재생 기술입니다. 이는 프로덕션 시스템에 영향을 주지 않고, 시스템의 변화를 테스트 할 수 있습니다.

캡처 된 Workload는 서로 다른 두 시스템을 비교하는데 유용합니다.

이는 독특한 접근 방식을 사용하여 캡처하여 데이터베이스 변경을 만들기 위해 하고 싶은 것을 수행할 수 있습니다.

SQL 레벨 하단에서 발생하는 모든 데이터베이스 액티비티를 바이너리 포맷에 저장하고 동일한 데이터베이스 또는, 상이한 데이터베이스에서 재생 가능합니다.

또한, 특정 유형의 활동을 포함하는 캡처 프로세스를 정의, 제외 할 수 있습니다.


Database Replay는 Oracle Database 10gR2와 새로운 릴리즈를 실행하는 시스템에서 Workload 캡처를 지원합니다.

Oracle Database 10gR2를 실행하는 시스템의 Workload를 캡처하기 위해, 데이터베이스 버전 10.2.0.4 이상이 되어야 합니다.

또, Workload Replay는 Oracle Database 11gR1 및 새로운 릴리즈를 실행하는 시스템에서 지원됩니다.


Oracle Database 11g Real Application Testing (RAT)은 Database ReplaySQL Performance Analyzer 두 가지를 제공합니다.

이 두 도구의 주요 차이점은 참여 범위입니다. 

Database Replay는 데이터베이스에 있는 모든 활동(일부 필터링에 따라)의 캡처 및 재생에 적용되는 반면,

SQL Performance Analyze는 특정 SQL 문을 캡처하고 재생할 수 있습니다. SQL Performance Analyze를 사용하는 동안 Database Replay에서 캡처된 특정 SQL을 참조하거나, 액세스 할 수 없습니다.

응용 프로그램에 의해 추출된 SQL문을 조정하고 그 Performance를 평가할 수 있기 때문에, 후자에는 SQL 튜닝에 상당한 이점을 제공합니다.


 

- 유저가 데이터베이스의 액티비티를 기록하는 캡처 프로세스를 가동합니다.

- 프로세스가 /capture directory/라는 이름의 디렉토리에 캡처파일(capture file)이라는 특수한 바이너리 파일을 생성하고 이곳에 데이터베이스 액티비티를 기록합니다.

- 유저가 캡처하고 하는 일정 시간이 지난 후, 캡처 프로세스를 중단합니다. 그 후, 테스트 시스템의 /replay directory/ 디렉토리로 이동합니다.

- 유저가 Replay 프로세스를 가동하면 Replay Client가 Replay 작업을 시작합니다.

- 테스트 환경의 시스템이 프로덕션 시스템과 동일하게 구성됩니다.



Database Replay 사용시 장점

먼저, 유저에게 직접적인 SQL 구문을 요구하지 않습니다. 또 SQL 하부의 모든 액티비티를 기록할 수 있으므로, 성능 문제의 원인이 될 수 있는 중요한 작업들이 테스트시 누락될 위험이 없습니다. 마지막으로는 사용자, 프로그램, 일정기간을 기준으로 선택적으로 캡처/재생 이 가능하여 전체 워크로드가 아닌 문제가 되는 워크로드만 캡처가 가능합니다.

예를 들어, 월말 이자 계산 프로그램이 문제를 일으키는 것이 원인이 된다 가정했을 때, 월말 프로그램이 실행되는 동안 워크로드를 캡처하여 테스트 시스템에서 매개 변수의 변화를 확인한 다음 그 테스트 시스템에서 캡처 파일을 재생할 수 있습니다. 성능이 향상되는지, 테스트할 수 있습니다. 이 작업은 프로덕션 데이터베이스의 영향을 주지 않습니다.


또, 데이터베이스 업그레이드 시 여러가지 테스트가 필요합니다. 실제 운영에 있어서 해당 트랜잭션이 버전이 업그레이드 되면서 문제가 될 가능성이 다수 존재합니다.

Oracle 10gR2에서 11g로 업그레이드 이전, Database Replay 기술을 이용하여 실제 운영중인 트랜잭션을 11g에서 테스트 할 수 있습니다. 이로써 업그레이드 시 나타날 수 있는 문제점을 미리 체크할 수 있습니다.



Database Replay Processing Workflow

1) Workload Capture

Database Replay를 사용하는 첫 단계로 Production Workload를 Capture하는 일입니다.

Workload Capture가 시작되면, 모든 Oracle Database Client의 요청까지도 모두 바이너리 파일에 기록합니다.

유저는 Capture File이 저장될 위치를 지정할 수 있습니다.

Capture File에는 SQL text, bind values, transaction 정보 등이 포함됩니다.

하지만, Background 작업이나, Scheduler Job 등을 기록되지 않습니다. 이러한 캡처 파일은 독립적이며, 다른 시스템으로 전송 될 수 있습니다.

전체 Workload가 아닌, 특정 시점대의 Workload만 Capture 가능합니다.


2) Workload Preprocessing

Workload가 캡처 된 후에 이 파일의 데이터를 미리 변환하고 Workload Replay에 필요한 모든 metadata가 생성됩니다.

한번 처리후의 Workload는 Oracle Database의 동일한 버전에서 반복적으로 Replay 할 수 있습니다.

일반적으로 캡처 파일은 다른 시스템에 복사해야 합니다. Workload의 사전 처리 시간이 소요 될 수 있으며, 작업 시 부하가 나타남으로써 테스트 시스템에서 수행하는 것이 좋습니다.


3) Workload Replay

작업이 모두 처리 된 후, 테스트 시스템에서 Replay 될 수 있습니다.

Oracle Database는 동일한 타이밍, 동시성 및 생산 시스템의 트랜잭션 종속성이 캡처 된 모든 외부 클라이언트 요청을 다시 작성하여 테스트 시스템의 Workload 캡처 단계에서 기록된 작업을 수행할 수 있습니다.

Workload를 캡처하는 동안 기록 된 모든 외부 클라이언트 요청을 다시 만들수 있는 재생 클라이언트라는 클라이언트 프로그램을 사용합니다.

캡처 된 Workload에 따라 적절하게 Workload를 재생하기 위해 하나 이상의 재생 클라이언트를 사용해야 할 수도 있습니다.

Calibration Tool(교정도구)는 특정 작업 부하에 필요한 재생 클라이언트의 수를 결정하는데 도움을 주기 위해 제공됩니다.

DML 및 SQL Query Replay에서는 이 데이터가 캡처 시스템에서 데이터에 논리적으로 유사해야 합니다. Calibration Tool은 이 차이를 최소화하고 Replay를 신뢰할 수 있는 분석을 도와줍니다.


4) Analysis and Reporting

Workload Replay 후에는 Workload의 Capture 및 Replay의 상세한 분석을 수행하기 위한 심층적인 Report가 제공됩니다.

Report Summary는 DML 또는 SQL 쿼리에 의해 반환된 행의 재생 및 데이터 분기 중 발생하는 오류와 같은 Workload의 캡처 및 재생에 대한 기본정보를 제공합니다.

데이터베이스의 시간, 평균 활성 세션 및 Workload의 캡처 및 Workload Replay와 Call 사이에 사용자와 같은 몇 가지의 예가 소개됩니다.

고급 분석을 위해 AWR(Automatic Workload Repository) 보고서는 Workload의 캡처 및 Workload Replay 사이의 성능 및 통계의 자세한 비교를 가능하게 합니다.

이 보고서에서 사용할 수 있는 정보는 매우 상세하고, Workload의 캡처 및 재생 사이에 약간의 차이가 발생할 수 있습니다.

응용 프로그램 수준의 검증을 위해, 전반적인 Replay 성공을 평가하는 Script를 개발 하는 것이 좋습니다.

Replay가 완료되면, Workload 포착 시 원래의 상태로 데이터베이스를 복원하고 작업 디렉토리 개체가 다른 물리적 위치로 백업하면 시스템에 따른 변경 사항을 테스트하기 위해 Workload Replay를 반복 할 수 있습니다.



SYSDATE 변경

SQL에 sysdate가 capture시와 replay가 서로 다른 경우 SQL 결과 값에 Divergency가 발생 됩니다. 이는 DB Replay시 SQL 문장안의 sysdate가 capture시의 SYSDATE_TIME이 아닌 Replay 시의 SYSDATE_TIME으로 계산되었기 때문입니다. 이 문제는 System Time을 해당 과거 시점으로 변경하면 문제가 해결됩니다.

저작자 표시 비영리 동일 조건 변경 허락
신고



New features in Performance and Resource Management

1) Automatic SQL Tuning

자동 SQL 튜닝의 일환으로, 오라클 11g는 Oracle 10g에서보다 Automatic Tuning Advisor의 성능이 향상되었습니다.

SQL 프로파일 형태로 튜닝을 제안합니다. Automatically apply SQL profiles는 기존의 프로파일링 기능보다 3배 이상의 성능을 기대할 수 있습니다.

성능 비교는 User-Specified Maintenance창에서 할 수 있습니다.


2) Access Advisor

오라클 11g 액세스 관리자는 파티셔닝에 대한 Advice을 포함하여 새로운 인터널 파티셔닝에 대한 Advice를 제공합니다.

인터널 파티셔닝은 필요할 때 똑같은 크기의 새로운 파티션이 자동 생성되는 Range Partitioning 버전입니다.

범위 파티셔닝과 인터널 파티셔닝 모두 하나의 테이블에 존재할 수 있으며, 범위 파티셔닝 테이블은 인터널 파티셔닝 테이블으로 변환할 수 있습니다.


3) Automatic Memory Tuning

오라클 9i 에서는 Automatic PGA Tuning이 소개 되었습니다.

오라클 10g 에서는 Automatic SGA Tuning이 소개 되었습니다.

오라클 11G 에서는 이 모든 메모리가 하나의 매개 변수를 설정하여 자동으로 조정할 수 있습니다.

오라클은 이 매개변수를 가지고 PGA, SGA 및 OS 프로세스에 얼마나 자원이 사용되는지 산정해 낼 수 있습니다. 또, 이것에 대해 최대 값과 최소값을 설정할 수 있습니다.


4) Resource Manager

오라클 11g Resource Manager는 CPU 뿐만 아니라, I/O를 관리 할 수 있습니다.

특정 파일, 파일 타입 또는 ASM Disk Group과 연관된 우선 순위를 설정 할 수 있습니다.


5) ADDM

오라클 11g ADDM은 인스턴스 수준에서 뿐만 아니라, 전체 RAC(데이터베이스 레벨)에 대한 Advice를 제공합니다.

Ignore Issues는 ADDM 지침에 추가 가능하도록 기능이 향상 되었습니다.

예를 들어, 더 많은 메시지를 원한다거나, 이미 문제점을 파악하고 있는 점에 대해 다시 언급하지 않도록 ADDM에 요청할 수 있습니다.


6) AWR Baselines

오라클 11g의 AWR Baselines는 다른 기능을 사용하기 위해 베이스 라인을 자동적으로 생성할 수 있도록 확장되었습니다.

Rolling Week를 기준으로 자동적으로 생성됩니다.


7) Adaptive Metric Baselines

기존 10g의 Notification thresholds는 고정된 값을 기준으로 하였습니다.

하지만, 오라클 11g의 Notification thresholds는 baseline과 관련될 수 있습니다. 그래서 Notification thresholds는 baseline에 맞춰 하루종일 변경됩니다.


저작자 표시 비영리 동일 조건 변경 허락
신고




New features in Fault Management

1) Automatic Diagnostic Repository (ADR)

기존의 Dump File, Trace File, Alert Log 그리고 오라클의 여러가지 Report와 같이 File Base로 생성되는 로그들의 집합소 입니다.

이 저장소 역시 File Base입니다. 이 기능을 위해 Oracle 11g 에서는 기존의 Dump Destination의 구조를 변경하였습니다.

중요한 오류가 발견 될 때, 자동으로 Incident를 만들 수 있습니다. 사건에 관한 정보를 자동으로 캡처되어, DBA 에게 Message 혹은 특정 Health Check를 자동으로 실행합니다.

ADR은 기본적으로 저장소 이기 때문에 log, dump, trace와 같은 다양한 로그(Diagnostics Log) 뿐만이 아니고 RAC와 같은 다수의 Instance 그리고, agent, module, component들이 쏟아내는 로그들도 한꺼번에 관리가 가능하도록 일원화하여 구현하고 있습니다.


2) Incident Packaging Service (IPS)

IPS는 사건 패키지 서비스로 Incident 정보를 Packaging(포장/압축) 하는 것을 의미합니다.

사건에 대한 모든 정보를 특정 폴더에 특정 Incident 정보를 압축 파일로 만들어 필요한 경우 추가 테스트 및 정보를 요청하고, Oracle Support에 전체 패키지를 보낼 수 있습니다.


3) 기능 기반의 패치 (Feature Based Patching)

이것은 영향을 미치는 기능을 할 모든 일회성 패치를 분류합니다. 쉽게 패치를 사용하는 기능에 대한 필요를 식별할 수 있습니다.

EM은 기능을 기반으로 패치 서비스에 가입할 수 있도록 하고, EM 자동으로 사용하는 기능에 대한 사용 가능한 패치를 검색합니다.



저작자 표시 비영리 동일 조건 변경 허락
신고



Subdivisions of the SGA

SYS> show sga

Total System Global Area 1073741824 bytes
Fixed Size                  2089400 bytes
Variable Size             645926472 bytes
Database Buffers          419430400 bytes
Redo Buffers                6295552 bytes

1) Total System Global Area

- SGA를 구성하는 모든 하위 부분의 Byte의 합입니다.

2) Fixed Size

- Background Processes가 액세스해야하는 데이터베이스의 상태와 인스턴스에 대한 일반적인 정보가 포함됩니다.

- 사용자 데이터는 여기에 저장되지 않습니다.

- 이 영역의 크기는 10K보다 일반적으로 작습니다.

3) Variable Size

- 이 부분은 initSID.ora 파일의 영향을 받습니다. (SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE)

4) Database Buffers

- 데이터 파일에서 읽은 데이터 블록의 사본을 보유하고 있습니다.

- 최소 값은 데이터베이스 블럭 크기의 4배 이상으로 지정되어 있습니다.

- SIZE = DB_BLOCK_BUFFERS * BLOCK 

5) Redo Buffers

- 데이터베이스 변경 내용에 대한 정보를 보유하고 있는 버퍼입니다.


SGA 크기 산정

오라클에서는 이전 릴리스에서와 같이 구성 또는 동적으로 구성이 가능합니다.
동적 SGA의 크기는 DB_BLOCK_SIZE, DB_CACHE_SIZE, SHARED_POOL_SIZE 및 LOG_BUFFER 파라미터 값에 의해 결정됩니다.
오라클의 시작됨과 동시에 SGA 인프라는 동적입니다. 이 인스턴스가 실행되는 동안 다음과 같은 기본 매개 변수로 SGA가 변경 될 수 있습니다.

1) Buffer Cache (DB_CACHE_SIZE)

- 표준 블록의 캐시 크기(Byte)

2) Shared Pool (SHARED_POOL_SIZE)

- 공유 SQL 및 PL/SQL문의 STATEMENTS를 저장하기 위한 공간입니다.

3) Large Pool (LARGE_POOL_SIZE) - Default 0 Byte

- 세션 메모리, 메시지 버퍼에 대한 병렬 실행을 위한 공유 서버 시스템에서 사용되는 Shared Pool, Backup 및 디스크 I/O 버퍼 백업 프로세스에 사용됩니다.


리두 로그를 다시 실행 항목을 버퍼링 할 때, LOG_BUFFER 매개 변수가 사용됩니다. 이는 정적 파라미터이고 SGA의 매우 작은 부분을 나타냅니다.

단지 초기화 매개 변수 파일(initSID.ora)에서 이 매개 변수 변경 값을 읽어 변경하며, 데이터베이스의 재가동이 필요합니다.

MAX_SGA_SIZE 파라미터를 동적으로는 바꿀 수 없으나, SGA를 구성하는 부문의 크기를 동적으로 변경 할 수 있습니다. DB_CACHE_SIZE, SHARED_POOL_SIZELARGE_POOL_SIZE는 데이터베이스 운영 중간에도 변경이 가능합니다.

최적의 Cache 크기를 유지하기 위해서, DB_CACHE_ADVICE 파라미터(동적 파라미터)를 이용하여, Cache 크기의 변화에 따른 변동 사항을 V$DB_CACHE_ADVICE 뷰를 통해 조회할 수 있습니다.

DB_CACHE_ADVICE 파라미터는 ALTER SYSTEM ...SET 절을 사용하여, 동작하게 설정 할 수 있습니다.


SGA 크기를 산정하기 위해서는 다음과 같은 공식을 사용합니다.

DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS + 1MB


참고로 사용중인 각각의 DB_nk_CACHE_SIZE 크기를 더해야 합니다. DB_nk_CACHE_SIZE는 (2, 4, 8, 16, 32K) 블럭에 대해 4개까지 지정 가능합니다. 이중 하나는 기본 Block Size로 지정 될 것이고, 그 크기는 DB_CACHE_SIZE에 별도로 지정하게 됩니다.


저작자 표시 비영리 동일 조건 변경 허락
신고




DB_BLOCK_BUFFERS (or DB_CACHE_SIZE)

이 매개변수의 자세한 내용은 데이터베이스 Buffer Cache 절을 참조하면 됩니다.

DB_BLOCK_BUFFERS는 버퍼 캐시 데이터베이스 버퍼의 수를 지정합니다.

이 파라미터는 DB_BLOCK_SIZE와, 버퍼 캐시의 전체크기를 결정합니다.

데이터베이스 버퍼 캐시의 실제 크기를 확인하려면, DB_BLOCK_SIZE에 의해 이 매개변수를 곱합니다.

데이터베이스 버퍼 캐시의 전체 크기는 SGA의 3분의 2정도의 크기가 되어야 합니다.

버퍼 캐시의 효과적인 사용은 크게 데이터베이스의 I/O 부하를 줄일 수 있습니다.

DB_BLOCK_SIZE는 데이터베이스를 처음 생성할 경우에만 설정할 수 있습니다. 그래서 버퍼 캐시의 크기를 제어할 수 있는 DB_BLOCK_BUFFERS를 사용합니다.


SHARED_POOL_SIZE

이 매개변수의 자세한 내용은 Shared Pool 절을 참조하면 됩니다.

SHARED_POOL_SIZE는 Shared Pool의 크기, Shared Pool의 커서, Stored Procedures, 제어 구조 및 기타 구조를 포함하고 있습니다.

SHARED_POOL_SIZE는 Byte 단위로 지정됩니다. 

만약, false로 PARALLEL_AUTOMATIC_TUNING을 설정하면 오라클은 Shared Pool에서 병렬 실행 메시지 버퍼를 할당합니다.

큰 값은 다중 사용자 시스템에서의 성능을 향상시킵니다. 이 값이 작을수록 더 적은 메모리를 사용합니다.

또, 바로 위의 DB_BLOCK_BUFFERS(or DB_CACHE_SIZE)처럼, 너무 낮게 설정하지 않아야 합니다. 만약 너무 낮게 설정할 경우에도 DB_BLOCK_BUFFERS(or DB_CACHE_SIZE)에 의해 할당 된 메모리를 이용할 수 없습니다.

관리자는 뷰 V$SGASTAT를 조회하여 Shared Pool을 모니터링 할 수 있습니다.


SORT_AREA_SIZE

이 매개변수는 바이트 단위로 지정되며, 오라클은 데이터 정렬을 수행하는 모든 사용자 프로세스에 SORT_AREA_SIZE의 크기 값으로 정렬 영역을 할당합니다.

정렬 영역(SORT_AREA_SIZE)는 SGA의 외부에 할당 된 메모리입니다.

SORT_AREA_SIZE 이 값이 클수록 메모리에 수행 할 수 있는 더 많은 종류를 허용합니다.

기본 값은 대부분의 OLTP 작업에 적합합니다. 의사결정시스템, 일괄 작업, 대량의 CREATE INDEX 작업등에 의해 이 파라미터를 변경 후 작업할 수 있습니다.

다만, SORT_AREA_SIZE를 너무 작게 설정하면 프로세는 사용자의 TEMP tablespace에 정렬하도록 강요합니다.


SORT_AREA_RETAINED_SIZE

이 매개변수는 바이트 단위로 지정되며, SORT_AREA_SIZE 매개 변수와 함께 사용됩니다.

초기화 파라미터 파일에서 지정하는 Sort 작업 관련된 파라미터로 이 값은 Sort가 끝난 후에도 유지하고자 하는 SORT_AREA_SIZE를 나타냅니다.

이 매개변수는 SORT_AREA_SIZE보다 작거나 동일하게 설정해야 하는데, 그 이유는  Sort 작업을 수행하기 위해 할당된 메모리 영역이 Sort 작업이 끝난 후가 아니라 세션이 종료될 때까지 유지될 수 있기 때문입니다.

일반적으로 권고되는 SORA_AREA_SIZE 값은 65K~1M 사이입니다. 이 매개변수들은 필요 이상으로 할당 시 낭비 가능성이 있습니다.

PGA_AGGREGATE_TARGET 초기화 파라미터를 사용하면 PGA용으로 할당된 전체 메모리 용량의 상한치를 정할 수 있습니다.

이 때에는 개개의 서버 프로세스의 필요량에 따라 PGA가 할당됩니다.


SHARED_POOL_RESERVE_SIZE

이 매개변수는 바이트 단위로 지정되며, 큰 객체를 저장하기 위한 공유 풀의 일부를 할당하여 제어합니다.

이 매개변수의 기본 설정은 SHARED_POOL_SIZE 설정의 5%입니다. 최대 10%이내로 설정하는 것을 권장합니다. 50%를 넘게 되면 startup시 에러가 발생합니다.

SHARED_POOL_RESERVE_SIZE는 Shared Pool 내에 Fragmentation이 나지 않은 일정 공간을 사용합니다. PL/SQL Compilation 이나 Trigger Compilation과 같은 Large allocation에 사용합니다.

상대적으로 낮은 값으로 설정하면 SHARED_POOL_RESERVE_SIZE, SHARED_POOL_SIZE를 늘려야 합니다. 그렇지 않으면 Reserved List에서 할당된 Memory수를 줄이게 됩니다.


SHARED_MEMORY_ADDRESS & HI_SHARED_MEMORY_ADDRESS

이 매개변수는 시스템 글로벌 영역(SGA)의 런타임시 시작 주소를 지정합니다.

이 매개변수는 Linktime에서 SGA의 시작 주소를 지정하는 많은 플랫폼에서 무시됩니다.

32비트 플랫폼의 전체 주소를 지정하고 64비트 플랫폼의 64비트 주소의 하위 32비트를 지정하려면 이 매개변수를 사용합니다.

64비트 플랫폼에서 64비트 주소의 상위 32비트를 지정하는 경우는 HI_SHARED_MEMORY_ADDRESS 매개변수를 사용합니다.

플랫폼의 위치 값에 두 매개변수 모두 0또는 지정하지 않는 경우는 SGA 주소가 기본값으로 사용됩니다.


LOG_BUFFER

이 매개변수는 리두 로그 파일을 다시 실행 항목을 버퍼링 할 때 오라클이 사용하는(Bytes) 메모리 양을 지정합니다.

오라클 10g부터 도입된 ASMM(Automatic Shared Memory Management) 기능은 Oracle SGA의 크기를 동적으로 관리하는 역할을 제공합니다.

하지만, Redo Buffer, Keep Cache, Recycle Cache, 다중 블록 크기에 의한 Cache 등의 크기는 ASMM 영역에 속하지 않습니다.

따라서, Redo Buffer의 크기는 여전히 LOG_BUFFER 파라미터를 이용해서 수동으로 지정해주어야 합니다.


JAVA_POOL_SIZE

이 매개변수는 런타임 실행동안 할당되는 자바 풀의 크기를 지정합니다.

이 메모리는 Java 메소드와 클래스 정의의 공유 메모리 내 표현뿐만 아니라, 자바 세션 공간으로 마이그레이션되는 자바 객체가 포함되어 있습니다.


LARGE_POOL_SIZE

이 매개변수는 Large Pool의 할당 Heap 크기를 지정합니다.

Large Pool Heap이 메시지 버퍼에 대한 병렬작업을 실행하며, Shared System 에서 세션 메모리에 사용되며, 디스크 I/O 버퍼 백업 프로세스에 사용됩니다.

Parallel 실행은 SGA_TARGET에 설정되어 있는 경우에만 Large Pool에서 버퍼를 할당합니다.


BUFFER_POOL__KEEP & BUFFER_POOL_RECYCLE

이 매개변수는 버퍼 풀로의 총 수(DB_BLOCK_BUFFERS 매개 변수의 값)의 일부를 따로 설정하여 버퍼 캐시에 객체를 저장할 수 있습니다.

또한 KEEP 버퍼풀에 LRU 래치의 총 수의 지정된 부분을 할당 할 수 있습니다.

문자열 값은 두 가지 형태 중 하나를 취할 수 있습니다. 사용자는 단순히 버퍼의 수를 지정할 수 있습니다.

또는, 버퍼와 LRU 래치의 조합을 지정할 수 있습니다. LRU 래치를 지정하는것은 선택 사항입니다.

이 매개변수는 BUFFER_POOL_RECYCLE 매개변수와 함께 사용하는 것이 가장 유용합니다.

-- 버퍼의 수 지정
BUFFER_POOL_KEEP = 5

-- LRU 래치의 조합 지정
BUFFER_POOL_KEEP = (BUFFERS:400, LRU_LATCHES:3)

DB_BLOCK_LRU_LATCHES

이 매개변수는 LRU 래치 세트의 최대수를 지정합니다.

이 값은 기본적으로 Oracle 8.0 이전은 CPU_COUNT/2, Oracle8i의 경우 CPU_COUNT, Oracle9i의 경우 CPU_COUNT*4 입니다. (Buffer Cache의 크기에 따라 변경 될 수 있습니다.)


저작자 표시 비영리 동일 조건 변경 허락
신고




What Are Latches ?

래치는 오라클의 공유 메모리(SGA) 영역을 보호하는 직렬화 매커니즘 입니다.

래치는 락보다 매우 신속하게 획득하고 해제할 수 있는 잠금의 유형입니다.

락은 보호되어진 데이터에 대해 한개 이상의 프로세스에게 접근을 허용하지만, 래치는 오직 한개의 프로세스만의 접근이 가능합니다.

락은 Shared Mode에서 제공되며, 래치는 Exclusive Access만 가능합니다.


Latches vs Enqueues ?

Enqueue는 오라클의 또 다른 Locking 유형입니다.

오라클은 특정 오브젝트를 사용하기 위해 반드시 해당 오브젝트를 보호하는 Enqueue락을 획득해야 합니다. 만약 획득하지 못한다면, Enqueue 이벤트를 대기합니다.

Enqueue 대기 이벤트는 오라클 9i 까지만 사용되며, 오라클 10g 부터는 Enqueue 락에 의한 대기현상들은 모두 개별 대기 이벤트로 나뉘어서 정의되었고, 락 경합이 발생하는 상황별로 세세히 나눠졌습니다.

V$SESSION_WAIT 뷰나 V$SESSION 뷰를 이용하면 현재 어떤 락에 대한 경합이 발생했는지 파악이 가능합니다.

EVENT 컬럼의 값이 "enqueue" 이거나 "enq: XXX"이면 Enqueue 락에 의한 경합이 발생한다는 의미입니다.


How Latches Work ?

오라클은 SGA 구조를 보호하기 위해서 래치를 사용합니다.

SGA는 로컬 인스턴스 단위로 존재하므로 래치는 해당 인스턴스에서 관촬 가능합니다.

래치 획득 방식에는 Willing-To-Wait No-Wait 방식 2가지가 있습니다.

대부분의 래치 획득은 Willing-To-Wait 모드를 사용하지만 현재 다른 래치들을 보유하고 있는 프로세스가 현재 보유한 래치 중 가장 최근에 획득한 래치보다 더 낮거나 같은 레벨의 래치를 획득하고자 할 때는 No-Wait 모드를 획득을 시도합니다.

이 두가지 모두 사용하는 이유는 래치의 데드락을 방지하기 위해서 입니다.

가장 최근에 획득한 래치의 레벨과 같거나 낮은 레벨의 래치를 획득할 때는 No-Wait 모드를 요청하고 가장 최근에 발생한 래치의 레벨 보다는 높은 레벨의 래치를 획득하고자 할 때는 Willing-To-Wait 모드를 요청합니다. 래치의 데드락 발생시 ORA-600에러가 발생됩니다.


Latches Level ?

래치 획득 과정에서 데드락을 방지하기 위해 모든 래치에 레벨을 부여 합니다.

레벨은 0~13까지 14가지의 값이 존재하며 버전마다 상이한 차이를 보일 수 있습니다. Child Latch는 항상 Parent Latch와 동일한 레벨 값을 같습니다.


Willing-To-Wait Mode ?

1) Willing-To-Wait Mode by Spin : WMS

1) Latch Get : 1번 프로세스가 해당 래치에 할당된 프로세스가 없을 경우 래치를 획득합니다.


2) Latch Miss : 2번 프로세스는 해당 래치에 프로세스(1번 프로세스)를 획득하고 있으므로 래치 획득에 실패 합니다.


3) SPIN(멀티 프로세스 환경) : 초기화 매개변수 _SPIN_COUNT의 수 만큼 SPIN을 수행합니다. SPIN의 기본값은 2000이며, 이러한 루프 후  다시 래치 획득을 시도합니다. 이러한 대기 응답 시간 및 처리량에 시스템은 영향을 받을 수 있습니다.


4) Sleep : 2번 프로세스가 _SPIN_COUNT 수 만큼 SPIN 후 새로운 래치 시도에도 실패를 하게 되면 Sleep 상태에 빠지게 됩니다.


5) Wake : 2번 프로세스는 일정한 시간이 지난 후에, 새롭게 래치 획득을 시도합니다.



2) Willing-To-Wait Mode by Posting : WMP

1) Latch Get : 1번 프로세스가 해당 래치에 할당된 프로세스가 없을 경우 래치를 획득합니다.


2) Latch Miss : 2번 프로세스는 해당 래치에 프로세스(1번 프로세스)를 획득하고 있으므로 래치 획득에 실패 합니다.


3) Wait List 등록 : 2번 프로세스를 래치 획득 대기 리스트에 등록합니다.

4) Done Posting : 1번 프로세스가 래치를 획득 할 때 래치 획득 대기 리스트에 알립니다.


5) Get Latch : 래치 획득 대기 리스트에 대기중인 프로세스 중 하나의 프로세스가 Latch를 획득합니다. 이 때 프로세스 할당 순서는 보장 되지 않습니다.


No-Wait Mode ?

1) No-Wait Mode : WMN

현재 다른 래치들을 보유하고 있는 프로세스가 현재 보유한 래치 중 가장 최근에 획득한 래치보다 더 낮거나 같은 레벨의 래치를 획득하고자 할 때 사용됩니다.

No-Wait 모드의 래치 획득에 실패하면 래치보다 높은 레벨의 래치는 모두 해제하고 올바른 순서로 다시 시도합니다.


1) Get Latch Lev1 : 1번 프로세스가 Level 1 래치를 획득합니다.

2) Get Latch Lev2 : 2번 프로세스가 Level 2 래치를 획득합니다.


3) Try Get Q2 : 1번 프로세스가 Level 2 래치 획득을 시도하지만 2번 프로세스가 획득하고 있으므로 래치 획득에 실패하고 대기합니다.


4) SPIN : 1번 프로세스는 Q2를 얻기 전에 SPIN을 수행합니다.


5) Try Get Q1 : 2번 프로세스가 WMN 모드로 Q1을 획득하려 시도합니다. (래치 레벨이 현재보다 낮기 때문에 WMN 모드로 동작합니다.) 하지만, 1번 프로세스가 획득하고 있으므로 래치 획득에 실패하고 대기합니다.


6) Release Latch : 현재 획득하려는 1번 프로세스가 가장 최근에 획득한 래치 보다 낮은 래치를 보유중이므로 2번 프로세스는 Q2를 반환하고 다시 Q1 획득을 시도합니다. (데드락 방지)


7) Get Latch Q2 : 1번 프로세는 SPIN 후 다시 Q2를 획득하려 하고, 현재 2번 프로세스가 Q2 래치를 해제한 상태이므로 획득이 가능하며 1번 프로세스는 모든 래치를 획득하였으므로, 모든 트랜잭션을 마무리하고 종료합니다.


8) 2번 프로세스는 Q2를 반환하고 다시 Q1을 획득하려 할 것이고, 1번 프로세스가 종료 되었다면 모든 래치를 점유할 수 있습니다.



특정 래치의 경합 원인

만약 필요한 래치가 Busy 상태인 경우, SPIN을 요청하는 경우, 다시 시도하지만 여전히 사용할 수 없을 경우, 다시 SPIN 합니다.

루프는 초기화 매개 변수 _SPIN_COUNT에 의해 결정 되며, 이 값의 최대 수까지 반복됩니다.

만약 이 전체 루프를 마치고도 아직 래치를 사용 할 수 없는 경우, 프로세스는 CPU를 양보하고 Sleep 상태에 빠지게 됩니다.

이 상태일 경우 CPU를 사용하게 됩니다. CPU 사용률은 SPINNING의 결과입니다.

SPINNING는 프로세스가 Sleep 상태일 동안 일정 간격 후 래치의 가용성을 찾기 위해 계속 움직입니다. 가장 빈번하게 성능에 영향을 미치는 Buffer Cache, Shared Pool, Redo Buffer 등에 경합을 일으킵니다.


1) Library Cache and Shared Pool Latches

- 이러한 래치는 공유 가능한 SQL이 저장된 라이브러리 캐시를 보호합니다.

- 잘 정의 된 응용 프로그램이 이러한 래치에 대한 경합이 없어야 하지만, 예를 들어 바인드변수를 사용할 경우 라이브러리 캐시 경합이 발생할 수 있습니다.

2) Redo Copy/Redo Allocation Latches

- 이러한 래치는 Redo Log에서 만들어진 Entries, Redo Log Buffer를 보호합니다.

3) Cache Buffers Chain Latches (Row Cache Objects Latch, Library Cache Latch, Shared Pool Latch)

- 이러한 래치는 세션을 읽거나 Buffer Cache에 쓸 때 발생합니다.

- 이 래치에 대한 경합은 일반적으로 일반적인 Hot Block와 Index Root, Branch Block에 발생합니다.



래치의 경합 측정

버퍼 캐시를 사용하기 위해 해시 체인을 탐색하거나 변경하려면 프로세스는 반드시 해당 체인을 관리하는 cache buffers chains 래치를 획득해야 합니다.

cache buffers chains 래치를 획득하는 과정에서 경합이 발생되면 latch: cache buffers chains 이벤트를 대기하게 됩니다.

cache buffers chains 래치 경합이 발생되는 대표적인 경우는 다음과 같습니다.


1) 비효율적인 SQL

- 비효율적인 SQL 문장이 cache buffers chains 래치 경합의 가장 중요한 원인입니다. 동시에 여러 프로세스가 넓은 범위의 인덱스나 범위의 테이블에 대해 스캔할 경우 래치 경합이 광범위하게 발생할 수 있습니다.

- cache buffer chains 래치 경합이 발생한 경우라면 핫블록에 의해 발생하는지, 비효율적인 SQL에 의해 발생하는지 판단해야 하는 근거는 SQL 문장입니다.

- 확실한 판단 근거가 있다면 SQL 문장을 튜닝함으로써 문제를 해결 할 수 있습니다.

- 판단 근거가 확실하지 않다면, V$LATCH_CHILDREN 뷰에서 자식 cache buffers chains 래치에 해당하는 CHILD# 과 GETS, SLEEPS 값을 비교하여 특정 자식 래치에 사용하는 회수와 경합이 집중되는지 판단해야 합니다.

SELECT *
  FROM (SELECT   child#, gets, sleeps
            FROM v$latch_children
           WHERE NAME = 'cache buffers chains'
        ORDER BY sleeps DESC)
 WHERE ROWNUM <= 20;

    CHILD#       GETS     SLEEPS
---------- ---------- ----------
      7420    1565347         35
      1900     634926         27
       262   29572177         23
      3276     107434         17
       753    2111916         15
        27     707074          5
      6370     388169          3
...

만약 특정 자식 래치의 GETS, SLEEPS 값이 다른 자식 래치에 비해서 비 정상적으로 높다면 해당 래치가 관장하는 체인에 핫블록이 있는 것으로 추측할 수 있습니다.

지금 테스트 결과로는 특정 래치에 대한 편중 현상이 보이지 않아 핫블록에 의한 문제는 없다고 판단할 수 있습니다.


2) 핫블록(Hot Block)

- SQL 문장에 적절히 튜닝되었음에도 불구하고 래치 경합이 해결되지 않는 경우, 여러 세션이 동시에 이 SQL 문을 수행하여 핫블록에 의한 cache buffers chains 래치 경합이 발생할 수 있습니다.

- V$LATCH_CHILDREN 뷰를 통해 특정 자식 래치 사용이 편중되어 있는지 확인하고, X$BH 뷰를 이용하여 정확하게 어떤 블록들이 핫블록인지 확인 가능합니다.

SELECT *
  FROM (SELECT   addr, child#, gets, sleeps
            FROM v$latch_children
           WHERE NAME = 'cache buffers chains'
        ORDER BY sleeps DESC)
 WHERE ROWNUM <= 20;

ADDR                                 CHILD#       GETS     SLEEPS
-------------------------------- ---------- ---------- ----------
00000003F7C7C138                       7420    1585896         999
00000003F808BFB8                       1900     657320         888
00000003F933F3B8                        262   29839543         777
00000003F817B2B8                       3276     108264         17
00000003F9393350                        753    2130546         15
00000003F9313C20                         27     707416          5
00000003F83963E8                       6370     388780          3
00000003F931DA98                         66      55390          3
...

결과를 보면 CHILD# 7420, 1900, 262 자식 래치가 집중적으로 사용되고 있으며 이에 의해 래치 경합이 발생된 것으로 보입니다. 

X$BH 뷰를 이용하여 정확하게 어떤 블록들이 핫 블록인지 확인 가능합니다.

X$BH 뷰로부터 1) 사용자의 객체(Table, Index)에 해당되며, 2) 접촉 횟수(Touch Count)가 높은 블록을 기준으로 핫블록을 추출 할 수 있습니다.

SELECT   hladdr, obj,
         (SELECT object_name
            FROM dba_objects
           WHERE (data_object_id IS NULL AND object_id = x.obj)
              OR data_object_id = x.obj AND ROWNUM = 1) AS object_name,
         dbarfil, dbablk, tch
    FROM x$bh x
   WHERE hladdr IN
                 ('00000003F7C7C138', '00000003F808BFB8', '00000003F933F3B8')
ORDER BY hladdr, obj;

HLADDR                OBJ OBJECT_NAME                      DBARFIL     DBABLK        TCH
----------------- -------- ----------------------------- ---------- ---------- ----------
00000003F7C7C138    19962 SI_CHPRICE_PK                       1          3125         1
00000003F7C7C138    40758 XT_수익율_PK                        1          1135         1
00000003F7C7C138    40795 TTT_수익율_PK                       4          12335        130
00000003F7C7C138   288233 FN_SCN_TO_TZE_AUX                   2          1635         34
00000003F808BFB8    19962 SI_CHPRICE_PK                       1          18135        20

위의 조회결과를 보면 TTT_수익율_PK 인덱스의 12335 블록에서 대부분의 경합이 발생하는 것을 확인 할 수 있다.


Solution

1) 비 효율적인 문장을 튜닝한다.

- 비 효율적인 SQL문을 튜닝함으로써 Logical Reads를 줄이면 자연스럽게 버퍼 캐시에 대한 액세스도 줄어든다.

2) 핫 블록을 분산시킨다.

- PCTFREE를 높게 주거나 작은 크기의 블록을 사용하여 블록 경합을 줄인다.

- 파티셔닝(Partitioning) 기법을 사용해서 로우가 물리적으로 다른 블록으로 들어가게끔 한다.

- 문제가 되는 블록의 로우들에 대해서만 삭제 후 재삽입 작업을 수행한다.


저작자 표시 비영리 동일 조건 변경 허락
신고