You notice that there’s a difference between DB02 expensive statement trace for the runtime and memory consumption if you compare it with the generated execution (PlanViz file).
In our example below we have a query that behaves erradically. At one point it will take a few seconds to return data, at another time it will take 10 minutes. (note: exactly the same query in both)
When I took the execution trace of the first query which was taking a long time and had a high memory, the execution trace (PlanViz) was showing me a completely different image:
executed in 5s with only 4.5GB of consumed memory
it took me a bit to figure out that it will actually rerun the query when you press the “execution trace” button in DB02 and it will take the execution trace of that run it did when you pressed the button.
after monitoring the query runs for a bit, I was able to determine the time when this query would start running longer. when the query was running longer, I looked up the query in the expensive statements and pressed the “execution trace” again. this time it resulted in a much more realistic planviz:
Conclusion
The execution trace button in DB02 – expensive statements will rerun the query and generated a PlanViz based on that run.