Re: SQL tuning nightmare - db file sequential reads
- From: yong321@xxxxxxxxx
- Date: 7 Jul 2006 09:33:24 -0700
joshic75@xxxxxxxxx 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",
p.194."
Yong Huang
.
- References:
- SQL tuning nightmare - db file sequential reads
- From: joshic75
- Re: SQL tuning nightmare - db file sequential reads
- From: Mladen Gogala
- Re: SQL tuning nightmare - db file sequential reads
- From: Jining Han
- Re: SQL tuning nightmare - db file sequential reads
- From: joshic75
- SQL tuning nightmare - db file sequential reads
- Prev by Date: Re: Error on starting Oracle Management Server
- Next by Date: Re: Metalink doc Note 160451.1
- Previous by thread: Re: SQL tuning nightmare - db file sequential reads
- Next by thread: Re: SQL tuning nightmare - db file sequential reads
- Index(es):
Relevant Pages
|
|
