Re: ORA-4030

From: Chuck <chuckh_at_softhome.net>
Date: 26 Feb 2003 17:13:07 GMT
Message-ID: <Xns932E7C4B9133Dchuckhsofthomenet@130.133.1.4>

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in news:pan.2003.02.26.11.09.25.228233_at_yahoo.com.au:

> On Tue, 25 Feb 2003 14:53:40 +0000, Chuck wrote:
>

>> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in
>> news:pan.2003.02.22.01.53.29.599942_at_yahoo.com.au: 
>>> 
>>> BUT... and here's the other thing, whilst the sort area shrinks back
>>> to zero, the memory is NOT returned to the operating system (and the
>>> bit of Oracle documentation that Chuck quoted doesn't say it will be
>>> returned to the O/S, either). Therefore, you could well get out of
>>> memory errors when no user has any sort area size: because it was
>>> *once* allocated. 
>> 
>> Where's the memory going then once it's "released" and how do I make
>> it available for other sessions to use once the first session is
>> finished with it? It seems like once a session grabs it's 10m, it's
>> held for that session and that session alone. I base this on the
>> simple math that the process starts out at 600m, and 24 hours later
>> with 120 users connected it's grown to 1800m (i.e. exactly 10m per
>> session). 

>
> It's a bit like asking where the Shared Pool memory "goes to" if
> no-one issues any SQL. It doesn't go anywhere: it's allocated to
> Oracle, and it stays with Oracle.
>
> I'd need to see some print-outs and rather more detailed descriptions
> of what your users are doing, and the sort of app they are running
> before leaping to the conclusion that they are being allocated 10M of
> sort_area_size at logon, and retaining it for the duration: that's
> simply not what happens.
>

>>> Also bear in mind that if you do any degree of parallel query, or
>>> non-parallel joins on unindexed tables, then it is possible that the
>>> execution plan will require two sorts to take place simultaneously,
>>> and that two lots of sort_area_size will therefore be required (and
>>> you should multiple THAT by the degree of parallelism if any). So
>>> you could be allocating far more sort_area_size than you were
>>> anticipating for this reason alone. 
>> 
>> This is standard edition so PQ is not possible.

>
> You might have missed the bit about 2xsort_area_size being required
> for any execution plan (whether parallelized or not) that involves a
> join on an unindexed table. PQ is not a requirement for this to
> happen.
>
> Regards
> HJR
>
>

I have just spoken to OTS about this issue and confirmed that there is a contradiction between the documentation, and the way the software actually works. In the 8i Reference manual (pg 1-113) it says that sort memory is released when the sort completes. A metalink note (46001.1) OTOH says on Windows the memory's released when the session terminates. The tech. I spoke to also confirmed that this is how it works on all platforms and not just on Windows.

If no memory is released until the session terminates, then what possible use could sort_area_retained_size have? Received on Wed Feb 26 2003 - 11:13:07 CST