Re: System i Navigator visual explain HOWTO?

On 5/24/2010 2:15 PM, CRPence wrote:

On 24-May-2010 12:10, Buck wrote:

I'm looking for a detailed description of what all the nice
graphics mean on Visual Explain. I'm trying to understand a
series of complex SQL statements in terms of performance
characteristics, and I'm pretty much overwhelmed by all the
little boxes.

I Searched The Fine Web, and although there is stuff out there
for V5R2, I'm not sure that's particularly applicable to V5R4. If
it is, I'll just go hunting down all the old articles I skipped
over because we didn't use SQL then :-)

<<SNIP>>

http://www.redbooks.ibm.com/abstracts/sg247326.html
"OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4"

That and others found in a google search on:
"visual explain" db2 "i5/os" v5r4

Yes, thanks Chuck. I didn't find that very helpful when I first saw it,
which is why I was hoping for more detail. For instance, I have a
six-way JOIN I'm trying to understand. When I set the arrow labels to
estimated rows, every one but 2 shows '< 1'. I realise it's an
estimate, but it's not even close.

I looked at the help for Arrow labels in the cited pdf and in the
included help and about all I can get out of that is it's an estimate.
Not how the optimiser comes by that estimate, not how I can influence
that estimate, nothing. OK, let me switch the Arrow labels to Estimated
processing time. Hm, it seems that the displayed results are
/cumulative/ estimated CPU time.

What does SG24-7326 say about Arrow labels? 'Arrow labels allow you to
show or hide the estimated number of rows, processing time, or the
degree of parallelism that the query is processing at each stage of the
implementation.' That's a reasonable exemplar of all of the help I have
been able to locate.

In the end, it seems that I had unreasonable expectations of Visual
Explain. That isn't IBM's fault, so the cited references have indeed
been helpful in that I have reset my expectations to something more
realistic. My thanks to both you and Rob for your help.
--buck