By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

Logical Reads = CPU Consumption ???

P: n/a
Until today I always thought as long as you see a lot of logical reads
as compared to physical reads, then you're good. But it looks like it
isn't so. But doesn't logical read mean it's being read from memory
and no I/O involved? So why is Logical Reads = CPU Consumption ?

I ran into an exact scenario last week when our applciation were
running something, and each time an application started, the CPU would
go from 99% idle to 48% idle. I took the snapshot for each application
and all i saw was logical reads. Here are some outputs from the
snapshot:

App 1
Buffer pool data logical reads = 5463749
Buffer pool data physical reads = 0
Buffer pool index logical reads = 1022588
Buffer pool index physical reads = 0

App2
Buffer pool data logical reads = 25831618
Buffer pool data physical reads = 0
Buffer pool index logical reads = 4836091
Buffer pool index physical reads= 0

Similarly, there are more applications with similar output. As you can
see, there are no physical reads and a lot of logical reads. And as I
said, each time an application was running, the CPU went up to 48%
idle. Does this suggest the logical reads taking all the CPU?

All applications were seen to be running the routine :
CALL SYSIBM.SQLCOLUMNS(?,?,?,?,?)

Have anyone heard of this routine? What does it do?

Oct 15 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
dbtwo wrote:
Until today I always thought as long as you see a lot of logical reads
as compared to physical reads, then you're good. But it looks like it
isn't so. But doesn't logical read mean it's being read from memory
and no I/O involved? So why is Logical Reads = CPU Consumption ?

I ran into an exact scenario last week when our applciation were
running something, and each time an application started, the CPU would
go from 99% idle to 48% idle. I took the snapshot for each application
and all i saw was logical reads. Here are some outputs from the
snapshot:

App 1
Buffer pool data logical reads = 5463749
Buffer pool data physical reads = 0
Buffer pool index logical reads = 1022588
Buffer pool index physical reads = 0

App2
Buffer pool data logical reads = 25831618
Buffer pool data physical reads = 0
Buffer pool index logical reads = 4836091
Buffer pool index physical reads= 0

Similarly, there are more applications with similar output. As you can
see, there are no physical reads and a lot of logical reads. And as I
said, each time an application was running, the CPU went up to 48%
idle. Does this suggest the logical reads taking all the CPU?

All applications were seen to be running the routine :
CALL SYSIBM.SQLCOLUMNS(?,?,?,?,?)

Have anyone heard of this routine? What does it do?
I think this routine is called by CLI. That's all I know.

In general, of course logical reads imply CPU usage.
If you read a page from memory there is no I/O wait. And if there is no
I/O wait there is no idle time between the request and the delivery of
your data.....

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Oct 15 '07 #2

P: n/a
Ian
dbtwo wrote:
Until today I always thought as long as you see a lot of logical reads
as compared to physical reads, then you're good. But it looks like it
isn't so. But doesn't logical read mean it's being read from memory
and no I/O involved? So why is Logical Reads = CPU Consumption ?
The issue is when you have a large number of logical reads (in relation
to the number of rows selected). The CPU still has to perform the
comparisons in memory.

Imagine a 4 Gb table, stored completely in the bufferpool, but with no
indexes. Every single query against this table has to look at 4 Gb
worth of bufferpool pages (even if the query returns only a single row).

So while you won't see any I/O, one CPU will be 100% consumed by the
db2 agent servicing the query.

Oct 20 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.