Re: query execution time is changing randonmly

Trifon Anguelov wrote:

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

Two questions and a suggestion based on your posting.

Is this a single users system or are there other people working on it? I notice parallel_max_servers = 5. How many CPUs do you have? If you are not using Java you should consider cutting the java_pool_size down to 32K

Daniel Morgan Received on Tue Jun 11 2002 - 14:34:57 CDT