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

Hi Jonathan,

I'm not sure what you really think about this new feature! Are you saying that Oracle is capable now of releasing the extra memory something it was not capable of before?
If yes, then what does it have to do with the work policy?

I see this feature useful (not really) for a database application that hosts N concurrent sessions
while the amount of available resources is capable of running only N / m sessions.
Where m is any integer.

In different words, it's the choice when we don't have the required resources to run the app efficiently without restriction to the performance and by using it, it will be able to torture any session that is asking for memory and give it enough guilt not to ask for it again and just try to get the job done by any means :)

Regards,

Waleed

-----Original Message-----
Sent: Saturday, December 27, 2003 3:09 AM To: Multiple recipients of list ORACLE-L 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).
-- 
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).