Re: ORA-4030

From: Chuck <chuckh_at_softhome.net>
Date: 26 Feb 2003 13:58:02 GMT
Message-ID: <Xns932E5B3866E27chuckhsofthomenet@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.

Are you saying that the memory is allocated as some sort of private memory pool and even when "released" is still held on to by the session so that only the same session can reuse it at a later time?

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

It's an OLTP app for scheduling workers. Very few large queries are run, but most users will need to allocate sort or hash join memory at some point during the life of their session as they lookup individual workers' schedules. These queries typically hit a few very large tables (30m-40m rows) via indexes and hash join them with smaller tables. The queries usually complete in < 10 seconds. They take noticeably longer if I don't give them sufficient memory for hash joins and in memory sorts.

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

I don't think I'm running into this as we have no unindexed tables in the database and the "session pga memory max" statistic never climbs above sort_area_size + the 1m or so that Oracle allocates upon the initial connection. Received on Wed Feb 26 2003 - 07:58:02 CST