473,320 Members | 2,027 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Interpreting dynamic SQL snapshot

Colleagues,

Consider the following snippets of a snapshot:

Number of executions = 13
Number of compilations = 4294967295
Rows read = 415532
Buffer pool data logical reads = 13741
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0

The query in question goes against three tables, two M:N parents
resolved by an intersection table. Two of the tables (one parent and
the intersection) have 14,000-plus rows, and the third has 1,200-plus,
for a total of a bit more than 31,000 rows total. There are no
predicates in this query other than ON clauses for the joins. In other
words, DB2 does FTS's of all three tables, which is what I'd expect.
Also, the query runs several times per day.

Looking at the total number of "rows read" reported above (415,532)
and dividing it by the "number of executions" reported above (13),
yielding 32,000-plus, I'm led to believe that yes indeed, the query is
doing three FTS's *every time it runs*. Yet, I also see that it's
doing 13,741 logical reads, and 13,741 just happens to be the number
of rows returned by the query.

So, every time the query runs is DB2 re-doing the three FTS's, or, as
expected, is it doing a single logical read of the cached result set?

Thanks,

--Jeff

May 30 '07 #1
1 3262
Ian
jefftyzzer wrote:
>
The query in question goes against three tables, two M:N parents
resolved by an intersection table. Two of the tables (one parent and
the intersection) have 14,000-plus rows, and the third has 1,200-plus,
for a total of a bit more than 31,000 rows total. There are no
predicates in this query other than ON clauses for the joins. In other
words, DB2 does FTS's of all three tables, which is what I'd expect.
Also, the query runs several times per day.
>
Looking at the total number of "rows read" reported above (415,532)
and dividing it by the "number of executions" reported above (13),
yielding 32,000-plus, I'm led to believe that yes indeed, the query is
doing three FTS's *every time it runs*. Yet, I also see that it's
doing 13,741 logical reads, and 13,741 just happens to be the number
of rows returned by the query.
A logical or physical read represents DB2 accessing a page in the
bufferpool (logical) or on disk (physical), not a single row.
So, when doing a full table scan, as long as your tables have more
than 1 row per page, you're getting some aggregation in a full
table scan.
So, every time the query runs is DB2 re-doing the three FTS's, or, as
expected, is it doing a single logical read of the cached result set?
A table scan is a table scan, regardless of whether the data exists
in the bufferpool or on disk. The only difference is that DB2 will scan
the table in the bufferpool a LOT faster. That's the whole point of
prefetchers. ;-)

FYI, the explain plan would give a much better idea of what is really
going on, since it sounds like you're just making an assumption that
DB2 is doing full table scans. Keep in mind that sorting rows (for
a merge-scan join or grouping/ordering in the result set) also will
increment rows read.


May 30 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Jennie | last post by:
I need to collect dynamic SQL execution times for a production database. However, the totals are not being updated, despite setting the DFT_MON_TIMESTAMP on. The DFT_MON_TIMESTAMP is listed in...
14
by: Ina Schmitz | last post by:
Hello, I would like to get the logical and physical reads for every sql statement executed. Thatfore, I used the command "db2 get snapshot for dynamic sql on <mydatabase>". There, I could see...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
4
by: Ken Wigle | last post by:
All, I would be very grateful for any help on this question. I have an application in asp.net 2.0 where I dynamically create a datatable and then bind that to a gridview. Unfortunately, the...
3
by: sethwai | last post by:
Hi, I find the "snapshot for dynamic sql" a great tool for identifying the most frequently executed dynamic sql statements, most cpu used, etc. It is a great help for system tuning. My...
0
by: mike | last post by:
Problem: the snapshot output for dynamic SQL shows some SELECT statements executed many times but all three CPU counters (total/system/user) in the dynamic-sql-snapshot are zero, but most other...
8
by: grant | last post by:
Hi I've copied Stephens code into my db, and can get it to work, but only on "plain Jane" reports with no images. Most of my reports has an unbound image obluect in them that I set to an...
3
by: aj | last post by:
DB2 LUW 8.1 fixpak 14 Red Hat EL AS 4.4 I'm trying to diagnose some nocturnal CPU pressure, and am trying to understand the dynamic statement cache as it applies to LUW. The only doc/redbooks...
2
by: Damir | last post by:
Hello! I have noticed that after (sucessfully) executing the command: FLUSH PACKAGE CACHE DYNAMIC the dynamic SQL statement cache is not completely cleared (some of the dynamic SQL statement...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.