>View query is getting hanged up
How to reconcile statement above with the observation that EXPLAIN PLAN posted above shows it completes in 1 second?

ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) output from SQL_TRACE & tkprof