gurus,

i have a query with "union" and over the time the execution time is changing. somethimes the execution time vary with more than 2 minutes. It happends randomly w/o any pattern. No statistics are being analyzed between the slow down occurances, moreover the query does not use any bind variables.

my execution plan is:

SELECT STATEMENT Cost = 6108
SORT UNIQUE
UNION-ALL
TABLE ACCESS FULL
NESTED LOOPS
TABLE ACCESS FULL
TABLE ACCESS BY INDEX ROWID
INDEX UNIQUE SCAN

I have checked the sort_area_size and sort_area_retained_size, sorts memory/disk ratio, library cache hit ratio, and they all looks ok.

anyone has any idea what might causing this to happend?

thank you in advance for looking into this issue.
vincent