병렬 프로세스별 I/O 확인 SQL
SELECT q.sql_id,
NVL(p.qcinst_id, p.inst_id) || ':' || p.qcsid AS qc,
p.inst_id, p.sid,
NVL2(p.server_set, 'Set'||p.server_set||'-'||p.server#, 'QC') AS role,
s.status, p.degree, p.req_degree,
MAX(DECODE(n.name,'session logical reads', p.value)) AS buffer_gets,
MAX(DECODE(n.name,'consistent gets', p.value)) AS cr_gets,
MAX(DECODE(n.name,'db block gets', p.value)) AS current_gets,
MAX(DECODE(n.name,'physical reads', p.value)) AS disk_reads,
MAX(DECODE(n.name,'physical reads direct', p.value)) AS direct_reads,
ROUND(MAX(DECODE(n.name,'physical read total bytes',p.value))/1048576,1) AS read_mb,
-- Exadata Smart Scan 확인용
ROUND(MAX(DECODE(n.name,'cell physical IO bytes eligible for predicate offload',p.value))/1048576,1) AS offload_elig_mb,
ROUND(MAX(DECODE(n.name,'cell physical IO interconnect bytes returned by smart scan',p.value))/1048576,1) AS smartscan_ret_mb,
DECODE(s.state,'WAITING',s.event,'ON CPU') AS event
FROM gv$px_sesstat p
JOIN v$statname n ON n.statistic# = p.statistic#
JOIN gv$session s ON s.inst_id = p.inst_id AND s.sid = p.sid AND s.serial# = p.serial#
JOIN gv$session q ON q.inst_id = NVL(p.qcinst_id, p.inst_id) AND q.sid = p.qcsid
WHERE q.sql_id = 'b1ad0sj26zcy0' -- 필터를 안쪽으로 밀어 넣음
AND n.name IN ('session logical reads','consistent gets','db block gets',
'physical reads','physical reads direct','physical read total bytes',
'cell physical IO bytes eligible for predicate offload',
'cell physical IO interconnect bytes returned by smart scan')
GROUP BY q.sql_id, p.qcinst_id, p.qcsid, p.inst_id, p.sid,
s.status, p.server_set, p.server#, p.degree, p.req_degree, s.state, s.event
ORDER BY NVL(p.qcinst_id, p.inst_id), p.qcsid,
p.server_set NULLS FIRST, p.server#, p.inst_id, p.sid;
- Exadata 지표 추가 : offload_elig_mb 대비 smartscan_ret_mb 비율로 Smart Scan이 실제로 효과를 내는지 볼 수 있습니다.
더 효율적인 방법: gv$sql_monitor
- Tuning Pack 라이선스가 있다면
- - v$statname 조인과 피벗이 필요 없어 훨씬 가벼움.
- - 약 1초 주기로 갱신.
- - Exadata 오프로드 컬럼이 기본으로 들어 있음.
SELECT inst_id, sid,
NVL2(px_server_set, 'Set'||px_server_set||'-'||px_server#, 'QC') AS role,
status, buffer_gets, disk_reads, physical_read_requests AS read_req,
ROUND(physical_read_bytes/1048576,1) AS read_mb,
ROUND(io_interconnect_bytes/1048576,1) AS interconnect_mb,
ROUND(io_cell_offload_eligible_bytes/1048576,1) AS offload_elig_mb,
ROUND(elapsed_time/1e6,1) AS ela_s,
ROUND(user_io_wait_time/1e6,1) AS io_wait_s
FROM gv$sql_monitor
WHERE sql_id = 'b1ad0sj26zcy0'
AND sql_exec_id = (SELECT MAX(sql_exec_id) FROM gv$sql_monitor
WHERE sql_id = 'b1ad0sj26zcy0' AND status = 'EXECUTING')
ORDER BY px_server_set NULLS FIRST, px_server#, inst_id, sid;
- plan line별 I/O를 보려면 `gv$sql_plan_monitor`를 사용
- 리얼모니터도 추천 : DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>..., type=>'ACTIVE')
- 참고: 두 쿼리 모두 값이 누적치라서 "지금 초당 몇 MB인지"는 바로 보이지 않음.
- 진짜 실시간 처리량은 두 번 조회한 값의 차이를 경과 시간으로 나눠야 합니다.