How to Cross-reference Application Snapshots and Explain Plans

How to Cross-reference Application Snapshots and Explain Plans

Post by Ron Bog » Sun, 27 Oct 2002 05:13:39



I have had no luck mapping the subsections in an application snapshot
with the query's explain plan output?  The number of sections are the
same, but the work being done seems to differ.  For example, an
explain plan may show a subsection 2 being broadcast to 16 partitions
(EEE) and subsection 3 going to 1 partition.  But the applicaition
snapshot may show subsection 3 on 16 partitions and subsection 2 on 1.

Also...any clarification on 'rows read' and 'rows written' would help.
 Does 'rows read include every row read on every table, whether it is
part of the result set or not?  Does 'rows written' include broadcast
table queues in a EEE environment or is it intra-partition worktables
and sorts only?

Thanks,
Ron

 
 
 

How to Cross-reference Application Snapshots and Explain Plans

Post by Bo Taramin » Mon, 28 Oct 2002 03:10:00


Hi... with db2exfmt it is sometimes a bit difficult to see exactly what
the subsections are... Many times analyzing queries, I will also generate
a dynamic explain plan using dynexpln... this clearly shows the
subsections and the numbers should match what is in the application
snapshots.

Rows read are all the rows that get scanned, not necessarily the ones that
are returned to the application... it is all the rows that had to be read
to obtain the result set. So if the rows read is very high, perhaps an
index could help, if tablescans are being done.

Rows written do not include table queue activity... it does include the
any table rows that are inserted, updated or deleted, including temporary
tables.


> I have had no luck mapping the subsections in an application snapshot
> with the query's explain plan output?  The number of sections are the
> same, but the work being done seems to differ.  For example, an
> explain plan may show a subsection 2 being broadcast to 16 partitions
> (EEE) and subsection 3 going to 1 partition.  But the applicaition
> snapshot may show subsection 3 on 16 partitions and subsection 2 on 1.

> Also...any clarification on 'rows read' and 'rows written' would help.
>  Does 'rows read include every row read on every table, whether it is
> part of the result set or not?  Does 'rows written' include broadcast
> table queues in a EEE environment or is it intra-partition worktables
> and sorts only?

> Thanks,
> Ron

--

Bohdan Taramina
DB2 UDB Consulting Services
IBM Toronto Lab

 
 
 

How to Cross-reference Application Snapshots and Explain Plans

Post by Ron Bog » Fri, 01 Nov 2002 07:16:21


Quote:> Rows written do not include table queue activity... it does include the
> any table rows that are inserted, updated or deleted, including temporary
> tables.

That's helpful...so it would include 'spillover' rows when sortheap
was exhausted from hash joins, sorts, etc?
 
 
 

How to Cross-reference Application Snapshots and Explain Plans

Post by Bo Taramin » Fri, 01 Nov 2002 13:48:27


Yes... if we have to spill over to a temp table, those rows will be included...
Note that spilling to a temp table means that we go through a bufferpool to
write the database temporary pages... it may never result in a physical write
to a temporary database file... if the entire temp contents end up in the
bufferpool only... but it still counts as a write for each row...


> > Rows written do not include table queue activity... it does include the
> > any table rows that are inserted, updated or deleted, including temporary
> > tables.

> That's helpful...so it would include 'spillover' rows when sortheap
> was exhausted from hash joins, sorts, etc?

--

Bohdan Taramina
DB2 UDB Consulting Services
IBM Toronto Lab

 
 
 

1. dynaset<>snapshot. Please explain

Hi

Could someone please explain the difference between opening a recordset with
dbOpendynaset and dbOpensnapshot. The reason I ask is that I have made an
application where I after long time of search found out that my problem was
that I used dbOpenDynaset instead of dbOpensnapshot

Steen

2. Multi-user environment using databases

3. The explain snapshot has been corrupted

4. HELP!!! CTT problem

5. Show/Explain Query Plan

6. Help Anybody

7. Explain plan

8. Any drawbacks to using ODBC/DSN connections vs. OLE DB

9. Explain query estimation plan

10. Explain Plan

11. Oracle and Explain Plan

12. explain plan counter part

13. Planned small change in EXPLAIN behavior