check if the sql used some other sql_id in hiostory and way to prevent that
The event on which it was waiting was db_file_sequential read.The rows_processed from v$sql were 0 event after 2 hrs of starting the job.
I checked the previous excution plan as below:
SQL> select SQL_ID,PLAN_HASH_VALUE,TIMESTAMP,REMARKS,time from dba_hist_sql_plan where SQL_ID='9j4x3uv6w6t0m' order by TIMESTAMP;
SQL_ID PLAN_HASH_VALUE TIMESTAMP REMARKS TIME
------------- --------------- ------------------- ------------------------------ ----------
9j4x3uv6w6t0m 1214502413 08-13-2013 21:34:17