Re: pga workarea and ora-04030
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_2exec 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
Re: pga workarea and ora-04030
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_2exec 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
RE: pga workarea and ora-04030
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_2exec 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_2exec testarray( 1 ); Exception raised insert i= 17613935 Running with a second table involved: after 17 minutes 29 seconds 22:40:20 VU_2exec 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
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_2exec testarray( 1 );
Exception raised insert i= 17613935
Running with a second table involved: after 17 minutes 29 seconds
22:40:20 VU_2exec 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
Re: pga workarea and ora-04030
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_2exec testarray( 1 );
Exception raised insert i= 17613935
Running with a second table involved: after 17 minutes 29 seconds
22:40:20 VU_2exec 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
Re: pga workarea and ora-04030
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
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, 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). winmail.dat
Re: pga workarea and ora-04030
:= '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
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_2exec testarray( 1 );
Exception raised insert i= 17613935
Running with a second table involved: after 17 minutes 29 seconds
22:40:20 VU_2exec 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
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
OPTYPEACTTIME
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 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. Regards, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED]
Re: pga workarea and ora-04030
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 Size
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 Size
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
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). _ Get 25MB, POP3, Spam Filtering with LYCOS MAIL PLUS for $19.95/year. http://login.mail.lycos.com/brandPage.shtml?pageId=plusref=lmtplus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: C.S.Venkata
Re: PGA
- 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
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=subp2=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
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
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
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
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 also send the HELP command for other information (like subscribing).
RE: PGA
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
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 and delete the material from any computer. This email may have been monitored for policy compliance. [021216] --
RE: PGA
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.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
RE: PGA
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 Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
RE: PGA
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.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
RE: PGA
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 message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from).
RE: PGA
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 FAQ: http://www.orafaq.net -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network
RE: PGA
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 mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PGA
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 strictly prohibited. If you have received this email in error please notify the
RE: PGA
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=plusref=lmtplus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: C.S.Venkata Subramanian 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
Re: PGA question
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).
