| Queries on PGA Settings and related Memory Management [message #494087] |
Fri, 11 February 2011 05:23  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello
I have few queries on PGA memory management
Since these queries are based on 2-3 examples not exactly same by nature I am summarising it after my understanding for the same
As I understand many workareas can be allocated to a single sql statement
and number and sizes of theses workareas is controlled internally by Oracle
when Automatic Memory management (PGA_aggregate_target and workarea_size_policy=Auto are set)
Since many sessions share the PGA memory, the amount of memory available to each session may vary and if less amount of memory is available for a session for sorting then TEMP tablespace is used
[1] Can we say paging happens and can be checked at this time?
[2] Is there a difference in handling memory while populating pl/sql tables?
As I have encountered ora-04030 while some our developers were populating pl/sql tables but never encountered this error for sorting, hash joins etc
Though I don't remember the width of pl/sql table, I am sure the developer used 'LIMIT' clause during bulk collect and still faced the issue.
With a single session on the server, I noticed that the difference in values displayed issuing 'free' command in linux and output values from sesstat did not match at all
while there wasn't any heavy OS process involved during the period.
I was expecting 'used' and 'free' values displayed by free command (linux) will change and difference would be approximately equals 'before and after values of session pga memory.
[3] Isn't it expected to match?
[4] Can we say in dedicated server, at any moment of time, the SUM of 'session pga memory' represents all the memory used by Oracle SGA, at that point of time?
select sum(value)/1024/1024 "memory in MB" from v$sesstat where statistic#=20;
During one of the tests I got following output (divide value by 10 for my visibility and avoid formatting)
SQL> select a.name, to_char(b.value/10, '999,999,999') value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga memory%'; 2 3 4
NAME VALUE
---------------------------------------------------------------- ------------
session uga memory 14,563
session uga memory max 14,563
session pga memory -194,147,859
session pga memory max -194,147,859
The above query is showing above values even when the pl/sql block execution is completed 30 minutes back
[5] Do we call this as 'memory leak' where memory is not released even while some time has passed since session has done something?
Of course I am not checking at OS level as mentioned in question [3] above the values won't match!
Still the output of free command for reference
(After the pl/sql block executed)
SQL> !free
total used free shared buffers cached
Mem: 3016796 2999660 17136 0 4308 1173260
-/+ buffers/cache: 1822092 1194704
Swap: 1048568 636124 412444
--(After the pl/sql block executed)
SQL> select * from v$pgastat;
NAME VALUE UNIT
aggregate PGA target parameter 524288000 bytes
aggregate PGA auto target 456256512 bytes
global memory bound 26214400 bytes
total PGA inuse 17328128 bytes
total PGA allocated 2379083776 bytes
maximum PGA allocated 2379948032 bytes
total freeable PGA memory 65536 bytes
PGA memory freed back to OS 0 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
maximum PGA used for manual workareas 0 bytes
over allocation count 8
bytes processed 886784 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
[6] What could be the significance of negative values of 'session pga memory/max'?
Last
We have an OLTP system and in the night we run batch processes in 2-4 sessions
Suppose I have 10 GB RAM and with PGA setting of 3.5 GB
Now I want the batch process sessions to use max possible memory during nighttime and toggle the setting back in the morning
[7] With above settings (10 GB RAM and 3.5 GB PGA) how can I divide the memory among 4 sessions?
Shall I set 1) PGA_aggregate_target=0 2)Workarea_size_policy=manual 3) Sort_are_size 4) Hash_area_size
[8] What would be approx values for parameter 3 and 4? will it be straight 3.5 GB/ 4?
Thanks and Regards,
OraKaran
|
|
|
|
|
|
| Re: Queries on PGA Settings and related Memory Management [message #494135 is a reply to message #494087] |
Fri, 11 February 2011 08:21  |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Hello Michel
Thanks for your prompt reply (with answers one by one)
Your reply has helped me with some of my queries and for others where 'I am not clear' I am writing my replies below
I said
Quote:
Since many sessions share the PGA memory, the amount of memory available to each session may vary and if less amount of memory is available for a session for sorting then TEMP tablespace is used
suppose I have set pga_aggregate_target=100M
and there is one session it can have workareas of say 5% of 100M
But if there are many users, say 30, the users may not get workareas of say 5%. Definitely less than what they were getting while there was only 1 session.
On this line I was saying PGA is shared by users, if above content is correct.
[1] No. No relation between the 2.
Ok. Thanks
I was assuming when things can not be done in memory and then are written to disk and back to memory like memory for PGA to TEMP and back, is paging.
[2] PL/SQL table are out of the scope of PGA_aggregate_target setting
Ok. Thanks
[3] No. I don't see any relation between the two.
So isn't there any mechanism to countercheck memory used by oracle sessions (summed up) at OS level?
[4] No. SGA and PGA are different. SGA is a shared memory area. PGA is a private memory area.
I think my question wasn't clear
Total memory = memory used by OS and non-oracle processes + memory used by oracle (i.e. SGA + PGA)
If above is true, does the Total memory used by Oracle sessions for sorting + hashing + pl/sql variables + pl/sql collection types = select sum(value)/1024/1024 "memory in MB" from v$sesstat where statistic#=20 ?
OK. I shall read more on this
OK. I shall read more on this
[7] "with PGA setting of 3.5 GB" What do you mean by this sentence?
[8] What you want
For [7] and [8]
Say I have following setting in an OLTP database with 120 users
pga_aggegate_target = 3584M
In the evening, when these users logoff, we start batch process at say 21:30
I want batch process to use all the possible memory for sorting and large hash joins etc.? How to do that?
Now in the batch process there would be 2 scenario
1) multiple session because of DIY parallelism, say 4 sessions
2) Single session
Here again I am expecting different settings for 1) and 2)
Right?
Thanks and Regards,
OraKaran
|
|
|
|
|
|
| Re: Queries on PGA Settings and related Memory Management [message #494138 is a reply to message #494087] |
Fri, 11 February 2011 08:28 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - I think you may be attempting to micro-manage a situation which could be better managed automatically. You are using 9i which is seriously out of date, and the automatic memory management is not as good as with the later releases, but the information you need is there. After a reasonably long period (say, a couple of days because you have day and night patterns of activity) of normal running, query these views:
v$db_cache_advice
v$shared_pool_advice
v$pga_target_advice
and set your db_cache_size, shared_pool_size, and pga_aggregate_target parameters to what the views suggest will reduce the disc I/O and maximized the time saved. Do NOT set the parameters to values greater than that. Then let the database run for a while, and repeat.
|
|
|
|
|
|
| Re: Queries on PGA Settings and related Memory Management [message #494152 is a reply to message #494087] |
Fri, 11 February 2011 10:22 |
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
|
Senior Member |
|
|
Thanks Michel, John and Knight
Quote:
statistic#=20 is UGA, not PGA. Oracle server process is UGA + PGA + code + some other bits.
May be because of my different Oracle version I am getting following results
SQL> select name from v$statname where statistic#=20;
NAME
----------------------------------------------------------------
session pga memory
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
John -> I was asking settings for managing memory in manual mode (workarea_size_policy=manual with sort_area_size=xxxx etc) so that few sessions can use memory as as possible for the batch process
Regards,
OraKaran
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
