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 1 3262
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
|
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...
| |