Re: ORA-4030

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

>
> Bear in mind that in Windows everything is running inside a single
> process anyway, and processes have a maximum amount of memory they can
> reference.
>
> 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. Received on Tue Feb 25 2003 - 08:53:40 CST