Database Tuning (MOSC)

MOSC Banner

Why a select statement get data from undo segment

edited May 16, 2010 7:10PM in Database Tuning (MOSC) 7 commentsAnswered
 I have a sql like below     SELECT * FROM WIP_DISCRETE_JOBS WHERE ROWID = 'AAATxwAAXAABVrnAAU';

 

Normally, it is impossible to occur a performance issue because I select this table using rowid directly. But this sql take me more than 30 mins and still not finished.

If I select the same table using another rowid,  there is no problem.

 

I check the v$session_wait, I found the waiting event is "db file sequencial read" and the file is a undo tablespace datafile.  As my understanding, that means someone is updating or deleting the same row and have not committed or rollbacked yet. But I do not found any locks for this table in v$lock.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center