Home » RDBMS Server » Performance Tuning » Doumentation of wait events (Oracle 10.2 documentation)
Doumentation of wait events [message #346933] Wed, 10 September 2008 02:31 Go to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Hi all,
a question about Oracle's documentation. It's about wait-events, so I post this in "performance tuning". If that's wrong feel free to move the post Smile

I was looking for the meaning of parameter P1, P2 and P3 in the oracle dodumentation. For the event : "direct path read"
First I looked here, and found:
Quote:

Parameter Description
descriptor address - This is a pointer to the I/O context of outstanding direct I/Os on which the session is currently waiting
first dba - The dba of the oldest I/O in the context referenced by the descriptor address
block cnt - Number of valid buffers in the context referenced by the descriptor address



After some more searching I also found this :
Quote:

Check the following V$SESSION_WAIT parameter columns:

P1 - File_id for the read call
P2 - Start block_id for the read call
P3 - Number of blocks in the read call



To me those are different things. Confused

Now my question:
Can someone explain or point out whether the documentation is inconsistent (unlikely) or whether I am misreading/misinterpreting the documentation (very likely).

If it is the latter, I also would appreciate some more explanation on how I am misreading and/or misinterpreting the documentation.

Best Regards,
Martijn Bos
Re: Doumentation of wait events [message #346950 is a reply to message #346933] Wed, 10 September 2008 03:22 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
It was just a case where they said the same thing using different words. Probably the two parts are written by different authors with different approaches to the subject.

The first one uses more general terms like address, pointer and buffer. The second one uses more typical terms for a database like file_id and block.

To be clear:

P1 stands for file_id of the resource the db is reading from. That is the id of a datafile you can find in dba_data_files.

P2 stands for the block_id where the read process begins ( remember that direct path reads are reads of consecutive blocks in a file ). That is a block_id you can find in dba_extents.

P3 stand for number of blocks to read during the event.

Mind that none of them is wrong. It's just a difference in the point of view. A pointer, in a relational database, must be a reference to a primary key. A block_id is the logical address of a particular database block.

The dba of the oldest I/O in the context should be the the first database address ( so the block_id ) read as first ( so oldest ).


Bye Alessandro

[Updated on: Wed, 10 September 2008 03:25]

Report message to a moderator

Re: Doumentation of wait events [message #347079 is a reply to message #346933] Wed, 10 September 2008 10:28 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Alessandro - excellent concise description - topic I was unfamiliar with in specifics that you shed quick light to.

Couple questions for you that come to mind to see if my hazy understanding of the general meaning/reason for the direct read waits is correct.

I was under the impression those were waits common to index usage vs the scattered read for table scan hits.

Quote:


( remember that direct path reads are reads of consecutive blocks in a file ).



Is the above in reference to consecutive blocks in the table data (for lack of better words to describe the "space" where the table records reside)?

Just was thinking that I would expect the direct read waits to increase with the poorer the clustering factor of the index - where there is more physical IO due to reading multiple table blocks and more so, non-consecutive blocks where there is longer read-write head movement?

But if the direct path reads are waits for just consecutive block reads, I suppose my entire grasp on meaning is hands on a slippery rope ! Smile

Any further insight of much appreciation.

Best Regards,
Harry

Re: Doumentation of wait events [message #347249 is a reply to message #347079] Thu, 11 September 2008 03:31 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
As you can see in the Performance Tuning Guide,
Quote:
when a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event.


It's called direct path read because it is a process that reads data directly on the disk without accessing the buffer cache of the SGA.

Disk access is more expensive than memory access and the most of its cost is caused by latency times (time occurring between a read request and the moment when data starts becoming effectively available to read). For this reason, in order to minimize the number of requests (and then the latency waits on disk), the direct read process is able to read consecutive blocks at each request.

The fact that a direct path read is a process that reads consecutive blocks is just the consequence of an optimized implementation, but it could be done also with a slower process that reads only one block at once.

As you can see, always in the Performance Tuning Guide,
Quote:
This happens in the following situations:

  • The sorts are too large to fit in memory and some of the sort data is written out directly to disk. This data is later read back in, using direct reads.
  • Parallel slaves are used for scanning data.
  • The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.




Bye Alessandro

[Updated on: Thu, 11 September 2008 04:29]

Report message to a moderator

Re: Doumentation of wait events [message #347557 is a reply to message #346933] Fri, 12 September 2008 02:23 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Thanx Allesandro - Correct, I should've done some reference reading as I would have found out I was thinking about the sequential read waits in regards to index relationship.

Very much appreciated for gathering the text quotes for me and your
explanation!

Best Regards
Harry
Previous Topic: Distinct versus group by
Next Topic: SUSE Oracle high CPU usgae problem
Goto Forum:
  


Current Time: Mon Dec 05 08:45:39 CST 2016

Total time taken to generate the page: 0.12117 seconds