Oracle_SQL_Scripts/OraLatencyMap/OraLatencyMap.sql at master · anilbodda/Oracle_SQL_Scripts

33 lines (31 loc) · 1.88 KB

-- OraLatencyMap, a tool to visualize Oracle I/O latency using Heat Maps

-- Luca.Canali@cern.ch, v1.1, May 2013

-- Credits: Brendan Gregg for "Visualizing System Latency", Communications of the ACM, July 2010

-- Tanel Poder (snapper, moats, sqlplus and color), Marcin Przepiorowski (topass)

-- Notes: This script needs to be run from sqlplus from a terminal supporting ANSI escape codes.

-- Tested on 11.2.0.3, Linux.

-- Run from a privileged user (select on v$event_histogram and execute on dbms_lock.sleep)

-- Use: @OraLatencyMap

-- Note: run from SQL*plus.

-- Better not use rlwrap when running this, or graphics smoothness will suffer

-- Output: 2 latency heat map of the "db file sequential read" wait event refreshed every few seconds.

-- The top map represents the number of waits per second and per latency bucket

-- The bottom map represented the estimated time waited per second and per latency bucket

-- Scope: Performance investigations of single block read latency

-- Examples: the latency bucket 1(ms) gives the details of 'fast reads', likely from the storage cache.

-- Latency buckets around 10 ms range would typically give details on physical reads from spindles

-- IO 'outliers' with latency higher than a few tens of ms can be sign of a problem that needs further

-- investigations.

-- Related: OraLatencyMap_advanced -> this is the main script for generic investigation of event latency with heat maps

-- OraLatencyMap_event -> another script based on OraLatencyMap_advanced

-- OraLatencyMap_internal -> the slave script where all the computation and visualization is done

-- OraLatencyMap_internal_loop -> the slave script that runs several dozens of iterations of the tool's engine

@@OraLatencyMap_advanced 3 "db file sequential read" 11 90 ""

Provide feedback