Visualizing SQL Plan Execution Time With FlameGraphs
Brendan Gregg invented and popularized a way to profile & visualize program response time by sampling stack traces and using his FlameGraph concept & tools. This technique is a great way for visualizing metrics in nested hierarchies, what stack-based program execution uses under the hood for invoking and tracking function calls. If you don’t know what FlameGraphs are, I suggest you read Brendan’s explanation first.
In this blog post I won’t be doing traditional stack profiling, but will apply FlameGraphs in a new way for visualizing Oracle Database SQL execution plan metrics. This visualization is not limited to Oracle only, it can be used on any RDBMS engine, as long as the engine reports actual time taken at execution plan operator (plan line) level.
Even though FlameCharts could be used for visualizing any cumulative metric (like amount of I/O generated in different stages of the plan), in this post I will measure what matters the most — the response time used by individual execution plan operators. I’m using Oracle’s DBMS_XPLAN with the statistics_level=all setting for the examples in this post.
