Re: any single serial session will never get more than 5% of pga

Notes in-line

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, December 26, 2003 11:39 PM

> To be honest I'm not sure why such a <feature> is available!
> I have not used it so I'm not really qualified enough to judge it.
>
> But in my opinion, a session asks for memory because it needs memory.
> So is it possible that a session is asking for memory that it does not
> really need and it can continue running without the requested memory?
> The answer could be yes, if the more memory means faster (like sorting)
and
> the sort_area_size is too big to be satisfied for all sessions, in this
> situation the DBA is responsible for the wrong settings.
>
> But what if more memory is required like: memory tables, associative
arrays,
> etc and memory was denied? Would the session fail? I think the answer
would
> be "YES" - Did anybody try this?
>

Memory for pl/sql objects falls outside the scope of the workarea policy If your memory demands for an associative array are excessive you can still grow your pga to extremes.

> Is the feature available because Oracle sessions don't deallocate the
extra
> memory and by using this feature, it will encourage the sessions that
> already succeeded in allocating memory that they don't need any more by
> punishing the ones that ask for more memory now by saying "NO"?

If the don't need the memory anymore they won't have it, because the code now ensures that the memory is releasee - so no question of "punishing" other sessions.

> Or is it going to ask the sessions that have extra allocated memory to
> release it which should be the normal behavior anyway without using any
> policies?
>

"Should" as in 'you think this happens already' (it doesn't) or "should" as in
'the way the code ought to have been written in the first place' (it tried, but
most unix libraries didn't implement the calls) ?

The database code does need a policy for sorting (for example), otherwise there is no way to determine whether an operation should be allowed to acquire an arbitrarily large amount of memory to do a sort / merge join rather
than doing a nested loop join. In the "old days", the DBA produced a policy called the 'sort_area_size', which stopped the optimizer from doing an optimum job in a hybrid system, and depended on the operating system handling issues of over-allocation and idle memory. The issues of idle memory and over-allocation are now (largely) back with the database.

> Regards,
>
> Waleed
>
> -----Original Message-----
> Sent: Friday, December 26, 2003 5:39 PM
> To: Multiple recipients of list ORACLE-L
> pga_aggregate_target
>
>
>
> For special cases like that I would switch the
> session back to a manual workarea policy and
> set a suitable sort area.
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___November
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Friday, December 26, 2003 9:49 PM
> pga_aggregate_target
>
>
> > Is there any way to give say 75% of pga_aggregate_target
> > to a single session? The reason I am asking this is -
> > sometimes we need to build an index as soon as possible
> > and the index creating is the only thing running and
> > other applications are stopped waiting for the index.
> >
> > Thanks,
> >
> > Roger
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Khedr, Waleed
> INET: Waleed.Khedr_at_FMR.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).