System Software User's Guide의 Operation Not Being Offloaded 항목에는 쿼리가 compressed LOB이나 out-of-line LOB을 포함하면 오프로딩이 동작하지 않는다는 내용이 포함되어 있습니다.
A smart I/O operation cannot be offloaded to the Exadata storage servers in the following cases:
The query contains a compressed or out-of-line LOB. An out-of-line LOB stores LOB data apart from the other row data and is typically larger than 4 KB in size.
테스트를 위해 아래와 같이 테이블을 생성하고 파라미터를 설정하겠습니다. c2 칼럼에 비압축 inline CLOB, c3 칼럼에 압축 inline CLOB, c4 칼럼에 비압축 out-of-line CLOB 값이 저장되어 있습니다.
-- 1-1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2, c3, c4)
LOB (c3) STORE AS SECUREFILE (COMPRESS HIGH)
AS
SELECT ROWNUM
, TO_CLOB (LPAD ('X', 1900, 'X'))
, TO_CLOB (LPAD ('Y', 4000, 'Y'))
, TO_CLOB (LPAD ('Z', 4000, 'Z'))
FROM XMLTABLE ('1 to 100000');
-- 1-2
SELECT segment_name, bytes / POWER (1024, 3) AS gb
FROM user_segments
WHERE segment_name = 'T1';
SEGMENT_NAME GB
------------ ----
T1 0.77
1개의 행이 선택되었습니다.
-- 1-3
SELECT a.column_name, a.compression, b.bytes / POWER (1024, 3) AS gb
FROM user_lobs a
, user_segments b
WHERE a.table_name = 'T1'
AND b.segment_name = a.segment_name;
COLUMN_NAME COMPRESSION GB
----------- ------------ ----
C2 NO 0.00
C3 HIGH 0.00
C4 NO 0.85
3 행이 선택되었습니다.
-- 1-4
ALTER SESSION SET "_serial_direct_read" = 'ALWAYS';
ALTER SESSION SET "_small_table_threshold" = 0;
아래 예제는 비압축 inline CLOB인 c2 칼럼을 조회합니다. 테이블을 smart scan하고 수행에 0.25초가 소요됩니다.
-- 2
SELECT /*+ MONITOR */
COUNT (TO_CHAR (c2))
FROM t1;
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.25 | 0.23 | 0.02 | 1116 | 781MB | 784MB | 781MB | 391MB | 50.27% |
======================================================================================================
+--------------------------------------------------+----------+-----------+-----------+--------------------+
|NAME | WAITS| WAITED| AVG_WAIT|CLASS |
+--------------------------------------------------+----------+-----------+-----------+--------------------+
|cell smart table scan | 363.00 | 22.01ms| 60.62us|User I/O |
+--------------------------------------------------+----------+-----------+-----------+--------------------+
아래 예제는 압축 inline CLOB인 c3 칼럼을 조회합니다. 테이블을 smart scan하고 수행에 1.35초가 소요됩니다. 문서의 내용과 달리 compressed LOB이라도 inline LOB이라면 오프로딩이 동작하는 것을 확인할 수 있습니다. 참고로 LOB 압축 해제는 오프로딩되지 않습니다. CLOB 압축을 해제하기 위해 Cpu Time이 증가한 것을 확인할 수 있습니다.
-- 3
SELECT /*+ MONITOR */
COUNT (TO_CHAR (c3))
FROM t1;
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 |
======================================================================================================
| 1.35 | 1.33 | 0.01 | 794 | 781MB | 781MB | 781MB | 19MB | 97.62% |
======================================================================================================
+--------------------------------------------------+----------+-----------+-----------+--------------------+
|NAME | WAITS| WAITED| AVG_WAIT|CLASS |
+--------------------------------------------------+----------+-----------+-----------+--------------------+
|cell smart table scan | 65.00 | 3.34ms| 51.35us|User I/O |
+--------------------------------------------------+----------+-----------+-----------+--------------------+
아래 예제는 비압축 out-of-line CLOB인 c4 칼럼을 조회합니다. 테이블을 smart scan, CLOB을 direct path read하고 수행에 10초가 소요됩니다. out-of-line LOB은 로우 수만큼 읽기 요청이 발생하며 이로 인해 Cpu Time, IO Waits이 증가합니다.
-- 4
SELECT /*+ MONITOR */
COUNT (TO_CHAR (c4))
FROM t1;
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 |
======================================================================================================
| 10 | 4.07 | 5.80 | 101K | 2GB | 781MB | 781MB | 16MB | 98.01% |
======================================================================================================
+--------------------------------------------------+----------+-----------+-----------+--------------------+
|NAME | WAITS| WAITED| AVG_WAIT|CLASS |
+--------------------------------------------------+----------+-----------+-----------+--------------------+
|cell smart table scan | 74.00 | 2.53ms| 34.19us|User I/O |
|direct path read | 100.00k| 5.21 s| 52.06us|User I/O |
+--------------------------------------------------+----------+-----------+-----------+--------------------+
아래는 SQL 모니터 리포트의 Global Stats 항목을 비교한 결과입니다.
-- 5
======================================================================================================
| Elapsed | Cpu | IO | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
======================================================================================================
| 0.25 | 0.23 | 0.02 | 1116 | 781MB | 784MB | 781MB | 391MB | 50.27% | -- 2
| 1.35 | 1.33 | 0.01 | 794 | 781MB | 781MB | 781MB | 19MB | 97.62% | -- 3
| 10 | 4.07 | 5.80 | 101K | 2GB | 781MB | 781MB | 16MB | 98.01% | -- 4
======================================================================================================
아래는 주요 세션 통계 값을 비교한 결과입니다.
-- 6
+-----------------------------------------------------------+----------+----------+----------+------+
|NAME | 2| 3| 4|CLASS |
+-----------------------------------------------------------+----------+----------+----------+------+
|physical read IO requests | 1.13K| 794.00 | 100.79K|Cache | -- !
|physical read bytes | 781.25m| 781.25m| 1.53g|Cache |
|physical read requests optimized | 1.13K| 794.00 | 100.79K|Cache |
|physical read total IO requests | 1.13K| 794.00 | 100.79K|Cache |
|physical read total bytes | 781.25m| 781.25m| 1.53g|Cache |
|physical read total bytes optimized | 781.25m| 781.25m| 1.53g|Cache |
|physical reads | 100.00K| 100.00K| 200.00K|Cache |
|physical reads direct | 100.00K| 100.00K| 200.00K|Cache |
|physical reads direct (lob) | 0.00 | 0.00 | 100.00K|Cache | -- !
|cell physical IO interconnect bytes | 391.00m| 18.58m| 796.78m|SQL |
|cell physical IO interconnect bytes returned by smart scan | 391.00m| 18.58m| 15.53m|SQL |
|securefile direct read bytes | 0.00 | 0.00 | 781.25m|Debug |
|securefile direct read ops | 0.00 | 0.00 | 100.00K|Debug | -- !
|shared io pool buffer get success | 0.00 | 0.00 | 100.00K|Debug |
+-----------------------------------------------------------+----------+----------+----------+------+