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
$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
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
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
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
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
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
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
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
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
;
-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
--
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
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
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
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
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
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
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
: 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
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
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
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
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
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
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
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
Hi,
How can I know the PGA size which my session is
utilizing?
Thanks in advance.
Syed
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
:
Sent by: Subject: PGA Size
[EMAIL PROTECTED
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
- 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.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
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
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
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
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
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
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
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
: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
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
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,
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
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
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
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
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,
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
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
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
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
'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
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
.
-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
.
-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
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
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
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
58 matches
Mail list logo
