Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-4030

Re: ORA-4030

From: Telemachus <telemachus_at_ulysseswillreturn.net>
Date: Mon, 24 Feb 2003 16:15:29 -0000
Message-ID: <Cyr6a.11541$V6.15610@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.

So : IMHE it releases it back to the O/S. Can you say it doesn't ?

Also please read this by Steve Adams

http://www.ixora.com.au/newsletter/2000_11.htm

"Chuck" <chuckh_at_softhome.net> wrote in message news:Xns932C6C3F3A087chuckhsofthomenet_at_130.133.1.4...
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in
> news:sF26a.54112$jM5.136291_at_newsfeeds.bigpond.com:
>
> > "Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message
> > news:srrc5vcde59o0j42jtkts0jdbiv9l2h8vj_at_4ax.com...
> >> On 21 Feb 2003 16:13:07 GMT, Chuck <chuckh_at_softhome.net> wrote:
> >>
> > <snip>
> >
> >> Your Oracle process gets way too much memory. You shouldn't allocate
> >> more than one third of physical memory. Allocating more will result
> >> in excessive paging. Also by design of the Winblows O/S no process
> >> will get more than 2G *ever*, even if you have Advanced Server
> >> installed.
> >>
> >
> > Hi Sybrand,
> >
> > *Ever* is probably too strong an expression as there are ways to get
> > past this limit, you can get to 3G easy enough but going any higher
> > comes at a questionable cost.
> >
> > But it is still possible.
> >
>
> You are referring to the /3g switch in the boot.ini file which we are
> doing tomorrow. Then we should be able to use all of the physical RAM in
> the box.
>
> My question is if this is only a database server, why wouldn't you want
> let the RDBMS use all the memory in the box that it can? If you don't
> use it, it's wasted. The /3g switch will allows users processes (i.e.
> oracle.exe) to go up to 3g while reducing memory available to the o/s to
> 1g. The o/s isn't using anywhere near that.
>
> My other question, and maybe I'm just dense, is why isn't Oracle
> releasing the sort memory back to the o/s as the documentation plainly
> states it's supposed to? Even if it's only releasing it back to the
> oracle.exe processes heap (someone referenced it uses malloc() and
> that's where that function get's it's memory from), that memory should
> be available for another process to use. IMO, the RDBMS is not
> functioning as documented. I've asked Oracle support this same question
> before and their answer is always "We'll get back to you on that", then
> they never do. I suppose it's time to ask them the same question again.
Received on Mon Feb 24 2003 - 10:15:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US