Re: ORA-4030

"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in news:Cyr6a.11541$V6.15610_at_news.indigo.ie:

> Someone (can't remember who - think it was Mr. Kyte) put together a
> lovely demo of PGA allocation (and the importance of
> SORT_AREA_RETAINED_SIZE) that proves what's going on.
>
>
> However - if I use SQL*Plus on W2K on the database server(dedicated
> server - not MTS) , do a big sort/join (Sort_area_size, _retained_,
> and hash_Area_size are all big too ) then in the taskbar we can see
> Oracle taking a lot of memory. When I quit out of SQL*Plus or
> otherwise end the session we can see Oracle release all that PGA
> memory.

Why do you have to disconnect to release the memory? According to the 8i manual, the memory is supposed to be released down to sort_area_retained_size whent he sort phase is finished. When all of the rows are returned to the app, the rest of the memory is supposed to be released. Our app gets to the point, but it does not appear that the memory ever gets released. BTW most of our users never disconnect from the database (and if the docs are right, they shouldn't have to).

According to the metalink docs on NT/2k memory architecture (which btw I have read Sybrand), it is the VirtualAlloc set of function calls that's used to dynamically allocate/deallocate memory for session memory, so the memory should be returned to the o/s right away.

I will loop for the Tom Kyte article you mentioned as well as the Ixora article. Received on Mon Feb 24 2003 - 15:31:00 CST