I have a view that when you open the view in say something like TOAD, or MS Access, or SMSS it opens fine the first time. The next time you open it or select from it it hangs.

Creating the view I get no errors or warnings. If I open the view in TOAD or try and do a SELECT * FROM xxxxxx.XXX_LOW_FLYERS_MEASPERLOC_VW in PL/SQL it opens fine. Takes approximately 8 seconds to run. I get approximately 60 rows returned. No errors. I go and open a different table/view and come back into this view or run the PL\SQL a second time it hangs. I experience the same results if I use MS Access as front end or using a remote linked account in SMSS.

It opens fine if I compile the view before opening or selecting every time.

Not to experienced with Oracle.

Any thoughts or suggestions would be greatly appreciated.

DROP VIEW xxxxxx.XXX_LOW_FLYERS_MEASPERLOC_VW;

/* Formatted on 10/18/2016 1:23:03 PM (QP5 v5.252.13127.32847) */
CREATE OR REPLACE FORCE VIEW xxxxxx.XXX_LOW_FLYERS_MEASPERLOC_VW
(
XXX_MASTER_ID,
MEASPERLOC
)
AS
SELECT f1.XXX_MASTER_ID, COUNT (r1.XXX_MASTER_ID) AS MeasPerLoc
FROM xxxxxx.XXX_FYLERCOUNT_VW f1
INNER JOIN (SELECT * FROM xxxxxx.XXX_RAW_DATA WHERE DTTM_MEAS > SYSDATE - 8) r1
ON f1.XXX_MASTER_ID = r1.XXX_MASTER_ID
WHERE r1.MEASUREMENT_LOCATION = 1 AND (r1.MEAS_TAG = 'XD' OR r1.MEAS_TAG = 'YD')
GROUP BY f1.XXX_MASTER_ID;


Also, had tried just using table xxxxxx.XXX_RAW_DATA to the JOIN rather than limiting it to the past 8 days.