| In 10046 trace why does Oracle call.... [message #116611] |
Wed, 20 April 2005 21:18  |
dallas
Messages: 9
Registered: April 2005
|
Junior Member |
|
|
|
..its most prevalent read wait events "db file sequential reads" and "db file scattered reads"? What is so scattered or sequential about these events such that the Oracle kernels named them this way in the Wait Interfact.
|
|
|
|
|
|
| Re: In 10046 trace why does Oracle call.... [message #116614 is a reply to message #116613] |
Wed, 20 April 2005 22:22  |
dallas
Messages: 9
Registered: April 2005
|
Junior Member |
|
|
|
I know what they indicate. It just seems funny that Oracle would name them seemingly backwards. I know the db file scattered read events typically indicate full table scans, fast full scans, etc. Sequential reads are usually single block reads. There must be a reason for the naming convention. I just bugs me.
|
|
|
|
|
|
|
|
|
|
| Re: In 10046 trace why does Oracle call.... [message #117779 is a reply to message #117774] |
Fri, 29 April 2005 10:16 |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
Hi,
The "db file sequential read" event indicates that Oracle read data into a sequential memory region in the SGA buffer cache. Some people mistakenly think that it refers to a sequential area on disk.
Similarly, "db file scattered read" suggest that data was read from disk and scattered into the SGA buffer cache.
Best regards.
Frank
|
|
|
|
| Re: In 10046 trace why does Oracle call.... [message #117782 is a reply to message #116611] |
Fri, 29 April 2005 10:21 |
dallas
Messages: 9
Registered: April 2005
|
Junior Member |
|
|
|
Frank, are you saying that successive db file sequential read events on behalf of a particular SQL place blocks into contiguous SGA buffers? Given, after 8i all db file sequential reads are single block reads what do you mean by "Oracle read data into a sequential memory region in the SGA buffer cache?" Your feedback is appreciated.
|
|
|
|
| Re: In 10046 trace why does Oracle call.... [message #117792 is a reply to message #117782] |
Fri, 29 April 2005 11:26 |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
Well, I'm not exactly an expert on this, but as I understand it:
db file sequential reads are most commonly (but not always) used for single block reads (typically index reads).
So, if you see 1 block as a contiguous memory region, the above definition still holds true.
Best regards.
Frank
|
|
|
|
| Re: In 10046 trace why does Oracle call.... [message #117816 is a reply to message #116611] |
Fri, 29 April 2005 14:25 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
From what I read (in the holt paper I think) the sequential read is into contiguous memory, which most often is a single block (by definition contiguous) but also can be a read of the temp tablespace (contiguous memory in pga rather than shared sga memory).
It makes sense now for me to think of the scattered read first. You have a big (multiblock) read, and gotta find a place in memory to put it. Do you want to search all over for a contiguous group of blocks big enough to hold it all, or save time in your search by just putting (scattering) blocks one at a time whereever the first place is that they will fit.
This isn't an issue with temp tablespace and pga because the memory is owned solely by the user session, and because temp data never needs to be persisted, so it can just be overwritten in one large sequential chunk.
So in the case of temp data, it is multiblock db sequential read, and in the case of non-temp data it is singleblock db sequential read. db scattered read is always multiblock.
Thanks for posting this question as it inspired me to learn something new.
|
|
|
|
| Re: In 10046 trace why does Oracle call.... [message #117871 is a reply to message #117816] |
Sat, 30 April 2005 07:09 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Interesting stuff. Seems like you need a login to access the Holt paper though, or am I missing something?
This is the kind of thing I would have liked to see in their book ("Optimizing Oracle Performance", Cary Millsap with Jeff Holt, ISBN 059600527X). There are chapters about calculating return on investment and which end users to take out to lunch, but not much discussion on how the database works.
From the 10g Performance Tuning Guide:
db file scattered read
db file sequential read
|
|
|
|
| Re: In 10046 trace why does Oracle call.... [message #117880 is a reply to message #117871] |
Sat, 30 April 2005 11:30 |
dallas
Messages: 9
Registered: April 2005
|
Junior Member |
|
|
Mr. Robertson, all you need to do is register an account at www.hotsos.com. It is free. They simply want to know who is requesting their white papers. Create an account and enjoy!
BTW, the "which end users to take out to lunch" statement is really funny. 
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
