[ORACLE] EXADATA 정리 중...



EXADATA 주요 기능

- Smart Scan

  .Storage에서 DB Server로의 데이터 전송을 최소화 하기 위한 개념 및 기능.
  .사용자가 필요로하는 최소한의 데이터를 읽고 선별하여 전송하는 방법으로,
  .내부적으로 Storage Index / Column Projection / Predicate Filtering 등의 기능을
   이용한다.

- Storage Index

  .Smart Scan을 지원하기 위한 기능 중 하나,
  .테이블의 데이터를 Storage Region으로 나누어 Region별 컬럼의 최대/최소 값을
  .저장해 놓은 것을 말한다. Where 절의 조건에 따라 블럭의 엑세스 여부를
   결정하는데 사용된다.
   
- Flash Cache   
  .Cell Server에 내장된 Disk Cache로, Random I/O (Index Scan)의 성능을 보안하기 위해
   존재한다.
  .Disk에서 블럭을 탐색하기 전에 Flash Cache에 해당 블럭이 존재하는지 확인한 후,
   존재할 경우 Flash Cache의 내용을 사용한다.

==================================================================================

EXADATA 성능요소

- OffLoading
  데이터베이스가 하던 처리를 스토리지 계층으로 옮겨서 처리하는 개념.
  즉 데이터베이스 서버로 반환할 데이터 양을 감소시켜
  대형 데이터베이스의 주요 병목현상을 감소시키는 목적이다.

    목적
        .디스크 시스템에서 데이터베이스 서버로 전송되는 데이터의 볼륨을 감소시킨다.
        .데이터베이스 서버의 CPU 사용량을 감소시킨다.
        .스토리지 계층에서 디스크 엑세스를 감소시킨다.

    3요소
        Column Projection :
            Column Projection이란 오직 관심이 있는 컬러만 반환함으로써 EXADATA의
            스토리지 계층과 데이터베이스 계층 사이에 전송되는 데이터의 양을 제한하는
            기능을 말함.

        Predicate Filtering :
            Predicate Filtering이란 오직 관심이 있는 로우만 반환하는 기능.
            일반 스토리지와는 달리 SQL 조건절 등을 메타정보로 스토리지 셀로 보낼수
              있어 필요한 정보만 필터링 하여 데이터베이스 서버로 반환하는 기능

        Storage Index : 아래 설명 참조
       
- Smart Scan
  Smart Scan은 Storage 서버에서 DB 서버로 전송하는 데이터 양을 최소화하기 위해
  필요한 블록만 엑세스하고, 조건을 만족하는 로우 중 필요한 컬럼만 선별하여
   DB 서버로 전송하는 처리 방식을 말한다.

    전제 조건
        .오브젝트의 전체 스캔(Table/Index Full Scan)이 발생해야 한다.
        .Full Scan은 오라클의 Direct Path Read 메커니즘을 사용해야 한다.
        .오브젝트는 EXADATA Storage에 저장되어 있어야 한다.

    불가능한 경우
        .클러스터 테이블에서 스마트 스캔을 사용할 수 없다.
        .Index Organized Table에서는 스마트 스캔을 사용할 수 없다.
        .ROWDEPENDENCIES가 활성화된 테이블에서는 스마트 스캔을 사용 할 수 없다.

- Storage Index
  Storage Index는 데이터가 인덱스 범위에 속하지 않을 경우,
  해당 블록의 스캔을 회피하여 불필요한 Disk I/O를 줄이기 위한 목적으로
  설계된 Storage 서버의 기능이다.

    기본 원리
        .Cellsrv 프로세스 메모리 내에 1MB 단위로 MIN/MAX 값을 관리함(Null 포함).
        .Cellsrv 프로세스 메모리 내에 관리되므로,
         Cellsrv 프로세스 또는 셀 서버 리부팅 후 마다 재 생성됨.
        .Storage Index의 효율을 높이기 위해서는 조건 칼럼에 대한
         Ordering이 중요한 요소임. (CTAS 를 통한 데이터 이관 시, 고려해야 할 점)
        .Cellsrv 가 기동되는 시점 이후에 신규 생성 또는 DATA_OBJECT_ID가
         변경된 테이블에 대해서는 Storage Index가 생성되지 않음.

    사용 필수 조건
        .Smart Scan 발생 시,
        .최소 하나 이상의 조건이 검색 되는 경우,
        .연산자 ( =, >, <, >=, <=, IN, BETWEEN, IS NULL, IS NOT NULL)를
         사용하여 조건을 비교할 경우
       
    이 외, Storage Index 사용 조건
        Bind 변수 사용,
        조인 사용,
        서브 쿼리

    제어 방법
        DB 서버에서 _kcfis_storageidx_disabled 히든 파라미터를 True로 설정하면
        Storage Index를 비활성화 시킬 수 있다
         (ALTER SESSION SET "kcfis_storageidx_disabled" = true;)

    모니터링
        Stat 지표 “cell physical IO bytes saved by storage index”
        V$SYSSTAT (시스템 레벨)
        V$SESSTAT (세션 레벨)
        V$MYSTAT (세션 레벨)

- Flash Cache
  .EXADATA의 Index Scan은 표준 Oracle과 마찬가지로 블록 단위로 데이터를
   전송하기 때문에 여전히 I/O 성능 저하가 발생할 수 있다.
  .EXADATA는 X2버전부터 Index Scan의 성능 향상을 위한
   EXADATA Flash Cache를 제공한다.
  .일반적으로 Flash Cache에서 블록을 검색하는 것이 Disk에서 검색하는 것보다
   5배 이상 성능이 빠르다.

==================================================================================

EXADATA 성능 관리 목표

표준 ORACLE에서 EXADATA로 변경되면서 나타난 핵심 변화는
기존 DB Server에서 처리되던 내용을 Storage Server에서 처리되도록 변경한 것이다.

그렇기 때문에 I/O PERFORMANCE, 불필요한 스캔 등,
기존의 맹점을 극복하기 위해 등장한 OffLoading 기능이
온전히 동작하는지 모니터링하는 것이야말로
EXADATA 성능모니터링의 가장 중요한 부분이라 할 수 있다.

- 모니터링 주의사항
  EXADATA에 최적화된 모니터링 방안은 Smart Scan 이
  원할히 이루어지는지 확인 하는데서 시작된다.


- Smart Scan 전제 조건 확인 방법

  Smart Scan을 수행하기 위한 전제 조건중의 하나는 DIRECT I/O이다
  11g에서는 Serial Full Scan에서도 Direct I/O를 제공함.
  11g에서는 Parallel Query인 경우에 Buffered I/O도 제공함.
  Direct I/O을 수행 여부는 Run-Time 시에 결정됨.
  이로 인해, Direct I/O 수행 여부에 대한 정확한 예측이 힘듦.
  따라서, Run-Time 시에 최대한 Direct I/O로 수행하도록 하는 환경 설정이 필요함

Smart Scan 수행 여부 확인 방법

  실행 계획 상의 TABLE ACCESS STORAGE FULL 오퍼레이션은 Smart Scan이
  수행될 수 있다는 것을 의미하는 것이지, 반드시 Smart Scan이 수행된다는것을
  의미하지 않음. 즉, 실행계획 (및 예상실행계획) 을 통해 SMART SCAN이 발생하였는지
  판단 불가하기 때문에 V$SYSSTAT, V$SESSTAT 이용하여 모니터링 해야 함
   
    확인하는 방법
        .10046 트레이스를 통해 확인해야 함.
        .Smart Scan 이 사용되었다면,
        .CELL SMART TABLE SCAN (Full Table Scan),
         CELL SMART INDEX SCAN (Index Fast Full Scan) 이벤트가 명시된다.


- 파라미터 셋팅
      _SERIAL_DIRECT_READ
          .기본값 : ALWAYS
          .변경 방법 : ALTER SESSION SET "_serial_direct_read" = true;
          .Serial Full Table Scan시 Direct Path I/O 수행 방식을 결정하는 파라미터
           해당 파라미터가 True인 경우 Full Table Scan 이나 Index Fast Full Scan은
            Direct Path I/O 수행될 가능성이 높아짐.
   
      CELL_OFFLOAD_PROCESSING
          .기본값 : TRUE   
          .변경 방법 : ALTER SESSION SET cell_offload_processing = true;
          .Exadata의 Offloading 기능의 사용 유무를 결정 하는 파라미터
           해당 파라미터가 True인 경우에는 OffLoading 기능인 Columns Projection,
           Predicate Filtering, Storage Index등의 기능을 사용 할 수 있다.
           
      _KCFIS_STORAGEIDX_DISABLED
          .기본값 : FALSE
          .변경 방법 : ALTER SESSION SET "_kcfis_storageidx_disabled" = true;
           .Exadata의 Storage Index 기능의 사용 유무를 결정 하는 파라미터 
            해당 파라미터가 False인 경우에는 Offloading 기능 중 Storage Index의
            기능을 사용 할 수 있다.
           
      _OPTIMIZER_SKIP_SCAN_ENABLED
          .기본값 : FALSE
          .변경 방법 :
             ALTER SESSION SET "_optimizer_skip_scan_enabled" = true;  
                  or
               /*+ opt_param('_OPTIMIZER_SKIP_SCAN_ENABLED','TRUE') */
          .Index Skip Scan Enable/Disable 여부 결정
          해당 파라미터가 False인 경우에는 Index Skip Scan을 사용하지 못함.
           
          (1) Index Skip Scan으로 인해 수행 속도가 빨라지는 쿼리도 존재할 것임.
          (2) 하지만, Index Skip Scan으로 인해 Smart Scan을 수행하지 못함에 따라,
            수행 속도가 느려지는 쿼리도 존재함.
          따라서, False로 변경 후 (1)의 경우에 대해서는 추가적인 인덱스 생성을
         고려할 필요가 있음.
           
      _BLOOM_PREDICATE_PUSHDOWN_TO_STORAGE
          .기본값 : FALSE
          .변경 방법 :
             ALTER SESSION SET "_bloom_predicate_pushdown_to_storage" = true;)
          .어떤 경우에는 조인 처리 또한 스토리지 계층으로 오프로드 될 수 있다.
          오프로드된 조인은 블룸 필터를 생성하여 이루어짐.
           오프로드된 쿼리는 스토리지 서버에서 테이블을 미리 조인할 수 있기 때문에
          더 빨리 실행될 수 있음. 이는 데이터베이스 서버로 반환되었을 때 매우 많은
          양의 데이터를 제거해 줄 수 있다.

==================================================================================

※ 별도 첨부 스크립트

- 기술적 관점에서 Cell Flash Keep 대상 후보 추출 - _serial_direct_reads가 True가 아닌 경우


SELECT c.owner , c.segment_name , c.segment_type ,sum( c.bytes/1024/1024 ) size_mb , sum(a.table_scan)
FROM   (
        SELECT obj# ,
               MAX( table_scan ) table_scan
        FROM   (
                SELECT obj# ,
                       MAX( table_scans_delta ) table_scan
                FROM   dba_hist_seg_stat
                WHERE  snap_id IN (
                                   SELECT snap_id
                                   FROM   dba_hist_snapshot
                                   WHERE  begin_interval_time BETWEEN TO_DATE( '201301210000' , 'yyyymmddhh24miss' )
                                   AND    TO_DATE( '201301250000' , 'yyyymmddhh24miss' )
                                  )
                GROUP  BY obj# ,
                       snap_id
                HAVING MAX( table_scans_delta ) >= 2700
               )
        GROUP  BY obj#
       ) a ,
       dba_objects b ,
       dba_segments c
WHERE  a.obj#=b.object_id
AND    b.object_name=c.segment_name
AND    b.owner=c.owner
AND    c.bytes/1024/1024 > :b1 -- _small_table_threshold 설정 값
AND    c.cell_flash_cache='DEFAULT'
AND    c.segment_type like 'TABLE%'
group by  c.owner , c.segment_name , c.segment_type


- 기술적 관점에서 Cell Flash Keep 대상 후보 추출 (상세) ? _serial_direct_reads가 True가 아닌 경우


SELECT c.cell_flash_cache, c.owner , c.segment_name , c.partition_name , c.segment_type ,ROUND( c.bytes/1024/1024 , 0 ) size_mb ,
       a.table_scan
FROM   (
        SELECT obj# ,
               MAX( table_scan ) table_scan
        FROM   (
                SELECT obj# ,
                       MAX( table_scans_delta ) table_scan
                FROM   dba_hist_seg_stat
                WHERE  snap_id IN (
                                   SELECT snap_id
                                   FROM   dba_hist_snapshot
                                   WHERE  begin_interval_time BETWEEN TO_DATE( '201301210000' , 'yyyymmddhh24miss' )
                                   AND    TO_DATE( '201301250000' , 'yyyymmddhh24miss' )
                                  )
                GROUP  BY obj# ,
                       snap_id
                HAVING MAX( table_scans_delta ) >= 2700
               )
        GROUP  BY obj#
       ) a ,
       dba_objects b ,
       dba_segments c
WHERE  a.obj#=b.object_id
AND    b.object_name=c.segment_name
AND    b.owner=c.owner
AND    c.bytes/1024/1024 > :b1 -- _small_table_threshold 설정 값
AND    c.cell_flash_cache='DEFAULT'
AND    c.segment_type='TABLE'
ORDER  BY c.bytes * a.table_scan DESC



- 일반적 관점에서 Cell Flash Keep 대상 후보 추출


SELECT *
FROM   (
        SELECT c.cell_flash_cache ,
               c.owner ,
               c.segment_name ,
               c.partition_name ,
               c.segment_type ,
               ROUND( c.bytes/1024/1024 , 0 ) size_mb ,
               a.table_scan ,
               a.request_counts ,
               a.hit_counts ,
               ( a.request_counts - a.hit_counts ) miss_counts
        FROM   (
                SELECT obj# ,
                       MAX( table_scans_delta ) table_scan ,
                       SUM( physical_read_requests_delta ) request_counts ,
                       SUM( optimized_physical_reads_delta ) hit_counts
                FROM   dba_hist_seg_stat
                WHERE  snap_id IN (
                                   SELECT snap_id
                                   FROM   dba_hist_snapshot
                                   WHERE  begin_interval_time BETWEEN TO_DATE( '201301210000' , 'yyyymmddhh24miss' )
                                   AND    TO_DATE( '201302060000' , 'yyyymmddhh24miss' )
                                  )
                GROUP  BY obj#
               ) a ,
               dba_objects b ,
               dba_segments c
        WHERE  a.obj#=b.object_id
        AND    b.object_name=c.segment_name
        AND    b.owner=c.owner
        AND    c.cell_flash_cache='DEFAULT'
        AND    c.segment_type='TABLE'
        ORDER  BY ( a.request_counts - a.hit_counts ) DESC
       )
WHERE  ROWNUM <= :b1 -- miss counts 기준 Top Segments 개수 선택


- AWR 및 V$SQL을 이용하여 I/O 처리량이 높은 프로시저 중에서 Smart Scan을 수행하지 않는 대상 추출


SELECT TO_CHAR( REPLACE( REPLACE( SUBSTR( sql_text , 1 , decode( INSTR( sql_text , '(' , 1 ) , 0 , 100 ,
                       INSTR( sql_text , '(' , 1 ) - 1 ) ) , 'BEGIN' , '' ) , '; END' , '' ) ) procedures , --sql_id ,
               SUM( buffer_get ) buffer_get ,
               SUM( io_offload_elig_bytes ) io_offload_elig_bytes ,
               SUM( disk ) disk ,
               SUM( cpu_time ) cpu_time ,
               ROUND( decode( SUM( elpas ) , 0 , 1 , SUM( elpas ) ) /decode( SUM( executions ) , 0 , 1 , SUM( executions ) ) ) /1000000 elpas_avg ,
               ROUND( decode( SUM( buffer_get ) , 0 , 1 , SUM( buffer_get ) ) /decode( SUM( executions ) , 0 , 1 , SUM( executions ) ) ) avg_buffer_gets ,
               SUM( executions ) executions ,
               MAX( parsing_schema_name ) parsing_schema_name
        FROM   (
                SELECT a.*
                FROM   (
                        SELECT a.* ,
                               b.sql_text
                        FROM   (
                                SELECT sql_id ,
                                       SUM( buffer_gets_delta ) buffer_get ,
                                       SUM( disk_reads_delta ) disk ,
                                       SUM( rows_processed_delta ) processed_rows ,
                                       SUM( cpu_time_delta ) cpu_time ,
                                       SUM( io_offload_elig_bytes_delta ) io_offload_elig_bytes ,
                                       SUM( elapsed_time_delta ) elpas ,
                                       ROUND( decode( SUM( buffer_gets_delta ) , 0 , 1 , SUM( buffer_gets_delta ) ) /
                                              decode( SUM( executions_delta ) , 0 , 1 , SUM( executions_delta ) ) ) avg_buffer_gets ,
                                       SUM( executions_delta ) executions ,
                                       MAX( parsing_schema_name ) parsing_schema_name
                                FROM   dba_hist_sqlstat
                                WHERE  buffer_gets_total IS NOT NULL
                                AND    snap_id IN (
                                                   SELECT snap_id
                                                   FROM   dba_hist_snapshot
                                                   WHERE  begin_interval_time BETWEEN TO_DATE( '20130103' )
                                                   AND    TO_DATE( '20130105' )
                                                   AND    io_offload_elig_bytes_delta = 0
                                                   AND    buffer_gets_total > 1000
                                                  )
                                AND    parsing_schema_name <> 'SYS'
                                GROUP  BY sql_id
                                ORDER  BY 1 , 2 DESC
                               ) a , dba_hist_sqltext b
                        WHERE  a.sql_id = b.sql_id( + )
                        AND    UPPER( b.sql_text ) LIKE '%SP%'
                        AND    UPPER( b.sql_text ) NOT LIKE '%SELECT%'
                        AND    UPPER( b.sql_text ) NOT LIKE '%UPDATE%'
                        AND    UPPER( b.sql_text ) NOT LIKE '%INSERT%'
                        AND    UPPER( b.sql_text ) NOT LIKE '%DELETE%'
                        AND    avg_buffer_gets > 100000
                       ) a
               ) GROUP  BY TO_CHAR( REPLACE( REPLACE( SUBSTR( sql_text , 1 , decode( INSTR( sql_text , '(' , 1 ) , 0 , 100 , INSTR( sql_text , '(' , 1 ) - 1 ) ) , 'BEGIN' , '' ) , '; END' , '' ) )
     ORDER  BY 2 DESC


※ EX-EM 기술 자료 참조

댓글

이 블로그의 인기 게시물

[ORACLE] Connect By 함수를 이용한 YYYYMMDD, HH24 출력

Linux 의 SWAP 에 대해서..