Why waiting for 'db file sequential read' whereas plan shows FTS?

Application team reported that a query usually run less than 1 hour now took almost 3 hour and still not finish. This query is only a select statement against one table. I checked the execution plan of this query it is a very simple FTS. But when check the wait event, it is waiting for 'db file sequential read', which usually means index scan.

The application team killed the session and re-run the query some time later, and this time it was finished much faster.

Later performance team provided a nice explaination, first of all they provided the following query result:

select x.* , (select file_name from dba_data_files where file_id=p1) name from (
  select p1, count(*) cnt
    from v$active_session_history where sql_id='8xnhck4a6d400' and event='db file sequential read'
   group by p1 order by 2 desc
  )x


                  P1                     CNT                                             NAME                                      
----------------------- ---------------------- --------------------------------------------------------
                    114                    188 /ora_36a_04/ORACLE/DBNAME/dbf/undoDBNAME_6.dbf
                      2                    188 /ora_36a_01/ORACLE/DBNAME/dbf/undoDBNAME_1.dbf                                
                    162                    176 /ora_36a_05/ORACLE/DBNAME/dbf/undoDBNAME_13.dbf                               
                     33                    173 /ora_36a_02/ORACLE/DBNAME/dbf/undoDBNAME_2.dbf                                
                    113                    172 /ora_36a_05/ORACLE/DBNAME/dbf/undoDBNAME_5.dbf                                
                     95                    170 /ora_36a_03/ORACLE/DBNAME/dbf/undoDBNAME_9.dbf                                
                    112                    169 /ora_36a_04/ORACLE/DBNAME/dbf/undoDBNAME_5.dbf                                
                    105                    168 /ora_36a_04/ORACLE/DBNAME/dbf/undoDBNAME_3.dbf                                
                     96                    167 /ora_36a_05/ORACLE/DBNAME/dbf/undoDBNAME_10.dbf                               
                     97                    166 /ora_36a_05/ORACLE/DBNAME/dbf/undoDBNAME_11.dbf                               
                     94                    165 /ora_36a_05/ORACLE/DBNAME/dbf/undoDBNAME_8.dbf                                
                     93                    161 /ora_36a_04/ORACLE/DBNAME/dbf/undoDBNAME_7.dbf                                
                    111                    161 /ora_36a_04/ORACLE/DBNAME/dbf/undoDBNAME_4.dbf                                

This query undoubtly demonstrate that the wait for 'db file sequntial read' is due to read from undo tablespace blocks.

They further expained:

The difference between the first and second run is that on the first (slow) run when query started , it appears there was a lot of DML activity was going on against CH_OBJECTS table - possibly involving longer than normal running non-committed transactions. This made Oracle perform a lot of extra I/O against the UNDO segments in order to maintain a read consistent image of the data true to the moment the query was kicked off.

When the query was kicked off the second time, the abnormal DML activity had already completed and there were no reads from UNDO segments, just the normal serial full scan occurred.

It is a great learning experience for me. Particularly what insterested to me is the way they query the v$active_session_history view. I did the following
exercise in another database:

system@DB2NAME> select distinct(event)  from v$active_session_history where sql_id='axuuxsm4p8hct';

EVENT
----------------------------------------------------------------

reliable message
os thread startup
direct path read
PX Deq: Signal ACK


system@DB2NAME> select p1, p1text, count(*) from v$active_session_history 
where sql_id='axuuxsm4p8hct' and  event='direct path read' group by p1, p1text;

        P1 P1TEXT                             COUNT(*)
---------- -------------------------------- ----------
       540 file number                               1
       407 file number                               2
       193 file number                               1

system@DB2NAME> select file_name from dba_data_files where file_id=540;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/ora_44a_04/ORACLE/DB2NAME/dbf/tlgp11mDB2NAME_06.dbf

system@DB2NAME> select file_name from dba_data_files where file_id=407;

FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/ora_44a_07/ORACLE/DB2NAME/dbf/tlgp11mDB2NAME_05.dbf



  1  select x.*, (select file_name from dba_data_files where file_id=p1) from
  2  (
  3  select p1, count(*) from v$active_session_history where sql_id='axuuxsm4p8hct' and  event='direct path read' group by p1
  4* ) x
system@DB2NAME> /

        P1   COUNT(*)
---------- ----------
(SELECTFILE_NAMEFROMDBA_DATA_FILESWHEREFILE_ID=P1)
------------------------------------------------------------------------------------------------------------------------
       407          2
/ora_44a_07/ORACLE/DB2NAME/dbf/tlgp11mDB2NAME_05.dbf

       193          1
/ora_44a_02/ORACLE/DB2NAME/dbf/tlgp11mDB2NAME_01.dbf

       540          1
/ora_44a_04/ORACLE/DB2NAME/dbf/tlgp11mDB2NAME_06.dbf