exasnap.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
--------------------------------------------------------------------------------
-- File name: exasnap.sql (Exadata Snapper) BETA
--
-- Purpose: Display various Exadata IO efficiency metrics of a session
-- from V$SESSTAT.
--
-- Author: Tanel Poder ( http://blog.tanelpoder.com | tanel@tanelpoder.com )
--
-- Copyright: (c) 2012 All Rights Reserved
--
-- Usage:
-- 1) TAKE A SNAPSHOT
--
-- SELECT exasnap.begin_snap(<sid>) FROM dual;
-- or
-- SELECT exasnap.begin_snap('<sid>[@<instance#>]') FROM dual;
--
-- 2) Run the measured query in the session you snapshotted
-- (or just wait for it to run a while)
--
-- 3) TAKE A 2ND SNAPSHOT
--
-- SELECT exasnap.begin_snap(<sid>) FROM dual;
-- or
-- SELECT exasnap.begin_snap('<sid>[@<instance#>]') FROM dual;
--
-- 4) REPORT SESSION METRICS
--
-- @exasnap.sql basic <begin_snap> <end_snap>
-- or
-- @exasnap.sql % <begin_snap> <end_snap>
--
-- The latter script gives you more output.
--
-- Other: This is still a pretty raw script in development and will
-- probably change a lot once it reaches v1.0.
--
-- The PX slaves aggregate their metrics back to the QC session
-- once the query completes, so querying the QC session only is
-- ok if you wait for the query to finish (or cancel it) before
-- taking a snapshot.
-- To measure a query still running,
--
--------------------------------------------------------------------------------
SET LINES 999 PAGES 5000 TRIMOUT ON TRIMSPOOL ON TAB OFF
COL ioeff_percentage FOR A52
BREAK ON inst_id SKIP 1 ON SID ON CATEGORY SKIP 1 DUP
-- keep in capital (MB or GB)
DEF unit=MB
-- adjust for MB or GB
DEF divisor=1024*1024
DEF blocksize=8192
DEF asm_mirrors=2
DEFINE nothing =""
PROMPT
PROMPT ---------------------------------------------------------------------------------------------------------------------------------------------¬hing
PROMPT -- Exadata Snapper v0.5 BETA by Tanel Poder @ Enkitec - The Exadata Experts ( http://www.enkitec.com )
PROMPT ---------------------------------------------------------------------------------------------------------------------------------------------¬hing
WITH stats AS (
SELECT
stat_name name
, SUM(delta) value
, AVG(snap_seconds) snap_seconds -- is the same for all records in this snap_id
FROM (
SELECT
esn1.snap_id
, esn1.snap_time begin_snap_time
, esn2.snap_time end_snap_time
, esn2.snap_time - esn1.snap_time snap_interval
, TO_NUMBER(EXTRACT(second from esn2.snap_time - esn1.snap_time)) +
TO_NUMBER(EXTRACT(minute from esn2.snap_time - esn1.snap_time)) * 60 +
TO_NUMBER(EXTRACT(hour from esn2.snap_time - esn1.snap_time)) * 60 * 60 +
TO_NUMBER(EXTRACT(day from esn2.snap_time - esn1.snap_time)) * 60 * 60 * 24 +
-- TODO this is not needed
TO_NUMBER(TO_CHAR(esn2.snap_time,'xFF')) -
TO_NUMBER(TO_CHAR(esn1.snap_time,'xFF')) snap_seconds -- looks like the last part is buggy but it's too late to figure this out!
, esn1.snap_name begin_snap_name
, esn2.snap_name end_snap_name
, ess1.stat_name
, ess1.value begin_value
, ess2.value end_value
, ess2.value - ess1.value delta
FROM
ex_snapshot esn1
, ex_session es1
, ex_sesstat ess1
, ex_snapshot esn2
, ex_session es2
, ex_sesstat ess2
WHERE
-- snap_id
esn1.snap_id = es1.snap_id
AND ess1.snap_id = esn1.snap_id
AND es1.snap_id = ess1.snap_id
AND es1.inst_id = ess1.inst_id
AND es1.sid = ess1.sid
AND es1.serial# = ess1.serial#
--
AND esn2.snap_id = es2.snap_id
AND es2.snap_id = ess2.snap_id
AND ess2.snap_id = esn2.snap_id
AND es2.inst_id = ess2.inst_id
AND es2.sid = ess2.sid
AND es2.serial# = ess2.serial#
AND ess1.stat_name = ess2.stat_name
AND ess1.inst_id = ess2.inst_id
AND ess1.sid = ess2.sid
AND ess1.serial# = ess2.serial#
--
AND esn1.snap_id = &2
AND esn2.snap_id = &3
--
-- AND ess2.value - ess1.value != 0 -- for testing
)
GROUP BY
stat_name
),
sq AS (
SELECT
*
FROM (
SELECT
0 inst_id
, 0 sid
, CASE WHEN TRIM(name) IN (
'cell physical IO bytes sent directly to DB node to balance CPU'
, 'cell physical IO bytes pushed back due to excessive CPU on cell'
, 'cell physical IO bytes sent directly to DB node to balanceCPU u'
) THEN
'cell physical IO bytes sent directly to DB node to balance CPU'
ELSE name
END name
, value
FROM
--gv$sesstat NATURAL JOIN v$statname
stats
WHERE
1=1
-- AND (name LIKE 'cell%bytes%' OR name LIKE 'physical%bytes%')
AND TRIM(name) IN (
'physical read total bytes'
, 'physical write total bytes'
, 'physical read total bytes optimized'
, 'cell physical IO bytes eligible for predicate offload'
, 'cell physical IO interconnect bytes'
, 'cell physical IO interconnect bytes returned by smart scan'
, 'cell physical IO bytes saved by storage index'
, 'cell IO uncompressed bytes'
, 'cell blocks processed by cache layer'
, 'cell blocks processed by txn layer'
, 'cell blocks processed by data layer'
, 'cell blocks processed by index layer'
, 'db block gets from cache'
, 'consistent gets from cache'
, 'db block gets direct'
, 'consistent gets direct'
-- following three stats are the same thing (named differently in different versions)
, 'cell physical IO bytes sent directly to DB node to balance CPU'
, 'cell physical IO bytes pushed back due to excessive CPU on cell'
, 'cell physical IO bytes sent directly to DB node to balanceCPU u'
, 'bytes sent via SQL*Net to client'
, 'bytes received via SQL*Net from client'
, 'table fetch continued row'
, 'chained rows skipped by cell'
, 'chained rows processed by cell'
, 'chained rows rejected by cell'
)
)
PIVOT (
SUM(value)
FOR name IN (
'physical read total bytes' AS phyrd_bytes
, 'physical write total bytes' AS phywr_bytes
, 'physical read total bytes optimized' AS phyrd_optim_bytes
, 'cell physical IO bytes eligible for predicate offload' AS pred_offloadable_bytes
, 'cell physical IO interconnect bytes' AS interconnect_bytes
, 'cell physical IO interconnect bytes returned by smart scan' AS smart_scan_ret_bytes
, 'cell physical IO bytes saved by storage index' AS storidx_saved_bytes
, 'cell IO uncompressed bytes' AS uncompressed_bytes
, 'cell blocks processed by cache layer' AS cell_proc_cache_blk
, 'cell blocks processed by txn layer' AS cell_proc_txn_blk
, 'cell blocks processed by data layer' AS cell_proc_data_blk
, 'cell blocks processed by index layer' AS cell_proc_index_blk
, 'db block gets from cache' AS curr_gets_cache_blk
, 'consistent gets from cache' AS cons_gets_cache_blk
, 'db block gets direct' AS curr_gets_direct_blk
, 'consistent gets direct' AS cons_gets_direct_blk
, 'cell physical IO bytes sent directly to DB node to balance CPU' AS cell_bal_cpu_bytes
, 'bytes sent via SQL*Net to client' AS net_to_client_bytes
, 'bytes received via SQL*Net from client' AS net_from_client_bytes
, 'table fetch continued row' AS chain_fetch_cont_row
, 'chained rows skipped by cell' AS chain_rows_skipped
, 'chained rows processed by cell' AS chain_rows_processed
, 'chained rows rejected by cell' AS chain_rows_rejected
)
)
),
precalc AS (
SELECT
inst_id
, sid
, ROUND((phyrd_bytes)/(&divisor)) db_physrd_&unit
, ROUND((phywr_bytes)/(&divisor)) db_physwr_&unit
, ROUND((phyrd_bytes+phywr_bytes)/(&divisor)) db_physio_&unit
, ROUND(pred_offloadable_bytes/(&divisor)) pred_offloadable_&unit
, ROUND(phyrd_optim_bytes/(&divisor)) phyrd_optim_&unit
, ROUND((phyrd_optim_bytes-storidx_saved_bytes)/(&divisor)) phyrd_flash_rd_&unit
, ROUND(storidx_saved_bytes/(&divisor)) phyrd_storidx_saved_&unit
, ROUND((phyrd_bytes-phyrd_optim_bytes)/(&divisor)) spin_disk_rd_&unit
, ROUND((phyrd_bytes-phyrd_optim_bytes+(phywr_bytes*&asm_mirrors))/(&divisor)) spin_disk_io_&unit
, ROUND(uncompressed_bytes/(&divisor)) scanned_uncomp_&unit
, ROUND(interconnect_bytes/(&divisor)) total_ic_&unit
, ROUND(smart_scan_ret_bytes/(&divisor)) smart_scan_ret_&unit
, ROUND((interconnect_bytes-smart_scan_ret_bytes)/(&divisor)) non_smart_scan_&unit
, ROUND(cell_proc_cache_blk * &blocksize / (&divisor)) cell_proc_cache_&unit
, ROUND(cell_proc_txn_blk * &blocksize / (&divisor)) cell_proc_txn_&unit
, ROUND(cell_proc_data_blk * &blocksize / (&divisor)) cell_proc_data_&unit
, ROUND(cell_proc_index_blk * &blocksize / (&divisor)) cell_proc_index_&unit
, ROUND(curr_gets_cache_blk * &blocksize / (&divisor)) curr_gets_cache_&unit
, ROUND(cons_gets_cache_blk * &blocksize / (&divisor)) cons_gets_cache_&unit
, ROUND(curr_gets_direct_blk * &blocksize / (&divisor)) curr_gets_direct_&unit
, ROUND(cons_gets_direct_blk * &blocksize / (&divisor)) cons_gets_direct_&unit
, ROUND(cell_bal_cpu_bytes / (&divisor)) cell_bal_cpu_&unit
, ROUND(net_to_client_bytes / (&divisor)) net_to_client_&unit
, ROUND(net_from_client_bytes / (&divisor)) net_from_client_&unit
, chain_fetch_cont_row
, chain_rows_skipped
, chain_rows_processed
, chain_rows_rejected
FROM sq
),
precalc2 AS (
SELECT
inst_id
, sid
, db_physio_&unit
, db_physrd_&unit
, db_physwr_&unit
, pred_offloadable_&unit
, phyrd_optim_&unit
, phyrd_flash_rd_&unit + spin_disk_rd_&unit phyrd_disk_and_flash_&unit
, phyrd_flash_rd_&unit
, phyrd_storidx_saved_&unit
, spin_disk_io_&unit
, spin_disk_rd_&unit
, ((spin_disk_io_&unit - spin_disk_rd_&unit)) AS spin_disk_wr_&unit
, scanned_uncomp_&unit
, ROUND((scanned_uncomp_&unit/NULLIF(spin_disk_rd_&unit + phyrd_flash_rd_&unit, 0))*db_physrd_&unit) est_full_uncomp_&unit
, total_ic_&unit
, smart_scan_ret_&unit
, non_smart_scan_&unit
, cell_proc_cache_&unit
, cell_proc_txn_&unit
, cell_proc_data_&unit
, cell_proc_index_&unit
, cell_bal_cpu_&unit
, curr_gets_cache_&unit
, cons_gets_cache_&unit
, curr_gets_direct_&unit
, cons_gets_direct_&unit
, net_to_client_&unit
, net_from_client_&unit
, chain_fetch_cont_row
, chain_rows_skipped
, chain_rows_processed
, chain_rows_rejected
FROM
precalc
),
--SELECT
-- inst_id
-- , SUM(db_physio_&unit)
-- , SUM(db_physrd_&unit)
-- , SUM(db_physwr_&unit)
-- , SUM(pred_offloadable_&unit)
-- , SUM(phyrd_optim_&unit)
-- , SUM(spin_disk_io_&unit)
-- , SUM(spin_disk_rd_&unit)
-- , SUM(spin_disk_io_&unit - spin_disk_rd_&unit) AS spin_disk_wr_&unit
-- , SUM(scanned_uncomp_&unit)
-- , ROUND(SUM((scanned_uncomp_&unit/spin_disk_rd_&unit)*db_physrd_&unit)) AS est_full_uncomp_&unit
-- , SUM(total_ic_&unit)
-- , SUM(smart_scan_ret_&unit)
-- , SUM(non_smart_scan_&unit)
--FROM
-- precalc2
--GROUP BY ROLLUP
-- (inst_id)
--/
unpivoted AS (
SELECT * FROM precalc2
UNPIVOT (
&unit
FOR metric
IN (
phyrd_optim_&unit
, phyrd_disk_and_flash_&unit
, phyrd_flash_rd_&unit
, phyrd_storidx_saved_&unit
, spin_disk_rd_&unit
, spin_disk_wr_&unit
, spin_disk_io_&unit
, db_physrd_&unit
, db_physwr_&unit
, db_physio_&unit
, scanned_uncomp_&unit
, est_full_uncomp_&unit
, non_smart_scan_&unit
, smart_scan_ret_&unit
, total_ic_&unit
, pred_offloadable_&unit
, cell_proc_cache_&unit
, cell_proc_txn_&unit
, cell_proc_data_&unit
, cell_proc_index_&unit
, cell_bal_cpu_&unit
, curr_gets_cache_&unit
, cons_gets_cache_&unit
, curr_gets_direct_&unit
, cons_gets_direct_&unit
, net_to_client_&unit
, net_from_client_&unit
, chain_fetch_cont_row
, chain_rows_skipped
, chain_rows_processed
, chain_rows_rejected
)
)
),
metric AS (
SELECT 'BASIC' type, 'DB_LAYER_IO' category, 'DB_PHYSIO_&unit' name FROM dual UNION ALL
SELECT 'BASIC', 'DB_LAYER_IO', 'DB_PHYSRD_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'DB_LAYER_IO', 'DB_PHYSWR_&unit' FROM dual UNION ALL
SELECT 'ADVANCED', 'AVOID_DISK_IO', 'PHYRD_OPTIM_&unit' FROM dual UNION ALL
SELECT 'ADVANCED', 'AVOID_DISK_IO', 'PHYRD_DISK_AND_FLASH_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'AVOID_DISK_IO', 'PHYRD_FLASH_RD_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'AVOID_DISK_IO', 'PHYRD_STORIDX_SAVED_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REAL_DISK_IO', 'SPIN_DISK_IO_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REAL_DISK_IO', 'SPIN_DISK_RD_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REAL_DISK_IO', 'SPIN_DISK_WR_&unit' FROM dual UNION ALL
SELECT 'ADVANCED', 'COMPRESS', 'SCANNED_UNCOMP_&unit' FROM dual UNION ALL
SELECT 'ADVANCED', 'COMPRESS', 'EST_FULL_UNCOMP_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REDUCE_INTERCONNECT', 'PRED_OFFLOADABLE_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REDUCE_INTERCONNECT', 'TOTAL_IC_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REDUCE_INTERCONNECT', 'SMART_SCAN_RET_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REDUCE_INTERCONNECT', 'NON_SMART_SCAN_&unit' FROM dual UNION ALL
SELECT 'ADVANCED', 'CELL_PROC_DEPTH', 'CELL_PROC_CACHE_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'CELL_PROC_DEPTH', 'CELL_PROC_TXN_&unit' FROM DUAL UNION ALL
SELECT 'BASIC', 'CELL_PROC_DEPTH', 'CELL_PROC_DATA_&unit' FROM DUAL UNION ALL
SELECT 'BASIC', 'CELL_PROC_DEPTH', 'CELL_PROC_INDEX_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'CELL_PROC_DEPTH', 'CELL_BAL_CPU_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'IN_DB_PROCESSING', 'CURR_GETS_CACHE_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'IN_DB_PROCESSING', 'CONS_GETS_CACHE_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'IN_DB_PROCESSING', 'CURR_GETS_DIRECT_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'IN_DB_PROCESSING', 'CONS_GETS_DIRECT_&unit' FROM DUAL UNION ALL
SELECT 'BASIC', 'CLIENT_COMMUNICATION', 'NET_TO_CLIENT_&unit' FROM DUAL UNION ALL
SELECT 'BASIC', 'CLIENT_COMMUNICATION', 'NET_FROM_CLIENT_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'FALLBACK_TO_BLOCK_IO', 'CHAIN_FETCH_CONT_ROW' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'FALLBACK_TO_BLOCK_IO', 'CHAIN_ROWS_SKIPPED' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'FALLBACK_TO_BLOCK_IO', 'CHAIN_ROWS_PROCESSED' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'FALLBACK_TO_BLOCK_IO', 'CHAIN_ROWS_REJECTED' FROM DUAL
)
SELECT
-- inst_id
-- , sid
category
-- , type
, metric
, '|'||RPAD(NVL(RPAD('#', ROUND(&unit / NULLIF( (SELECT MAX(&unit) FROM unpivoted u, metric m WHERE u.metric = m.name AND m.type LIKE UPPER('&1')), 0) * 50 ), '#'), ' '), 50, ' ')||'|' ioeff_percentage
, &unit
, TO_CHAR(ROUND(&unit / (SELECT snap_seconds FROM stats WHERE rownum = 1),1), '9999999.9') AS " &unit/sec"
FROM
unpivoted u
, metric m
WHERE
u.metric = m.name
AND m.type LIKE UPPER('&1')
/
Comments
Post a Comment