query execution time is changing randonmly

From: Trifon Anguelov <trifona_at_pacbell.net>
Date: Tue, 11 Jun 2002 17:59:26 GMT
Message-ID: <2UqN8.18223$gj6.449184806@newssvr14.news.prodigy.com>

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 some of mine init.ora parameters are:

db_file_multiblock_read_count = 16
db_block_buffers = 51200
shared_pool_size = 209715200
large_pool_size = 614400
java_pool_size = 20971520
parallel_max_servers = 5
log_buffer = 10485760
optimizer_mode=choose
db_block_size = 8192
DB_WRITER_PROCESSES=1 Oracle version is 8.1.6.0.0 and it is Windows NT 4.0 SP6.

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. Received on Tue Jun 11 2002 - 12:59:26 CDT