Re: ORA-4030

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Wed, 26 Feb 2003 22:09:25 +1100
Message-Id: <pan.2003.02.26.11.09.25.228233@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 Received on Wed Feb 26 2003 - 05:09:25 CST