468,309 Members | 1,140 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,309 developers. It's quick & easy.

Bufferpool tuning hint

Hello,
can someone tell me what exactly the value "Direct reads" means (i.e. how is
it calculated), when analysing the bufferpool snapshot?
What I do know is that it shows the number of reads that didn't go through
the bufferpool, but I would like to know why.

I have a very specific problem with a bufferpool that has one tablespace
(SMS) assigned to it with only one table inside it.
This table doesn't contain any LOB nor LONG VARCHAR, just "short" columns.
The BP stats looks like this:
....
Buffer pool data logical reads = 32436071
Buffer pool data physical reads = 9713326
....
Buffer pool index logical reads = 18206199
Buffer pool index physical reads = 4498458
....
No victim buffers available = 1964271
Direct reads = 194494344
Direct read requests = 24512
....

So, why the direct reads appear at all - when no "long" columns are present?
Is there some situation other than reading "long" columns, when direct
reading is triggered?
(apparently yes, but what is it??)
Is it perhaps because of the "No victim buffers available" being non-zero?

Any light on this subject would be appreciated.

Regards,
Damir Wilder

Oct 24 '06 #1
3 4942
Your buffer pool statistics show activity since the database was
activated. You are correct that direct reads are used for LOB and LONG
data but forgot that they are also used by the BACKUP command.

I suspect that the tablespace was the target of a backup command and you
are seeing the results of that. You can verify that direct reads are not
being used in normal processing by taking two snapshots an hour apart
and comparing the counts.

Phil Sherman
Damir Wilder wrote:
Hello,
can someone tell me what exactly the value "Direct reads" means (i.e. how is
it calculated), when analysing the bufferpool snapshot?
What I do know is that it shows the number of reads that didn't go through
the bufferpool, but I would like to know why.

I have a very specific problem with a bufferpool that has one tablespace
(SMS) assigned to it with only one table inside it.
This table doesn't contain any LOB nor LONG VARCHAR, just "short" columns.
The BP stats looks like this:
...
Buffer pool data logical reads = 32436071
Buffer pool data physical reads = 9713326
...
Buffer pool index logical reads = 18206199
Buffer pool index physical reads = 4498458
...
No victim buffers available = 1964271
Direct reads = 194494344
Direct read requests = 24512
...

So, why the direct reads appear at all - when no "long" columns are present?
Is there some situation other than reading "long" columns, when direct
reading is triggered?
(apparently yes, but what is it??)
Is it perhaps because of the "No victim buffers available" being non-zero?

Any light on this subject would be appreciated.

Regards,
Damir Wilder
Oct 24 '06 #2
More on this if you are concerned on their rate.
You should reset your monitor switches so that your coounters are brought
down. Wait the interval you need and take your snapshot, reset again, wait,
snapshot .... for as often you think you need.
Look at the output and see which appls. are causing the direct reads.
Regards, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Phil Sherman" <ps******@ameritech.neta écrit dans le message de news:
Hh*****************@newssvr29.news.prodigy.net...
Your buffer pool statistics show activity since the database was
activated. You are correct that direct reads are used for LOB and LONG
data but forgot that they are also used by the BACKUP command.

I suspect that the tablespace was the target of a backup command and you
are seeing the results of that. You can verify that direct reads are not
being used in normal processing by taking two snapshots an hour apart and
comparing the counts.

Phil Sherman
Damir Wilder wrote:
> Hello,
can someone tell me what exactly the value "Direct reads" means (i.e. how
is
it calculated), when analysing the bufferpool snapshot?
What I do know is that it shows the number of reads that didn't go
through
the bufferpool, but I would like to know why.

I have a very specific problem with a bufferpool that has one tablespace
(SMS) assigned to it with only one table inside it.
This table doesn't contain any LOB nor LONG VARCHAR, just "short"
columns.
The BP stats looks like this:
...
Buffer pool data logical reads = 32436071
Buffer pool data physical reads = 9713326
...
Buffer pool index logical reads = 18206199
Buffer pool index physical reads = 4498458
...
No victim buffers available = 1964271
Direct reads = 194494344
Direct read requests = 24512
...

So, why the direct reads appear at all - when no "long" columns are
present?
Is there some situation other than reading "long" columns, when direct
reading is triggered?
(apparently yes, but what is it??)
Is it perhaps because of the "No victim buffers available" being
non-zero?

Any light on this subject would be appreciated.

Regards,
Damir Wilder

Oct 24 '06 #3
I didn't know the BACKUP also counts in direct reads (but of course it
should!), now I will reset the counters and see what happens between two
backups.

Thanks a lot (both replies)!

Damir
---

"Pierre Saint-Jacques" <se*****@invalid.netwrote in message
news:M0********************@wagner.videotron.net.. .
More on this if you are concerned on their rate.
You should reset your monitor switches so that your coounters are brought
down. Wait the interval you need and take your snapshot, reset again,
wait,
snapshot .... for as often you think you need.
Look at the output and see which appls. are causing the direct reads.
Regards, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Phil Sherman" <ps******@ameritech.neta écrit dans le message de news:
Hh*****************@newssvr29.news.prodigy.net...
Your buffer pool statistics show activity since the database was
activated. You are correct that direct reads are used for LOB and LONG
data but forgot that they are also used by the BACKUP command.

I suspect that the tablespace was the target of a backup command and you
are seeing the results of that. You can verify that direct reads are not
being used in normal processing by taking two snapshots an hour apart
and
comparing the counts.

Phil Sherman
Damir Wilder wrote:
Hello,
can someone tell me what exactly the value "Direct reads" means (i.e.
how
is
it calculated), when analysing the bufferpool snapshot?
What I do know is that it shows the number of reads that didn't go
through
the bufferpool, but I would like to know why.

I have a very specific problem with a bufferpool that has one
tablespace
(SMS) assigned to it with only one table inside it.
This table doesn't contain any LOB nor LONG VARCHAR, just "short"
columns.
The BP stats looks like this:
...
Buffer pool data logical reads = 32436071
Buffer pool data physical reads = 9713326
...
Buffer pool index logical reads = 18206199
Buffer pool index physical reads = 4498458
...
No victim buffers available = 1964271
Direct reads = 194494344
Direct read requests = 24512
...

So, why the direct reads appear at all - when no "long" columns are
present?
Is there some situation other than reading "long" columns, when direct
reading is triggered?
(apparently yes, but what is it??)
Is it perhaps because of the "No victim buffers available" being
non-zero?

Any light on this subject would be appreciated.

Regards,
Damir Wilder


Oct 25 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by xixi | last post: by
5 posts views Thread by Paul | last post: by
1 post views Thread by Christian Berg | last post: by
5 posts views Thread by Hemant Shah | last post: by
20 posts views Thread by Hemant Shah | last post: by
1 post views Thread by Raja Shekar | last post: by
3 posts views Thread by Mark A | last post: by
3 posts views Thread by dunleav1 | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by Teichintx | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.