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

In article <4ck71f$p6s_at_nntpd.lkg.dec.com>, roderick_at_eps.enet.dec.com (Lisa Roderick) writes:
>In article <4bakj5$7ca_at_dartvax.dartmouth.edu>, marty.himmelstein_at_valley.net
>says...
>>
>>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.
>
>Strange. Did you make any changes to the UBC? You most likely want to
>minimize it. In /etc/sysconfigtab:
>
> vm:
> ubc-minpercent=1
> ubc-maxpercent=2
>--
>Lisa Roderick
>Applications Systems Engineering Performance Group
>Digital Equipment Corporation
>Nashua, NH
>lisa.roderick_at_zko.mts.dec.com
>
>

And that ofcourse assuming your datafiles are not on filesystem. If you do, you should consider moving them to raw devices (and use async_write=1) - and tune DOWN UBC parameters as Lisa suggested.

In 7.2, PQ servers like to bypass SGA and read the data directly into process private memory. The page-in that you're seeing is "normal" under the circumstances. If you'd rather fill up the SGA with data blocks from the table in question as the select progresses, you might want to 'alter table XXX cache' (and adjust cache_size_threshold parameter accordingly) first.

-- 
=====================================================================
Vipin Gokhale
DEC SBU
Oracle Corporation
Disclaimer:         Any opinions expressed here are mine and not that
                    of my employer.
=====================================================================