System Software User's Guide의 Operation Not Being Offloaded 항목에 함수 오프로딩과 관련된 아래의 내용이 포함되어 있습니다. 암시적 데이터 변환은 오프로딩되지 않는 INTERNAL_FUNCTION 함수를 수행할 수 있고 이로 인해 쿼리의 성능이 저하될 수 있습니다.
A smart I/O operation cannot be offloaded to the Exadata storage servers in the following cases:
Although offloading is supported for most SQL operators and functions, Oracle Exadata System Software does not support offloading for some SQL operators and functions. The dynamic performance view V$SQLFN_METADATA includes information about whether offloading is supported for a SQL operator or function. If the OFFLOADABLE column contains YES, then offloading is supported for the corresponding operator or function. NO indicates that offloading is not supported for the corresponding operator or function.
참고로 아래 쿼리로 오프로딩을 지원하는 함수를 조회할 수 있습니다.
-- 1
SELECT func_id, name FROM v$sqlfn_metadata WHERE offloadable = 'YES';
FUNC_ID NAME
------- ---------
...
21 TO_NUMBER
22 TO_CHAR
...
444 행이 선택되었습니다.
테스트를 위해 아래와 같이 테이블을 생성하고 파라미터를 설정하겠습니다.
-- 2-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2)
AS
SELECT TO_CHAR (DATE '2049-12-31' + CEIL (ROWNUM / 100), 'YYYYMMDD')
, LPAD ('X', 100, 'X')
FROM XMLTABLE ('1 to 10000000');
-- 2-2
ALTER SESSION SET "_serial_direct_read" = 'ALWAYS';
ALTER SESSION SET "_small_table_threshold" = 0;
아래 예제는 c1 = '20500101' 조건으로 t1 테이블 조회하고 수행에 0.01초가 소요됩니다. Predicate Information 항목에서 storage("C1"='20500101')가 표시된 것을 확인할 수 있습니다.
-- 3
SELECT /*+ MONITOR */
*
FROM t1
WHERE c1 = '20500101';
----------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads | Used-Mem |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 |00:00:00.01 | 161K| 161K| |
|* 1 | TABLE ACCESS STORAGE FULL| T1 | 100 |00:00:00.01 | 161K| 161K| 3086K (0)|
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("C1"='20500101')
filter("C1"='20500101')
Global Stats
======================================================================================================
| Elapsed | Cpu | IO | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
======================================================================================================
| 0.01 | 0.01 | 0.00 | 1273 | 1GB | 944KB | 1GB | 14384 | 100.00% |
======================================================================================================
아래 예제는 c1 = DATE '2050-01-01' 조건으로 t1 테이블 조회하고 수행에 3.36초가 소요됩니다. 암시적 데이터 변환으로 인해 Predicate Information 항목에서 INTERNAL_FUNCTION 함수가 사용된 것을 확인할 수 있습니다.
-- 4
SELECT /*+ MONITOR */
*
FROM t1
WHERE c1 = DATE '2050-01-01';
----------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads | Used-Mem |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 |00:00:03.36 | 161K| 161K| |
|* 1 | TABLE ACCESS STORAGE FULL| T1 | 100 |00:00:03.36 | 161K| 161K| 3086K (0)|
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("C1")=TO_DATE(' 2050-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Global Stats
======================================================================================================
| Elapsed | Cpu | IO | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
======================================================================================================
| 3.36 | 3.30 | 0.05 | 2326 | 1GB | 1GB | 1GB | 1GB | 12.80% |
======================================================================================================
아래 예제는 TO_DATE (c1, 'YYYYMMDD') = DATE '2050-01-01' 조건으로 t1 테이블 조회하고 수행에 0.09초가 소요됩니다. 명시적 데이터 변환으로 인해 Predicate Information 항목에서 TO_DATE 함수가 사용된 것을 확인할 수 있습니다.
-- 5
SELECT /*+ MONITOR */
*
FROM t1
WHERE TO_DATE (c1, 'YYYYMMDD') = DATE '2050-01-01';
----------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Reads | Used-Mem |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 |00:00:00.09 | 161K| 161K| |
|* 1 | TABLE ACCESS STORAGE FULL| T1 | 100 |00:00:00.09 | 161K| 161K| 3086K (0)|
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage(TO_DATE("C1",'YYYYMMDD')=TO_DATE(' 2050-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter(TO_DATE("C1",'YYYYMMDD')=TO_DATE(' 2050-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Global Stats
======================================================================================================
| Elapsed | Cpu | IO | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
======================================================================================================
| 0.09 | 0.02 | 0.08 | 1273 | 1GB | 1GB | 1GB | 449KB | 99.97% |
======================================================================================================
아래는 SQL 모니터 리포트의 Global Stats 항목을 비교한 결과입니다. Uncompressed Bytes의 차이는 storage index 사용 여부에 따른 차이로 표현식을 사용한 칼럼은 storage index를 사용할 수 없다는 것을 알 수 있습니다. 아울러 Offload Returned Bytes의 차이는 오프로딩을 지원하지 않는 함수에 따른 차이인 것을 알 수 있습니다.
Global Stats
======================================================================================================
| Elapsed | Cpu | IO | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
======================================================================================================
| 0.01 | 0.01 | 0.00 | 1273 | 1GB | 944KB | 1GB | 14384 | 100.00% | -- 3
| 3.36 | 3.30 | 0.05 | 2326 | 1GB | 1GB | 1GB | 1GB | 12.80% | -- 4
| 0.09 | 0.02 | 0.08 | 1273 | 1GB | 1GB | 1GB | 449KB | 99.97% | -- 5
======================================================================================================
아래는 주요 세션 통계 값을 비교한 결과입니다.
+----------------------------------------------------------------+----------+----------+----------+--------------------+
|NAME | 3| 4| 5|CLASS |
+----------------------------------------------------------------+----------+----------+----------+--------------------+
|cell flash cache read hits | 8| 2307| 1273|Cache |
|cell flash cache read hits for smart IO | 8| 2307| 1273|Cache |
|cell physical IO bytes saved by storage index |1320329216| 0| 0|Cache | -- !
|physical read IO requests | 1273| 2307| 1273|Cache |
|physical read requests optimized | 1273| 2307| 1273|Cache |
|physical read total IO requests | 1273| 2307| 1273|Cache |
|physical read total multi block requests | 1264| 2162| 1264|Cache |
|cell IO uncompressed bytes | 966656|1329766400|1321295872|SQL |
|cell blocks helped by minscn optimization | 118| 236684| 161291|SQL |
|cell blocks pivoted | 2| 162325| 2|SQL | -- !
|cell blocks returned by data layer | 2| 161291| 2|SQL | -- !
|cell physical IO interconnect bytes | 14384|1160812328| 459664|SQL |
|cell physical IO interconnect bytes returned by smart scan | 14384|1160812328| 459664|SQL |
|table scan blocks gotten | 3| 161292| 3|SQL |
|table scan disk non-IMC rows gotten | 162| 10000062| 162|SQL |
|table scan rows gotten | 162| 10000062| 162|SQL |
|table scan rs1 | 3| 0| 3|SQL |
+----------------------------------------------------------------+----------+----------+----------+--------------------+