반응형
Exadata의 강력한 기능 중 하나인 Smart Scan
스토리지에서 DB 서버로의 데이터 전송을 최소화 하기 위한 개념 및 기능.
사용자가 필요로 하는 최소한의 데이터를 읽고 선별하여 전송하는 방법
Smart Scan 사용 유도 방법
1.full scan
2.cell_offload_processing 파라미터는 true(pip의 경우 6-node이므로 모든 sid가 다 적용되어야 함)
3.특정 SQL문장에서 오프로드 하려면 힌트적용 /*+ OPT_PARAM('cell_offload_processing' 'true')*/
4.sql plan에서 storage라는 절이 보이면 cell offload되고 있다고 보면 됨
5.lob이나 long컬럼의 select 절에서는 celloffload가 안됨
출처 : https://otsteam.tistory.com/18
Cell Offload
엑사의 장점을 살릴려면 cell offload 되도록 쿼리 플랜을 풀리도록 해야 합니다. Cell offload는 말 그대로 cellserver에서 원하는 데이터만 db server 로 전송 시키는 기능을 말합니다. Cell offload 유도를 하.
otsteam.tistory.com
smart scan과 cell offload는 같은 의미의 단어라고 할 수 있습니다.
Smart scan and cell offload 에 대한 출처
출처 : https://link.springer.com/chapter/10.1007/978-1-4302-4915-3_15
실제로 exadata 서버에서 조건절을 추가하고 쿼리 수행이 안된 케이스 가 있어서 SQL Monitor를 확인하여 비교하였습니다.
AS - IS
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : IBMDBA04 (6100:1666)
SQL ID : 1h59nu0f5wca4
SQL Execution ID : 16777216
Execution Started : 11/02/2021 18:16:00
REPORT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
First Refresh Time : 11/02/2021 18:16:00
Last Refresh Time : 11/02/2021 18:16:01
Duration : 1s
Module/Action : Orange for ORACLE (Unicode) DBA /7.0.0 (Build:77,S)
Service : dbedwp
Program : TorangeV7.1.exe
Fetch Calls : 2
Global Stats
=================================================================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Fetch | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell |
REPORT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
=================================================================================================================================================
| 0.61 | 0.36 | 0.24 | 0.00 | 0.01 | 2 | 6M | 47124 | 45GB | 44GB | 45GB | 13MB | 98.85% |
=================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1531138408)
===============================================================================================================================================================================
| 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 | +1 | 1 | 83 | | | . | | |
REPORT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 577K | 1 | +1 | 1 | 83 | | | 9MB | | |
| 2 | NESTED LOOPS | | 1 | 577K | 1 | +1 | 1 | 544 | | | . | | |
| 3 | NESTED LOOPS | | 1 | 577K | 1 | +1 | 1 | 544 | | | . | | |
| 4 | NESTED LOOPS | | 1 | 577K | 1 | +1 | 1 | 544 | | | . | | |
| 5 | HASH JOIN | | 1 | 577K | 1 | +1 | 1 | 544 | | | 4MB | | |
| 6 | JOIN FILTER CREATE | :BF0000 | 1 | 288K | 1 | +1 | 1 | 136 | | | . | | |
| 7 | VIEW | | 1 | 288K | 1 | +1 | 1 | 136 | | | . | | |
| 8 | FILTER | | | | 1 | +1 | 1 | 136 | | | . | | |
| 9 | HASH GROUP BY | | 1 | 288K | 1 | +1 | 1 | 136 | | | 5MB | | |
| 10 | TABLE ACCESS STORAGE FULL | DM_FTR058S | 620 | 288K | 1 | +1 | 1 | 544 | 23559 | 22GB | 7MB | 100.00 | Cpu (1) |
| 11 | JOIN FILTER USE | :BF0000 | 614 | 288K | 1 | +1 | 1 | 544 | | | . | | |
REPORT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 12 | TABLE ACCESS STORAGE FULL | DM_FTR058S | 614 | 288K | 1 | +1 | 1 | 544 | 23559 | 22GB | 7MB | | |
| 13 | PARTITION RANGE SINGLE | | 1 | 2 | 1 | +1 | 544 | 544 | | | . | | |
| 14 | TABLE ACCESS BY LOCAL INDEX ROWID | DM_DOE003M | 1 | 2 | 1 | +1 | 544 | 544 | | | . | | |
| 15 | INDEX UNIQUE SCAN | PK_DM_DOE003M | 1 | 1 | 1 | +1 | 544 | 544 | 3 | 24576 | . | | |
| 16 | PARTITION RANGE SINGLE | | 1 | | 1 | +1 | 544 | 544 | | | . | | |
| 17 | INDEX UNIQUE SCAN | PK_DM_DOC003M | 1 | | 1 | +1 | 544 | 544 | | | . | | |
| 18 | TABLE ACCESS BY LOCAL INDEX ROWID | DM_DOC003M | 1 | 1 | 1 | +1 | 544 | 544 | 3 | 24576 | . | | |
===============================================================================================================================================================================
TO-BE
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : IBMDBA04 (2551:5249)
SQL ID : 06h98hz2mxbyu
SQL Execution ID : 16777216
Execution Started : 11/02/2021 17:54:15
REPORT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
First Refresh Time : 11/02/2021 17:54:15
Last Refresh Time : 11/02/2021 18:13:49
Duration : 1177s
Module/Action : Orange for ORACLE (Unicode) DBA /7.0.0 (Build:77,S)
Service : dbedwp
Program : TorangeV7.1.exe
Global Stats
==================================================================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Uncompressed | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Bytes | Elig Bytes | Returned Bytes | Offload |
REPORT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
==================================================================================================================================================================
| 1173 | 1163 | 3.79 | 0.00 | 0.00 | 0.95 | 4.75 | 219M | 27812 | 21GB | 21GB | 21GB | 6MB | 98.81% |
==================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2107659493)
=========================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) |
=========================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 1164 | +11 | 1 | 0 | | | . | | |
| -> 1 | HASH GROUP BY | | 1 | 379K | 1164 | +11 | 1 | 0 | | | 9MB | | |
REPORT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| -> 2 | NESTED LOOPS | | 1 | 379K | 1164 | +11 | 1 | 237 | | | . | | |
| -> 3 | NESTED LOOPS | | 1 | 379K | 1164 | +11 | 1 | 237 | | | . | | |
| -> 4 | NESTED LOOPS | | 1 | 379K | 1164 | +11 | 1 | 237 | | | . | | |
| -> 5 | NESTED LOOPS | | 1 | 379K | 1169 | +6 | 1 | 237 | | | . | | |
| 6 | TABLE ACCESS STORAGE FULL | DM_FTR058S | 15 | 288K | 1174 | +1 | 1 | 237 | 22178 | 21GB | 7MB | 0.09 | Cpu (1) |
| -> 7 | VIEW PUSHED PREDICATE | | 1 | 6066 | 1164 | +11 | 238 | 237 | | | . | | |
| -> 8 | FILTER | | | | 1164 | +11 | 238 | 237 | | | . | | |
| -> 9 | SORT GROUP BY | | 1 | 6066 | 1169 | +6 | 238 | 237 | | | . | | |
| -> 10 | TABLE ACCESS BY INDEX ROWID BATCHED | DM_FTR058S | 1 | 6065 | 1169 | +6 | 238 | 949 | 227 | 2MB | . | | |
| -> 11 | INDEX SKIP SCAN | PK_DM_FTR058S | 13 | 6052 | 1175 | +2 | 238 | 949 | 5402 | 42MB | . | 99.91 | gc cr grant 2-way (1) |
| | | | | | | | | | | | | | log buffer space (1) |
REPORT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| | | | | | | | | | | | | | Cpu (1166) |
| | | | | | | | | | | | | | cell single block physical read (5) |
| -> 12 | PARTITION RANGE SINGLE | | 1 | 2 | 1164 | +11 | 237 | 237 | | | . | | |
| -> 13 | TABLE ACCESS BY LOCAL INDEX ROWID | DM_DOE003M | 1 | 2 | 1164 | +11 | 237 | 237 | | | . | | |
| -> 14 | INDEX UNIQUE SCAN | PK_DM_DOE003M | 1 | 1 | 1164 | +11 | 237 | 237 | 5 | 40960 | . | | |
| -> 15 | PARTITION RANGE SINGLE | | 1 | | 1164 | +11 | 238 | 237 | | | . | | |
| -> 16 | INDEX UNIQUE SCAN | PK_DM_DOC003M | 1 | | 1164 | +11 | 238 | 237 | | | . | | |
| -> 17 | TABLE ACCESS BY LOCAL INDEX ROWID | DM_DOC003M | 1 | 1 | 1164 | +11 | 237 | 237 | | | . | | |
=========================================================================================================================================================================================================To-BE의 10번을 보면 TABLE ACCESS BY INDEX ROWID BATCHED로 AS-IS의 10번에서 STORAGE FULL 을 타면서 Smart Scan을 타던게 안타고 있는것을 확인할 수 있었습니다.
해결으로는 해당 테이블이 Smart Scan을 탈 수 있게 강제로 FULL 힌트를 부여하여쿼리를 수행하여 해결하였습니다.
Exadata에 대해 하나 배운 경험이었습니다..
반응형
'Oracle > Exadata' 카테고리의 다른 글
| Exadata 스마트 스캔 힌트로 제어 하기. (2) | 2024.01.13 |
|---|---|
| 장비 시리얼 번호 알기 (0) | 2023.06.29 |
| [Exadata HCC] 각 압축 옵션간의 DML 시 변화 체크 (0) | 2023.06.23 |
| [Sundiag] 한 노드에서 한번에 EXADATA H/W 수집하는 쉘 (0) | 2023.02.08 |
댓글