101 lines
3.8 KiB
MySQL
101 lines
3.8 KiB
MySQL
-- Copyright 2018 Tanel Poder. All rights reserved. More info at https://tanelpoder.com
|
|
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
|
|
|
|
--------------------------------------------------------------------------------
|
|
--
|
|
-- Name: sqlflame.sql
|
|
--
|
|
-- Purpose: Demo script for displaying execution plan profile as a flame chart
|
|
--
|
|
-- Usage: @sqlflame.sql <sqlid> <child#>
|
|
-- Note that you'll need to download Brendan Gregg's flamegraph.pl
|
|
-- https://github.com/brendangregg/FlameGraph and make sure that it's
|
|
-- in your PATH (or edit this script to use it from a hardcoded location)
|
|
--
|
|
-- Note that you'll have to replace HOST OPEN with HOST START in the
|
|
-- end of this file if you're using sqlplus client on Windows
|
|
--
|
|
-- Author: Tanel Poder - https://tanelpoder.com
|
|
--
|
|
-- Examples: https://tanelpoder.com/posts/visualizing-sql-plan-execution-time-with-flamegraphs/
|
|
-- https://tanelpoder.com/posts/sql-plan-flamegraph-loop-row-counts/
|
|
--
|
|
-- Other: This is an early version mostly meant as a proof of concept for
|
|
-- illustrating that a modern RDBMS SQL execution plan profile
|
|
-- can be treated just like any code profile (FlameGraphs were
|
|
-- initially used for process stack profiling)
|
|
--
|
|
-- This script currently relies on the V$SQL_PLAN_STATISTICS_ALL source
|
|
-- so you'll need to run your query with statistics_level=all or
|
|
-- with the GATHER_PLAN_STATISTICS hint.
|
|
--
|
|
-- Credits: Brendan Gregg invented and popularized FlameGraphs, if you want to
|
|
-- understand their logic better, read the articles at:
|
|
-- http://www.brendangregg.com/flamegraphs.html
|
|
--
|
|
--------------------------------------------------------------------------------
|
|
|
|
SET HEADING OFF LINESIZE 32767 PAGESIZE 0 TRIMSPOOL ON TRIMOUT ON LONG 9999999 VERIFY OFF LONGCHUNKSIZE 100000 FEEDBACK OFF APPINFO OFF
|
|
|
|
PROMPT
|
|
PROMPT -- SQLFlame 0.4 by Tanel Poder ( https://tanelpoder.com )
|
|
|
|
SET TERMOUT OFF
|
|
|
|
WITH sq AS (
|
|
SELECT /*+ MATERIALIZE */
|
|
sp.id, sp.parent_id, sp.operation, sp.options
|
|
, sp.object_owner, sp.object_name, ss.last_elapsed_time, ss.elapsed_time, ss.last_starts, ss.last_output_rows
|
|
FROM v$sql_plan_statistics_all ss INNER JOIN
|
|
v$sql_plan sp
|
|
ON (
|
|
sp.sql_id=ss.sql_id
|
|
AND sp.child_number=ss.child_number
|
|
AND sp.address=ss.address
|
|
AND sp.id=ss.id
|
|
)
|
|
AND sp.sql_id='&1'
|
|
AND sp.child_number=&2
|
|
), deltas AS (
|
|
SELECT par.id, par.elapsed_time - SUM(chi.elapsed_time) self_elapsed_time
|
|
FROM sq par LEFT OUTER JOIN
|
|
sq chi
|
|
ON chi.parent_id = par.id
|
|
GROUP BY par.id, par.elapsed_time
|
|
), combined AS (
|
|
SELECT sq.id, sq.parent_id, sq.operation, sq.options
|
|
, sq.object_owner, sq.object_name, sq.last_elapsed_time, sq.elapsed_time, sq.last_starts, sq.last_output_rows
|
|
, NVL(deltas.self_elapsed_time, sq.elapsed_time) self_elapsed_time
|
|
FROM
|
|
sq, deltas
|
|
WHERE
|
|
sq.id = deltas.id
|
|
)
|
|
SELECT
|
|
'0 - SELECT STATEMENT'||TRIM(SYS_CONNECT_BY_PATH(id||' - '||operation||NVL2(options,' '||options,NULL)
|
|
||NVL2(object_owner||object_name, ' ['||object_owner||'.'||object_name||']', NULL)
|
|
||' starts='||last_starts|| ' rows='||last_output_rows, ';'))||' '||TRIM(ROUND(self_elapsed_time/1000))
|
|
FROM
|
|
combined
|
|
CONNECT BY
|
|
parent_id = PRIOR id
|
|
START WITH parent_id = 0
|
|
ORDER BY
|
|
id ASC
|
|
.
|
|
|
|
spool sqlflame_stacks.txt
|
|
/
|
|
spool off
|
|
|
|
SET TERMOUT ON HEADING ON PAGESIZE 5000 LINESIZE 999 FEEDBACK ON
|
|
|
|
HOST flamegraph.pl --countname=Milliseconds --title="sql_id=&1" sqlflame_stacks.txt > sqlflame_&1..svg
|
|
|
|
-- Windows
|
|
-- HOST START sqlflame_&1..svg
|
|
|
|
-- MacOS
|
|
HOST OPEN sqlflame_&1..svg
|
|
|