Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL tuning nightmare - db file sequential reads

Re: SQL tuning nightmare - db file sequential reads

From: <>
Date: 7 Jul 2006 09:33:24 -0700
Message-ID: <> wrote:
> Agreed. But why on the same single block? Every time I query the
> V$SESSION_WAIT view it gives the same P1, P2, P3 parameters, thus
> indicating that the 'db file sequential read' event is accessing the
> same block.
> ...
> An interesting (unrelated) point regarding the fast full scan - the
> event shown is 'sequential read' , but the FFS more resembles
> 'scattered read' isn't it?

It's actually quite common to see a db file read event with the same p1 and p2 remaining the same or updating very slowly. Unless there's hardware error which is rare, it's necessary to supplement this wait information with some critical statistics. Statspack includes 'CPU used by this session' but it should really also include 'consistent gets', 'db block gets' and possibly 'buffer is pinned count'. I suggest you look at these statistics when the SQL runs. As to the remedy, it still comes down to SQL tuning.

I'm probably missed in the long thread. Does file 263 block 50960 belong to S_ASSET_BT_W1_X or S_ETL_R_IMG_6_M1? If it's S_ASSET_BT_W1_X, is the block at an extent boundary?

> Michel,

> Your last wait time lasted 1cs and it started 897s before.
> </MC>
> Yikes! I have read this in the reference guide but am somewhat confused
> about what it means. I can understand 'Your last wait time lasted 1cs'
> but don't get what 'it started 897s before' means.

Here's my study note that helps me remember things like this.

"Wait_time, seconds_in_wait of v$session_wait (and v$session in 10g). It's
easy to get confused because they mean the same thing in plain English.

Wait_time is better called last_wait_time or last_wait_duration because it's
only meaningful when the session is using CPU (on CPU) and not waiting.[note]
Seconds_in_wait may be called seconds_since_start_of_latest_wait. If the
session is waiting i.e. not on CPU, it may be called Current_wait_time for
short (but unit is seconds, not centiseconds as for wait_time). If the session
is on CPU, you have to use that mouthful long name."

"[note] v$session_wait.wait_time=0 generally means the session is waiting, and
v$session_wait.state will be 'WAITING'. But in 9i, wait_time can also be zero
because of rounding a very small wait time. In this case, only state 'WAITING'
should be trusted. Ref: C. Millsap et al. "Optimizing Oracle Performance",

Yong Huang Received on Fri Jul 07 2006 - 11:33:24 CDT

Original text of this message