반응형
스마트 스캔은 이전글을 참고해주세요.
https://bae9086.tistory.com/332
Smart Scan(Cell offload)
Exadata의 강력한 기능 중 하나인 Smart Scan 스토리지에서 DB 서버로의 데이터 전송을 최소화 하기 위한 개념 및 기능. 사용자가 필요로 하는 최소한의 데이터를 읽고 선별하여 전송하는 방법 Smart Sca
bae9086.tistory.com
힌트로 제어가 가능한지. 그러면 어떻게 확인할 수 있는지 테스트로 정리해보겠습니다.
SQL 레벨에서 힌트 사용[dbms_sqltune.report_sql_monitor] 으로 제어 할 수 있고, 1)dbms_sqltune.report_sql_monitor 의 global_stats 부분을 보거나 2) v$sql_stats의 cell_offload관련 컬럼의 값들을 통해 알 수 있습니다.
1) 힌트 미 사용
select /*NO_HINT*//*+ MONITOR */
count(*)
from dm_fim030m A
where bs_ym = '202207';
sql_monitor
확인결과, global_stats에는 따로 cell_offload관련 정보가 없으며 실행계획도 index를 사용한 fast full scan으로 수행되었습니다.
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.59 | 0.58 | 0.00 | 1 | 72620 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=3942200234)
===============================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
===============================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | |
| 2 | PARTITION RANGE SINGLE | | 21M | 1624 | 1 | +0 | 1 | 13M | | |
| 3 | INDEX STORAGE FAST FULL SCAN | PK_DM_FIM030M | 21M | 1624 | 1 | +0 | 1 | 13M | | |
===============================================================================================================================================2)힌트 사용
select /*HINT1*//*+ MONITOR OPT_PARAM('cell_offload_processing' 'true') FULL(A) */
count(*)
from dm_fim030m A
where bs_ym = '202207';
sql_monitor
global_stat 관련해서 offload 관련 정보가 보입니다.
원래는 2G읽어야 되는데 offload 사용되서 78M 읽음을 확인할 수 있습니다.
========================================
Offload | Offload | Cell |
Elig Bytes | Returned Bytes | Offload |
========================================
2GB | 78MB | 424.07% |
========================================
Global Stats
=====================================================================================================================================
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
=====================================================================================================================================
| 0.42 | 0.39 | 0.03 | 0.00 | 1 | 210K | 1655 | 2GB | 7GB | 2GB | 78MB | 424.07% |
=====================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2044884927)
================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | . | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | | . | | |
| 2 | PARTITION RANGE SINGLE | | 21M | 428K | 1 | +0 | 1 | 13M | | | . | | |
| 3 | TABLE ACCESS STORAGE FULL | DM_FIM030M | 21M | 428K | 1 | +0 | 1 | 13M | 1655 | 2GB | 3MB | 100.00 | Cpu (1) |
================================================================================================================================================================
3)스마트스캔 사용안하는 힌트 사용
select /*HINT2*//*+ MONITOR OPT_PARAM('cell_offload_processing' 'false') FULL(A) */ count(*) from dm_fim030m A where bs_ym = '202401'
sql_monitor
global stats보면 offload관련 stats가 안보입니다. 또 실행계획을보면 full scan으로 수행되는것을 볼 수 있습니다.
Global Stats
=======================================
| Elapsed | Other | Fetch | Buffer |
| Time(s) | Waits(s) | Calls | Gets |
=======================================
| 0.00 | 0.00 | 1 | 3 |
=======================================
SQL Plan Monitoring Details (Plan Hash Value=2044884927)
=========================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
=========================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 2 | | | 1 | | | |
| 3 | TABLE ACCESS STORAGE FULL | DM_FIM030M | 1 | 2 | | | 1 | | | |
=========================================================================================================================================
4)제어 힌트만 사용, full(a) 는 빼고
select /*HINT3*//*+ MONITOR OPT_PARAM('cell_offload_processing' 'true') */ count(*) from dm_fim030m A where bs_ym = '202207'
sql_monitor
제어 힌트만 사용했을때는 옵티마이저가 스마트스캔보다 그냥 스캔하는것이 더 낫다고 판단하는 것인지 사용하지 않네요.
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.52 | 0.52 | 0.00 | 1 | 72620 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=3942200234)
===============================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
===============================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | |
| 2 | PARTITION RANGE SINGLE | | 21M | 1624 | 1 | +0 | 1 | 13M | | |
| 3 | INDEX STORAGE FAST FULL SCAN | PK_DM_FIM030M | 21M | 1624 | 2 | +0 | 1 | 13M | 100.00 | Cpu (1) |
===============================================================================================================================================
++) 추가적으로 v$sql_Stats 뷰에서도 볼 수 있습니다.
스마트 스캔을 사용한 sql은 cell_offload관련 컬럼에 값이 들어가있고 스마트스캔으로 자원을 얼마나 사용했는지 알 수 있습니다.
SQL> select SQL_TEXT,SQL_ID,LAST_ACTIVE_TIME,DISK_READS,IO_CELL_OFFLOAD_ELIGIBLE_BYTES,IO_INTERCONNECT_BYTES,IO_CELL_UNCOMPRESSED_BYTES,IO_CELL_OFFLOAD_RETURNED_BYTES From v$sqlstats
where sql_id in ('90vzr2jr2uwrh','gndf2f414yu3z');
SQL_TEXT SQL_ID LAST_ACTIVE_TIME DISK_READS IO_CELL_OFFLOAD_ELIGIBLE_BYTES IO_INTERCONNECT_BYTES IO_CELL_UNCOMPRESSED_BYTES IO_CELL_OFFLOAD_RETURNED_BYTES
------------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------- ---------- ------------------------------ --------------------- -------------------------- ------------------------------
select /*NO_HINT*//*+ MONITOR */ count(*) from dm_fim030m A where bs_ym = '202207' gndf2f414yu3z 2024/01/12 17:39:02 0 0 0 0 0
select /*HINT1*//*+ MONITOR OPT_PARAM('cell_offload_processing' 'true') FULL(A) */ count(*) from dm_fim030m A where bs_ym = '202207' 90vzr2jr2uwrh 2024/01/12 17:51:23 209132 1.7e+009 81483120 7.3e+009 81483120반응형
'Oracle > Exadata' 카테고리의 다른 글
| 장비 시리얼 번호 알기 (0) | 2023.06.29 |
|---|---|
| [Exadata HCC] 각 압축 옵션간의 DML 시 변화 체크 (0) | 2023.06.23 |
| [Sundiag] 한 노드에서 한번에 EXADATA H/W 수집하는 쉘 (0) | 2023.02.08 |
| Smart Scan(Cell offload) (0) | 2021.11.04 |
댓글