System Software User's Guide의 Operation Not Being Offloaded 항목에는 쿼리가 255개 이상의 단일 테이블 칼럼을 참조하면 오프로딩이 동작하지 않고 HCC 압축을 사용하면 제한을 회피할 수 있다는 내용이 포함되어 있습니다.
A smart I/O operation cannot be offloaded to the Exadata storage servers in the following cases: A query that has more than 255 columns referenced, and the heap table is uncompressed, or Basic or OLTP compressed. However, such queries on tables compressed using Exadata Hybrid Columnar Compression are offloaded.
테스트를 위해 아래와 같이 테이블을 생성하고 파라미터를 설정하겠습니다.
-- 1-1
SET SERVEROUT ON
DECLARE
v_sql_text VARCHAR2(32767);
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE t1 PURGE';
v_sql_text := 'CREATE TABLE t1 (c001';
FOR i IN 2 .. 300 LOOP v_sql_text := v_sql_text || ', c' || LPAD (i, 3, '0'); END LOOP;
v_sql_text := v_sql_text || ') AS ';
v_sql_text := v_sql_text || 'SELECT ROWNUM';
FOR i IN 2 .. 300 LOOP v_sql_text := v_sql_text || ', ROWNUM + ' || i; END LOOP;
v_sql_text := v_sql_text || ' FROM XMLTABLE (''1 to 10000000'')';
EXECUTE IMMEDIATE v_sql_text;
DBMS_OUTPUT.PUT_LINE (v_sql_text);
END;
/
-- 1-2
ALTER SESSION SET "_serial_direct_read" = 'ALWAYS';
ALTER SESSION SET "_small_table_threshold" = 0;
아래 예제는 300개 칼럼을 조회하고 수행에 12초가 소요됩니다. Global Stats 항목에서 smart scan이 동작하지 않은 것을 확인할 수 있습니다.
-- 2
SELECT /*+ MONITOR */
*
FROM t1
WHERE c001 = 1;
Global Stats
================================================
| Elapsed | Cpu | IO | Read | Read |
| Time(s) | Time(s) | Waits(s) | Reqs | Bytes |
================================================
| 12 | 6.47 | 5.59 | 25422 | 25GB |
================================================
아래 예제는 255개 칼럼을 조회하고 수행에 0.13초가 소요됩니다. Global Stats 항목에서 smart scan이 동작한 것을 확인할 수 있습니다.
-- 4
SELECT /*+ MONITOR */
c003, c004, c005, c006, c007, c008, c009, c010, c011, c012, c013, c014, c015, c016, c017, c018, c019, c020
, c021, c022, c023, c024, c025, c026, c027, c028, c029, c030, c031, c032, c033, c034, c035, c036, c037, c038, c039, c040
, c041, c042, c043, c044, c045, c046, c047, c048, c049, c050, c051, c052, c053, c054, c055, c056, c057, c058, c059, c060
, c061, c062, c063, c064, c065, c066, c067, c068, c069, c070, c071, c072, c073, c074, c075, c076, c077, c078, c079, c080
, c081, c082, c083, c084, c085, c086, c087, c088, c089, c090, c091, c092, c093, c094, c095, c096, c097, c098, c099, c100
, c101, c102, c103, c104, c105, c106, c107, c108, c109, c110, c111, c112, c113, c114, c115, c116, c117, c118, c119, c120
, c121, c122, c123, c124, c125, c126, c127, c128, c129, c130, c131, c132, c133, c134, c135, c136, c137, c138, c139, c140
, c141, c142, c143, c144, c145, c146, c147, c148, c149, c150, c151, c152, c153, c154, c155, c156, c157, c158, c159, c160
, c161, c162, c163, c164, c165, c166, c167, c168, c169, c170, c171, c172, c173, c174, c175, c176, c177, c178, c179, c180
, c181, c182, c183, c184, c185, c186, c187, c188, c189, c190, c191, c192, c193, c194, c195, c196, c197, c198, c199, c200
, c201, c202, c203, c204, c205, c206, c207, c208, c209, c210, c211, c212, c213, c214, c215, c216, c217, c218, c219, c220
, c221, c222, c223, c224, c225, c226, c227, c228, c229, c230, c231, c232, c233, c234, c235, c236, c237, c238, c239, c240
, c241, c242, c243, c244, c245, c246, c247, c248, c249, c250, c251, c252, c253, c254, c255, c256
FROM t1
WHERE c001 = 1;
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.13 | 0.07 | 0.06 | 25422 | 25GB | 944KB | 25GB | 10264 | 100.00% |
=======================================================================================================
아래 예제는 256개 칼럼을 조회하고 수행에 9.3초가 소요됩니다. Global Stats 항목에서 smart scan이 동작하지 않은 것을 확인할 수 있습니다. 문서 내용처럼 테이블 칼럼 수가 아닌 쿼리에서 참조한 단일 테이블 칼럼 수에 의해 smart scan의 동작 여부가 결정된 것을 알 수 있습니다.
-- 5
SELECT /*+ MONITOR */
c003, c004, c005, c006, c007, c008, c009, c010, c011, c012, c013, c014, c015, c016, c017, c018, c019, c020
, c021, c022, c023, c024, c025, c026, c027, c028, c029, c030, c031, c032, c033, c034, c035, c036, c037, c038, c039, c040
, c041, c042, c043, c044, c045, c046, c047, c048, c049, c050, c051, c052, c053, c054, c055, c056, c057, c058, c059, c060
, c061, c062, c063, c064, c065, c066, c067, c068, c069, c070, c071, c072, c073, c074, c075, c076, c077, c078, c079, c080
, c081, c082, c083, c084, c085, c086, c087, c088, c089, c090, c091, c092, c093, c094, c095, c096, c097, c098, c099, c100
, c101, c102, c103, c104, c105, c106, c107, c108, c109, c110, c111, c112, c113, c114, c115, c116, c117, c118, c119, c120
, c121, c122, c123, c124, c125, c126, c127, c128, c129, c130, c131, c132, c133, c134, c135, c136, c137, c138, c139, c140
, c141, c142, c143, c144, c145, c146, c147, c148, c149, c150, c151, c152, c153, c154, c155, c156, c157, c158, c159, c160
, c161, c162, c163, c164, c165, c166, c167, c168, c169, c170, c171, c172, c173, c174, c175, c176, c177, c178, c179, c180
, c181, c182, c183, c184, c185, c186, c187, c188, c189, c190, c191, c192, c193, c194, c195, c196, c197, c198, c199, c200
, c201, c202, c203, c204, c205, c206, c207, c208, c209, c210, c211, c212, c213, c214, c215, c216, c217, c218, c219, c220
, c221, c222, c223, c224, c225, c226, c227, c228, c229, c230, c231, c232, c233, c234, c235, c236, c237, c238, c239, c240
, c241, c242, c243, c244, c245, c246, c247, c248, c249, c250, c251, c252, c253, c254, c255, c256, c257
FROM t1
WHERE c001 = 1;
Global Stats
================================================
| Elapsed | Cpu | IO | Read | Read |
| Time(s) | Time(s) | Waits(s) | Reqs | Bytes |
================================================
| 9.30 | 5.66 | 3.64 | 25422 | 25GB |
================================================
바람직한 방법은 아니지만 셀프 조인을 통해 255개 이상의 단일 테이블 칼럼을 smart scan으로 조회할 수 있습니다. 아래 예제는 t1 테이블을 셀프 조인하여 300개 칼럼을 조회하고 smart scan이 동작하여 수행에 0.29초가 소요됩니다.
-- 6
SELECT /*+ MONITOR USE_HASH(A B) */
a.c001, a.c002, a.c003, a.c004, a.c005, a.c006, a.c007, a.c008, a.c009, a.c010, a.c011, a.c012, a.c013, a.c014, a.c015
, a.c016, a.c017, a.c018, a.c019, a.c020, a.c021, a.c022, a.c023, a.c024, a.c025, a.c026, a.c027, a.c028, a.c029, a.c030
, a.c031, a.c032, a.c033, a.c034, a.c035, a.c036, a.c037, a.c038, a.c039, a.c040, a.c041, a.c042, a.c043, a.c044, a.c045
, a.c046, a.c047, a.c048, a.c049, a.c050, a.c051, a.c052, a.c053, a.c054, a.c055, a.c056, a.c057, a.c058, a.c059, a.c060
, a.c061, a.c062, a.c063, a.c064, a.c065, a.c066, a.c067, a.c068, a.c069, a.c070, a.c071, a.c072, a.c073, a.c074, a.c075
, a.c076, a.c077, a.c078, a.c079, a.c080, a.c081, a.c082, a.c083, a.c084, a.c085, a.c086, a.c087, a.c088, a.c089, a.c090
, a.c091, a.c092, a.c093, a.c094, a.c095, a.c096, a.c097, a.c098, a.c099, a.c100, a.c101, a.c102, a.c103, a.c104, a.c105
, a.c106, a.c107, a.c108, a.c109, a.c110, a.c111, a.c112, a.c113, a.c114, a.c115, a.c116, a.c117, a.c118, a.c119, a.c120
, a.c121, a.c122, a.c123, a.c124, a.c125, a.c126, a.c127, a.c128, a.c129, a.c130, a.c131, a.c132, a.c133, a.c134, a.c135
, a.c136, a.c137, a.c138, a.c139, a.c140, a.c141, a.c142, a.c143, a.c144, a.c145, a.c146, a.c147, a.c148, a.c149, a.c150
, a.c151, a.c152, a.c153, a.c154, a.c155, a.c156, a.c157, a.c158, a.c159, a.c160, a.c161, a.c162, a.c163, a.c164, a.c165
, a.c166, a.c167, a.c168, a.c169, a.c170, a.c171, a.c172, a.c173, a.c174, a.c175, a.c176, a.c177, a.c178, a.c179, a.c180
, a.c181, a.c182, a.c183, a.c184, a.c185, a.c186, a.c187, a.c188, a.c189, a.c190, a.c191, a.c192, a.c193, a.c194, a.c195
, a.c196, a.c197, a.c198, a.c199, a.c200, a.c201, a.c202, a.c203, a.c204, a.c205, a.c206, a.c207, a.c208, a.c209, a.c210
, a.c211, a.c212, a.c213, a.c214, a.c215, a.c216, a.c217, a.c218, a.c219, a.c220, a.c221, a.c222, a.c223, a.c224, a.c225
, a.c226, a.c227, a.c228, a.c229, a.c230, a.c231, a.c232, a.c233, a.c234, a.c235, a.c236, a.c237, a.c238, a.c239, a.c240
, a.c241, a.c242, a.c243, a.c244, a.c245, a.c246, a.c247, a.c248, a.c249, a.c250, a.c251, a.c252, a.c253, a.c254, a.c255
, b.c256, b.c257, b.c258, b.c259, b.c260, b.c261, b.c262, b.c263, b.c264, b.c265, b.c266, b.c267, b.c268, b.c269, b.c270
, b.c271, b.c272, b.c273, b.c274, b.c275, b.c276, b.c277, b.c278, b.c279, b.c280, b.c281, b.c282, b.c283, b.c284, b.c285
, b.c286, b.c287, b.c288, b.c289, b.c290, b.c291, b.c292, b.c293, b.c294, b.c295, b.c296, b.c297, b.c298, b.c299, b.c300
FROM t1 a, t1 b
WHERE a.c001 = 1
AND b.c001 = a.c001;
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.29 | 0.21 | 0.07 | 50844 | 50GB | 2MB | 50GB | 7008 | 100.00% |
=======================================================================================================
t1 테이블을 HCC 압축한 후에 300개 칼럼을 조회하면 smart scan이 동작하여 수행에 0.06초가 소요됩니다. HCC 압축 테이블은 테이블 단위가 아닌 CU 단위로 255개 칼럼 제한이 동작함을 유추할 수 있습니다.
-- 7-1
ALTER TABLE t1 MOVE COMPRESS FOR QUERY HIGH PARALLEL 32;
-- 7-2
SELECT /*+ MONITOR */
*
FROM t1
WHERE c001 = 1;
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.06 | 0.01 | 0.05 | 194 | 191MB | 16GB | 191MB | 76760 | 8818.93% |
=======================================================================================================