Logical Reads = CPU Consumption ???

[dbtwo]

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.SQLCOLUM NS(?,?,?,?,?)

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

[Serge Rielau]

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.SQLCOLUM NS(?,?,?,?,?)

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

[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.