Re: pga workarea and ora-04030

[Connor McDonald]

No longer having the versions to validate this, what
follows might be waffle, but I thought:

v7.0 => full allocated space
v.7.2 => space allocated more intelligently, never
freed
v7.3 => space allocated more intelligently, reusable
when you delete entries

Cheers
Connor

 --- Jonathan Lewis <[EMAIL PROTECTED]>
wrote: > 
> If you want to work out how much difference there
> is in different code paths, then you have to do some
> very patient testing.
> 
> Run your test program for lots of different array
> sizes,
> say 1,  2,  3,  and so on up to 100M.
> On each run, disconnect and reconnect your session,
> and check v$sesstat for pga and uga memory usage
> before and after each run, as well as the memory
> reported from the O/S (I think ps -al and look at
> the
> RSS figure for your shadow process is the HP-UX
> option - but someone may have a better idea).
> 
> You then need to run a second set of tests where
> the size of an array element is significantly
> different
> from the first test - e.g. test1 uses a varchar2(32)
> test2 uses varchar2(1000)  (and the third test uses
> varchar2(8000) ). Then you may be able to
> figure out the significant differences in handling
> 
> 
> It is quite likely that there is a different code
> path
> for allocating and freeing memory as you change
> versions of Oracle, or change parameters within
> a version; and it is quite possible that a piece of
> code for handling arrays changed from version
> to version - and any change could have introduced
> an unreasonable error.
> 
> 
> In passing, I thought the 'array is a fully
> pre-allocated'
> was a version 6 thing that got fixed in version 7.
> I would be amazed if arrays had gone backwards
> a step - it's easy enough to check: change your
> test to populate just element 1 and element
> 1
> and see if your session still crashes.
> 
> 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
> 
> 
> Next public appearance2:
>  March 2004 Hotsos Symposium - Keynote
>  March 2004 Charlotte NC - OUG Tutorial
>  April 2004 Iceland
> 
> 
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
> 
> 
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> UK___February
> 
> 
> 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"
> <[EMAIL PROTECTED]>
> Sent: Monday, January 12, 2004 11:44 AM
> 
> 
> > Jonathan,
> >
> > Thanks for your answer this clarifies a bit more
> > But it still bothers me that this program can
> swallow
> > 4Gb of physical memory and 4 Gb of swap and it is
> still not
> > enough. You explain that the memory of pl/sql
> tables is not in
> > the sga so that's clear now.
> >
> > What still bothers me is that my original program
> works fine
> > with pga_target = 0 and wa-size-policy=manual
> > When I try this with this test-program it fails
> (see below)
> > VU_2>exec testarray(1);
> > begin testarray(1); end;
> >
> > *
> > ERROR at line 1:
> > ORA-00604: error occurred at recursive SQL level 1
> > ORA-04030: out of process memory when trying to
> allocate 8144 bytes
> (cursor
> > work he,qesaQBInit:buffer)
> > ORA-06508: PL/SQL: could not find program unit
> being called
> > ORA-06512: at "SYS.DBMS_OUTPUT", line 127
> > ORA-06512: at "VRIJ_UIT.TESTARRAY", line 23
> > ORA-06500: PL/SQL: storage error
> > ORA-06512: at line 1
> >
> > Somehow these setting influence the way the pl/sql
> program works.
> > This testprogram is clearly not enough to explain
> this behaviour. Because
> we
> > Use quite some pl/sql I would like to know more
> because it could happen
> > Maybe with other programs.
> >
> > Oracle 7 the same code runs fine also. I read a
> post that the difference
> for
> > pl/sql tables is that they are now implemented as
> fully allocated arrays
> in
> > memory whether they were implemented in oracle 7
> and chained linked lists.
> >
> > Obviously this takes more memory but why do these
> 2 settings play such a
> > role? Is the memory involved differently when
> using these settings?
> > Can I monitor specific memory usage with these
> setting and how should this
> > be done on

Re: pga workarea and ora-04030

[Jonathan Lewis]

If you want to work out how much difference there
is in different code paths, then you have to do some
very patient testing.

Run your test program for lots of different array sizes,
say 1,  2,  3,  and so on up to 100M.
On each run, disconnect and reconnect your session,
and check v$sesstat for pga and uga memory usage
before and after each run, as well as the memory
reported from the O/S (I think ps -al and look at the
RSS figure for your shadow process is the HP-UX
option - but someone may have a better idea).

You then need to run a second set of tests where
the size of an array element is significantly different
from the first test - e.g. test1 uses a varchar2(32)
test2 uses varchar2(1000)  (and the third test uses
varchar2(8000) ). Then you may be able to
figure out the significant differences in handling


It is quite likely that there is a different code path
for allocating and freeing memory as you change
versions of Oracle, or change parameters within
a version; and it is quite possible that a piece of
code for handling arrays changed from version
to version - and any change could have introduced
an unreasonable error.


In passing, I thought the 'array is a fully pre-allocated'
was a version 6 thing that got fixed in version 7.
I would be amazed if arrays had gone backwards
a step - it's easy enough to check: change your
test to populate just element 1 and element 1
and see if your session still crashes.

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


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


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


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


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" <[EMAIL PROTECTED]>
Sent: Monday, January 12, 2004 11:44 AM


> Jonathan,
>
> Thanks for your answer this clarifies a bit more
> But it still bothers me that this program can swallow
> 4Gb of physical memory and 4 Gb of swap and it is still not
> enough. You explain that the memory of pl/sql tables is not in
> the sga so that's clear now.
>
> What still bothers me is that my original program works fine
> with pga_target = 0 and wa-size-policy=manual
> When I try this with this test-program it fails (see below)
> VU_2>exec testarray(1);
> begin testarray(1); end;
>
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-04030: out of process memory when trying to allocate 8144 bytes
(cursor
> work he,qesaQBInit:buffer)
> ORA-06508: PL/SQL: could not find program unit being called
> ORA-06512: at "SYS.DBMS_OUTPUT", line 127
> ORA-06512: at "VRIJ_UIT.TESTARRAY", line 23
> ORA-06500: PL/SQL: storage error
> ORA-06512: at line 1
>
> Somehow these setting influence the way the pl/sql program works.
> This testprogram is clearly not enough to explain this behaviour. Because
we
> Use quite some pl/sql I would like to know more because it could happen
> Maybe with other programs.
>
> Oracle 7 the same code runs fine also. I read a post that the difference
for
> pl/sql tables is that they are now implemented as fully allocated arrays
in
> memory whether they were implemented in oracle 7 and chained linked lists.
>
> Obviously this takes more memory but why do these 2 settings play such a
> role? Is the memory involved differently when using these settings?
> Can I monitor specific memory usage with these setting and how should this
> be done on HPUX?
>
> Regards,
>
> Jeroen
> -Oorspronkelijk bericht-
> Van: Jonathan Lewis [mailto:[EMAIL PROTECTED]
> Verzonden: Saturday, January 10, 2004 6:54 PM
> Aan: Multiple recipients of list ORACLE-L
> Onderwerp: Re: pga workarea and ora-04030
>
>
> I think what you've demonstrated is
> that pl/sql tables are not limited by
> pga-aggregate target, and that a pl/sql
> table can grow until it has taken up all
> the available memory on your machine.
>
> I'd guess that each element in your table
> takes about the same space - with a little
> error round the edges - so you can have
> 17.6M rows before you are out of memory -
> either as two tables of 8.8M or one table
> of 17.6M.
>
> The sleep time is probably because you start
> going to SWAP and your session spends time
> dumping real memory to disc.
>
> When the SGA is 1.5G smaller, that frees up
> an extra 1.5G of memory for you to use as
> PGA -

RE: pga workarea and ora-04030

[Jeroen van Sluisdam]

Jonathan,

Thanks for your answer this clarifies a bit more
But it still bothers me that this program can swallow
4Gb of physical memory and 4 Gb of swap and it is still not
enough. You explain that the memory of pl/sql tables is not in
the sga so that's clear now.

What still bothers me is that my original program works fine
with pga_target = 0 and wa-size-policy=manual
When I try this with this test-program it fails (see below)
VU_2>exec testarray(1);
begin testarray(1); end;

*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04030: out of process memory when trying to allocate 8144 bytes (cursor
work he,qesaQBInit:buffer)
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_OUTPUT", line 127
ORA-06512: at "VRIJ_UIT.TESTARRAY", line 23
ORA-06500: PL/SQL: storage error
ORA-06512: at line 1

Somehow these setting influence the way the pl/sql program works. 
This testprogram is clearly not enough to explain this behaviour. Because we
Use quite some pl/sql I would like to know more because it could happen
Maybe with other programs.

Oracle 7 the same code runs fine also. I read a post that the difference for
pl/sql tables is that they are now implemented as fully allocated arrays in
memory whether they were implemented in oracle 7 and chained linked lists.

Obviously this takes more memory but why do these 2 settings play such a
role? Is the memory involved differently when using these settings?
Can I monitor specific memory usage with these setting and how should this
be done on HPUX?

Regards,

Jeroen
-Oorspronkelijk bericht-
Van: Jonathan Lewis [mailto:[EMAIL PROTECTED] 
Verzonden: Saturday, January 10, 2004 6:54 PM
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Re: pga workarea and ora-04030


I think what you've demonstrated is
that pl/sql tables are not limited by
pga-aggregate target, and that a pl/sql
table can grow until it has taken up all
the available memory on your machine.

I'd guess that each element in your table
takes about the same space - with a little
error round the edges - so you can have
17.6M rows before you are out of memory -
either as two tables of 8.8M or one table
of 17.6M.

The sleep time is probably because you start
going to SWAP and your session spends time
dumping real memory to disc.

When the SGA is 1.5G smaller, that frees up
an extra 1.5G of memory for you to use as
PGA - so you get lots more entries in the
table before you run out of memory.


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


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


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


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


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" <[EMAIL PROTECTED]>
Sent: Friday, January 09, 2004 10:34 PM


> Hi,
>
> I followed you advice and made small testprogram see below:
> I only get the ora-06500 which I have had before in the original program
as
> A followup error so to me it seems to be reproducible.
>
> In manuals I only find that the index
> Of a pl/sql table cannot be more than 2**31, which is something like
> 2.000.000.000
> I found on metalink some posts which suggested this might be functioning
> better enlarging shared pool and also max user data (ulimit of oracle)
> We increased maxdseg in the ux-kernel parameters to 4Gb to increase this
> limit (was 2Gb). There is 4Gb available of physical memory in the box.
>
> Using a shared pool 0f 500Mb, pga_aggregate_target 100Mb
> setting 100.000.000 elements -> 22 minutes and it fails
> Exception raised insert i= 68102540
>
> Using 1 table, shared pool 2Gb
> setting 1.000.000 elements -> 14 seconds
> setting 10.000.000 elements -> 282 seconds
> setting 100.000.000 elements -> 12 min 24 seconds fails
> 21:54:37 VU_2>exec testarray( 1 );
> Exception raised insert i= 17613935
>
> Running with a second table involved: after 17 minutes 29 seconds
> 22:40:20 VU_2>exec testarray( 1 );
> Exception raised insert i= 8806960
> So it is reduced by 50%. But why is the result with a smaller sga
> Giving me more elements set?
>
> Watching the oracle serverprocess with top utility
> I see the memory resident part
> Most of the time around 2600M but more interesting the process is
> Most of the time sleeping, what the heck is it doing all the time before
> Going into an error?
>
> 1   ?4728 oracle   128 20  4116M  2626M 

Re: pga workarea and ora-04030

[Tim Gorman]

 top utility
>>> I see the memory resident part
>>> Most of the time around 2600M but more interesting the process is
>>> Most of the time sleeping, what the heck is it doing all the time before
>>> Going into an error?
>>> 
>>> 1   ?4728 oracle   128 20  4116M  2626M sleep7:49  1.20
> 1.20
>>> oracleVU_2
>>> 
>>> I cannot find any other restriction then 2**31 limit on the index.
>>> I don't know how to calculate how much memory this is taking because
>>> watching sqlworkarea of pgastat doesn't show any useful info in this
> case.
>>> But it looks to I'm hitting a limit somehow.
>>> 
>>> Can somebody explain which limit this is and how is it composed or
>>> influenced (temp, sga ?) ?
>>> 
>>> Is this reproducible on other systems / versions ?( Metalink post
> reports
>>> This also on early 8.1.x versions , I couldn't find this on 9.x
> versions)
>>> 
>>> create or replace procedure testarray( psize number ) as
>>> begin
>>> declare
>>> TYPE nAllotment_tabtypIS TABLE OF number
>>>   INDEX BY BINARY_INTEGER;
>>>   assarray nAllotment_tabtyp;
>>>   assarray2 nAllotment_tabtyp;
>>>   assarray3 nAllotment_tabtyp;
>>>   uitleg   varchar2(100);
>>> begin
>>>   uitleg := 'start loop';
>>>   for i in 1..psize loop
>>> uitleg := 'insert i= ' || i;
>>> assarray(i) := i;
>>> /*
>>> uitleg := 'insert i2= ' || i;
>>> assarray2(i) := i;
>>> */
>>>   end loop;
>>>   EXCEPTION
>>> WHEN OTHERS THEN
>>>   dbms_output.enable(2);
>>>   dbms_output.put_line(' Exception raised ' || uitleg );
>>> end;
>>> end;
>>> -Oorspronkelijk bericht-
>>> Van: Jonathan Lewis [mailto:[EMAIL PROTECTED]
>>> Verzonden: dinsdag 6 januari 2004 16:49
>>> Aan: Multiple recipients of list ORACLE-L
>>> Onderwerp: Re: pga workarea and ora-04030
>>> 
>>> 
>>> The workarea_policy stuff does not apply
>>> to things like pl/sql tables, only to tuneable
>>> memory.  Given that you don't have the
>>> problem when you disable p_a_t and w_p,
>>> it may be that there is some buggy event
>>> occurring where the workarea_policy code
>>> is being infringed by an abuse of pga memory.
>>> 
>>> You could try setting up test cases where
>>> you use a pl/sql loop to build a pl/sql table.
>>> Make it a procedure with an input parameter
>>> that is the table size, and see how big the table
>>> has to before the procedure crashes.  Fiddle
>>> with the p_a_t, and w_p (they can be set
>>> separately) to see if the crash point moves.
>>> 
>>> This may give you (or Oracle Corp) some clues.
>>> 
>>> 
>> 
>> --
>> Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> --
>> Author: Jonathan Lewis
>>   INET: [EMAIL PROTECTED]
>> 
>> 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: [EMAIL PROTECTED] (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: Tim Gorman
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: pga workarea and ora-04030

[Bobak, Mark]

Ryan,

As Jonathan mentioned, global PL/SQL tables are allocated from the UGA.
In MTS, they will be allocated from the SGA's large pool (or, in the event one
has not been defined, which is a big no-no for MTS, then from the shared pool.)  Note 
in this case, if the large_pool is insufficient to satisfy the
demands, the user's session will encounter ORA-4031.  

In dedicated server, the UGA is allocated in the PGA.  The user's session
will allocate more private memory (by growing the PGA heap) from system
memory.  If/when the PL/SQL table grows so large as the process can no longer
allocate memory from the O/S, then the user's session will encounter ORA-4030.

Note the slightly different allocation mechanism, and the different Oracle
errors encountered (4031 vs. 4030).  Based on the fact that you're 
encountering ORA-4030, I'd guess that your user is connect via dedicated
server.

Hope that helps,

-Mark

PS  Note that when this error occurs, if you simply kill the background
process of the session that has allocated all that RAM, it should immediately
be freed and the problem should clear up.  It should not be necessary to
bounce the entire instance, just the problem user.



-Original Message-
From:   Ryan [mailto:[EMAIL PROTECTED]
Sent:   Sat 1/10/2004 4:34 PM
To: Multiple recipients of list ORACLE-L
Cc: 
Subject:Re: pga workarea and ora-04030
I have seen people bulk collect into pl/sql tables so much data that you
cannot even connect to the server. So I'm assuming that ones the UGA fills
up, Oracle will allocate whatever unused memory is left on the server for
pl/sql tables?
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, January 10, 2004 4:04 PM


>
> In the UGA, I should think (which also means the
> SGA if you are running MTS).  It can't be in the
> PGA (ignoring the fact that the UGA is in the PGA
> for non-MTS) or you couldn't have global pl/sql
> tables that persist across database calls.
>
> 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
>
>
> Next public appearance2:
>  March 2004 Hotsos Symposium - Keynote
>  March 2004 Charlotte NC - OUG Tutorial
>  April 2004 Iceland
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> UK___February
>
>
> 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" <[EMAIL PROTECTED]>
> Sent: Saturday, January 10, 2004 6:04 PM
>
>
> > Where does oracle store pl/sql tables? I have run into problems with
> > developers doing massive bulk collects and I have to bounce the entire
> > server...
> >
> >
> > - Original Message -
> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > Sent: Saturday, January 10, 2004 12:54 PM
> >
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
>   INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Ryan
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).


<>

Re: pga workarea and ora-04030

[Ryan]

I have seen people bulk collect into pl/sql tables so much data that you
cannot even connect to the server. So I'm assuming that ones the UGA fills
up, Oracle will allocate whatever unused memory is left on the server for
pl/sql tables?
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, January 10, 2004 4:04 PM


>
> In the UGA, I should think (which also means the
> SGA if you are running MTS).  It can't be in the
> PGA (ignoring the fact that the UGA is in the PGA
> for non-MTS) or you couldn't have global pl/sql
> tables that persist across database calls.
>
> 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
>
>
> Next public appearance2:
>  March 2004 Hotsos Symposium - Keynote
>  March 2004 Charlotte NC - OUG Tutorial
>  April 2004 Iceland
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> UK___February
>
>
> 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" <[EMAIL PROTECTED]>
> Sent: Saturday, January 10, 2004 6:04 PM
>
>
> > Where does oracle store pl/sql tables? I have run into problems with
> > developers doing massive bulk collects and I have to bounce the entire
> > server...
> >
> >
> > - Original Message -
> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > Sent: Saturday, January 10, 2004 12:54 PM
> >
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
>   INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Ryan
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: pga workarea and ora-04030

[Jonathan Lewis]

In the UGA, I should think (which also means the
SGA if you are running MTS).  It can't be in the 
PGA (ignoring the fact that the UGA is in the PGA
for non-MTS) or you couldn't have global pl/sql
tables that persist across database calls.

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


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


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


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


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" <[EMAIL PROTECTED]>
Sent: Saturday, January 10, 2004 6:04 PM


> Where does oracle store pl/sql tables? I have run into problems with
> developers doing massive bulk collects and I have to bounce the entire
> server...
> 
> 
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Saturday, January 10, 2004 12:54 PM
> 
> 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: pga workarea and ora-04030

[Ryan]

Where does oracle store pl/sql tables? I have run into problems with
developers doing massive bulk collects and I have to bounce the entire
server...


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, January 10, 2004 12:54 PM


>
> I think what you've demonstrated is
> that pl/sql tables are not limited by
> pga-aggregate target, and that a pl/sql
> table can grow until it has taken up all
> the available memory on your machine.
>
> I'd guess that each element in your table
> takes about the same space - with a little
> error round the edges - so you can have
> 17.6M rows before you are out of memory -
> either as two tables of 8.8M or one table
> of 17.6M.
>
> The sleep time is probably because you start
> going to SWAP and your session spends time
> dumping real memory to disc.
>
> When the SGA is 1.5G smaller, that frees up
> an extra 1.5G of memory for you to use as
> PGA - so you get lots more entries in the
> table before you run out of memory.
>
>
> 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
>
>
> Next public appearance2:
>  March 2004 Hotsos Symposium - Keynote
>  March 2004 Charlotte NC - OUG Tutorial
>  April 2004 Iceland
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> UK___February
>
>
> 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" <[EMAIL PROTECTED]>
> Sent: Friday, January 09, 2004 10:34 PM
>
>
> > Hi,
> >
> > I followed you advice and made small testprogram see below:
> > I only get the ora-06500 which I have had before in the original program
> as
> > A followup error so to me it seems to be reproducible.
> >
> > In manuals I only find that the index
> > Of a pl/sql table cannot be more than 2**31, which is something like
> > 2.000.000.000
> > I found on metalink some posts which suggested this might be functioning
> > better enlarging shared pool and also max user data (ulimit of oracle)
> > We increased maxdseg in the ux-kernel parameters to 4Gb to increase this
> > limit (was 2Gb). There is 4Gb available of physical memory in the box.
> >
> > Using a shared pool 0f 500Mb, pga_aggregate_target 100Mb
> > setting 100.000.000 elements -> 22 minutes and it fails
> > Exception raised insert i= 68102540
> >
> > Using 1 table, shared pool 2Gb
> > setting 1.000.000 elements -> 14 seconds
> > setting 10.000.000 elements -> 282 seconds
> > setting 100.000.000 elements -> 12 min 24 seconds fails
> > 21:54:37 VU_2>exec testarray( 1 );
> > Exception raised insert i= 17613935
> >
> > Running with a second table involved: after 17 minutes 29 seconds
> > 22:40:20 VU_2>exec testarray( 1 );
> > Exception raised insert i= 8806960
> > So it is reduced by 50%. But why is the result with a smaller sga
> > Giving me more elements set?
> >
> > Watching the oracle serverprocess with top utility
> > I see the memory resident part
> > Most of the time around 2600M but more interesting the process is
> > Most of the time sleeping, what the heck is it doing all the time before
> > Going into an error?
> >
> > 1   ?4728 oracle   128 20  4116M  2626M sleep7:49  1.20
1.20
> > oracleVU_2
> >
> > I cannot find any other restriction then 2**31 limit on the index.
> > I don't know how to calculate how much memory this is taking because
> > watching sqlworkarea of pgastat doesn't show any useful info in this
case.
> > But it looks to I'm hitting a limit somehow.
> >
> > Can somebody explain which limit this is and how is it composed or
> > influenced (temp, sga ?) ?
> >
> > Is this reproducible on other systems / versions ?( Metalink post
reports
> > This also on early 8.1.x versions , I couldn't find this on 9.x
versions)
> >
> > create or replace procedure testarray( psize number ) as
> > begin
> > declare
> > TYPE nAllotment_tabtypIS TABLE OF number
> >   INDEX BY BINARY_INTEGER;
> >   assarray nAllotment_tabtyp;
> >   assarray2 nAllotment_tabtyp;
> >   assarray3 nAllotment_tabtyp;
> >   uitleg   varchar2(100);
>

Re: pga workarea and ora-04030

[Jonathan Lewis]

I think what you've demonstrated is
that pl/sql tables are not limited by
pga-aggregate target, and that a pl/sql
table can grow until it has taken up all
the available memory on your machine.

I'd guess that each element in your table
takes about the same space - with a little
error round the edges - so you can have
17.6M rows before you are out of memory -
either as two tables of 8.8M or one table
of 17.6M.

The sleep time is probably because you start
going to SWAP and your session spends time
dumping real memory to disc.

When the SGA is 1.5G smaller, that frees up
an extra 1.5G of memory for you to use as
PGA - so you get lots more entries in the
table before you run out of memory.


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


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


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


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


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" <[EMAIL PROTECTED]>
Sent: Friday, January 09, 2004 10:34 PM


> Hi,
>
> I followed you advice and made small testprogram see below:
> I only get the ora-06500 which I have had before in the original program
as
> A followup error so to me it seems to be reproducible.
>
> In manuals I only find that the index
> Of a pl/sql table cannot be more than 2**31, which is something like
> 2.000.000.000
> I found on metalink some posts which suggested this might be functioning
> better enlarging shared pool and also max user data (ulimit of oracle)
> We increased maxdseg in the ux-kernel parameters to 4Gb to increase this
> limit (was 2Gb). There is 4Gb available of physical memory in the box.
>
> Using a shared pool 0f 500Mb, pga_aggregate_target 100Mb
> setting 100.000.000 elements -> 22 minutes and it fails
> Exception raised insert i= 68102540
>
> Using 1 table, shared pool 2Gb
> setting 1.000.000 elements -> 14 seconds
> setting 10.000.000 elements -> 282 seconds
> setting 100.000.000 elements -> 12 min 24 seconds fails
> 21:54:37 VU_2>exec testarray( 1 );
> Exception raised insert i= 17613935
>
> Running with a second table involved: after 17 minutes 29 seconds
> 22:40:20 VU_2>exec testarray( 1 );
> Exception raised insert i= 8806960
> So it is reduced by 50%. But why is the result with a smaller sga
> Giving me more elements set?
>
> Watching the oracle serverprocess with top utility
> I see the memory resident part
> Most of the time around 2600M but more interesting the process is
> Most of the time sleeping, what the heck is it doing all the time before
> Going into an error?
>
> 1   ?4728 oracle   128 20  4116M  2626M sleep7:49  1.20  1.20
> oracleVU_2
>
> I cannot find any other restriction then 2**31 limit on the index.
> I don't know how to calculate how much memory this is taking because
> watching sqlworkarea of pgastat doesn't show any useful info in this case.
> But it looks to I'm hitting a limit somehow.
>
> Can somebody explain which limit this is and how is it composed or
> influenced (temp, sga ?) ?
>
> Is this reproducible on other systems / versions ?( Metalink post reports
> This also on early 8.1.x versions , I couldn't find this on 9.x versions)
>
> create or replace procedure testarray( psize number ) as
> begin
> declare
> TYPE nAllotment_tabtypIS TABLE OF number
>   INDEX BY BINARY_INTEGER;
>   assarray nAllotment_tabtyp;
>   assarray2 nAllotment_tabtyp;
>   assarray3 nAllotment_tabtyp;
>   uitleg   varchar2(100);
> begin
>   uitleg := 'start loop';
>   for i in 1..psize loop
> uitleg := 'insert i= ' || i;
> assarray(i) := i;
> /*
> uitleg := 'insert i2= ' || i;
> assarray2(i) := i;
> */
>   end loop;
>   EXCEPTION
>     WHEN OTHERS THEN
>   dbms_output.enable(2);
>   dbms_output.put_line(' Exception raised ' || uitleg );
> end;
> end;
> -Oorspronkelijk bericht-
> Van: Jonathan Lewis [mailto:[EMAIL PROTECTED]
> Verzonden: dinsdag 6 januari 2004 16:49
> Aan: Multiple recipients of list ORACLE-L
> Onderwerp: Re: pga workarea and ora-04030
>
>
> The workarea_policy stuff does not apply
> to things like pl/sql tables, only to tuneable
> memory.  Given that you don't have the
> problem when you disable p_a_t and w_p,
> it may be 

RE: pga workarea and ora-04030

[Jeroen van Sluisdam]

Hi,

I followed you advice and made small testprogram see below:
I only get the ora-06500 which I have had before in the original program as
A followup error so to me it seems to be reproducible. 

In manuals I only find that the index
Of a pl/sql table cannot be more than 2**31, which is something like
2.000.000.000
I found on metalink some posts which suggested this might be functioning
better enlarging shared pool and also max user data (ulimit of oracle)
We increased maxdseg in the ux-kernel parameters to 4Gb to increase this
limit (was 2Gb). There is 4Gb available of physical memory in the box.

Using a shared pool 0f 500Mb, pga_aggregate_target 100Mb
setting 100.000.000 elements -> 22 minutes and it fails
Exception raised insert i= 68102540

Using 1 table, shared pool 2Gb 
setting 1.000.000 elements -> 14 seconds
setting 10.000.000 elements -> 282 seconds
setting 100.000.000 elements -> 12 min 24 seconds fails
21:54:37 VU_2>exec testarray( 1 );
Exception raised insert i= 17613935

Running with a second table involved: after 17 minutes 29 seconds
22:40:20 VU_2>exec testarray( 1 );
Exception raised insert i= 8806960
So it is reduced by 50%. But why is the result with a smaller sga
Giving me more elements set?

Watching the oracle serverprocess with top utility
I see the memory resident part 
Most of the time around 2600M but more interesting the process is
Most of the time sleeping, what the heck is it doing all the time before
Going into an error?

1   ?4728 oracle   128 20  4116M  2626M sleep7:49  1.20  1.20
oracleVU_2

I cannot find any other restriction then 2**31 limit on the index.
I don't know how to calculate how much memory this is taking because
watching sqlworkarea of pgastat doesn't show any useful info in this case.
But it looks to I'm hitting a limit somehow.

Can somebody explain which limit this is and how is it composed or
influenced (temp, sga ?) ? 

Is this reproducible on other systems / versions ?( Metalink post reports
This also on early 8.1.x versions , I couldn't find this on 9.x versions)

create or replace procedure testarray( psize number ) as
begin
declare
TYPE nAllotment_tabtypIS TABLE OF number
  INDEX BY BINARY_INTEGER;
  assarray nAllotment_tabtyp;
  assarray2 nAllotment_tabtyp;
  assarray3 nAllotment_tabtyp;
  uitleg   varchar2(100);
begin
  uitleg := 'start loop';
  for i in 1..psize loop
uitleg := 'insert i= ' || i;
assarray(i) := i;
/*
uitleg := 'insert i2= ' || i;
assarray2(i) := i;
*/
  end loop;
  EXCEPTION
WHEN OTHERS THEN
  dbms_output.enable(2);
  dbms_output.put_line(' Exception raised ' || uitleg );
end;
end;
-Oorspronkelijk bericht-
Van: Jonathan Lewis [mailto:[EMAIL PROTECTED] 
Verzonden: dinsdag 6 januari 2004 16:49
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Re: pga workarea and ora-04030


The workarea_policy stuff does not apply
to things like pl/sql tables, only to tuneable
memory.  Given that you don't have the
problem when you disable p_a_t and w_p,
it may be that there is some buggy event
occurring where the workarea_policy code
is being infringed by an abuse of pga memory.

You could try setting up test cases where
you use a pl/sql loop to build a pl/sql table.
Make it a procedure with an input parameter
that is the table size, and see how big the table
has to before the procedure crashes.  Fiddle
with the p_a_t, and w_p (they can be set
separately) to see if the crash point moves.

This may give you (or Oracle Corp) some clues.


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" <[EMAIL PROTECTED]>
Sent: Tuesday, January 06, 2004 2:54 PM


>
> Hi,
>
> I have posted a problem before which I can only solve with a workaround
but
> because I'm not getting
> satisdactory answers from Oracle I'm trying alternatives.
> - problem is a batch pl/sql package which ends with ora-4030
> - batch runs fine on oracle 7.3.4, we migrated to oracle 9.2.0.4
> recently
> - setting pga_aggegrate_target=0 and workarea_size_policy=manual
> solves the error
> - This is the only batch which results in errors
>
> Due to recent posts I have tried smm_max_size set to 100Mb and I still get
> this error. All of the following
> is done with _smm_max_size set and first setting pga_aggegrate_target=50M
> and workarea_size_policy=auto
>

Re: pga workarea and ora-04030

[Tanel Poder]

> When I increase the pga_aggregate_target to 2Gb and the
> smm_max_size also the program fails around the following numbers from
pgastat

I'm not advocating fiddling with hidden parameters here, but there is one
more parameter which limits PGA usage, it's _pga_max_size and defaults to
200M.

Anyway, go with Jonathan's recommendation and try to allocate a large PL/SQL
table to test whether it fails.

Btw, is your batch job running in serial mode or with parallel slaves?

Tanel.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: pga workarea and ora-04030

[Jonathan Lewis]

The workarea_policy stuff does not apply
to things like pl/sql tables, only to tuneable
memory.  Given that you don't have the
problem when you disable p_a_t and w_p,
it may be that there is some buggy event
occurring where the workarea_policy code
is being infringed by an abuse of pga memory.

You could try setting up test cases where
you use a pl/sql loop to build a pl/sql table.
Make it a procedure with an input parameter
that is the table size, and see how big the table
has to before the procedure crashes.  Fiddle
with the p_a_t, and w_p (they can be set
separately) to see if the crash point moves.

This may give you (or Oracle Corp) some clues.


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" <[EMAIL PROTECTED]>
Sent: Tuesday, January 06, 2004 2:54 PM


>
> Hi,
>
> I have posted a problem before which I can only solve with a workaround
but
> because I'm not getting
> satisdactory answers from Oracle I'm trying alternatives.
> - problem is a batch pl/sql package which ends with ora-4030
> - batch runs fine on oracle 7.3.4, we migrated to oracle 9.2.0.4
> recently
> - setting pga_aggegrate_target=0 and workarea_size_policy=manual
> solves the error
> - This is the only batch which results in errors
>
> Due to recent posts I have tried smm_max_size set to 100Mb and I still get
> this error. All of the following
> is done with _smm_max_size set and first setting pga_aggegrate_target=50M
> and workarea_size_policy=auto
>
>
> Monitoring v$sql_workarea_active leads me a max. use of  532Kb. The
figures
> below didn't change during the batch
> I only saw temporarily another workarea for the same sid
> OPTYPEACTTIMEWA_SIZE   EXP_SIZEACT MAXMEM
PASS
> TEMPSEG TBLSP
> -- -- -- -- -- -- 
--
> -- ---
> GROUP BY ( 1378396893 532480 532480 532480 532480
0
>
> 14:30:44 SQL> /
>
> NAME
VALUE
> UNIT
>  -
-
> 
> aggregate PGA target parameter
104857600
> bytes
> aggregate PGA auto target
6553600
> bytes
> global memory bound
104857600
> bytes
> total PGA inuse
1105825792
> bytes
> total PGA allocated
1129529344
> bytes
> maximum PGA allocated
1135382528
> bytes
> total freeable PGA memory
458752
> bytes
> PGA memory freed back to OS
1303117824
> bytes
> total PGA used for auto workareas
737280
> bytes
> maximum PGA used for auto workareas
1163264
> bytes
> total PGA used for manual workareas
0
> bytes
>
> NAME
VALUE
> UNIT
>  -
-
> 
> maximum PGA used for manual workareas
16384
> bytes
> over allocation count
979
> bytes processed
3141169152
> bytes
> extra bytes read/written
0
> bytes
> cache hit percentage
100
> percent
>
> a) Why do I see manual workarea used despite workarea_size_policy=auto
?
> It is a test environment with just me and a developer on it
> b) Total pga used reports as 737Kb and total pga allocated finishes on
> 1.1Gb How can I relate this to the workarea?
> c) What's the exact connection with the _smm_max_size?
>
> When I increase the pga_aggregate_target to 2Gb and the smm_max_size also
> the program fails around
> the following numbers from pgastat
> QL> /
>
> NAME
VALUE
> UNIT
>  -
-
> 
> aggregate PGA target parameter
2147483648
> bytes
> aggregate PGA auto target
1895003136
> bytes
> global memory bound
2097152000
> bytes
> total PGA inuse
41918464
> bytes
> total PGA allocated
1137232896
> bytes
> maximum PGA allocated
1137249280
> bytes
> total freeable PGA memory
1074987008
> bytes
> PGA memory freed back to OS
131072
> bytes
> total PGA used for auto workareas
0
> bytes
> maximum PGA used for auto workareas
0
> bytes
> total PGA used for manual workareas
0
> bytes
>
> NAME
VALUE
> UNIT
>  -
-
> 
> maximum PGA used for manual workareas
2347008
> bytes
> over allocation count
0
> bytes processed
1603424256
> bytes
> extra bytes read/written
6708224
> bytes
> cache hit percentage
99.58
> percent
>
> d) What is remarkable that auto workareas are now on 0, the manual
stuff
> might be by another testuser, the total pga_allocated is just a little bit
> higher.
>
>  I am completely confused by now and I hope you can shed some light on
this.
>
> Rega

Re: PGA Size

[ManojKr Jha]

select * from v$process where addr in (select paddr from v$session where
sid='<>');


With Regards,
Manoj Kumar Jha



A transcendentalist engaged in auspicious activities does not meet with
destruction either in this world or in the spiritual world; one who does
good,
is never overcome by evil.



   

"Sultan Syed"  

<[EMAIL PROTECTED]To: Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>   
e>   cc:   

Sent by: Subject: PGA Size 

[EMAIL PROTECTED]  

city.com   

   

   

10/07/03   

01:19 PM   

Please 

respond to 

ORACLE-L   

   

   





Hi,
How can I know the PGA size which my session is utilizing?
Thanks in advance.
Syed





DISCLAIMER: The information contained in this message is intended only and
solely for the addressed individual or entity indicated in this message and
for the exclusive use of the said addressed individual or entity indicated
in this message (or responsible for delivery of the message to such person)
and may contain legally privileged and confidential information belonging
to Tata Consultancy Services. It must not be printed, read, copied,
disclosed, forwarded, distributed or used (in whatsoever manner) by any
person other than the addressee. Unauthorized use, disclosure or copying is
strictly prohibited and may constitute unlawful act and can possibly
attract legal action, civil and/or criminal. The contents of this message
need not necessarily reflect or endorse the views of Tata Consultancy
Services on any subject matter. Any action taken or omitted to be taken
based on this message is entirely at your risk and neither the originator
of this message nor Tata Consultancy Services takes any responsibility or
liability towards the same. Opinions, conclusions and any other information
contained in this message that do not relate to the official business of
Tata Consultancy Services shall be understood as neither given nor endorsed
by Tata Consultancy Services or any affiliate of Tata Consultancy Services.
If you have received this message in error, you should destroy this message
and may please notify the sender by e-mail. Thank you.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: ManojKr Jha
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: PGA Size

[Mudhalvan, Moovarkku]

Title: Message



Hi 
Syed
 
    Do you have Enterprise manager installed on your machine... 
What is the version. You can install Enterprise Version Client on your Desktop 
and connect your database so that you can see the PGA size and 
all.
    Let me know if u have doubt or 
difficulties
 
 

  
  -Original Message-From: Sultan Syed 
  [mailto:[EMAIL PROTECTED] Sent: Monday, October 06, 2003 11:49 
  PMTo: Multiple recipients of list ORACLE-LSubject: PGA 
  Size
  Hi,
  How can I know the PGA size which my session is 
  utilizing?
  Thanks in advance.
  Syed
   
   

RE: PGA

[Mark Leith]

http://www.quest-pipelines.com/newsletter-v3/0302_F.htm (Gaja Krishna
Vaidyanatha)
www.jlcomp.demon.co.uk/myths.html (Jonathan Lewis)
http://www.orapub.com/cgi/genesis.cgi?p1=sub&p2=abs119 (Cary Milsap)


-Original Message-
Foote
Sent: 01 April 2003 14:34
To: Multiple recipients of list ORACLE-L


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, April 01, 2003 4:03 PM


> where can one find the other myths about oracle?
>
> Venkat
> --

A number of Oracle Press books ...

Cheers

Richard


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Richard Foote
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: Mark Leith
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: PGA

[Richard Foote]

- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, April 01, 2003 4:03 PM


> where can one find the other myths about oracle?
> 
> Venkat 
> --

A number of Oracle Press books ...

Cheers

Richard 
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Foote
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: PGA

[Ganesh Raja]

Get GAJA's Book .. Oracle 101 Perf. Tunning.. U will find a Lot there ..

HTH

Best Regards,
Ganesh R
DID : +65-6215-8413
HP  : +65-9067-8474 

-Original Message-
C.S.Venkata Subramanian
Sent: Tuesday, April 01, 2003 2:04 PM
To: Multiple recipients of list ORACLE-L


where can one find the other myths about oracle?

Venkat 
--

On Mon, 31 Mar 2003 06:18:35  
 Connor McDonald wrote:
>Oracle Myth #1745
>
>"When u set the sort_area_size for a database, it will allocate that 
>much memory PER USER for any sort operation being performed on the 
>database"
>
>
>
> --- "SARKAR, Samir"
><[EMAIL PROTECTED]> wrote: >
>Arvind,
>> 
>> When u set the sort_area_size for a database, it
>> will allocate that much
>> memory PER USER
>> for any sort operation being performed on the
>> database. In practice, it
>> means that if u allocate
>> too high a value for the sort_area_size and multiple
>> users are performing
>> multiple sorts, the Unix
>> system may run out of memory and Oracle will return
>> u a process memory
>> error.
>> 
>> For eg. if u have ur sort_area_size as 100MB and u
>> have 10 users performing
>> sort operations
>> on the database, Oracle will take up 100*10 MB
>> memory. This might degrade ur
>> system performance.
>> 
>> Samir
>> 
>> Samir Sarkar
>> Oracle DBA
>> SchlumbergerSema
>> Email  :  [EMAIL PROTECTED] 
>> Phone : +44 (0) 115 - 957 6028
>> EPABX : +44 (0) 115 - 957 6418 Ext. 76028
>> Fax : +44 (0) 115 - 957 6018
>> 
>> 
>> -Original Message-
>> Sent: 31 March 2003 11:04
>> To: Multiple recipients of list ORACLE-L
>> 
>> 
>> hello all,
>> 
>>  how does increasing the value of SORT_AREA_SIZE
>> affect the unix system
>> perfomance.
>> 
>> 
>> Thanks
>> Arvind
>> --
>> Please see the official ORACLE-L FAQ:
>> http://www.orafaq.net
>> -- 
>> Author: Arvind Kumar
>>   INET: [EMAIL PROTECTED]
>> 
>> 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: [EMAIL PROTECTED] (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).
>> 
>> 
>>
>_
>> This email is confidential and intended solely for
>> the use of the
>> individual to whom it is addressed. Any views or
>> opinions presented are 
>> solely those of the author and do not necessarily
>> represent those of 
>> SchlumbergerSema.
>> If you are not the intended recipient, be advised
>> that you have received
>> this email in error and that any use, dissemination,
>> forwarding, printing, 
>> or copying of this email is strictly prohibited.
>> 
>> If you have received this email in error please
>> notify the
>> SchlumbergerSema Helpdesk by telephone on +44 (0)
>> 121 627 5600.
>>
>_
>> 
>> --
>> Please see the official ORACLE-L FAQ:
>> http://www.orafaq.net
>> -- 
>> Author: SARKAR, Samir
>>   INET: [EMAIL PROTECTED]
>> 
>> 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: [EMAIL PROTECTED] (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).
>>  
>
>=
>Connor McDonald
>web: http://www.oracledba.co.uk
>web: http://www.oaktable.net
>email: [EMAIL PROTECTED]
>
>"GIVE a man a fish and he will eat for a day. But TEACH him how to 
>fish, and...he will sit in a boat and drink beer all day"
>
>__
>Yahoo! Plus
>For a better Internet experience http://www.yahoo.co.uk/btoffer
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: =?iso-8859-1?q?Connor=20McDonald?=
>  INET: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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).
>
>


_

RE: PGA

[C.S.Venkata Subramanian]

where can one find the other myths about oracle?

Venkat 
--

On Mon, 31 Mar 2003 06:18:35  
 Connor McDonald wrote:
>Oracle Myth #1745
>
>"When u set the sort_area_size for a database, it will
>allocate that much memory PER USER for any sort
>operation being performed on the database"
>
>
>
> --- "SARKAR, Samir"
><[EMAIL PROTECTED]> wrote: >
>Arvind,
>> 
>> When u set the sort_area_size for a database, it
>> will allocate that much
>> memory PER USER
>> for any sort operation being performed on the
>> database. In practice, it
>> means that if u allocate 
>> too high a value for the sort_area_size and multiple
>> users are performing
>> multiple sorts, the Unix
>> system may run out of memory and Oracle will return
>> u a process memory
>> error.
>> 
>> For eg. if u have ur sort_area_size as 100MB and u
>> have 10 users performing
>> sort operations 
>> on the database, Oracle will take up 100*10 MB
>> memory. This might degrade ur
>> system performance.
>> 
>> Samir
>> 
>> Samir Sarkar
>> Oracle DBA 
>> SchlumbergerSema
>> Email  :  [EMAIL PROTECTED] 
>> Phone : +44 (0) 115 - 957 6028
>> EPABX : +44 (0) 115 - 957 6418 Ext. 76028
>> Fax : +44 (0) 115 - 957 6018
>> 
>> 
>> -Original Message-
>> Sent: 31 March 2003 11:04
>> To: Multiple recipients of list ORACLE-L
>> 
>> 
>> hello all,
>> 
>>  how does increasing the value of SORT_AREA_SIZE
>> affect the unix system
>> perfomance.
>> 
>> 
>> Thanks
>> Arvind
>> -- 
>> Please see the official ORACLE-L FAQ:
>> http://www.orafaq.net
>> -- 
>> Author: Arvind Kumar
>>   INET: [EMAIL PROTECTED]
>> 
>> 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: [EMAIL PROTECTED] (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).
>> 
>> 
>>
>_
>> This email is confidential and intended solely for
>> the use of the 
>> individual to whom it is addressed. Any views or
>> opinions presented are 
>> solely those of the author and do not necessarily
>> represent those of 
>> SchlumbergerSema.
>> If you are not the intended recipient, be advised
>> that you have received
>> this email in error and that any use, dissemination,
>> forwarding, printing, 
>> or copying of this email is strictly prohibited.
>> 
>> If you have received this email in error please
>> notify the
>> SchlumbergerSema Helpdesk by telephone on +44 (0)
>> 121 627 5600.
>>
>_
>> 
>> -- 
>> Please see the official ORACLE-L FAQ:
>> http://www.orafaq.net
>> -- 
>> Author: SARKAR, Samir
>>   INET: [EMAIL PROTECTED]
>> 
>> 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: [EMAIL PROTECTED] (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).
>>  
>
>=
>Connor McDonald
>web: http://www.oracledba.co.uk
>web: http://www.oaktable.net
>email: [EMAIL PROTECTED]
>
>"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he 
>will sit in a boat and drink beer all day"
>
>__
>Yahoo! Plus
>For a better Internet experience
>http://www.yahoo.co.uk/btoffer
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: =?iso-8859-1?q?Connor=20McDonald?=
>  INET: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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).
>
>


_
Get 25MB, POP3, Spam Filtering with LYCOS MAIL PLUS for $19.95/year.
http://login.mail.lycos.com/brandPage.shtml?pageId=plus&ref=lmtplus
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: C.S.Venkata Subr

RE: PGA

[Goulet, Dick]

Pete,

Then send it privately: [EMAIL PROTECTED]  No I have not seen it, but I still 
believe it's more like giving a cat a bath.

Dick Goulet

-Original Message-
Sent: Monday, March 31, 2003 3:44 PM
To: Multiple recipients of list ORACLE-L


Did you ever see the EDS commercial on herding cats?  Damn, it was funny!  I
still have it on my laptop, but I won't send it to the list (over 1 Mb is a
bit much to send).

Pete
"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook
"Oh no, it's not.  It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA
 


-Original Message-
Sent: Monday, March 31, 2003 11:49 AM
To: Multiple recipients of list ORACLE-L


Pate,

Controlling duhvelopers is not like herding cats, it's more like
giving them a bath!!  Heavy body armor required!

Dick Goulet

-Original Message-
Sent: Monday, March 31, 2003 2:29 PM
To: Multiple recipients of list ORACLE-L


IIRC, even this release down to S_A_R_S stuff was a myth in earlier
releases.  The MALLOC call never really returned the memory to the OS.  Of
course, I don't have any earlier releases to prove it now!  :)

Pete
"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook
"Oh no, it's not.  It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA
 


-Original Message-
Sent: Monday, March 31, 2003 10:09 AM
To: Multiple recipients of list ORACLE-L


>From the Manual:

SORT_AREA_SIZE specifies in bytes the maximum amount of memory
Oracle will use for a sort. After   the sort is complete, but before the
rows are returned, Oracle releases memory down to the size  specified by
the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned,
Oracle  releases the remainder of the memory. 

Now since the memory is released you won't get a process error, and besides
on a Unix machine it will just trigger the swapper into action which slows
the entire machine down.  Now if you want to know if the swapper is active,
check out 'vmstat -s' and look for 'rotations of the clock hand'.  Then
check out the system's Uptime.  Divide 'rotations of the clock hand' by
uptime.  If it's greater then  or equal to 1, you've got a problem.

Dick Goulet
-Original Message-
Sent: Monday, March 31, 2003 10:59 AM
To: Multiple recipients of list ORACLE-L


If it is a myth where do we find the truth?

Allan

-Original Message-
Sent: Monday, March 31, 2003 8:19 AM
To: Multiple recipients of list ORACLE-L


Oracle Myth #1745

"When u set the sort_area_size for a database, it will
allocate that much memory PER USER for any sort
operation being performed on the database"



 --- "SARKAR, Samir"
<[EMAIL PROTECTED]> wrote: >
Arvind,
> 
> When u set the sort_area_size for a database, it
> will allocate that much
> memory PER USER
> for any sort operation being performed on the
> database. In practice, it
> means that if u allocate
> too high a value for the sort_area_size and multiple
> users are performing
> multiple sorts, the Unix
> system may run out of memory and Oracle will return
> u a process memory
> error.
> 
> For eg. if u have ur sort_area_size as 100MB and u
> have 10 users performing
> sort operations
> on the database, Oracle will take up 100*10 MB
> memory. This might degrade ur
> system performance.
> 
> Samir
> 
> Samir Sarkar
> Oracle DBA
> SchlumbergerSema
> Email  :  [EMAIL PROTECTED]
> Phone : +44 (0) 115 - 957 6028
> EPABX : +44 (0) 115 - 957 6418 Ext. 76028
> Fax : +44 (0) 115 - 957 6018
> 
> 
> -Original Message-
> Sent: 31 March 2003 11:04
> To: Multiple recipients of list ORACLE-L
> 
> 
> hello all,
> 
>  how does increasing the value of SORT_AREA_SIZE
> affect the unix system
> perfomance.
> 
> 
> Thanks
> Arvind
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Arvind Kumar
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
> 
> 
>
_
> This email is confidential and intended solely for
> the use of the
> individual to whom it is addressed. Any views or
> opinions presented are
> solely those of the author and do not necessarily
> represent those of
> SchlumbergerSema.
> If you are not the intended recipient, be advised
> that you have received
> this email in error and that any use, dissemination,
> forwarding, printing, 
> or copying of this email is s

RE: PGA

[Gogala, Mladen]

S_A_R_S stuff? Please, be careful with those abbreviations.

-Original Message-
Sent: Monday, March 31, 2003 2:29 PM
To: Multiple recipients of list ORACLE-L


IIRC, even this release down to S_A_R_S stuff was a myth in earlier
releases.  The MALLOC call never really returned the memory to the OS.  Of
course, I don't have any earlier releases to prove it now!  :)

Pete
"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook
"Oh no, it's not.  It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA
 


-Original Message-
Sent: Monday, March 31, 2003 10:09 AM
To: Multiple recipients of list ORACLE-L


>From the Manual:

SORT_AREA_SIZE specifies in bytes the maximum amount of memory
Oracle will use for a sort. After   the sort is complete, but before the
rows are returned, Oracle releases memory down to the size  specified by
the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned,
Oracle  releases the remainder of the memory. 

Now since the memory is released you won't get a process error, and besides
on a Unix machine it will just trigger the swapper into action which slows
the entire machine down.  Now if you want to know if the swapper is active,
check out 'vmstat -s' and look for 'rotations of the clock hand'.  Then
check out the system's Uptime.  Divide 'rotations of the clock hand' by
uptime.  If it's greater then  or equal to 1, you've got a problem.

Dick Goulet
-Original Message-
Sent: Monday, March 31, 2003 10:59 AM
To: Multiple recipients of list ORACLE-L


If it is a myth where do we find the truth?

Allan

-Original Message-
Sent: Monday, March 31, 2003 8:19 AM
To: Multiple recipients of list ORACLE-L


Oracle Myth #1745

"When u set the sort_area_size for a database, it will
allocate that much memory PER USER for any sort
operation being performed on the database"



 --- "SARKAR, Samir"
<[EMAIL PROTECTED]> wrote: >
Arvind,
> 
> When u set the sort_area_size for a database, it
> will allocate that much
> memory PER USER
> for any sort operation being performed on the
> database. In practice, it
> means that if u allocate
> too high a value for the sort_area_size and multiple
> users are performing
> multiple sorts, the Unix
> system may run out of memory and Oracle will return
> u a process memory
> error.
> 
> For eg. if u have ur sort_area_size as 100MB and u
> have 10 users performing
> sort operations
> on the database, Oracle will take up 100*10 MB
> memory. This might degrade ur
> system performance.
> 
> Samir
> 
> Samir Sarkar
> Oracle DBA
> SchlumbergerSema
> Email  :  [EMAIL PROTECTED]
> Phone : +44 (0) 115 - 957 6028
> EPABX : +44 (0) 115 - 957 6418 Ext. 76028
> Fax : +44 (0) 115 - 957 6018
> 
> 
> -Original Message-
> Sent: 31 March 2003 11:04
> To: Multiple recipients of list ORACLE-L
> 
> 
> hello all,
> 
>  how does increasing the value of SORT_AREA_SIZE
> affect the unix system
> perfomance.
> 
> 
> Thanks
> Arvind
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Arvind Kumar
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
> 
> 
>
_
> This email is confidential and intended solely for
> the use of the
> individual to whom it is addressed. Any views or
> opinions presented are
> solely those of the author and do not necessarily
> represent those of 
> SchlumbergerSema.
> If you are not the intended recipient, be advised
> that you have received
> this email in error and that any use, dissemination,
> forwarding, printing, 
> or copying of this email is strictly prohibited.
> 
> If you have received this email in error please
> notify the
> SchlumbergerSema Helpdesk by telephone on +44 (0)
> 121 627 5600.
>
_
> 
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: SARKAR, Samir
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailin

RE: PGA

[Pete Sharman]

Did you ever see the EDS commercial on herding cats?  Damn, it was funny!  I
still have it on my laptop, but I won't send it to the list (over 1 Mb is a
bit much to send).

Pete
"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook
"Oh no, it's not.  It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA
 


-Original Message-
Sent: Monday, March 31, 2003 11:49 AM
To: Multiple recipients of list ORACLE-L


Pate,

Controlling duhvelopers is not like herding cats, it's more like
giving them a bath!!  Heavy body armor required!

Dick Goulet

-Original Message-
Sent: Monday, March 31, 2003 2:29 PM
To: Multiple recipients of list ORACLE-L


IIRC, even this release down to S_A_R_S stuff was a myth in earlier
releases.  The MALLOC call never really returned the memory to the OS.  Of
course, I don't have any earlier releases to prove it now!  :)

Pete
"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook
"Oh no, it's not.  It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA
 


-Original Message-
Sent: Monday, March 31, 2003 10:09 AM
To: Multiple recipients of list ORACLE-L


>From the Manual:

SORT_AREA_SIZE specifies in bytes the maximum amount of memory
Oracle will use for a sort. After   the sort is complete, but before the
rows are returned, Oracle releases memory down to the size  specified by
the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned,
Oracle  releases the remainder of the memory. 

Now since the memory is released you won't get a process error, and besides
on a Unix machine it will just trigger the swapper into action which slows
the entire machine down.  Now if you want to know if the swapper is active,
check out 'vmstat -s' and look for 'rotations of the clock hand'.  Then
check out the system's Uptime.  Divide 'rotations of the clock hand' by
uptime.  If it's greater then  or equal to 1, you've got a problem.

Dick Goulet
-Original Message-
Sent: Monday, March 31, 2003 10:59 AM
To: Multiple recipients of list ORACLE-L


If it is a myth where do we find the truth?

Allan

-Original Message-
Sent: Monday, March 31, 2003 8:19 AM
To: Multiple recipients of list ORACLE-L


Oracle Myth #1745

"When u set the sort_area_size for a database, it will
allocate that much memory PER USER for any sort
operation being performed on the database"



 --- "SARKAR, Samir"
<[EMAIL PROTECTED]> wrote: >
Arvind,
> 
> When u set the sort_area_size for a database, it
> will allocate that much
> memory PER USER
> for any sort operation being performed on the
> database. In practice, it
> means that if u allocate
> too high a value for the sort_area_size and multiple
> users are performing
> multiple sorts, the Unix
> system may run out of memory and Oracle will return
> u a process memory
> error.
> 
> For eg. if u have ur sort_area_size as 100MB and u
> have 10 users performing
> sort operations
> on the database, Oracle will take up 100*10 MB
> memory. This might degrade ur
> system performance.
> 
> Samir
> 
> Samir Sarkar
> Oracle DBA
> SchlumbergerSema
> Email  :  [EMAIL PROTECTED]
> Phone : +44 (0) 115 - 957 6028
> EPABX : +44 (0) 115 - 957 6418 Ext. 76028
> Fax : +44 (0) 115 - 957 6018
> 
> 
> -Original Message-
> Sent: 31 March 2003 11:04
> To: Multiple recipients of list ORACLE-L
> 
> 
> hello all,
> 
>  how does increasing the value of SORT_AREA_SIZE
> affect the unix system
> perfomance.
> 
> 
> Thanks
> Arvind
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Arvind Kumar
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
> 
> 
>
_
> This email is confidential and intended solely for
> the use of the
> individual to whom it is addressed. Any views or
> opinions presented are
> solely those of the author and do not necessarily
> represent those of
> SchlumbergerSema.
> If you are not the intended recipient, be advised
> that you have received
> this email in error and that any use, dissemination,
> forwarding, printing, 
> or copying of this email is strictly prohibited.
> 
> If you have received this email in error please
> notify the
> SchlumbergerSema Helpdesk by telephone on +44 (0)
> 121 627 5600.
>
_
> 
> --
> Please see the official ORACLE-L FA

RE: PGA

[Goulet, Dick]

Pate,

Controlling duhvelopers is not like herding cats, it's more like giving them a 
bath!!  Heavy body armor required!

Dick Goulet

-Original Message-
Sent: Monday, March 31, 2003 2:29 PM
To: Multiple recipients of list ORACLE-L


IIRC, even this release down to S_A_R_S stuff was a myth in earlier
releases.  The MALLOC call never really returned the memory to the OS.  Of
course, I don't have any earlier releases to prove it now!  :)

Pete
"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook
"Oh no, it's not.  It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA
 


-Original Message-
Sent: Monday, March 31, 2003 10:09 AM
To: Multiple recipients of list ORACLE-L


>From the Manual:

SORT_AREA_SIZE specifies in bytes the maximum amount of memory
Oracle will use for a sort. After   the sort is complete, but before the
rows are returned, Oracle releases memory down to the size  specified by
the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned,
Oracle  releases the remainder of the memory. 

Now since the memory is released you won't get a process error, and besides
on a Unix machine it will just trigger the swapper into action which slows
the entire machine down.  Now if you want to know if the swapper is active,
check out 'vmstat -s' and look for 'rotations of the clock hand'.  Then
check out the system's Uptime.  Divide 'rotations of the clock hand' by
uptime.  If it's greater then  or equal to 1, you've got a problem.

Dick Goulet
-Original Message-
Sent: Monday, March 31, 2003 10:59 AM
To: Multiple recipients of list ORACLE-L


If it is a myth where do we find the truth?

Allan

-Original Message-
Sent: Monday, March 31, 2003 8:19 AM
To: Multiple recipients of list ORACLE-L


Oracle Myth #1745

"When u set the sort_area_size for a database, it will
allocate that much memory PER USER for any sort
operation being performed on the database"



 --- "SARKAR, Samir"
<[EMAIL PROTECTED]> wrote: >
Arvind,
> 
> When u set the sort_area_size for a database, it
> will allocate that much
> memory PER USER
> for any sort operation being performed on the
> database. In practice, it
> means that if u allocate
> too high a value for the sort_area_size and multiple
> users are performing
> multiple sorts, the Unix
> system may run out of memory and Oracle will return
> u a process memory
> error.
> 
> For eg. if u have ur sort_area_size as 100MB and u
> have 10 users performing
> sort operations
> on the database, Oracle will take up 100*10 MB
> memory. This might degrade ur
> system performance.
> 
> Samir
> 
> Samir Sarkar
> Oracle DBA
> SchlumbergerSema
> Email  :  [EMAIL PROTECTED]
> Phone : +44 (0) 115 - 957 6028
> EPABX : +44 (0) 115 - 957 6418 Ext. 76028
> Fax : +44 (0) 115 - 957 6018
> 
> 
> -Original Message-
> Sent: 31 March 2003 11:04
> To: Multiple recipients of list ORACLE-L
> 
> 
> hello all,
> 
>  how does increasing the value of SORT_AREA_SIZE
> affect the unix system
> perfomance.
> 
> 
> Thanks
> Arvind
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Arvind Kumar
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
> 
> 
>
_
> This email is confidential and intended solely for
> the use of the
> individual to whom it is addressed. Any views or
> opinions presented are
> solely those of the author and do not necessarily
> represent those of 
> SchlumbergerSema.
> If you are not the intended recipient, be advised
> that you have received
> this email in error and that any use, dissemination,
> forwarding, printing, 
> or copying of this email is strictly prohibited.
> 
> If you have received this email in error please
> notify the
> SchlumbergerSema Helpdesk by telephone on +44 (0)
> 121 627 5600.
>
_
> 
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: SARKAR, Samir
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the mes

RE: PGA

[Pete Sharman]

IIRC, even this release down to S_A_R_S stuff was a myth in earlier
releases.  The MALLOC call never really returned the memory to the OS.  Of
course, I don't have any earlier releases to prove it now!  :)

Pete
"Controlling developers is like herding cats."
Kevin Loney, Oracle DBA Handbook
"Oh no, it's not.  It's much harder than that!"
Bruce Pihlamae, long-term Oracle DBA
 


-Original Message-
Sent: Monday, March 31, 2003 10:09 AM
To: Multiple recipients of list ORACLE-L


>From the Manual:

SORT_AREA_SIZE specifies in bytes the maximum amount of memory
Oracle will use for a sort. After   the sort is complete, but before the
rows are returned, Oracle releases memory down to the size  specified by
the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned,
Oracle  releases the remainder of the memory. 

Now since the memory is released you won't get a process error, and besides
on a Unix machine it will just trigger the swapper into action which slows
the entire machine down.  Now if you want to know if the swapper is active,
check out 'vmstat -s' and look for 'rotations of the clock hand'.  Then
check out the system's Uptime.  Divide 'rotations of the clock hand' by
uptime.  If it's greater then  or equal to 1, you've got a problem.

Dick Goulet
-Original Message-
Sent: Monday, March 31, 2003 10:59 AM
To: Multiple recipients of list ORACLE-L


If it is a myth where do we find the truth?

Allan

-Original Message-
Sent: Monday, March 31, 2003 8:19 AM
To: Multiple recipients of list ORACLE-L


Oracle Myth #1745

"When u set the sort_area_size for a database, it will
allocate that much memory PER USER for any sort
operation being performed on the database"



 --- "SARKAR, Samir"
<[EMAIL PROTECTED]> wrote: >
Arvind,
> 
> When u set the sort_area_size for a database, it
> will allocate that much
> memory PER USER
> for any sort operation being performed on the
> database. In practice, it
> means that if u allocate
> too high a value for the sort_area_size and multiple
> users are performing
> multiple sorts, the Unix
> system may run out of memory and Oracle will return
> u a process memory
> error.
> 
> For eg. if u have ur sort_area_size as 100MB and u
> have 10 users performing
> sort operations
> on the database, Oracle will take up 100*10 MB
> memory. This might degrade ur
> system performance.
> 
> Samir
> 
> Samir Sarkar
> Oracle DBA
> SchlumbergerSema
> Email  :  [EMAIL PROTECTED]
> Phone : +44 (0) 115 - 957 6028
> EPABX : +44 (0) 115 - 957 6418 Ext. 76028
> Fax : +44 (0) 115 - 957 6018
> 
> 
> -Original Message-
> Sent: 31 March 2003 11:04
> To: Multiple recipients of list ORACLE-L
> 
> 
> hello all,
> 
>  how does increasing the value of SORT_AREA_SIZE
> affect the unix system
> perfomance.
> 
> 
> Thanks
> Arvind
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Arvind Kumar
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
> 
> 
>
_
> This email is confidential and intended solely for
> the use of the
> individual to whom it is addressed. Any views or
> opinions presented are
> solely those of the author and do not necessarily
> represent those of 
> SchlumbergerSema.
> If you are not the intended recipient, be advised
> that you have received
> this email in error and that any use, dissemination,
> forwarding, printing, 
> or copying of this email is strictly prohibited.
> 
> If you have received this email in error please
> notify the
> SchlumbergerSema Helpdesk by telephone on +44 (0)
> 121 627 5600.
>
_
> 
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: SARKAR, Samir
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
>  

=
Connor McDonald
web: http://www.oracledb

RE: PGA

[Goulet, Dick]

>From the Manual:

SORT_AREA_SIZE specifies in bytes the maximum amount of memory Oracle will use 
for a sort. Afterthe sort is complete, but before the rows are returned, 
Oracle releases memory down to the size specified by the 
SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle  
releases the remainder of the memory. 

Now since the memory is released you won't get a process error, and besides on a Unix 
machine it will just trigger the swapper into action which slows the entire machine 
down.  Now if you want to know if the swapper is active, check out 'vmstat -s' and 
look for 'rotations of the clock hand'.  Then check out the system's Uptime.  Divide 
'rotations of the clock hand' by uptime.  If it's greater then  or equal to 1, you've 
got a problem.

Dick Goulet
-Original Message-
Sent: Monday, March 31, 2003 10:59 AM
To: Multiple recipients of list ORACLE-L


If it is a myth where do we find the truth?

Allan

-Original Message-
Sent: Monday, March 31, 2003 8:19 AM
To: Multiple recipients of list ORACLE-L


Oracle Myth #1745

"When u set the sort_area_size for a database, it will
allocate that much memory PER USER for any sort
operation being performed on the database"



 --- "SARKAR, Samir"
<[EMAIL PROTECTED]> wrote: >
Arvind,
> 
> When u set the sort_area_size for a database, it
> will allocate that much
> memory PER USER
> for any sort operation being performed on the
> database. In practice, it
> means that if u allocate
> too high a value for the sort_area_size and multiple
> users are performing
> multiple sorts, the Unix
> system may run out of memory and Oracle will return
> u a process memory
> error.
> 
> For eg. if u have ur sort_area_size as 100MB and u
> have 10 users performing
> sort operations
> on the database, Oracle will take up 100*10 MB
> memory. This might degrade ur
> system performance.
> 
> Samir
> 
> Samir Sarkar
> Oracle DBA
> SchlumbergerSema
> Email  :  [EMAIL PROTECTED] 
> Phone : +44 (0) 115 - 957 6028
> EPABX : +44 (0) 115 - 957 6418 Ext. 76028
> Fax : +44 (0) 115 - 957 6018
> 
> 
> -Original Message-
> Sent: 31 March 2003 11:04
> To: Multiple recipients of list ORACLE-L
> 
> 
> hello all,
> 
>  how does increasing the value of SORT_AREA_SIZE
> affect the unix system
> perfomance.
> 
> 
> Thanks
> Arvind
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Arvind Kumar
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
> 
> 
>
_
> This email is confidential and intended solely for
> the use of the
> individual to whom it is addressed. Any views or
> opinions presented are 
> solely those of the author and do not necessarily
> represent those of 
> SchlumbergerSema.
> If you are not the intended recipient, be advised
> that you have received
> this email in error and that any use, dissemination,
> forwarding, printing, 
> or copying of this email is strictly prohibited.
> 
> If you have received this email in error please
> notify the
> SchlumbergerSema Helpdesk by telephone on +44 (0)
> 121 627 5600.
>
_
> 
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: SARKAR, Samir
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
>  

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

__
Yahoo! Plus
For a better Internet experience
http://www.yahoo.co.uk/btoffer
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

Fat City Netwo

RE: PGA

[Jared . Still]

>From MetaLink Note # 102339.1

For each session there will be a moment in time when the first sort 
has to be performed. At that moment, memory is allocated for performing 
that sort (sort area). The size of the sort area grows incrementally 
until sufficient memory has been allocated to perform the sort or until
it reaches the maximum as specified by SORT_AREA_SIZE. 


Jared





"Nelson, Allan" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 03/31/2003 07:58 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
        Subject:RE: PGA


If it is a myth where do we find the truth?

Allan

-Original Message-
Sent: Monday, March 31, 2003 8:19 AM
To: Multiple recipients of list ORACLE-L


Oracle Myth #1745

"When u set the sort_area_size for a database, it will
allocate that much memory PER USER for any sort
operation being performed on the database"



 --- "SARKAR, Samir"
<[EMAIL PROTECTED]> wrote: >
Arvind,
> 
> When u set the sort_area_size for a database, it
> will allocate that much
> memory PER USER
> for any sort operation being performed on the
> database. In practice, it
> means that if u allocate
> too high a value for the sort_area_size and multiple
> users are performing
> multiple sorts, the Unix
> system may run out of memory and Oracle will return
> u a process memory
> error.
> 
> For eg. if u have ur sort_area_size as 100MB and u
> have 10 users performing
> sort operations
> on the database, Oracle will take up 100*10 MB
> memory. This might degrade ur
> system performance.
> 
> Samir
> 
> Samir Sarkar
> Oracle DBA
> SchlumbergerSema
> Email  :  [EMAIL PROTECTED] 
> Phone : +44 (0) 115 - 957 6028
> EPABX : +44 (0) 115 - 957 6418 Ext. 76028
> Fax : +44 (0) 115 - 957 6018 
> 
> 
> -Original Message-
> Sent: 31 March 2003 11:04
> To: Multiple recipients of list ORACLE-L
> 
> 
> hello all,
> 
>  how does increasing the value of SORT_AREA_SIZE
> affect the unix system
> perfomance.
> 
> 
> Thanks
> Arvind
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Arvind Kumar
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
> 
> 
>
_
> This email is confidential and intended solely for
> the use of the
> individual to whom it is addressed. Any views or
> opinions presented are 
> solely those of the author and do not necessarily
> represent those of 
> SchlumbergerSema.
> If you are not the intended recipient, be advised
> that you have received
> this email in error and that any use, dissemination,
> forwarding, printing, 
> or copying of this email is strictly prohibited.
> 
> If you have received this email in error please
> notify the
> SchlumbergerSema Helpdesk by telephone on +44 (0)
> 121 627 5600.
>
_
> 
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: SARKAR, Samir
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
> 

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

__
Yahoo! Plus
For a better Internet experience
http://www.yahoo.co.uk/btoffer
-- 
Please see the official ORACLE-L FAQ: http://www.oraf

RE: PGA

[Nelson, Allan]

If it is a myth where do we find the truth?

Allan

-Original Message-
Sent: Monday, March 31, 2003 8:19 AM
To: Multiple recipients of list ORACLE-L


Oracle Myth #1745

"When u set the sort_area_size for a database, it will
allocate that much memory PER USER for any sort
operation being performed on the database"



 --- "SARKAR, Samir"
<[EMAIL PROTECTED]> wrote: >
Arvind,
> 
> When u set the sort_area_size for a database, it
> will allocate that much
> memory PER USER
> for any sort operation being performed on the
> database. In practice, it
> means that if u allocate
> too high a value for the sort_area_size and multiple
> users are performing
> multiple sorts, the Unix
> system may run out of memory and Oracle will return
> u a process memory
> error.
> 
> For eg. if u have ur sort_area_size as 100MB and u
> have 10 users performing
> sort operations
> on the database, Oracle will take up 100*10 MB
> memory. This might degrade ur
> system performance.
> 
> Samir
> 
> Samir Sarkar
> Oracle DBA
> SchlumbergerSema
> Email  :  [EMAIL PROTECTED] 
> Phone : +44 (0) 115 - 957 6028
> EPABX : +44 (0) 115 - 957 6418 Ext. 76028
> Fax : +44 (0) 115 - 957 6018
> 
> 
> -Original Message-
> Sent: 31 March 2003 11:04
> To: Multiple recipients of list ORACLE-L
> 
> 
> hello all,
> 
>  how does increasing the value of SORT_AREA_SIZE
> affect the unix system
> perfomance.
> 
> 
> Thanks
> Arvind
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Arvind Kumar
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
> 
> 
>
_
> This email is confidential and intended solely for
> the use of the
> individual to whom it is addressed. Any views or
> opinions presented are 
> solely those of the author and do not necessarily
> represent those of 
> SchlumbergerSema.
> If you are not the intended recipient, be advised
> that you have received
> this email in error and that any use, dissemination,
> forwarding, printing, 
> or copying of this email is strictly prohibited.
> 
> If you have received this email in error please
> notify the
> SchlumbergerSema Helpdesk by telephone on +44 (0)
> 121 627 5600.
>
_
> 
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: SARKAR, Samir
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
>  

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

__
Yahoo! Plus
For a better Internet experience
http://www.yahoo.co.uk/btoffer
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).



__
This email is intended solely for the person or entity to which it is addressed and 
may contain confidential and/or privileged information.  Copying, forwarding or 
distributing this message by persons or entities other than the addressee is 
prohibited. If you have received this email in error, please contact the sender 
immediately

RE: PGA

[Nelson, Allan]

Hello Arvind,

This is sort of an open ended question.  In general this parameter
controls how much memory is allocated on a per session basis for in
memory sorts.  Sorts that require more memory than this will sort on the
disk with a performance penalty.  If you have the memory you should stay
well north of 90% of your sorts in memory by bumping this parameter
appropriately.  The hitch comes when you consider the fact that an
increase in this parameter uses the incremental amount that you
increased sort area size and mulitplies it by the number of connected
sessions.  You can allocate enough memory this way to force your box to
page if you are close to the line anyway.  So you have a trade off.
More memory to sort area size can reduce disk sorts and so speed up your
process.  UNIX will be happy right up to the point where you have to
page or swap things to disk and at that point your system will crawl.

Allan

-Original Message-
Sent: Monday, March 31, 2003 4:04 AM
To: Multiple recipients of list ORACLE-L


hello all,

 how does increasing the value of SORT_AREA_SIZE affect the unix
system perfomance.


Thanks
Arvind
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arvind Kumar
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).



__
This email is intended solely for the person or entity to which it is addressed and 
may contain confidential and/or privileged information.  Copying, forwarding or 
distributing this message by persons or entities other than the addressee is 
prohibited. If you have received this email in error, please contact the sender 
immediately and delete the material from any computer.  This email may have been 
monitored for policy compliance.  [021216]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nelson, Allan
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: PGA

[SARKAR, Samir]

So whats the reality, Connor ?? I thought it was the reverse which was the
myth i.e. it 
is often misunderstood as the total memory available to the database user as
a whole for sort 
operations.I believe the reality is that the sort area size is the size
allocated by Oracle
per user process for sorting data.

Do enlighten me if I am wrong

Samir

Samir Sarkar
Oracle DBA 
SchlumbergerSema
Email  :  [EMAIL PROTECTED] 
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018


-Original Message-
Sent: 31 March 2003 15:19
To: Multiple recipients of list ORACLE-L


Oracle Myth #1745

"When u set the sort_area_size for a database, it will
allocate that much memory PER USER for any sort
operation being performed on the database"



 --- "SARKAR, Samir"
<[EMAIL PROTECTED]> wrote: >
Arvind,
> 
> When u set the sort_area_size for a database, it
> will allocate that much
> memory PER USER
> for any sort operation being performed on the
> database. In practice, it
> means that if u allocate 
> too high a value for the sort_area_size and multiple
> users are performing
> multiple sorts, the Unix
> system may run out of memory and Oracle will return
> u a process memory
> error.
> 
> For eg. if u have ur sort_area_size as 100MB and u
> have 10 users performing
> sort operations 
> on the database, Oracle will take up 100*10 MB
> memory. This might degrade ur
> system performance.
> 
> Samir
> 
> Samir Sarkar
> Oracle DBA 
> SchlumbergerSema
> Email  :  [EMAIL PROTECTED] 
> Phone : +44 (0) 115 - 957 6028
> EPABX : +44 (0) 115 - 957 6418 Ext. 76028
> Fax : +44 (0) 115 - 957 6018
> 
> 
> -Original Message-
> Sent: 31 March 2003 11:04
> To: Multiple recipients of list ORACLE-L
> 
> 
> hello all,
> 
>  how does increasing the value of SORT_AREA_SIZE
> affect the unix system
> perfomance.
> 
> 
> Thanks
> Arvind
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Arvind Kumar
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
> 
> 
>
_
> This email is confidential and intended solely for
> the use of the 
> individual to whom it is addressed. Any views or
> opinions presented are 
> solely those of the author and do not necessarily
> represent those of 
> SchlumbergerSema.
> If you are not the intended recipient, be advised
> that you have received
> this email in error and that any use, dissemination,
> forwarding, printing, 
> or copying of this email is strictly prohibited.
> 
> If you have received this email in error please
> notify the
> SchlumbergerSema Helpdesk by telephone on +44 (0)
> 121 627 5600.
>
_
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: SARKAR, Samir
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
>  

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

__
Yahoo! Plus
For a better Internet experience
http://www.yahoo.co.uk/btoffer
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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

RE: PGA

[Connor McDonald]

Oracle Myth #1745

"When u set the sort_area_size for a database, it will
allocate that much memory PER USER for any sort
operation being performed on the database"



 --- "SARKAR, Samir"
<[EMAIL PROTECTED]> wrote: >
Arvind,
> 
> When u set the sort_area_size for a database, it
> will allocate that much
> memory PER USER
> for any sort operation being performed on the
> database. In practice, it
> means that if u allocate 
> too high a value for the sort_area_size and multiple
> users are performing
> multiple sorts, the Unix
> system may run out of memory and Oracle will return
> u a process memory
> error.
> 
> For eg. if u have ur sort_area_size as 100MB and u
> have 10 users performing
> sort operations 
> on the database, Oracle will take up 100*10 MB
> memory. This might degrade ur
> system performance.
> 
> Samir
> 
> Samir Sarkar
> Oracle DBA 
> SchlumbergerSema
> Email  :  [EMAIL PROTECTED] 
> Phone : +44 (0) 115 - 957 6028
> EPABX : +44 (0) 115 - 957 6418 Ext. 76028
> Fax : +44 (0) 115 - 957 6018
> 
> 
> -Original Message-
> Sent: 31 March 2003 11:04
> To: Multiple recipients of list ORACLE-L
> 
> 
> hello all,
> 
>  how does increasing the value of SORT_AREA_SIZE
> affect the unix system
> perfomance.
> 
> 
> Thanks
> Arvind
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: Arvind Kumar
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
> 
> 
>
_
> This email is confidential and intended solely for
> the use of the 
> individual to whom it is addressed. Any views or
> opinions presented are 
> solely those of the author and do not necessarily
> represent those of 
> SchlumbergerSema.
> If you are not the intended recipient, be advised
> that you have received
> this email in error and that any use, dissemination,
> forwarding, printing, 
> or copying of this email is strictly prohibited.
> 
> If you have received this email in error please
> notify the
> SchlumbergerSema Helpdesk by telephone on +44 (0)
> 121 627 5600.
>
_
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> -- 
> Author: SARKAR, Samir
>   INET: [EMAIL PROTECTED]
> 
> 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: [EMAIL PROTECTED] (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).
>  

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will 
sit in a boat and drink beer all day"

__
Yahoo! Plus
For a better Internet experience
http://www.yahoo.co.uk/btoffer
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: PGA

[Ganesh Raja]

Your PGA directly Affects the Amt memory the OS has to shell out for
Oracle Server Process so Increasing the Sort_Area_Size does not have
immd effect but if your users are going to do a sort then your PGA can
grow to a Maximum of Sort_Area_Size before being pulled down to the Temp
Segments.

Just make sure u size it appropriatley.

HTH

Best Regards,
Ganesh R
DID : +65-6215-8413
HP  : +65-9067-8474 

-Original Message-
Kumar
Sent: Monday, March 31, 2003 6:04 PM
To: Multiple recipients of list ORACLE-L


hello all,

 how does increasing the value of SORT_AREA_SIZE affect the unix
system perfomance.


Thanks
Arvind
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arvind Kumar
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: Ganesh Raja
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: PGA

[SARKAR, Samir]

Arvind,

When u set the sort_area_size for a database, it will allocate that much
memory PER USER
for any sort operation being performed on the database. In practice, it
means that if u allocate 
too high a value for the sort_area_size and multiple users are performing
multiple sorts, the Unix
system may run out of memory and Oracle will return u a process memory
error.

For eg. if u have ur sort_area_size as 100MB and u have 10 users performing
sort operations 
on the database, Oracle will take up 100*10 MB memory. This might degrade ur
system performance.

Samir

Samir Sarkar
Oracle DBA 
SchlumbergerSema
Email  :  [EMAIL PROTECTED] 
Phone : +44 (0) 115 - 957 6028
EPABX : +44 (0) 115 - 957 6418 Ext. 76028
Fax : +44 (0) 115 - 957 6018


-Original Message-
Sent: 31 March 2003 11:04
To: Multiple recipients of list ORACLE-L


hello all,

 how does increasing the value of SORT_AREA_SIZE affect the unix system
perfomance.


Thanks
Arvind
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arvind Kumar
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).


_
This email is confidential and intended solely for the use of the 
individual to whom it is addressed. Any views or opinions presented are 
solely those of the author and do not necessarily represent those of 
SchlumbergerSema.
If you are not the intended recipient, be advised that you have received
this email in error and that any use, dissemination, forwarding, printing, 
or copying of this email is strictly prohibited.

If you have received this email in error please notify the
SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.
_

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: SARKAR, Samir
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Re: PGA question

[Connor McDonald]

Presumably they affect some of the required memory
structures - shouldn't be too hard to work out I would
have thought.  Just measure the pga usage via sesstat
and play with the parameters to see the impact

hth
connor

 --- "Koivu, Lisa" <[EMAIL PROTECTED]> wrote: 
-
PGA question
Hi everyone, 

I'm looking through the documentation regarding the
PGA.  It doesn't say specifically how the following
parms affect the size of the PGA, just that it does:

OPEN_LINKS
DB_FILES
LOG_FILES - Obsolete w/8i

On Metalink they just say "this is proprietary".  I'm
curious - does anyone know? I'm non-MTS, 8.1.7 W2K and
AIX (kinda - I can at least look at it & compare...)

db_files is defaulting to 4000 on w2k and it's set to
1021 on the AIX instances for some reason.  

Thanks

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA  33063
Office: 954-935-4117  
Fax:954-935-3639
Cell:954-683-4459

"The sender believes that this E-Mail and any
attachments were free of any virus, worm, Trojan
horse, and/or malicious code when sent. This message
and its attachments could have been infected during
transmission.  By reading the message and opening any
attachments, the recipient accepts full responsibility
for taking proactive and remedial action about viruses
and other defects. The sender's business entity is not
liable for any loss or damage arising in any way from
this message or its attachments."
--Please see the official ORACLE-L FAQ:
http://www.orafaq.net--Author: Koivu, Lisa  INET:
[EMAIL PROTECTED] City Network Services   
-- 858-538-5051 http://www.fatcity.comSan Diego,
California-- Mailing list and web hosting
services-To
REMOVE yourself from this mailing list, send an E-Mail
messageto: [EMAIL PROTECTED] (note EXACT spelling
of 'ListGuru') and inthe message BODY, include a line
containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from).  You mayalso send the
HELP command for other information (like subscribing). 

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will 
sit in a boat and drink beer all day"

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).