Oracle Exadata 스마트 스캔 잠금 해제: 엔지니어드 시스템으로 SQL 성능 향상

이 블로그는 Oracle® Exadata® 스마트 스캔 기능의 세부 사항을 다루며, Exadata에서 SQL 처리가 어떻게 다른지와 사용 예시를 포함합니다.

소개

Oracle은 2008년에 엔지니어드 시스템 제품군의 최초 중 하나로 Exadata를 설계하고 도입했습니다. 사용자가 구성, 설치, 튜닝, 관리하는 전통적인 데이터베이스 시스템과 달리, 엔지니어드 시스템은 OS, 데이터베이스, 메모리, 스토리지와 같은 모든 필수 구성 요소가 솔루션 제공업체(예: Oracle)에 의해 사전 설치, 구성, 튜닝, 최적화되어 있습니다.

Exadata 머신에는 다양한 구성 요소가 있습니다. 다음 구성 요소들이 주요합니다:

• 데이터베이스 서버: 데이터베이스 소프트웨어, 자동 스토리지 관리 (ASM), 또는 Oracle Grid 인프라를 실행하는 엔터프라이즈급 서버.

• 스토리지 서버: 데이터베이스 서버에서 오는 I/O 요청과 사용자에게 반환되는 데이터 요청을 관리하는 Exadata 스토리지 서버 소프트웨어를 실행하는 스토리지 서버 또는 셀.

• InfiniBand 네트워크: 내부 Exadata 네트워크는 고성능 컴퓨팅 환경을 위해 주로 설계된 InfiniBand로 구축되었습니다.

Exadata는 데이터베이스 성능을 향상시키는 Exadata 스토리지 소프트웨어 (ESS)라는 소프트웨어와 함께 제공됩니다. ESS는 각 스토리지 셀에 설치된 스토리지 관리 서버로, 스토리지 서버를 관리하고 스토리지 관련 요청에 대해 데이터베이스 서버와 통신합니다.

ESS의 주요 기능

다음은 ESS의 주요 기능들입니다:

• 플래시 캐시: 셀 서버의 고성능 캐시로 최근에 액세스한 객체를 캐시합니다.

• 플래시 로그: 플래시 로그는 리두-로그 쓰기를 위한 고성능, 저지연, 신뢰할 수 있는 임시 저장소를 제공합니다.

• EHCC: Exadata Hybrid Columnar Compression (EHCC)은 최고 수준의 데이터 압축을 가능하게 합니다. EHCC는 데이터를 압축 단위라고 하는 행 세트로 구성합니다. 압축 단위 내에서 EHCC는 데이터를 열별로 구성한 다음 데이터를 압축합니다.

• 스토리지 인덱스: 스토리지 인덱스는 스토리지 서버의 인메모리 구조로, 스토리지 서버에서 디스크에서 데이터를 읽는 데 소요되는 시간을 줄이도록 설계되었습니다.

• 셀 오프로딩: 전통적인 스토리지 설계와 달리, Exadata 스토리지 셀은 컴퓨트 또는 데이터베이스 노드의 전체 부담을 줄이기 위해 일부 워크로드를 내부적으로 처리하도록 설계되었습니다. 이 과정을 셀 오프로딩이라고 합니다.

• 스마트 스캔: 스마트 스캔은 대부분의 SQL 처리를 데이터베이스 계층 대신 스토리지 계층에서 발생하게 하여 쿼리 성능을 극적으로 향상시킵니다. 스마트 스캔은 데이터베이스 계층으로 전송되는 데이터 볼륨을 줄여 데이터베이스 노드의 CPU 사용량을 줄입니다.

SQL 처리

전통적으로 SQL 처리는 데이터베이스 서버에서만 발생합니다. Exadata에서는 SQL 처리가 분할되어 스토리지 계층에서도 발생합니다.

전통적인 SQL 처리

다음 단계는 다음 다이어그램에 표시된 전통적인 SQL 처리를 구성합니다:

1. 클라이언트가 쿼리를 제출합니다.
2. 이 작업은 풀 테이블 스캔을 트리거합니다.
3. 데이터베이스는 요청을 파일과 스캔 중인 테이블을 포함하는 해당 익스텐트에 매핑합니다.
4. 풀 스캔과 유사하게, 데이터베이스는 모든 블록을 읽기 위해 I/O 작업을 발행합니다.
5. 테이블의 쿼리된 모든 블록이 메모리에 읽혀집니다.
6. 프로세스는 프레디케이트를 만족하는 행을 찾기 위해 블록을 검색합니다.
7. 마지막으로 요청된 행이 클라이언트에게 반환됩니다.

큰 테이블인 경우, 해당 테이블의 모든 블록이 읽혀지고, 스토리지 네트워크를 통해 전송되어 메모리에 복사됩니다. 프로세스는 요청된 SQL 작업을 완료하기 위해 많은 원치 않는 행을 메모리에 읽어들입니다. 전송된 대량의 데이터는 대역폭을 소비하고, 응답 시간에 영향을 주며, 데이터베이스 계층에 불필요한 부담을 줍니다.

Exadata 서버 SQL 처리

Exadata 스토리지를 사용하면 데이터베이스 로직이 내장된 Exadata 스토리지 소프트웨어를 사용하기 때문에 SQL 처리가 훨씬 더 효율적으로 처리됩니다. 다음 단계는 다음 다이어그램에 표시된 Exadata SQL 처리를 구성합니다:

1. 클라이언트가 쿼리를 제출합니다.
2. 데이터베이스 서버는 쿼리 기준을 포함하는 지능형 데이터베이스 (iDB) 메시지를 구성합니다. 이 iDB 메시지는 랙의 모든 스토리지 서버로 전송됩니다.
3. ESS의 cellsrv 구성 요소가 데이터 블록을 스캔하여 요청을 만족하는 일치하는 행과 열을 식별합니다.
4. 모든 스토리지 서버가 쿼리 기준을 병렬로 실행하고 상호 연결을 사용하여 관련 행 또는 순 결과만 데이터베이스 서버로 보냅니다.
5. 데이터베이스가 결과를 통합하고 클라이언트에게 행을 반환합니다.

스마트 스캔 요구사항

스마트 스캔을 사용하기 위한 요구사항은 다음과 같습니다:

• 풀 테이블 스캔 또는 풀 인덱스 스캔에만 스마트 스캔을 사용할 수 있습니다.
• 쿼리는 다이렉트 패스 읽기를 수행해야 합니다.
• 데이터베이스 초기화 파라미터 CELL_OFFLOAD_PROCESSING을 TRUE로 설정해야 합니다.
• 세그먼트는 Exadata 셀에 완전히 저장된 디스크 그룹에 저장되어야 합니다.
• 세그먼트 데이터를 저장하는 ASM 디스크 그룹에는 다음 속성 설정이 있어야 합니다: – compatible.rdbms = 11.2.0.0.0 (또는 이후) – compatible.asm = 11.2.0.0.0 (또는 이후) – cell.smart_scan_capable = TRUE

스마트 스캔 예시

다음 단계를 먼저 스마트 스캔이 활성화된 상태에서, 그다음 비활성화된 상태에서 실행하여 통계에 대한 스마트 스캔 효과를 측정하세요.

1단계: 데이터베이스 연결

SQL*Plus를 사용하여 데이터베이스에 연결하려면 다음 명령어를 실행하세요:

[oracle@nd01db01 ~]$ sqlplus nd/nd SQL*Plus: Release 11.2.0.3.0 Production... 

2단계: 통계 확인

통계가 0이거나 근접해 있는지 확인하려면 다음 쿼리를 실행하세요:

SQL> select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name in ('physical read total bytes', 'physical write total bytes', 'cell IO uncompressed bytes') or a.name like 'cell phy%'); NAME MB --------------------------------------------------------------- --- physical read total bytes 0 physical write total bytes 0 cell physical IO interconnect bytes 0 cell physical IO bytes saved during optimized file creation 0 cell physical IO bytes saved during optimized RMAN file restore 0 cell physical IO bytes eligible for predicate offload 0 cell physical IO bytes saved by storage index 0 cell physical IO bytes sent directly to DB node to balance CPU 0 cell physical IO interconnect bytes returned by smart scan 0 cell IO uncompressed bytes 0 10 rows selected. 

3단계: 스마트 스캔 비활성화 상태에서 select 쿼리 실행

스마트 스캔을 비활성화하는 옵티마이저 힌트와 함께 다음 select 쿼리를 실행하세요:

SQL> select /*+ OPT_PARAM('cell_offload_processing' 'false') */ count(*) from sales where time_id between '01-JUN-2017' and '30-OCT-2017' and amount_sold = 1; COUNT(*) ---------- 134055 

4단계: 통계 다시 확인

통계를 다시 확인하려면 다음 명령어를 실행하세요. 3단계의 쿼리에 의해 처리된 모든 데이터(physical read total bytes)가 스토리지 네트워크를 통해 데이터베이스 서버로 반환됨(cell physical IO interconnect bytes)을 주목하세요.

SQL> select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name in ('physical read total bytes', 'physical write total bytes', 'cell IO uncompressed bytes') or a.name like 'cell phy%'); NAME MB --------------------------------------------------------------- ----------- physical read total bytes 759.429688 physical write total bytes 0 cell physical IO interconnect bytes 759.429688 cell physical IO bytes saved during optimized file creation 0 cell physical IO bytes saved during optimized RMAN file restore 0 cell physical IO bytes eligible for predicate offload 0 cell physical IO bytes saved by storage index 0 cell physical IO bytes sent directly to DB node to balance CPU 0 cell physical IO interconnect bytes returned by smart scan 0 cell IO uncompressed bytes 0 10 rows selected. 

5단계: 통계 초기화

세션 수준 통계를 초기화하기 위해 데이터베이스에 세션을 재연결하려면 다음 명령어를 실행하세요:

[oracle@nd01db01 ~]$ sqlplus nd/nd SQL*Plus: Release 11.2.0.3.0 Production... 

6단계: 통계 다시 확인

통계가 0이거나 근접해 있는지 확인하려면 다음 쿼리를 실행하세요:

SQL> select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name in ('physical read total bytes', 'physical write total bytes', 'cell IO uncompressed bytes') or a.name like 'cell phy%'); NAME MB --------------------------------------------------------------- --- physical read total bytes 0 physical write total bytes 0 cell physical IO interconnect bytes 0 cell physical IO bytes saved during optimized file creation 0 cell physical IO bytes saved during optimized RMAN file restore 0 cell physical IO bytes eligible for predicate offload 0 cell physical IO bytes saved by storage index 0 cell physical IO bytes sent directly to DB node to balance CPU 0 cell physical IO interconnect bytes returned by smart scan 0 cell IO uncompressed bytes 0 10 rows selected. 

7단계: 스마트 스캔 비활성화 없이 select 쿼리 실행

스마트 스캔을 비활성화하는 옵티마이저 힌트 없이 다음 select 쿼리를 실행하세요:

SQL> select count(*) from sales where time_id between '01-JUN-2017' and '30-OCT-2017'and amount_sold = 1; COUNT(*) ------- 134055 

8단계: 통계 확인

통계를 확인하려면 다음 쿼리를 실행하세요. 쿼리는 여전히 4단계에서와 같이 약 759 MB의 I/O(physical read total bytes)를 수행합니다. 그러나 이번에는 1.7 MB만 DB 서버로 반환됨(cell physical IO interconnect bytes). 이것은 스마트 스캔이 작동한 결과입니다. 이 경우, 스마트 스캔은 이 쿼리와 관련된 I/O에 작용하는데, cell physical IO bytes eligible for predicate offload가 physical read total bytes와 같고, cell physical IO interconnect bytes returned by smart scan이 cell physical IO interconnect bytes와 같기 때문입니다.

SQL> select a.name, b.value/1024/1024 mb from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name in ('physical read total bytes', 'physical write total bytes','cell IO uncompressed bytes') or a.name like 'cell phy%'); NAME MB ---------------------------------------------------------------- ---------- physical read total bytes 759.429688 physical write total bytes 0 cell physical IO interconnect bytes 1.71562805 cell physical IO bytes saved during optimized file creation 0 cell physical IO bytes saved during optimized RMAN file restore 0 cell physical IO bytes eligible for predicate offload 759.429688 cell physical IO bytes saved by storage index 0 cell physical IO bytes sent directly to DB node to balance CPU 0 cell physical IO interconnect bytes returned by smart scan 1.71562805 cell IO uncompressed bytes 759.429688 10 rows selected. 

결론

Exadata의 스마트 스캔 기능은 SQL 처리를 데이터베이스 계층 대신 스토리지 계층에서 발생하게 하여 쿼리 성능을 향상시킵니다. 스마트 스캔은 데이터베이스 계층으로 전송되는 데이터 볼륨을 줄여 데이터베이스 노드의 CPU 사용량을 줄입니다.

피드백 탭을 사용하여 의견을 남기거나 질문하세요.

데이터베이스 서비스 및 Rackspace 애플리케이션 서비스에 대해 자세히 알아보세요.