Techdata VINA 지식 공유

Exadata Smart Scan 모니터링 하는 3가지 쿼리 입니다.

" Exadata에서 Smart Scan은 Query 성능 향상을 위한 핵심 기술입니다. Smart Scan은 Partitioning,HCC 압축과 함께 사용되면 그 효율성이 더욱 좋아집니다. Smart Scan으로 수행되던 Quer y가 Smart Scan으로 동작하지 않을  때, 성능저하의 문제가 야기될 수 있습니다 "

MOS Doc ID: 1438173.1 에서 Smart Scan과 관련된 통계를 조회할 수 있는  Query를 제공하고 있습니다.

## Doc ID: 1438173.1

set pagesize 999
set lines 190
col sql_text format a70 trunc
col child format 99999
col execs format 9,999
col avg_etime format 99,999.99
col "IO_SAVED_%" format 999.99
col avg_px format 999
col offload for a7

select sql_id, child_number child, plan_hash_value plan_hash, executions execs, 
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions)/
decode(px_servers_executions,0,1,px_servers_executions/decode(nvl(executions,0),0,1,executions)) avg_etime, 
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') Offload,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,0,100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)
/decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,1,IO_CELL_OFFLOAD_ELIGIBLE_BYTES)) "IO_SAVED_%",
sql_text
from v$sql s
where upper(sql_text) like upper(nvl(q'[&sql_text]',sql_text))
and sql_text not like 'BEGIN :sql_text := %'
and sql_text not like '%IO_CELL_OFFLOAD_ELIGIBLE_BYTES%'
and sql_text not like '/* SQL Analyze(%'
and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
/

SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT

------ ---------- ---------- ------ ---------- ------ ------- ---------- -------------------------- ----------------

abgqtq8zt90sb 0 3570092908 1 .79 0 Yes 99.86 select * from test_objects where object_id='54'

select b.name, a.value from v$mystat a, v$statname b
  where a.STATISTIC# = b.STATISTIC# and
        (b.name = 'cell session smart scan efficiency' or
         b.name = 'cell physical IO bytes saved by storage index' or
         b.name = 'cell physical IO bytes eligible for predicate offload' or
         b.name = 'cell physical IO interconnect bytes returned by smart scan'
         or
         b.name = 'cell IO uncompressed bytes' 
         or
         b.name like '%cell blocks processed%');


select name, value from v$mystat s, v$statname n
 where n.statistic#=s.statistic#
 and name like 'cell scans';

Smart Scan이 동작하지 않는 대표적인 예 입니다. 

 (※ Full List는 Doc ID 1927934.1의 What are the situations that prevent Smart Scan from happening ? 부분 참조)

1. IOT와 Clustred Table의 Scan

2. Index Range Scans

3. Access to a compressed index

4. Access to a reverse key index

5. Cell Storage Server CPU의 과부하 상태일 때 (관련 통계정보: cell physical IO bytes sent directly to DB node to balance CPU)

6. Table에 CACHE property가 있을 때

7. Table이 KEEP Buffer 로 설정되어 있을 때

8. 255개 이상의 column이 access될 때

9. Cahined Rows에 대해 Smart Scan이 발생될 때 Computing Node에 의한 추가적인 Single Block Access가 발생될 수 있음.

Smart Scan  하기 위한 전제가 몇 가지 있습니다.

  (출처 : Exadata Smart Scan FAQ (Doc ID 1927934.1) )

1. Full Table Sacn 또는 Fast Full Index Scan, Full Bitmap Index Scan이 발생 되어야 합니다.

2. Buffer Cache로 부터 블록을 읽어들이지 않고 Disk로 부터 Direct Path Read를 해야 합니다.

-  Parallel Query는 SMALL TABLE보다 큰 테이블에 대해 Direct Path Read를 합니다.

-  Serial Query는 VERYLARGE TABLE보다 작은 테이블에 대해 Buffer Read를 합니다.,

_serial_direct_read=TRUE 로 direct read를 강제할 수 있습니다.

3. Scan되는 Segment는 Exadata Cell Storage상의 Disk Group에 존재해야 합니다. 즉, Exadata 나 SuperCluster 등에서만 동작합니다.

4. DB는 11.2 이상이고 Parameter CELL_OFFLOAD_PROCESSING=TRUE로 설정되어 있어야 합니다.

5. ASM은 11.2 이상이고 Diskgroup의 Attribute인 cell.smart_scan_capable=TRUE 로 설정되어 있어야 합니다.

6. No quarantines for the sql_id

dcli -g cell_group -l root cellcli -e list quarantine list quarantine <#> detail -- run for each quarantine

( MOS Doc ID 1534130.1 How To Tell If a Statement is Quarantined by Cellsrv )