pl/sql tables in pga and ora-4030 was pga_aggregate_target and a
I contined testing with pl/sql testprogram and found some interesting prove
about this 1 gb limit for pga with pat set.
All tests are done on hpux11.11 9.2.0.4
Testprogram
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;
Quotes from my last update to the tar:
When setting all manual
I see the pga going over 2Gb and the showing negative numbers by looking
at v$sessstat, os-level I only have top and like I mentioneed earlier you
see
that going up 2 Gb also and further to 4Gb not above this limit as
expected!!
Notice I am now testing with a hpux setting datasegment 4Gb (ulimit 4194303)
Test 1: workarea_size_policy=manual pat=0
After a few minutes running
NAME VALUE
--
session uga memory 81312
session uga memory max 112960
session pga memory 2132275152
session pga memory max 2132275152
Still monitoring this, the amount seem to stuck after 15 minutes or so at
this
4Gb (value of top)
end value:
NAME VALUE
--
session uga memory 81312
session uga memory max 112960
session pga memory -154903592
session pga memory max -154903592
After more then 30 minutes finally
it crashes agaIN
VU_2exec testarray( 1 );
begin testarray( 1 ); end;
*
ERROR at line 1:
ORA-06500: PL/SQL: storage error
ORA-06512: at VRIJ_UIT.TESTARRAY, line 14
ORA-06512: at line 1
U_2select pool, sum(bytes) from v$sgastat group by pool;
POOL SUM(BYTES)
--- --
large pool 218103808
shared pool 570425344
68143904
AME TYPE VALUE
---
--
pga_aggregate_target big integer 0
23:32:33 SQL show parameter workarea
It seems impossible that such a simple pl/sql can eat up 4Gb of memory.
Other
bugs like 3194895 and docid 3156574 are suggesting a 1Gb pga limit (which
might
be raised by changing data segment). There is mentioned a patch also
according
to 3194895 to lift this, can you find this patch and see if it might be
Text continued in next action...
23-JAN-04 22:40:10
Text continued from previous action...
appropriate?
Output from top
Memory: 3733508K (3051156K) real, 5720660K (4872688K) virtual, 70976K free
Page# 1/14
CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU
COMMAND
0 ? 23640 oracle 128 20 4116M 2436M sleep 10:00 2.54 2.53
oracleVU_2
New info : Test 2: workarea_size_policy=auto pat=200M same shared_pool of
500m
I am utterly convinced this is all done in pga outside shared pool so
enlarging this only gets me the same problem sooner
Ahh and now after already 2.5 minutes I get a similar problem
at the 1Gb limit
U_2exec testarray( 1 );
begin testarray( 1 ); end;
*
ERROR at line 1:
ORA-06500: PL/SQL: storage error
ORA-06512: at VRIJ_UIT.TESTARRAY, line 14
ORA-06512: at line 1
Elapsed: 00:02:32.62
23:42:57 SQL /
NAME VALUE
--
session uga memory 78464
session uga memory max 143872
session pga memory 1071096624
session pga memory max 1071096624
23:44:17 SQL /
NAME VALUE
--
session uga memory 78464
session uga memory max 143872
session pga memory 1071096624
session pga memory max 1071096624
23:45:08 SQL show parameter pga
NAME TYPE VALUE
---
--
pga_aggregate_target big integer 209715200
23:47:18 SQL show parameter workarea
NAME TYPE VALUE
---
--
workarea_size_policy string AUTO
So it looks workarea_size_policy is definitely limiting max pga available
but strange thing is that the 200Mb for pat is meant to be for sort_area and
we
are not using sort_area here just filling an array
I don't know why you don't get the same results but this is definitely weird
and looks familiar with other bugs filed
Regards,
Jeroen
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jeroen van Sluisdam
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
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
Both the PGA and PL/SQL tables are stored in the data (a.k.a. heap) section of process memory in the Oracle server process. If using Solaris, running the pmap utility against the Oracle server process is useful. I have a posted script named oramem.sh posted at http://www.EvDBT.com/tools.htm; which uses pmap to provide a good idea of how much virtual memory an Oracle instance is demanding. For each process, it segregates text and shm (i.e. shared memory) from stack and data (i.e. private process memory). A max() aggregation is performed on the shared memory and a sum() aggregation is performed on the private memory, and the sum is essentially what the Oracle instance is demanding in terms of virtual memory. Similar utilities exist for Linux and HP-UX... on 1/10/04 11:04 AM, Ryan at [EMAIL PROTECTED] wrote: 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
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
pga workarea and ora-04030
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 OPTYPE ACTTIME WA_SIZE EXP_SIZE ACT 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
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: any single serial session will never get more than 5% of pga
Notes in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 28, 2003 12:39 AM Hi Jonathan, I'm not sure what you really think about this new feature! I view the feature as a positive step forward. Instead of a DBA having to guess an artificially low limit on the sort_area_size because (say) 1200 users might be connected to a machine with 4GB of memory, you now give Oracle a directive like: I have 1.5GB available for sort operations; please be as generous as you can when the demand for memory is low, and ration it carefully when the demand is high. In theory, this ensures that more processes get in-memory sorting because there is a known spare capacity - in practice, the algorithms and options for over-ride will, no doubt, evolve over time. Are you saying that Oracle is capable now of releasing the extra memory something it was not capable of before? Yes If yes, then what does it have to do with the work policy? Nothing - but since the O/S used to take care of the problem by paging out unused memory there was little point in fixing something which wasn't totally broken. On the other hand, if you are trying to operate a policy of maximising the amount of memory you give to a session, based on your estimates of expected data volume, it makes sense to use code that allows a session to de-allocate memory properly. I see this feature useful (not really) for a database application that hosts N concurrent sessions while the amount of available resources is capable of running only N / m sessions. Where m is any integer. In different words, it's the choice when we don't have the required resources to run the app efficiently without restriction to the performance and by using it, it will be able to torture any session that is asking for memory and give it enough guilt not to ask for it again and just try to get the job done by any means :) Now, if the techies on Redwood Shores could get the concepts of hungry and greedy into the code, perhaps we wouldn't have to do any more tuning ever again ;) Regards, Waleed -- 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: any single serial session will never get more than 5% of pga
Notes in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 11:39 PM To be honest I'm not sure why such a feature is available! I have not used it so I'm not really qualified enough to judge it. But in my opinion, a session asks for memory because it needs memory. So is it possible that a session is asking for memory that it does not really need and it can continue running without the requested memory? The answer could be yes, if the more memory means faster (like sorting) and the sort_area_size is too big to be satisfied for all sessions, in this situation the DBA is responsible for the wrong settings. But what if more memory is required like: memory tables, associative arrays, etc and memory was denied? Would the session fail? I think the answer would be YES - Did anybody try this? Memory for pl/sql objects falls outside the scope of the workarea policy If your memory demands for an associative array are excessive you can still grow your pga to extremes. Is the feature available because Oracle sessions don't deallocate the extra memory and by using this feature, it will encourage the sessions that already succeeded in allocating memory that they don't need any more by punishing the ones that ask for more memory now by saying NO? If the don't need the memory anymore they won't have it, because the code now ensures that the memory is releasee - so no question of punishing other sessions. Or is it going to ask the sessions that have extra allocated memory to release it which should be the normal behavior anyway without using any policies? Should as in 'you think this happens already' (it doesn't) or should as in 'the way the code ought to have been written in the first place' (it tried, but most unix libraries didn't implement the calls) ? The database code does need a policy for sorting (for example), otherwise there is no way to determine whether an operation should be allowed to acquire an arbitrarily large amount of memory to do a sort / merge join rather than doing a nested loop join. In the old days, the DBA produced a policy called the 'sort_area_size', which stopped the optimizer from doing an optimum job in a hybrid system, and depended on the operating system handling issues of over-allocation and idle memory. The issues of idle memory and over-allocation are now (largely) back with the database. Regards, Waleed -Original Message- Sent: Friday, December 26, 2003 5:39 PM To: Multiple recipients of list ORACLE-L pga_aggregate_target For special cases like that I would switch the session back to a manual workarea policy and set a suitable sort area. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 9:49 PM pga_aggregate_target Is there any way to give say 75% of pga_aggregate_target to a single session? The reason I am asking this is - sometimes we need to build an index as soon as possible and the index creating is the only thing running and other applications are stopped waiting for the index. Thanks, Roger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: any single serial session will never get more than 5% of pga
Hi Jonathan, I'm not sure what you really think about this new feature! Are you saying that Oracle is capable now of releasing the extra memory something it was not capable of before? If yes, then what does it have to do with the work policy? I see this feature useful (not really) for a database application that hosts N concurrent sessions while the amount of available resources is capable of running only N / m sessions. Where m is any integer. In different words, it's the choice when we don't have the required resources to run the app efficiently without restriction to the performance and by using it, it will be able to torture any session that is asking for memory and give it enough guilt not to ask for it again and just try to get the job done by any means :) Regards, Waleed -Original Message- Sent: Saturday, December 27, 2003 3:09 AM To: Multiple recipients of list ORACLE-L pga Notes in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 11:39 PM To be honest I'm not sure why such a feature is available! I have not used it so I'm not really qualified enough to judge it. But in my opinion, a session asks for memory because it needs memory. So is it possible that a session is asking for memory that it does not really need and it can continue running without the requested memory? The answer could be yes, if the more memory means faster (like sorting) and the sort_area_size is too big to be satisfied for all sessions, in this situation the DBA is responsible for the wrong settings. But what if more memory is required like: memory tables, associative arrays, etc and memory was denied? Would the session fail? I think the answer would be YES - Did anybody try this? Memory for pl/sql objects falls outside the scope of the workarea policy If your memory demands for an associative array are excessive you can still grow your pga to extremes. Is the feature available because Oracle sessions don't deallocate the extra memory and by using this feature, it will encourage the sessions that already succeeded in allocating memory that they don't need any more by punishing the ones that ask for more memory now by saying NO? If the don't need the memory anymore they won't have it, because the code now ensures that the memory is releasee - so no question of punishing other sessions. Or is it going to ask the sessions that have extra allocated memory to release it which should be the normal behavior anyway without using any policies? Should as in 'you think this happens already' (it doesn't) or should as in 'the way the code ought to have been written in the first place' (it tried, but most unix libraries didn't implement the calls) ? The database code does need a policy for sorting (for example), otherwise there is no way to determine whether an operation should be allowed to acquire an arbitrarily large amount of memory to do a sort / merge join rather than doing a nested loop join. In the old days, the DBA produced a policy called the 'sort_area_size', which stopped the optimizer from doing an optimum job in a hybrid system, and depended on the operating system handling issues of over-allocation and idle memory. The issues of idle memory and over-allocation are now (largely) back with the database. Regards, Waleed -Original Message- Sent: Friday, December 26, 2003 5:39 PM To: Multiple recipients of list ORACLE-L pga_aggregate_target For special cases like that I would switch the session back to a manual workarea policy and set a suitable sort area. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 9:49 PM pga_aggregate_target Is there any way to give say 75% of pga_aggregate_target to a single session? The reason I am asking this is - sometimes we need to build an index as soon as possible and the index creating is the only thing running and other applications are stopped waiting for the index. Thanks, Roger -- Please see
RE: any single serial session will never get more than 5% of pga
To be honest I'm not sure why such a feature is available! I have not used it so I'm not really qualified enough to judge it. But in my opinion, a session asks for memory because it needs memory. So is it possible that a session is asking for memory that it does not really need and it can continue running without the requested memory? The answer could be yes, if the more memory means faster (like sorting) and the sort_area_size is too big to be satisfied for all sessions, in this situation the DBA is responsible for the wrong settings. But what if more memory is required like: memory tables, associative arrays, etc and memory was denied? Would the session fail? I think the answer would be YES - Did anybody try this? Is the feature available because Oracle sessions don't deallocate the extra memory and by using this feature, it will encourage the sessions that already succeeded in allocating memory that they don't need any more by punishing the ones that ask for more memory now by saying NO? Or is it going to ask the sessions that have extra allocated memory to release it which should be the normal behavior anyway without using any policies? Regards, Waleed -Original Message- Sent: Friday, December 26, 2003 5:39 PM To: Multiple recipients of list ORACLE-L pga_aggregate_target For special cases like that I would switch the session back to a manual workarea policy and set a suitable sort area. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 9:49 PM pga_aggregate_target Is there any way to give say 75% of pga_aggregate_target to a single session? The reason I am asking this is - sometimes we need to build an index as soon as possible and the index creating is the only thing running and other applications are stopped waiting for the index. Thanks, Roger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [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: Khedr, Waleed 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: any single serial session will never get more than 5% of pga
Waleed, Please feel free to determine the answers to those questions. :) Jared Khedr, Waleed [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/26/2003 03:39 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: any single serial session will never get more than 5% of pga To be honest I'm not sure why such a feature is available! I have not used it so I'm not really qualified enough to judge it. But in my opinion, a session asks for memory because it needs memory. So is it possible that a session is asking for memory that it does not really need and it can continue running without the requested memory? The answer could be yes, if the more memory means faster (like sorting) and the sort_area_size is too big to be satisfied for all sessions, in this situation the DBA is responsible for the wrong settings. But what if more memory is required like: memory tables, associative arrays, etc and memory was denied? Would the session fail? I think the answer would be YES - Did anybody try this? Is the feature available because Oracle sessions don't deallocate the extra memory and by using this feature, it will encourage the sessions that already succeeded in allocating memory that they don't need any more by punishing the ones that ask for more memory now by saying NO? Or is it going to ask the sessions that have extra allocated memory to release it which should be the normal behavior anyway without using any policies? Regards, Waleed -Original Message- Sent: Friday, December 26, 2003 5:39 PM To: Multiple recipients of list ORACLE-L pga_aggregate_target For special cases like that I would switch the session back to a manual workarea policy and set a suitable sort area. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 9:49 PM pga_aggregate_target Is there any way to give say 75% of pga_aggregate_target to a single session? The reason I am asking this is - sometimes we need to build an index as soon as possible and the index creating is the only thing running and other applications are stopped waiting for the index. Thanks, Roger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [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: Khedr, Waleed 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).
ora-4030 pga memory allocation running wild
Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen
RE: ora-4030 pga memory allocation running wild
This is scary, I'm planning to upgrade 9.2.0.4 from 9.2.0.2. I don't know how removing pga_aggegrate_target will help reducing memory!! Does the program have any memory tables, etc? Did you monitor the PGA size from the Oracle side using v$sesstat? A sql by itself can't consume this memory except there is a major bug some where, which I doubt! Please keep us updated. Thanks Waleed -Original Message-From: Jeroen van Sluisdam [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 23, 2003 10:24 AMTo: Multiple recipients of list ORACLE-LSubject: ora-4030 pga memory allocation running wild Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen
Re: ora-4030 pga memory allocation running wild
I'm using auto pga allocation on 9.2.0.3 without any problem. You don't mention which version. You can turn it off with 'alter system set workarea_size_policy=manual; Jared On Tue, 2003-12-23 at 07:24, Jeroen van Sluisdam wrote: Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: ora-4030 pga memory allocation running wild
Hi, I'm using oracle 9.2.0.4. I put it off tonight with the statement You mentioned and unfortunately no success. Maybe interesting to know that I started without the event 4030 set And I get the following ora-600 in my alert file: Tue Dec 23 16:46:42 2003 Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_15251.trc: ORA-00600: internal error code, arguments: [17271], [instantiation space leak], [], [], [], [], [], This one is reproducible without the event set and a pga_aggregate_target set either 250Mb or 160Mb With the event set I got the following error Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_10264.trc: ORA-04030: out of process memory when trying to allocate 2464 bytes (cursor work he,rworalo : rwordops) Tue Dec 23 14:24:40 2003 Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_10249.trc: ORA-00600: internal error code, arguments: [17271], [instantiation space leak], [], [], [], [], [], [] ORA-04030: out of process memory when trying to allocate 32 bytes (callheap,allocator state) This second tracefile lead me to the sql-statement which explained with a very nice result When I issued the statement to set off auto handling I did not get any such error in my alert file but my batch returned again after an hour With ERROR at line 1: ORA-04030: out of process memory when trying to allocate 56 bytes (callheap,PESBLT space) Could a UX kernel parameter be of any influence here, like max data segment? Could it help to increase this to say 3Gb. Note that we have 4Gb physical memory and 4Gb swap configured. I used to run this in an oracle 7 enviroment on hpux 10.20 and now we moved To 64bit hpux11.11. I can imagine oracle is using more memory here than compared to oracle 7 with the same program such that in the old environment we might stayed below 2Gb and now we are exceeding this. For what it might be worth, this batch is quite big. Sofar this seems to be the only program having memory problems. I have put back workare_size_policy=auto back to be on the safe default side. I hope you can give some more leads because this is quite confusing And causing me headaches because it is causing troubles in my production environment. By the way we tested the migration ofcourse but this batch was not included in the test. Regards, Jeroen -Oorspronkelijk bericht- Van: Jared Still [mailto:[EMAIL PROTECTED] Verzonden: dinsdag 23 december 2003 18:34 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: ora-4030 pga memory allocation running wild I'm using auto pga allocation on 9.2.0.3 without any problem. You don't mention which version. You can turn it off with 'alter system set workarea_size_policy=manual; Jared On Tue, 2003-12-23 at 07:24, Jeroen van Sluisdam wrote: Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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
SGA PGA
I can use ipcs -am to see the sga at os level. but I do not see any pga? Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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: SGA PGA
If your intention is to find the amount of memory used by an Oracle Process at the OS level, use pmap command in Solaris. -Ravi. --- Roger Xu [EMAIL PROTECTED] wrote: I can use ipcs -am to see the sga at os level. but I do not see any pga? Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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). __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ravi Kulkarni 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: SGA PGA
Well, for which process do you want to see PGA? Go to the /proc/$PID directory and look into the memory maps. prank The other way of looking into PGA would be interpreting process tables from /dev/kmem. If you know how to do that, you can do something like dd if=/dev/mem of=`tty` /prank On 10/16/2003 05:39:25 PM, Roger Xu wrote: I can use ipcs -am to see the sga at os level. but I do not see any pga? Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
PGA Size
Hi, How can I know the PGA size which my session is utilizing? Thanks in advance. Syed
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).
PGA
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).
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
PGA question
Title: 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] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PGA question
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).
RE: EXCESSIVE PGA MEMORY 9i
Title: RE: EXCESSIVE PGA MEMORY 9i Guys, Installed a database - currently doing an upgrade to 9.0.2.2 following instructs in README that says to open migrate then run catpatch.sql. The database is using excessive amounts of memory for process global area. I reduced pga_aggregate_target from Oracle's default setting to 10M - read something about using this stops the use of sort_area_size. Any pro/cons on using pga_aggregate_target, experiences with sizing of it. Thanks, Paula
What's in PGA ?
Hi, Oracle : 7.3.4.5.0 OS : AIX 4.3.3 I have a PL/SQL code of statistics (hours of execution). The server process created by Oracle take more and more memory over the time by looking at v$sesstat about 'session pga memory' and OS with 'ps'. I am quite sure it is a bug in our program, but my question is What is in PGA ? How can we know the size of each element with v$... views ? Thanks for your support. Cordialement, Frédéric Major DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?Q?Fr=E9d=E9ric_MAJOR?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: What's in PGA ?
Frédéric, This may be a long shot, but have you got PL/SQL tables in use? They extend forever in a heap unless you use DBMS_SESSION.FREE_UNUSED_US ER_MEMORY. My experience is that use of this call uses so much CPU it outweighs the advantages of using PL/SQL tables. Cheers, John Thomas Oracle development DBA In article [EMAIL PROTECTED], Frédéric MAJOR [EMAIL PROTECTED] writes Hi, Oracle : 7.3.4.5.0 OS : AIX 4.3.3 I have a PL/SQL code of statistics (hours of execution). The server process created by Oracle take more and more memory over the time by looking at v$sesstat about 'session pga memory' and OS with 'ps'. I am quite sure it is a bug in our program, but my question is What is in PGA ? How can we know the size of each element with v$... views ? Thanks for your support. Cordialement, Frédéric Major DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- John Thomas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Automatic PGA Memory Management via PGA_AGGREGATE_TARGET paramete
I just stumbled upon the Automatic PGA Memory Management section of the 9i docs. Ive never heard of this before. Does anyone know how long its been around? Anyone using it found any bugs, concerns, etc.? Granted, this is intended for *dedicated* server mode, it seems like a good option for our Test databases and perhaps other databases that have only a handful of users and dont require MTS. By setting PGA_AGGREGATE_TARGET=X, where X can be a value in K, M, or G, Oracle will automatically distribute this memory allocation to dedicated sessions instead of using SORT_AREA_SIZE. Heres an excerpt from the docs: Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility. Learn something new every day in Oracle land david David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide, Inc. 8000 Regency Parkway, Suite 110 Cary, NC 27511-8582 Tel. (919) 466-6723 Fax (919) 466-6783 Mobile (919) 225-4962 [EMAIL PROTECTED] http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender by phone or email and delete this e-mail message from your computer. Thank you.
RE: Automatic PGA Memory Management via PGA_AGGREGATE_TARGET para
I cover this topic and many others in my Oracle9i New Features book :-)) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 04, 2002 1:58 PM To: Multiple recipients of list ORACLE-L I just stumbled upon the Automatic PGA Memory Management section of the 9i docs. I've never heard of this before. Does anyone know how long it's been around? Anyone using it found any bugs, concerns, etc.? Granted, this is intended for *dedicated* server mode, it seems like a good option for our Test databases and perhaps other databases that have only a handful of users and don't require MTS. By setting PGA_AGGREGATE_TARGET=X, where X can be a value in K, M, or G, Oracle will automatically distribute this memory allocation to dedicated sessions instead of using SORT_AREA_SIZE. Here's an excerpt from the docs: Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility. Learn something new every day in Oracle land... david David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide, Inc. 8000 Regency Parkway, Suite 110 Cary, NC 27511-8582 Tel. (919) 466-6723 Fax (919) 466-6783 Mobile (919) 225-4962 [EMAIL PROTECTED] http://www.arsenaldigital.com/ http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender by phone or email and delete this e-mail message from your computer. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Automatic PGA Memory Management via PGA_AGGREGATE_TARGET para
I bet if you wowed us with a really good explanation of this new feature then some readers would be inclined to go check out your book :). david David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide, Inc. 8000 Regency Parkway, Suite 110 Cary, NC 27511-8582 Tel. (919) 466-6723 Fax (919) 466-6783 Mobile (919) 225-4962 [EMAIL PROTECTED] http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender by phone or email and delete this e-mail message from your computer. Thank you. -Original Message- Sent: Monday, March 04, 2002 2:53 PM To: Multiple recipients of list ORACLE-L I cover this topic and many others in my Oracle9i New Features book :-)) RF Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -Original Message- Sent: Monday, March 04, 2002 1:58 PM To: Multiple recipients of list ORACLE-L I just stumbled upon the Automatic PGA Memory Management section of the 9i docs. I've never heard of this before. Does anyone know how long it's been around? Anyone using it found any bugs, concerns, etc.? Granted, this is intended for *dedicated* server mode, it seems like a good option for our Test databases and perhaps other databases that have only a handful of users and don't require MTS. By setting PGA_AGGREGATE_TARGET=X, where X can be a value in K, M, or G, Oracle will automatically distribute this memory allocation to dedicated sessions instead of using SORT_AREA_SIZE. Here's an excerpt from the docs: Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility. Learn something new every day in Oracle land... david David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide, Inc. 8000 Regency Parkway, Suite 110 Cary, NC 27511-8582 Tel. (919) 466-6723 Fax (919) 466-6783 Mobile (919) 225-4962 [EMAIL PROTECTED] http://www.arsenaldigital.com/ http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender by phone or email and delete this e-mail message from your computer. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: David Wagoner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
weird pga swelling
I'm experiencing a weird issue I've never encountered before. One of my clients runs a moderately active database being connected to by a group of webservers running mod_perl, making a 'standard' set of queries to generate dynamic content. 'standard' in this sense means that the queries are unchanged for many months, none of them are particularly expensive. Randomly (once/twice week) I'm having shadow processes (LOCAL=NO sessions) whose PGA is welling to huge sizes. I am measuring this with pmem. I see things like: 3606: oracleFLPDT (LOCAL=NO) Address Kbytes Resident Shared Private Permissions Mapped File 0001 24776 19960 19960 - read/exec oracle 0185 240 240 208 32 read/write/exec oracle 0188C000 1083848 946408 - 946408 read/write/exec[ heap ] 8000 1143448 1143448 1143448 - read/write/exec/shared [shmid=0x2db5] That's 1G of private heap usage by that one process. These sessions are not stuck running any particular sql, I have caught them executing any and all of the standard application code and they don't seem to be spinning on any particular query. Any thoughts? This seems like a PGA memory leak to me. // George Schlossnagle // www.pythian.com -- [EMAIL PROTECTED] -- 877-PYTHIAN // Smarter than adding another team member, Pythian has new services // for supplementing DBAs: get our help with monitoring, 24x7 on-call, // daily verifications, storage management, performance and more.
RE: session pga memory value is much higher than Sort_Area_Size
I had the same problem. First, i realized that - it seems - there are other things that will bump the PGA. For me it was insert /*+ append */. However, after upgrading from 8.1.5 to 8.1.7 the problem went away. Hope this helps, and your mileage may vary. Good luck, Yosi -Original Message- From: Srikannan Gopalsamy [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 29, 2001 6:58 PM To: Multiple recipients of list ORACLE-L Subject: session pga memory value is much higher than Sort_Area_Size We've set sort_area_size to 8M, but the session pga memory usage shows much higher than 8M, in fact its about 176M. The heap value for the shadow process also confirms the size of the PGA area. 01A4 180496K read/write/exec [ heap ] I thought that the PGA size should not go beyond sort_area_size. Are we missing anything here? Thank for your help. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Srikannan Gopalsamy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
session pga memory value is much higher than Sort_Area_Size
We've set sort_area_size to 8M, but the session pga memory usage shows much higher than 8M, in fact its about 176M. The heap value for the shadow process also confirms the size of the PGA area. 01A4 180496K read/write/exec [ heap ] I thought that the PGA size should not go beyond sort_area_size. Are we missing anything here? Thank for your help. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Srikannan Gopalsamy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
