Re: Parallel Query Option causes excessive paging on DEC Unix 3.0 (Alpha)

From: Marty Himmelstein <marty.himmelstein_at_valley.net>
Date: 1995/12/26
Message-ID: <4bngor$2nc_at_dartvax.dartmouth.edu>#1/1


an416965_at_anon.penet.fi (an416965_at_anon.penet.fi) wrote:
>Marty Himmelstein <marty.himmelstein_at_valley.net> wrote:
>
>>I recently upgraded from 7.1.4 to 7.2.2 on a DEC Unix 3.0 Alpha platform.
>>The Alpha is a 4-CPU system with 640 megabytes of physical memory. The SGA is about
>>250 megabytes, mostly buffers. When the following problems occurred, nothing else
>>was running on the system. A query such as:
>>
>> select count(*) from table where date-attribute between startDate and endDate
 

>>caused excessive page-in activity. This did not happen on 7.1.4. The table is
>>defined with parallel(degree 3). The paging does not occur if less than 3 PQO
>>servers are initiated. It seems that each PQ server process is paging in stuff that
>>should be shared. The resident working set of each PQ server is about 210
>>megabytes, but most of this is probably the SGA, which is a shared segment. It
>>almost seems that each PQ server is dealing with a private SGA!
 

>>This is a serious problem, and not one that was caused by an exotic combination of
>>factors. (How could this get past Oracle's QA? ) I'd appreciate any help.
>
>
>Have you checked your SORT_AREA_SIZE?
>

Yes. SORT_AREA_SIZE & SORT_AREA_RETAINED_SIZE = 1MEG. Hardly enough to make a dent in a system with 640Meg of RAM, with nothing else happening. I ran some more tests: the query runs more quickly w/o parallelization!!!!

Marty Himmelstein
marty.himmelstein_at_valley.net Received on Tue Dec 26 1995 - 00:00:00 CET

Original text of this message