Expert Oracle Exadata 2-B. 오프로딩 / 스마트 스캔
- 스마트 스캔의 전제 조건: 엑사데이터에서 실행되는 모든 쿼리에서 스마트 스캔이 일어나지는 않는다. 스마트 스캔이 일어나기 위한 세가지 필요 조건은 다음과 같다.
1. 오브젝트의 풀 스캔이 있어야 한다.
2. 스캔은 오라클의 Direct path read 매커니즘을 사용해야 한다.
3. 오브젝트는 엑사데이터 스토리지에 저장되어 있어야 한다.
- Full scan
쿼리가 엑사데이터의 오프로딩 기능을 활용하기 위해서 옵티마이저는 구문을 Full table scan 또는 Fast full index scan으로 실행하여야 한다. 이것은 실행 계획상의 TABLE ACCESS FULL, INDEX FAST FULL SCAN 오퍼레이션과 일치한다. 엑사데이터에서의 이것들의 이름은 TABLE ACCESS STORAGE FULL, INDEX STORAGE FAST FULL SCAN이다.
- Direct path read
- 스마트 스캔을 비활성화시키는 것들
1. 단순 이용 불가한 상황
1.1. 클러스터 테이블에서 스마트 스캔을 사용할 수 없다.
1.2. IOT(Index organized table)에서
1.3. ROWDEPENDENCIES가 활성화된 테이블에서
- 스마트 스캔 사용 여부 확인하는 방법
1. DBMS_XPLAN 패키지에 의해 생성되는 일반 실행 계획의 결과로는 알 수 없다.
2. 10046 트레이스: 해당 구문에 10046 트레이스를 걸어보면 된다. 스마트 스캔이 사용되었다면 트레이스 파일에 CELL SMART TABLE SCAN 또는 CELL SMART INDEX SCAN 이벤트가 있을 것이다.
3. V$SESSTAT, V$ACTIVE_SESSION_HISTORY 같은 동적 성능 뷰를 보고 알 수 있다.
4. Offload eligible bytes: V$SQL 뷰의 IO_CELL_OFFLOAD_ELIGIBLE_BYTES 컬럼은 오프로딩 적용이 가능한 바이트 수를 보여주어 스마트 스캔이 사용됐다면 해당 컬럼이 0보다 큰 값으로 설정된다. 다음 스크립트는 0보다 큰지에 따라 yes또는 no를 반환한다.
----------------------------------------------------------------------------------------
--
-- File name: fsx.sql
--
-- Purpose: Find SQL and report whether it was Offloaded and % of I/O saved.
--
-- Author: Kerry Osborne
--
-- Usage: This scripts prompts for two values.
--
-- sql_text: a piece of a SQL statement like %select col1, col2 from skew%
--
-- sql_id: the sql_id of the statement if you know it (leave blank to ignore)
--
-- Description:
--
-- This script can be used to locate statements in the shared pool and
-- determine whether they have been executed via Smart Scans.
--
-- It is based on the observation that the IO_CELL_OFFLOAD_ELIGIBLE_BYTES
-- column in V$SQL is only greater than 0 when a statement is executed
-- using a Smart Scan. The IO_SAVED_% column attempts to show the ratio of
-- of data received from the storage cells to the actual amount of data
-- that would have had to be retrieved on non-Exadata storage. Note that
-- as of 11.2.0.2, there are issues calculating this value with some queries.
--
-- Note that the AVG_ETIME will not be acurate for parallel queries. The
-- ELAPSED_TIME column contains the sum of all parallel slaves. So the
-- script divides the value by the number of PX slaves used which gives an
-- approximation.
--
-- Note also that if parallel slaves are spread across multiple nodes on
-- a RAC database the PX_SERVERS_EXECUTIONS column will not be set.
--
-- See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
set pagesize 999
set lines 190
col sql_text format a70 trunc
col child format 99999
col execs format 9,999
col avg_etime format 99,999.99
col "IO_SAVED_%" format 999.99
col avg_px format 999
col offload for a7
select sql_id, child_number child, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions)/
decode(px_servers_executions,0,1,px_servers_executions/decode(nvl(executions,0),0,1,executions)) avg_etime,
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') Offload,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,0,100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)
/decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,1,IO_CELL_OFFLOAD_ELIGIBLE_BYTES)) "IO_SAVED_%",
sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like 'BEGIN :sql_text := %'
and sql_text not like '%IO_CELL_OFFLOAD_ELIGIBLE_BYTES%'
and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
/하드웨어 아키텍처가 데이터를 제공하는 스토리지 계층과 그것을 소비하는 데이터베이스 계층 사이에 균형을 제공하는 일을 처리하는 동안 성능 향상의 대부분은 소프트웨어에 의해 제공된다. 이러한 향상은 스마트 스캔이 책임지고 있다. 최적화의 주요 초점은 스토리지 계층과 데이터베이스 계층 사이에 전송되는 데이터 양을 줄이는 것이다.