Clarification and Question about UGA/PGA and sort_area_sze
A reader, April 14, 2003 - 9:42 pm UTC
Thanks Tom. Great answer..
now ..
You said - "In MTS mode, there are LESS pgas then in dedicated server mode"
You mean in terms of memory size. Correct ? Beacause each session (dedicated or MTS) will have a PGA .. In MTS how would the PGA process show when doing a ps -ef .. Dedicated shows as LOCAL=NO or LOCAL=yes ..
As for the PGA_AGGREGATE - we have been looking at it .. sooner or later we will come around to it ..
Thanks for your help ..
April 15, 2003 - 8:10 am UTC
No, I do *not* mean in terms of memory size.
I mean in terms of CARDINALITY.
In dedicated server, if you have 100 dedicated servers for 100 sessions, you have 100 PGA's.
In shared server, if you have 10 shared servers for the same 100 sessions, you have 10 PGA's.
Each of the 10 PGA's may (or may not) be smaller in shared server then in dedicated server (they can be, they don't have to be).
sort_area_size, hash_area_size, bitmap etc
PINGU SAN, May 16, 2003 - 5:18 am UTC
Hi
In MTS or Oracle Shared Servers whatever they are called, from your previous replies I understand that sort_area_size, hash_area_size are part of PGA and not UGA (located in shared pool or large pool). My question is, if we make a big hash join and a big sort using 100MB for each operation then the shared server PGA would becme 200MB, is there anyway to free this? In dedicated server processes the memory is freed when we quit our session but in MTS since server processes are shared I guess there is no way to free the 200MB I mentioned without killing the process. By the way what can go wrong if we kill a shared server process (assumming that noone has been assigned to use it!)
May 16, 2003 - 10:03 am UTC
no, it would be 100mb. the sort would use it - and then give it back and then the hash would use it and then the sort and then the hash.
the OS takes care of it. if you are not using it, it gets paged out. In 9i, using the pga_aggregate_target, the memory is returned to the OS directly. (so the answer would be -- 9i does that, but in 8i, the OS does it effectively enough)
pga
reader, May 16, 2003 - 10:37 am UTC
Does it mean that prior to 9i, can PGA grow depending upon the sort operation? My understanding is that if I set my sort_area_size = sort_area_retained_size = 1M, whenever a session starts, the corresponding PGA of the server process will be allocated 1M of memory for sort operation whether it needs it or not. If the session performs sorting that requires, say 5M of memory, what Oracle would do is, it will sort 1M in memory and retain the sorted result of 1M in the temp segment and then proceed to sort the next batch of 1M and will keep on retaining the sorted results in temp segment until it finishes sorting. After completing sorting of 5M data, it will merge the sorted results in PGA (?) and gives the output to the session. When it merges the results, it may need 5M of memory in PGA and at that time the PGA memory grows from 1M to 5M. As my sort_area_retained_size is set to 1M, after the sort is done, it will give back the memory by keeping 1M memory in the PGA. Could you please clarify, is this how it works prior to 9i.
Thanks as always.
May 16, 2003 - 5:04 pm UTC
your understanding is wrong.
sort area size is an upper bound, it is dynamically allocated from the pga at runtime to the size needed, if you only needed 64k, that is all that would be allocated. After the sort is done, the sort area size is "freed" back to the pga for reuse by any other pga allocation.
it does not work that way.
Please confirm this
David, May 16, 2003 - 1:20 pm UTC
1) PGA_AGGREGATE_TARGET is the MAXIMUM size this area can take, so that when more memory is needed and it is not available in this area, then TEMP ts will be used ?
2) Is it called "target" because it's an upper bound (maximum size allowed) or because it's the size Oracle "tries" to reach (maybe it can stay a bit smaller or a bit greater than the specified value.
3) if sum(pga_max_mem) from v$process is greater than my pga_aggregate_target (as in fact it is in my system now), it doesn't mean that my pga_aggregate_target is small. pga_max_mem is the maximum size the process has even taken, so I think I does not mean much, right ?
May 16, 2003 - 5:17 pm UTC
1) no, it is the suggest bound on the PGA memory that will be used by all concurrent sessions. Oracle will look at the PGA aggregate target (say it is 500meg) and based on the number of active sessions and other factors it might decide "your query shall use 25meg of sort area", later, cause the load went up, it might decide "your query shall use 512K of sort area" and so on.
It dynamically adjusts these settings all day long based on the current workload.
2) it is a target, it may overshoot it.
3) no, that is the max mem reached at a point in time, doesn't mean all sessions reached that point at the same time. read
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8759826405304 <code>
PGA Contents
Ashish, May 17, 2003 - 11:21 am UTC
Is there any way to see the contents of the PGA allocated
out of PGA_AGGREGATE_TARGET?
May 17, 2003 - 3:11 pm UTC
pga grows beyond my sort_area_size
reader, May 17, 2003 - 1:08 pm UTC
Tom, I tried the example from your book. Please see below. It looks like PGA grows depending upon how many rows I see after sorting.
Could you clarify what is happening? I thought sort_area_size is the upper bound as you say but my pga has grown beyond the sort_area_size.
I appreciate your time.
scott@oracle> show parameter sort_area
NAME TYPE VALUE
------------------------------------ ------- --------------------
sort_area_retained_size integer 1000000
sort_area_size integer 1000000
scott@oracle> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 44780
session uga memory max 57844
session pga memory 599588
session pga memory max 599588
scott@oracle> set pause on
scott@oracle> set pagesize 10
scott@oracle> select * from all_objects order by 1,2,3,4;
..................
OWNER OBJECT_NAME SUBOBJECT_NAME
------------------------------ ------------------------------ ------------------------------
OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S
---------- -------------- ------------------ --------- --------- ------------------- ------- - - -
DEMO ITEM
10931 10931 TABLE 23-JUN-99 23-JUN-99 1999-06-23:12:47:08 VALID N N N
DEMO I_CUSTOMER$CUSTOMER_ID
10940 10940 INDEX 23-JUN-99 23-JUN-99 1999-06-23:12:47:19 VALID N N N
10 rows selected.
scott@oracle> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 53252
session uga memory max 1106660
session pga memory 1603492
session pga memory max 1603492
scott@oracle> select * from all_objects order by 1,2,3,4;
............
OWNER OBJECT_NAME SUBOBJECT_NAME
------------------------------ ------------------------------ ------------------------------
OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S
---------- -------------- ------------------ --------- --------- ------------------- ------- - - -
5554 SYNONYM 23-JUN-99 23-JUN-99 1999-06-23:12:02:31 VALID N N N
PUBLIC /34ecef2c_ImageConsumerQueue
3898 SYNONYM 23-JUN-99 23-JUN-99 1999-06-23:11:59:28 VALID N N N
PUBLIC /34f3aa56_DeploymentDescriptor
377 rows selected.
scott@oracle> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 53244
session uga memory max 1106660
session pga memory 2333620
session pga memory max 2333620
May 17, 2003 - 3:24 pm UTC
no version, perhaps 9i with workarea_size_policy is thankfully ignoring your sort area size.
perhaps the method of testing -- which appears to be ctl-c?? is contributing to it. maybe the cursors are not closed, hence the sort areas never go away (until the cursor is closed). that would be a side effect of sqlplus.
run the entire query with
connect user/password
set autotrace traceonly
run the query
set autotrace off
query up the pga usage.
perhaps it only takes 600k to sort the entire thing (you see, the entire result was sorted before you got row 1). And you just kept getting 600k sort areas. You can have many sort areas going -- many many sort area retained areas. that is what this sort of looks like.
pga grows
reader, May 17, 2003 - 5:31 pm UTC
Please see below. still I could see pga grow if i repeat the steps as suggested. It is 8i personaledition on windows. Thanks for your time.
SQL*Plus: Release 8.1.5.0.0 - Production on Sat May 17 16:29:21 2003
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Personal Edition Release 8.1.5.0.0 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production
scott@oracle> show parameter sort_area;
NAME TYPE VALUE
------------------------------------ ------- --------------------
sort_area_retained_size integer 1000000
sort_area_size integer 1000000
scott@oracle> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 41628
session uga memory max 54344
session pga memory 110640
session pga memory max 110640
scott@oracle> set autotrace traceonly
scott@oracle> select * from all_objects order by 1,2,3,4;
11007 rows selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
1222 recursive calls
197 db block gets
31036 consistent gets
626 physical reads
23452 redo size
1297149 bytes sent via SQL*Net to client
82041 bytes received via SQL*Net from client
737 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
11007 rows processed
scott@oracle> set autotrace off
scott@oracle> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 42700
session uga memory max 1093636
session pga memory 1300036
session pga memory max 1300036
scott@oracle> set autotrace traceonly;
scott@oracle> select * from all_objects order by 1,2,3,4;
11007 rows selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
1222 recursive calls
195 db block gets
31036 consistent gets
625 physical reads
23372 redo size
1297161 bytes sent via SQL*Net to client
82041 bytes received via SQL*Net from client
737 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
11007 rows processed
scott@oracle> set autotrace off
scott@oracle> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 41628
session uga memory max 1096588
session pga memory 2216856
session pga memory max 2216856
scott@oracle>
May 17, 2003 - 6:50 pm UTC
it is like I said -- a side effect of the way sqlplus manages cursors. do it like this:
ops$tkyte@ORA815> alter session set sort_area_size = 1000000;
Session altered.
ops$tkyte@ORA815> alter session set sort_area_retained_size = 1;
Session altered.
ops$tkyte@ORA815>
ops$tkyte@ORA815> set autotrace traceonly statistics
ops$tkyte@ORA815> select * from all_objects order by 1,2,3,4;
10845 rows selected.
Statistics
----------------------------------------------------------
8 recursive calls
9 db block gets
27936 consistent gets
463 physical reads
0 redo size
1246609 bytes sent via SQL*Net to client
80820 bytes received via SQL*Net from client
726 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
10845 rows processed
ops$tkyte@ORA815> set autotrace off
ops$tkyte@ORA815> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
------------------------------ ----------
session uga memory 44364
session uga memory max 94320
session pga memory 1202948
session pga memory max 1202948
ops$tkyte@ORA815> set autotrace traceonly statistics
ops$tkyte@ORA815> select * from all_objects order by 1,2,3,4;
10845 rows selected.
Statistics
----------------------------------------------------------
8 recursive calls
9 db block gets
27936 consistent gets
457 physical reads
0 redo size
1246631 bytes sent via SQL*Net to client
80820 bytes received via SQL*Net from client
726 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
10845 rows processed
ops$tkyte@ORA815> set autotrace off
ops$tkyte@ORA815> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
------------------------------ ----------
session uga memory 44360
session uga memory max 94320
session pga memory 1914768
session pga memory max 1914768
ops$tkyte@ORA815> set autotrace traceonly statistics
ops$tkyte@ORA815> select * from all_objects order by 1,2,3,4;
10845 rows selected.
Statistics
----------------------------------------------------------
8 recursive calls
9 db block gets
27936 consistent gets
452 physical reads
0 redo size
1246631 bytes sent via SQL*Net to client
80820 bytes received via SQL*Net from client
726 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
10845 rows processed
ops$tkyte@ORA815> set autotrace off
ops$tkyte@ORA815> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
------------------------------ ----------
session uga memory 44364
session uga memory max 94320
session pga memory 1914768
session pga memory max 1914768
ops$tkyte@ORA815> set autotrace traceonly statistics
ops$tkyte@ORA815> select * from all_objects order by 1,2,3,4;
10845 rows selected.
Statistics
----------------------------------------------------------
8 recursive calls
9 db block gets
27936 consistent gets
479 physical reads
0 redo size
1246631 bytes sent via SQL*Net to client
80820 bytes received via SQL*Net from client
726 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
10845 rows processed
ops$tkyte@ORA815> set autotrace off
ops$tkyte@ORA815> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
------------------------------ ----------
session uga memory 44364
session uga memory max 94320
session pga memory 1914768
session pga memory max 1914768
ops$tkyte@ORA815> set autotrace traceonly statistics
ops$tkyte@ORA815> select * from all_objects order by 1,2,3,4;
10845 rows selected.
Statistics
----------------------------------------------------------
8 recursive calls
9 db block gets
27936 consistent gets
467 physical reads
0 redo size
1246631 bytes sent via SQL*Net to client
80820 bytes received via SQL*Net from client
726 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
10845 rows processed
ops$tkyte@ORA815> set autotrace off
ops$tkyte@ORA815> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
------------------------------ ----------
session uga memory 44364
session uga memory max 94320
session pga memory 1914768
session pga memory max 1914768
ops$tkyte@ORA815>
pga grows
Reader, May 17, 2003 - 5:48 pm UTC
Tome, continuation from above results. If i did that one more time, pga seems to be the same size. please refer above to see this. However, it has grown beyond my sort_area_size. Thanks.
scott@oracle> set autotrace off
scott@oracle> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 38460
session uga memory max 1096588
session pga memory 2216856
session pga memory max 2216856
scott@oracle> set autotrace traceonly;
scott@oracle> select * from all_objects order by 1,2,3,4;
11007 rows selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
1222 recursive calls
195 db block gets
31036 consistent gets
625 physical reads
23412 redo size
1297161 bytes sent via SQL*Net to client
82041 bytes received via SQL*Net from client
737 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
11007 rows processed
scott@oracle> set autotrace off
scott@oracle> select a.name,b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %';
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 38464
session uga memory max 1096588
session pga memory 2216856
session pga memory max 2216856
Thanks!
Reader, May 17, 2003 - 7:25 pm UTC
How can session pga memory be more than the physical RAM ?
Vivek Sharma, March 04, 2004 - 7:37 am UTC
Dear Tom,
I have an Oracle 8i Database with version as 8.1.7.4.. The Server has around 24 GB of Physical Ram and the Size of the SGA is around 2.4 GB (modified the libraries to relocate the SGA limit). The Sort_Area_size is 10M and sort_area_retained_size is 5M. During the peak time, the number of sessions connected to the database is around 1300. The output of the below mentioned query is
select name, value/1024/1024/1024
from v$sysstat where name like '%memory%'
NAME Value in GB
------------------------- -----------
session uga memory 3.39125823
session uga memory max 58.3202833
session pga memory 115.802932
session pga memory max 121.057358
My Question is
How can the PGA grow upto 115 GB and max UGA grew upto 58 GB ? Will it not affect the performance due to paging and swapping ? How do I avoid this ?
Regards
Vivek
March 04, 2004 - 12:44 pm UTC
it is a cumulative number since instance startup -- it is not "very meaningful"
pga_aggregate_target
A Reader, June 15, 2004 - 4:07 am UTC
Dear Tom:
If in Oracle 9i (9201), the pga_aggregate_target is 64MB. And there are 50 sessions in the database. What would be the size of PGA / UGA??
Thanks
June 15, 2004 - 3:30 pm UTC
unknown :)
somewhere approx between 0 and 64m.
pga aggregate target is allocated and freed as needed, one the fly
pga_aggregate_target -9iR1
Pravesh Karthik from chennai, September 16, 2004 - 10:35 am UTC
Tom,
Please see the pga aggregate part from statspack
PGA Aggreg Target Memory Stats for DB: PRDAI Instance: PRDAI Snaps: 20 -21
%Optim %Non-W/A %Auto %Manual
PGA Aggr PGA W/A PGA 1-Pass W/A PGA W/A W/A
Target(M) in Use(M) in Use(M) Mem Req(M) Execs Memory PGA Mem PGA Mem
- --------- ---------- ---------- ---------- ------- -------- ------- -------
B 500 648.1 38.3 3,371.1 94.09 5.91 .00
E 500 671.7 36.6 3,371.1 94.55 5.45 .00
-------------------------------------------------------------
PGA Memory Stats for DB: PRDAI Instance: PRDAI Snaps: 20 -21
-> WorkArea (W/A) memory is used for: sort, bitmap merge, and hash join ops
Statistic Begin (M) End (M) % Diff
----------------------------------- ---------------- ---------------- ----------
aggregate PGA auto target 31.250 31.250 .00
estimated PGA memory for one-pass 63.504 75.415 18.76
estimated PGA memory for optimal 63.504 214.847 238.32
global memory bound .001 .001 1.27
maximum PGA allocated 3,526.584 3,526.584 .00
maximum PGA memory for one-pass 3,371.104 3,371.104 .00
maximum PGA memory for optimal 9,609.228 9,609.228 .00
maximum PGA used for auto workareas 370.853 370.853 .00
maximum PGA used for manual workare .506 .506 .00
total PGA allocated 707.596 734.038 3.74
total PGA inuse 648.083 671.730 3.65
total expected memory 31.222 31.228 .02
-------------------------------------------------------------
pga_aggregate_target 524288000 == 500 MB
Can you tell me how to read this. This is from 9iR1 -- Advisory not there, please help me how to understand and what the above one say's.. and what may be the right value from this statsreport
thanks a lot for consideration
Pravesh Karthik
September 16, 2004 - 10:43 am UTC
A reader, September 16, 2004 - 11:11 am UTC
aggregate PGA auto target 31.250 31.250 .00
maximum PGA used for auto workareas 370.853 370.853 .00
"
In Oracle9iR1 - you would be interested in reviewing the maximum PGA used for
auto workareas and comparing that to the aggregate PGA auto target. This will
show you if you've been exceeding or hitting the ceiling on the PGA workarea
sizes. If the maximum you've used is very close to the auto target - it would
indicate that you hit the ceiling on the amount of memory Oracle was allowed to
use for PGA's and you most likely resorted to disk based operations at that
point.
"
The above two values are the one's you are referring to, right? ..
I this case, i dont have any problem with pga aggregate target size - right?
Please correct me if am wrong
Thanks
Pravesh Karthik
September 16, 2004 - 11:31 am UTC
The aggregate PGA auto target should not be small compared to the value of
PGA_AGGREGATE_TARGET. You must ensure that enough PGA memory is left for work areas running in automatic mode.
Pravesh Karthik from chennai, September 16, 2004 - 12:14 pm UTC
Tom,
do you mean to say that i need to increase the pga aggregate target?
if so, how to find, how much to increase. (9ir1)
Sorry for my ignorance.
Thanks,
Pravesh Karthik
September 16, 2004 - 1:09 pm UTC
how many users do you concurrently have doing stuff.
how much ram is on your machine.
would you categorize yourself as "oltp" or "warehouse"
Pravesh Karthik from chennai, September 16, 2004 - 2:47 pm UTC
Tom,
Concurrent users - 30
ram - 4gb
warehouse
Thanks,
Pravesh Karthik
September 16, 2004 - 3:22 pm UTC
what is the database configured for session wise -- show me the non-default settings for parameters you have? something doesn't sound right.
Pravesh Karthik from chennai, September 17, 2004 - 3:06 am UTC
Tom,
Sorry, there was a wrong info..
Concurrent users - 50 to 100
ram - 64 gb
warehouse
please see below for non-default parameter
processes = 299
timed_statistics = TRUE
shared_pool_size = 452984832
shared_pool_reserved_size = 41943040
nls_date_format = YYYYMMDD
db_block_size = 8192
db_cache_size = 3154116608
compatible = 9.0.1
log_archive_start = TRUE
log_archive_dest = /u70/archivelog/prdai
log_archive_format = prdai_%S.log
log_buffer = 1048576
log_checkpoint_interval = 999999999
log_checkpoint_timeout = 3600
db_file_multiblock_read_count = 32
log_checkpoints_to_alert = FALSE
undo_management = AUTO
undo_tablespace = undo01
undo_retention = 2700
max_enabled_roles = 100
O7_DICTIONARY_ACCESSIBILITY = TRUE
remote_login_passwordfile = SHARED
background_dump_dest = /u01/app/oracle/admin/prdai/bdump
user_dump_dest = /u01/app/oracle/admin/prdai/udump
max_dump_file_size = 6400
core_dump_dest = /u01/app/oracle/admin/prdai/cdump
audit_trail = DB
db_name = prdai
optimizer_mode = CHOOSE
parallel_threads_per_cpu = 4
parallel_automatic_tuning = FALSE
query_rewrite_enabled = TRUE
query_rewrite_integrity = trusted
pga_aggregate_target = 524288000
open_cursors=256
#event="604 trace name errorstack level 10"
Thanks for your time.
Pravesh Karthik
September 17, 2004 - 9:05 am UTC
from
Concurrent users - 30
ram - 4gb
to
Concurrent users - 50 to 100
ram - 64 gb
that is like "night" vs "day".
I cannot reproduce, for santity sake - please verify these settings are in fact *in use* in the database.
Maurice, September 19, 2004 - 4:56 pm UTC
Hi Pravesh Karthik,
If you have 64Gb of RAM why do you set pga_aggregate_target = 500MB?
I would rather set it to something like 20-30Gb.
Also for your information: if you are using workarea_size_policy=auto Oracle will, by default, never allocate more then 200MB to a process.
HTH
Maurice
September 19, 2004 - 5:13 pm UTC
more than Xmb to a process for dynamic workareas
it can go more than that, a plsql table, lots of cursors, whatever -- Oracle *tries* to keep the pga within the pga_aggregate_target but the only thing it can control are the dynamic workareas, not "plsql variables and the like"
Calculating Cache Hit Percentage
A reader, September 20, 2004 - 4:16 pm UTC
Hi Tom,
First of all thanks for all these interesting information.
I have question about the view V$PGASTAT described under </code>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/memory.htm#47791 <code>
Did I understand it correctly that the value of "extra bytes read/written" will always be smaller or equal (<=) to the value of "total bytes processed"?
If this is so then "cache hit percentage" will never be lower then 50% because of the forumla BP x 100 / (BP + EBP).
So even if all my queries run in one pass mode I would have a cache hit percentage of 50%?
Did I miss understand something or is it that correct?
Thanks for your help
September 20, 2004 - 7:11 pm UTC
I set my pga aggregate target small restarted and:
ops$tkyte@ORA9IR2> select * from v$pgastat;
NAME VALUE UNIT
-------------------------------------------------- ---------- ------------
aggregate PGA target parameter 10485760 bytes
aggregate PGA auto target 0 bytes
global memory bound 0 bytes
total PGA inuse 14840832 bytes
total PGA allocated 22585344 bytes
maximum PGA allocated 22585344 bytes
total freeable PGA memory 65536 bytes
PGA memory freed back to OS 524288 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 0
bytes processed 6889472 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
16 rows selected.
ops$tkyte@ORA9IR2> set autotrace traceonly
ops$tkyte@ORA9IR2> select * from all_objects
2 order by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13;
30683 rows selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
372 recursive calls
34 db block gets
174437 consistent gets
915 physical reads
0 redo size
2177279 bytes sent via SQL*Net to client
22994 bytes received via SQL*Net from client
2047 SQL*Net roundtrips to/from client
15 sorts (memory)
1 sorts (disk)
30683 rows processed
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select * from v$pgastat;
NAME VALUE UNIT
-------------------------------------------------- ---------- ------------
aggregate PGA target parameter 10485760 bytes
aggregate PGA auto target 0 bytes
global memory bound 0 bytes
total PGA inuse 14814208 bytes
total PGA allocated 22716416 bytes
maximum PGA allocated 22716416 bytes
total freeable PGA memory 65536 bytes
PGA memory freed back to OS 1507328 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 0
bytes processed 15386624 bytes
extra bytes read/written 31776768 bytes
cache hit percentage 32.62 percent
16 rows selected.
<b>so, extra bytes read/written can exceed bytes processed.</b>
Mike, September 21, 2004 - 2:56 pm UTC
Hi Tom,
Thanks for this clarifications. I see now that because of the queries running in MULTIPASSES_EXECUTIONS it is possible to get more "extra bytes read/written" then "total bytes processed".
I have to admit that I don't like so much this "cache hit percentage" because, as I already said before, even if all the queries run in ONEPASS_EXECUTIONS you will still get 50% "cache hit percentage". From my point of view if ALL the queries have to access the temp tablespace you should get a hit ratio of 0%.
September 21, 2004 - 3:07 pm UTC
no, that would be a 50% "in memory vs on disk" exactly at that point.
you seem to want the "sort to disk vs not sort to disk" ratio
Mike, September 22, 2004 - 2:18 am UTC
Thanks for your, as usuall, quick feedback.
Everthing is clear now.
Mike
SGA not mapped-ORA-00603 ORACLE Session terminated
mAg, August 12, 2005 - 9:16 am UTC
Hi Tom
This happens when creating composite index on four column which contains 81 million records in the table...
Alert file content:
ORA-27072: skgfdisp: I/O error
Additional information: 138354
ORA-01114: IO error writing block to file 203 (block # 138354)
....
....
----- Dump of the Fixed SGA -----
***** SGA not mapped *****
----- Dump of the Fixed UGA -----
***** no UGA *****
KSTDUMP: SGA no longer mapped
EOF
Trace file contents:
ORA-01114: IO error writing block to file 203 (block # 138354)
ORA-27072: skgfdisp: I/O error
Additional information: 138354
Searched in the metalink, they telling there is some memory leakage how to fix this???
Thanks for the same...
mAg
August 12, 2005 - 9:29 am UTC
you were already at the site to get support???? use it, that is what they are there for.
I/O error however, looks more like "I/O error" than memory leak
but -- this is what support clearly does.
sort_area_retained_size
James, August 23, 2005 - 3:30 pm UTC
What would be effect of setting sort_area_retained_size = 0? Is it going to affect the sort performance? we are using 8.1.7. Thanks.
August 24, 2005 - 8:50 am UTC
the docs say the valid values are the equivalent of at least 2 blocks up to the sort_area_size.
The raw sort performance? That will be mostly gated by sort_area_size, however, it could affect overall retrieval by not caching the results in memory as much. So, if you had a sort area size if 512k and your sorted data was 468k - and your sort_area_retained size was small -- you would end up writing out most of that to disk at the end of the sort and then reading it back in as the client fetched it again.
23(08/2005 additional info
A reader, September 01, 2005 - 3:34 pm UTC
Hi
I have read the your new comments regarding shared server and pga size.
Do you mean that pga_aggregate_target is only useful when shared server is NOT used? In shared server we must use the old fashion sort/hash area size parameters?
September 01, 2005 - 4:09 pm UTC
if ( version = 9i )
then
if ( pga_aggregate_target is set and workarea_size_policy is auto )
then
if ( connection is dedicated server )
then
these parameters (sort area [retained] size) need not be set
else
they do need to be set, shared server uses sort area size
end if
else
they do need to be set
end if
elsif (version > 9i )
then
if ( pga_aggregate_target is set and workarea_size_policy is auto )
then
these parameters (sort area [retained] size) need not be set
else
they do need to be set
end if
end if
PGA and SGA
Enayet, September 02, 2005 - 6:01 am UTC
I have some confusion here.
As defined, PGA is the private are retained for the paticular session (consider dedicated server). I am wondering how PGA data block transfer to SGA (ie, buffer cache)? have there any corelation? I know vagely, if some sort block need to be shared, they retained in the buffer cache, true?
September 02, 2005 - 6:24 am UTC
the PGA is work area in the server process.
blocks are buffered in the SGA.
It is true that some data is copied from the SGA into the PGA (for sorting ,for example) but blocks are cached in the SGA, the PGA is a processes private work area.
Do I have this right?
A reader, September 15, 2005 - 3:32 pm UTC
I just want to make sure that I understand this.
A. Tom asks that the SearchOracle tip be removed. Tom does not contact the authors, he does not suggest changes, he identifies himself as an Oracle Vice President and demands removal.
B. Tom Kytes starts two blogs about this tip (one is not enough evidently) and taunts Mike and Don for not responding, even though he knows that it is against BC rules to engage.
C. Mike and Don are impersonated on asktom, and they complain. It is a crime, afterall.
D. Tom takes THE EXACT same complaint from his blogs to asktom, nothing new really. Same insults and unprofessional taunts against Mike.
E. Someone named Mike Ault tries to defend themselves here and Tom uses the fact that Mike com[plaoned about impersonation to remove the comments, twice, without even trying to e-mail Mike for verification. Tom's been around, and knows full well that there are two Mike Ault's who publish Oracle content, but hey, let's not spoil the fun.
F. So many of the original comments have been removed by Tom that it is impossible to follow anymore.
If you as outraged about this one-sided debate as me, lets do the right thing and let our voices be heard. Wouldn't it be great if Tom, like Don and Mike, was prohibited from posting on forums?
Oracle Legal Department
500 Oracle Parkway
Redwood City, CA 94065
Let's call a spade a spade and put an end to this. No Oracle VP should be associated with this embarassing, unprofessional conduct.
September 15, 2005 - 4:41 pm UTC
A) Tim DiChiara knew who I was, I've worked with him in the past.
Tom *has* contacted the authors in the past.
Tom *has* has said authors complain greatly about such contact.
Tom *will not* be contacting said authors in the future - it isn't worth the hassle.
Tom *did* point out 8 things in the article just to show "this is is sort of messed up"
B) Where did I taunt ANYONE. And you do see that Mike Ault did in fact follow up on his blog. You probably missed Mike Aults blog where he said I was leading a "vicious" attack - the one where he first pulled all comments and then secondly pull the article??
C) And did you know the context behind that? That it was a thread they were active on. A thread they posted no less then FIVE TIMES on before complaining. A thread where I immediately followed up (way way before their complaint) and said "this doesn't look right - I don't believe it is them, ip address not recognized, if they let me know - be glad to just take it down".
No, they saw it - ignored it and called it out when it suited them. So be it, I thought I was doing the right thing, but guess not. The only safe thing is apparently to remove them.
D) Where did I say *a thing* about Mike? Geez.
E) Lets see, please read C.
F) not true, entirely false. Where did you make that up from?
DO I HAVE This right ?
Rao, September 15, 2005 - 6:03 pm UTC
A reader,
Who are you after all ? You blame Tom for starting two blogs for the same tip . What did you do now ? Very good discussion was going on in this thread about SGA/PGA until you posted something irrelevant to this thread. You could have posted to another thread where lots of discussion going on about the tip. I have read Mike Ault's post. If he wants defend himself or Don then he should come up with explanation and facts proving their tip. Tom already given his explanation on the tip. He or /anybody defending the tip did not give answers to Tom's questions. It looks like Don and his company wants to make money by publishing as many books as possible, completely ignoring the quality and accuracy. I have read few books written by Don. They were useless and had many inconsistencies.They give themselves good rating in Amazon.com. Since they are exposed now, they realize that they can not fool people just by claiming "greatest Oracle DBA". If you have not read Tom's book then please read first. Then you will realize why people like Tom.
Why do think Tom, to be prohibited from posting on forums ? He just wanted to remove incorrect information from the web. He has done the right thing.I don't think there was any personal dispute behind his intention.
please delete 'a readers' posts
A different read who wants to learn something, September 15, 2005 - 11:48 pm UTC
This is a technical forum. A reader had his say. Just delete his posts they are cluttering up the forum. If you leave them up there and don't respond other people will and it's harder to find the good technical material. You are under no obligation to give this person his repeated say.
Most of us just want to read technical material. I am sure you would rather answer technical questions.
I can't help it, just one last post on 'A reader'. Does his IP address match anyone we know?
September 16, 2005 - 8:04 am UTC
the ip address is that of an anonymous unprotected proxy server in austria germany. Anyone can use it (I did just to test it).
So, you can only follow your hunches on this one.
Let's see - DO I HAVE THIS RIGHT?
Bill S., September 16, 2005 - 12:11 am UTC
NO, I just want to make sure that I understand THIS.
According to what I saw (my opinions are after your questions, reader):
>>A. Tom asks that the SearchOracle tip be removed. Tom does not contact the authors, he does not suggest changes, he identifies himself as an Oracle Vice President and demands removal.
No, Tom provides SearchOracle with evidence (PROOF, if you will) that an article they have hosted is rife with errors.
Tom asks them to consider retracting the article, which they do not. That's their perogative. Jonathan Lewis confirms that the article is very wrong.
>>B. Tom Kytes starts two blogs about this tip (one is not enough evidently) and taunts Mike and Don for not responding, even though he knows that it is against
BC rules to engage.
Tom starts a blog about the incorrect article, hoping to keep folks from actually BELIEVING the factually inaccurate material.
When this does not seem to help, Tom creates a second blog wondering why. Or so it seems to me. The author didn't need to engage anyone - just correct the article or retract it. Based on the evidence provided by Tom and Jonathan, it was simply just wrong.
>>C. Mike and Don are impersonated on asktom, and they complain. It is a crime, afterall.
When did this occur? Oh, right - must be the Predictive Reorg thread a long time ago.
And as soon as Mike confirmed that he had not made that post, Tom took it down.
>>D. Tom takes THE EXACT same complaint from his blogs to asktom, nothing new really. Same insults and unprofessional taunts against Mike.
A READER posts the article on AskTom and asks Tom (gee, surprise!) to evaluate it for clarification.
So Tom does. Funny how that works, isn't it? And funny,
I didn't see any insults and unprofessional taunts against Mike, most certainly not from Tom. The thread is still there - go back and look again.
>>E. Someone named Mike Ault tries to defend themselves here and Tom uses the fact that Mike com[plaoned about impersonation to remove the comments, twice, without even trying to e-mail Mike for verification. Tom's been around, and knows full well that there are two Mike Ault's who publish Oracle content, but hey, let's not spoil the fun.
No, someone named Cancun Kid tries to defend something (we're still not sure what), then makes a post from the same IP address but this times claims to be Mike Ault. Ooooooh, so now we aren't sure if:
a) It really is Mike (and he has just been joshing us as Cancun Kid)
b) It is just Cancun Kid pretending to be Mike Ault.
So, Tom removes the post since it is unclear who the person REALLY is. I don't blame him for being concerned about that - obviously, Mike does not want people claiming to be him posting material, and rightly so.
>>F. So many of the original comments have been removed by Tom that it is impossible to follow anymore.
Really? Can we have a count please? I counted maybe, MAYBE a max of 5 posts that were removed, mostly because they were simply inflammatory.
>>If you as outraged about this one-sided debate as me, lets do the right thing and let our voices be heard. Wouldn't it be great if Tom, like Don and Mike, was prohibited from posting on forums?
>>
>>Oracle Legal Department
>>500 Oracle Parkway
>>Redwood City, CA 94065
>>
>>Let's call a spade a spade and put an end to this. No Oracle VP should be
>>associated with this embarassing, unprofessional conduct.
So tell me, WHO has prohibited Don and Mike from posting in forums? Oh, sorry - that would be.....Don and Mike. Nobody as far as I can tell has prevented Don or Mike from posting. I am unclear on that one, please explain it.
I am merely disappointed that professionals cannot correct material that is proven to be wrong.
Not ONCE in that thread that has now been shut down did ANYBODY manage to provide ANY evidence that Tom or Jonathan were wrong in their assessment. NOT ONCE.
Oh, one last thing - Jonathan is STILL waiting for his answers.
PGA on 8i
A reader, October 26, 2005 - 2:07 am UTC
Hi Tom,
Oracle 8i Rel 3 on Win 2k
How can i find out the total PGA memeory being used by each session on Oracle 8i.
Thanks as always.
October 26, 2005 - 11:51 am UTC
v$sesstat joined to v$statname
David Aldridge http://oraclesponge.blogspot.com, October 26, 2005 - 4:34 pm UTC
"A Reader" is very prudent to stay anonymous and use proxy servers, since his comments on unprofessionalism and insults appear to meet _some_ people's standards of defamation.
pga_agregate_target on Windows 2003
A reader, March 22, 2006 - 8:31 am UTC
Tom,
as far as I know on 32-bit Windows 2003 the whole Oracle instance is one process and restricted to 2GB. Does this restriction include the sum of all PGAs for any given time (dedicated server, 9i)?
So if I do
select sum(a.value) from v$sesstat a, v$statname b where a.statistic# = b.statistic# and b.name like 'session pga memory'
and get something about 200 MB - I should reduce my sga setting to 2GB minus 200 MB (I have W2003 server with 4GB phys. memory)
Thanks
March 22, 2006 - 4:03 pm UTC
on 32bit windows, the process is memory constrained - yes. that is the sum of the pga and sga and stack and any other memory we might need.
that query just gives you a "point in time". You should set your SGA to the size you want and the PGA likewise. You really won't get 2gb (it is more like 1.75gig or something with stacks and other limits - without using switches to get more possible).
uga/pga mts where in the documentation?
A reader, August 10, 2006 - 8:41 am UTC
Hi
I have been searching the 9i documentation for pga/uga usage in shared server. I cannot find it. I have searched net admin guide, admin guide, concepts guide but nothing.
Do you know if this is explaine din 9i documentation?
Cheers
August 10, 2006 - 9:34 am UTC
uga
A reader, August 11, 2006 - 2:27 am UTC
where is uga explained in the documentation such what does it contain?
August 11, 2006 - 10:42 am UTC
The UGA is, in effect, your sessions state. It is memory that your session must always be able to get to. The location of the UGA is wholly dependent on how you connected to Oracle. If you connected via a shared server, then the UGA must be stored in a memory structure that every shared server process has access toand that would be the SGA. In this way, your session can use any one of the shared servers, since any one of them can read and write your sessions data. On the other hand, if you are using a dedicated server connection, this need for universal access to your session state goes away, and the UGA becomes virtually synonymous with the PGA; it will, in fact, be contained in the PGA of your dedicated server. When you look at the system statistics, youll find the UGA reported in the PGA in dedicated server mode (the PGA will be greater than or equal to the UGA memory used; the PGA memory size will include the UGA size as well).
So, the PGA contains process memory and may include the UGA. The other areas of PGA memory are generally used for in-memory sorting, bitmap merging, and hashing. It would be safe to say that, besides the UGA memory, these are the largest contributors by far to the PGA.