Query execution time vary time to time

[vince]

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

[sambavan]

What are these parameters DBWR(s), db_file_multiblock_read_count, and optimizer_mode set to?

Sam

[vince]

They 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.

vince

[Edited by vince on 06-11-2002 at 12:52 PM]

[sambavan]

Try increasing your DB writer process. Also remember to increase your latches in parallel. For every thing, I would suggest that you run a statspack and gather the stats to make a wise decisions...

Sam

[vince]

What is the idea of increasing DBWR processes? My query gets randomly executed with diff times. It is not constant issue.

I am not convinced that is the right way. Any one else experienced same issue ??

vincent

[sambavan]

If the database is a very active one, then what happens was that the one DBWR process that has to do the work of reading and writing. If your reports are going to put in some load the DBWR would be a slowdown point. This would be the general notion. Now further, as I said above, I strongly would suggest that you run the statspack during the regular time and one when running the report. then do an analysis and see where exactly the cholk point comes from.

Hope this would explain.

Sam

[pando]

we do because we run queries at different time, sometimes morning sometimes afternoon and sometimes in earli morning, early morning runs faster

we have a materiliazed view, takes 30 secs to refresh at 3:00am, 2 minutes at 12:00pm

btw your

db_file_multiblock_read_count is a bit big, I cannot assure you is too big because I dont know your OS max I/O but seems a bit big...

log_buffer is definitely too big, more than 3MB wont give you extra benefit