This content has been marked as final. Show 4 replies
First of all, you REALLY should learn to include a version in ALL of your posts.
The units of those columns has changed several times between releases.
This brings me to the second remark.
All views are documented. In the table dict, in the table dict_columns, in the table dict_comments and in the Oracle Reference Manual for your version.
If you have a query about the meaning of a column, PLEASE visit http://tahiti.oracle.com and look up the column yourself.
Please do NOT ask volunteers to abstract the information for three or four different versions.
That said, if I recall correctly, since 9i wait_time is expressed in centiseconds.
and seconds_in_wait is the total acummulated time Oracle has waited for this block.
Your unformatted output simply shows the block has to be revisited.
Direct path read temp is a result of operations involving sort and the like, so yes, revisits are quite probable.
As seconds_in_wait is huge, you appear to have an I/O bottleneck, however it looks like you have bigger problems: I never saw a file with the number 4523.
Senior Oracle DBA
"Seconds in wait" is a bad name for the column. A more realistic name would be something like: "Approximate number of seconds, plus or minus 3, since the last wait event began".
Your STATE is 'waited known time' - so you are currently not WAITING. This means your LAST wait was the direct path read temp, it happened roughly 916 seconds ago (seconds in wait is in seconds, and is updated roughly every 3 seconds), and you wait for approximately 1 centi-second (wait time is in hundredths of second).
This means the session has (probably) been hammering the CPU for the last 916 seconds.
"The greatest enemy of knowledge is not ignorance,
it is the illusion of knowledge." (Stephen Hawking)
"SECONDS_IN_WAIT" is the wait time of either the current wait or the last wait, which is to be interpreted based on STATE. (Jonathan has also explain this).
File number 4523 might make sense if DB_FILES is high. Since this is a 'direct path read' wait it could be a temp segment read from a temporary tablespace, where File#s reported in waits are always DB_FILES + 1 and higher.