Thanks
A reader, March 21, 2003 - 5:47 pm UTC
Thanks for your great explanation.
I have a related question. I know there is no out of box numbers for deciding buffer sizes. But, can you advise an initial size for pga_aggregate_target size? I can use this parameter as starting value. I will use PGA advisory to find right value on my system and I will change it if necessary.
Our server is an 8 processor with 4 GB memory. Oracle 9.2 is running using dedicated server option. We are expecting 300 concurrent connections at a given peak point of time.
Currently parameters are
shared_pool_size 100 Mb
db_cache_size 300 Mb
db_block_size 16 Kb
shared_pool_reserved_size 5Mb
hash_area_size 2 Mb
sort_area_size 1 Mb
sort_area_retained_size 1 Mb
Please comment on the above parameter if you find anything as non-appropriate. Please let me know if I need to provide any other information.
Thanks in advance.
March 21, 2003 - 5:52 pm UTC
I would start with 500 meg for the pga_aggregate_target -- 1+ meg/connection to start.
assuming the database "owns" the machine -- you want to make sure your SGA+PGA doesn't blow out real memory.
Thanks again
A reader, March 21, 2003 - 5:55 pm UTC
Thanks for your suggestion.
Yes, the DB is the owner for that machine and no other processes are running on it.
I will go with 500 Mb.
As far as I know PGA advisory stores the memory usage history and based on that it will advise the optimum number, Should I wait couple of days to get the correct number?
A reader, March 24, 2003 - 8:02 am UTC
Hi Tom great answer.
You have said "the PGA memory is actually physically de-allocated" on some systems. Do you know how it is work with
Win2000 Advanced Server?
Thanks
kiro.
March 24, 2003 - 9:05 am UTC
the PGA "workareas" are, yes. this applies to sort areas and hash areas.
We may keep some around -- to share from session to session -- but they are released by the session
Great to learn so many things from you Tom
Vijay Sehgal, March 25, 2003 - 6:27 am UTC
Performance
Prasad, April 01, 2003 - 9:53 pm UTC
But since workarea_size_policy is configurable parameter(can be set to manual), is there a possibility that setting of parameters like hash_area_size and sort_area_size could be better than workarea_size_policy=auto.
I am asking this because i have seen performance degradation in few update statements which use hash joins e.g
UPDATE
(
SELECT
a.col1,b.col2
from a,b
where a.id=b.id
)
set col1=col2
When work area policy is set to auto and pga_aggregate_target=220M, this statement takes 1 minute to execute for 109,508
but if i set work area policy to manual and hash_area_size=20M , the same statement gets executed in 20 Seconds.
The results are consistent after several tests and no other sessions were active. I am connecting through a dedicated connection
What could be the possible reasons?
How to find the optimum PGA_aggregate_target value?
Regards
Prasad
I am using Oracle 9.2
April 02, 2003 - 7:34 am UTC
We'll start with Oracle9iR1 techniques and work up to 9iR2 since the 9iR1 techniques are useful in 9iR2 just to "see what is happening". The first view we'll start with is the V$PGASTAT dynamic performance view. It has two columns in 9iR1 - NAME, holding the name of the statistic and VALUE holding a value for it. In 9iR2 there is a third column UNITS that describes what the number in VALUE represents such as "bytes" or "percent". Here is an example:
ops$tkyte@ORA9I> select name,
2 to_char(decode( unit,
3 'bytes', value/1024/1024,
4 value ),'999,999,999.9') value,
5 decode( unit, 'bytes', 'mbytes', unit ) unit
6 from v$pgastat
7 /
NAME VALUE UNIT
---------------------------------------- -------------- ----------
aggregate PGA target parameter 432.0 mbytes
aggregate PGA auto target 338.1 mbytes
global memory bound 21.6 mbytes
total PGA inuse 249.0 mbytes
total PGA allocated 285.0 mbytes
maximum PGA allocated 293.8 mbytes
total freeable PGA memory .3 mbytes
PGA memory freed back to OS 4,270,536.8 mbytes
total PGA used for auto workareas 191.9 mbytes
maximum PGA used for auto workareas 195.1 mbytes
total PGA used for manual workareas .0 mbytes
maximum PGA used for manual workareas .5 mbytes
over allocation count 640,263.0
bytes processed 664,782.7 mbytes
extra bytes read/written 57,335.7 mbytes
cache hit percentage 92.1 percent
16 rows selected.
The relevant values are:
o aggregate PGA auto target: amount of tunable PGA memory the system can use. This is the memory left over for sorts, hash joins, bitmap operations and the like. The difference between this and the PGA target parameter (93.9 megabytes in this example) represents parts of the PGA that are not tunable such as cursor areas, PLSQL and/or Java memory, context information for the session and the like.
o total PGA inuse: the total amount of PGA currently in use. Details by process may be found in V$PROCESS.PGA_MEM_USED
o total PGA used for auto workareas: The current amount of memory being used by tunable workareas (sorts/hashes and such). This display shows that I had 191.9 megabytes of memory in use by workareas when I ran this query.
o Maximum PGA used for auto workareas: The maximum memory used by this instance since startup for these tunable workareas.
o Over allocation count (Oracle9iR2 and up): the number of times Oracle had to allocate more PGA memory then the PGA_AGGREGATE_TARGET suggested. This indicates the PGA target was set too small to accommodate the untunable (session context, cursor caches, etc) memory plus the tunable component. This count should be zero ideally. On the system I was using, we started the PGA_AGGREGATE_TARGET at 25megabytes and increased it to 250megabytes and finally settled in on 432megabytes at the suggestion of "the database".
o Cache hit percentage (Oracle9iR2 and up): this is a measure of how effective your PGA size is currently and is computed from instance startup on. If all work is done in memory - with no temporary space needed, this will be 100% (very rare, maybe in pure OLTP environments this will be observed). In this example, the value of 92.1% is artificially low due to the initial setting of 25megabytes on this system.
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.
In Oracle9iR2 and up - there is another crucial view for measuring the effectiveness of the PGA workareas - that is the V$SQL_WORKAREA_HISTOGRAM view. This view breaks the PGA workarea sizes into discrete ranges (eg: executions that used between X and Y bytes of PGA workarea) and keeps counters of how many times:
o Operations were done in "optimal" mode, optimal being totally in memory
o Operations were done in "one pass" mode, some disk was used but used in a one pass mode - limited IO was performed
o Operations were done in "multi-pass" mode, the operation was very disk intensive.
So, for example, on my system we can see:
ops$tkyte@ORA9I> SELECT
2 case when low_optimal_size < 1024*1024
3 then to_char(low_optimal_size/1024,'999999') ||
4 'kb <= PGA < ' ||
5 (HIGH_OPTIMAL_SIZE+1)/1024|| 'kb'
6 else to_char(low_optimal_size/1024/1024,'999999') ||
7 'mb <= PGA < ' ||
8 (high_optimal_size+1)/1024/1024|| 'mb'
9 end pga_size,
10 optimal_executions,
11 onepass_executions,
12 multipasses_executions
13 from v$sql_workarea_histogram
14 where total_executions <> 0
15 order by low_optimal_size
16 /
Optimal One Pass Multi Pass
PGA_SIZE Executions Executions Executions
------------------------------ ---------- ---------- ----------
8kb <= PGA < 16kb 60725456 0 0
16kb <= PGA < 32kb 128742 0 0
32kb <= PGA < 64kb 55579 0 0
64kb <= PGA < 128kb 36622 0 0
128kb <= PGA < 256kb 31909 0 0
256kb <= PGA < 512kb 756 0 0
512kb <= PGA < 1024kb 402 33 0
1mb <= PGA < 2mb 76 24454 0
2mb <= PGA < 4mb 4716 835 0
4mb <= PGA < 8mb 734 723 20
8mb <= PGA < 16mb 37 0 160
16mb <= PGA < 32mb 0 2 64
32mb <= PGA < 64mb 0 0 32
13 rows selected.
What we can see here is that the larger the requested workarea size - the more frequently we resorted to using the disk - in fact for sorts/hashes that consumed more than 16mb of RAM, we resorted to disk each time. This would be an indication that our PGA aggregate target was insufficient (the 24,454 one pass executions for sorts beween 1 to 2m of RAM really indicates that). In this case - the tables didn't lie, I had the PGA aggregate target set to 25meg initially and by correcting the problem and setting it to 250m and later 432m - the disk usaged dropped off considerably (well, all together).
The last two views are very straightforward (and are only available in Oracle9iR2 and up). The first is V$PGA_TARGET_ADVICE and the other is V$PGA_TARGET_ADVICE_HISTOGRAM. V$PGA_TARGET_ADVICE has 14 different PGA_AGGREGATE_TARGET sizes in it - from 12.5% of the current size on up to 800 of the current size. It then tells you for each size what the estimated PGA cache hit would be (all work done in memory gives 100%) and how many bytes it estimates would be read/written to temporary space with that size. Currently, on the weekend, my system is telling me:
ops$tkyte@ORA9I> select
2 trunc(pga_target_for_estimate/1024/1024)
pga_target_for_estimate,
3 to_char(pga_target_factor * 100,'999.9') ||'%'
pga_target_factor,
4 trunc(bytes_processed/1024/1024) bytes_processed,
5 trunc(estd_extra_bytes_rw/1024/1024) estd_extra_bytes_rw,
6 to_char(estd_pga_cache_hit_percentage,'999') || '%'
estd_pga_cache_hit_percentage,
7 estd_overalloc_count
8 from v$pga_target_advice;
Est Est Est
PGA Extra PGA Over
Target Factor MBytes MBytes Cache Alloc
MB % Proc. RW Hit Count
---------- ------- ---------- ---------- ----- ----------
54 12.5% 2752 3146 47% 4
108 25.0% 2752 835 77% 0
216 50.0% 2752 0 100% 0
324 75.0% 2752 0 100% 0
432 100.0% 2752 0 100% 0
518 120.0% 2752 0 100% 0
604 140.0% 2752 0 100% 0
691 160.0% 2752 0 100% 0
777 180.0% 2752 0 100% 0
864 200.0% 2752 0 100% 0
1296 300.0% 2752 0 100% 0
1728 400.0% 2752 0 100% 0
2592 600.0% 2752 0 100% 0
3456 800.0% 2752 0 100% 0
14 rows selected.
Currently, this is telling me that I could cut my PGA aggregate target in half and retain the same level of "cache hit" - however, I did this report on a Sunday afternoon - after 2 days of relative inactivity. I know on Monday afternoon - it will be a different story. Which points out an important fact. You want to inspect this view during your periods of peak activity - the values will change over time to reflect what is happening in the system. Also - if you take the advice and set the values - you will find that the advice table "resets". For example:
ops$tkyte@ORA9I> alter system set pga_aggregate_target = 216m;
System altered.
ops$tkyte@ORA9I> select
2 trunc(pga_target_for_estimate/1024/1024)
pga_target_for_estimate,
3 to_char(pga_target_factor * 100,'999.9') ||'%'
pga_target_factor,
4 trunc(bytes_processed/1024/1024) bytes_processed,
5 trunc(estd_extra_bytes_rw/1024/1024) estd_extra_bytes_rw,
6 to_char(estd_pga_cache_hit_percentage,'999') || '%'
estd_pga_cache_hit_percentage,
7 estd_overalloc_count
8 from v$pga_target_advice;
Est Est Est
PGA Extra PGA Over
Target Factor MBytes MBytes Cache Alloc
MB % Proc. RW Hit Count
---------- ------- ---------- ---------- ----- ----------
27 12.5% 0 0 0% 0
54 25.0% 0 0 0% 0
108 50.0% 0 0 0% 0
162 75.0% 0 0 0% 0
216 100.0% 0 0 0% 0
259 120.0% 0 0 0% 0
302 140.0% 0 0 0% 0
345 160.0% 0 0 0% 0
388 180.0% 0 0 0% 0
432 200.0% 0 0 0% 0
648 300.0% 0 0 0% 0
864 400.0% 0 0 0% 0
1296 600.0% 0 0 0% 0
1728 800.0% 0 0 0% 0
14 rows selected.
It is now saying we could go down to 27mb! But, since we just reset that value - we know we must wait a while - until some activity occurs - and query it again. For example - I just hit the system with 30 processes I know will take about 6-7meg of sort area each. After running that (and not much else), the advice is:
Est Est Est
PGA Extra PGA Over
Target Factor MBytes MBytes Cache Alloc
MB % Proc. RW Hit Count
---------- ------- ---------- ---------- ----- ----------
27 12.5% 432 824 34% 7
54 25.0% 432 824 34% 1
108 50.0% 432 824 34% 0
162 75.0% 432 206 68% 0
216 100.0% 432 141 75% 0
259 120.0% 432 0 100% 0
302 140.0% 432 0 100% 0
345 160.0% 432 0 100% 0
388 180.0% 432 0 100% 0
432 200.0% 432 0 100% 0
648 300.0% 432 0 100% 0
864 400.0% 432 0 100% 0
1296 600.0% 432 0 100% 0
1728 800.0% 432 0 100% 0
14 rows selected.
So, now it is telling me that it is a little "short" on memory given this sudden unexpected burst of activity I threw at it! We need a little more PGA to be optimum.
Quite good and exhaustive explaination of the concept
Prasad, April 02, 2003 - 8:48 am UTC
Excellent !!!
Christo Kutrovsky, April 02, 2003 - 9:42 pm UTC
Very important stuff.
Tom, can you comment on the following results:
NAME VALUE UNIT
------------------------------------- -------------- -------
aggregate PGA target parameter 38.0 mbytes
aggregate PGA auto target 4.0 mbytes
global memory bound 1.9 mbytes
total PGA inuse 163.4 mbytes
total PGA allocated 182.6 mbytes
maximum PGA allocated 186.6 mbytes
total freeable PGA memory 1.1 mbytes
PGA memory freed back to OS 72,224.0 mbytes
total PGA used for auto workareas .0 mbytes
maximum PGA used for auto workareas 4.8 mbytes
total PGA used for manual workareas .3 mbytes
maximum PGA used for manual workareas 2.7 mbytes
over allocation count 54,251.0
bytes processed 29,567.0 mbytes
extra bytes read/written 1,782.0 mbytes
cache hit percentage 94.3 percent
As you can see, the TARGET parameter is set far too low from the actual need.
My tests showed that almost all sorts are executed in MANUAL mode, although the workarea_size_policy is set to AUTO. And the sorts are affected by the SORT_AREA_SIZE parameter. It seems that if the pga_aggregate_target parameter is set far too low (or some other reason?), Oracle still uses SORT_AREA_SIZE.
What exactly represents (in this case) the "global memory bound" ? From the manual it sais:
"This gives the maximum size of a work area executed in AUTO mode. " What happens if you pass this bound? How is that measured ?
The advice statistics clearly shows what the TARGET value should be like:
PGA ESTD
TARGET EXTRA
ESTIMATE PGA_TAR BYTES_PROCESSED BYTES_RW ESTD_ ESTD_OVERALLOC_COUNT
-------- ------- --------------- -------- ----- --------------------
19 50.0% 12149 19720 38% 1782
28 75.0% 12149 19652 38% 1771
38 100.0% 12149 274 98% 1727
45 120.0% 12149 274 98% 1678
53 140.0% 12149 274 98% 1636
60 160.0% 12149 274 98% 1611
68 180.0% 12149 274 98% 1577
76 200.0% 12149 274 98% 1555
114 300.0% 12149 274 98% 981
152 400.0% 12149 68 99% 424
228 600.0% 12149 68 99% 0
304 800.0% 12149 68 99% 0
But I cannot understand why the histogram looks relativelly well (although strange):
OPTIMAL ONEPASS MULTIPASSES
PGA_SIZE EXECUTIONS EXECUTIONS EXECUTIONS
------------------------- ---------- ---------- ----------
8kb <= PGA < 16kb 704255 0 0
16kb <= PGA < 32kb 11135 0 0
32kb <= PGA < 64kb 26254 0 0
64kb <= PGA < 128kb 1718 0 0
128kb <= PGA < 256kb 35 0 0
256kb <= PGA < 512kb 20 0 0
512kb <= PGA < 1024kb 21454 44 2
1mb <= PGA < 2mb 2688 12 0
2mb <= PGA < 4mb 0 0 2
4mb <= PGA < 8mb 0 2 4
8mb <= PGA < 16mb 0 0 7
16mb <= PGA < 32mb 2 0 2
32mb <= PGA < 64mb 4 0 4
64mb <= PGA < 128mb 2 0 1
Anyway, the most important question here is:
When does Oracle start to use MANUAL (even when AUTO is specified) and thus using SORT_AREA_SIZE?
Thanks.
April 03, 2003 - 7:28 am UTC
are you using mts?
did you alter system to set the workarea size policy?
if so, the shared servers that were started -- still are manual, until 10g
Followup to previous
Christo Kutrovsky, April 03, 2003 - 8:25 am UTC
No, I am not using MTS, all sessions are dedicated.
Yes I did:
alter system set workarea workarea_size_policy = AUTO;
Although it is in AUTO for the system, and i did 'show parameterr workarea' which confirmed it is in AUTO. However, in V$SQL_WORKAREA I can see it at MANUAL.
April 03, 2003 - 8:36 am UTC
then you are looking at old statistics -- from before you did the alter system.
run for a couple more days and the workarea's will outweight the manual way.
the stats don't "flush" at the alter system -- so you have people who used manual and until you bounce, they'll stay in the v$ table that way. (and if you bounce, make sure the workarea size policy is set right in your init.ora or spfile.
Followup
Christo Kutrovsky, April 03, 2003 - 7:25 pm UTC
No, I am afraid this is not the case. I run a query, then find it in v$sql, get the hash_value and query V$SQL_WORKAREA with that.
And I am 100% positive, that the query I just executed, is executed in MANUAL, although I have AUTO for the session (and database). If you insist, I will do it again and paste my steps here.
Besides, as I played arround with the SORT_AREA_SIZE I could see the change in V$SQL_WORKAREA the allocated memory got changed, so I am absolutelly positive that I had it auto but Oracle executed in MANUAL.
I think, that there is some condition, which when met Oracle starts executing in manual (for example when target_pga is way off) I am just trying to find the condition.
April 03, 2003 - 9:23 pm UTC
tell you what -- set it so auto is in the spfile/init.ora and restart. that'll clear out ANY confusion.
Followup
Christo Kutrovsky, April 03, 2003 - 10:29 pm UTC
That's how it's been setup for months. And the base is boucing every week (for offline backup).
So I am certain, that it IS on auto. It's never been changed from the default.
I hope this helps (just did it):
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Apr 3 22:10:53 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning and Oracle Label Security options
JServer Release 9.2.0.1.0 - Production
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
SQL> show parameter pga_aggregate_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 39845888
SQL>
SQL> declare
2 CURSOR XCUR IS
3 select 123 from activities_values
4 order by free_value;
5 P NUMBER;
6 begin
7 open XCUR;
8 FETCH XCUR into P;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select sql_text, sorts, loaded_versions, first_load_time,hash_value from v$sql where sql_text like 'SELECT 123%';
SQL_TEXT
--------------------------------------------------------------------------------
SORTS LOADED_VERSIONS FIRST_LOAD_TIME HASH_VALUE
---------- --------------- ------------------- ----------
SELECT 123 from activities_values order by free_value
2 1 2003-04-04/06:14:36 28529089
SQL> select * from v$sql_workarea where hash_value = 28529089;
-- result has been altered, to be more readable
ADDRESS 511F06F0
HASH_VALUE 28529089
CHILD_NUMBER 0
WORKAREA_ADDRESS 528327BC
OPERATION_TYPE SORT
OPERATION_ID 1
POLICY MANUAL
ESTIMATED_OPTIMAL_SIZE 6018048
ESTIMATED_ONEPASS_SIZE 1011712
LAST_MEMORY_USED 484352
LAST_EXECUTION 2 PASSES
LAST_DEGREE 1
TOTAL_EXECUTIONS 2
OPTIMAL_EXECUTIONS 0
ONEPASS_EXECUTIONS 0
MULTIPASSES_EXECUTIONS 2
ACTIVE_TIME 1232350
MAX_TEMPSEG_SIZE
LAST_TEMPSEG_SIZE
SQL> alter session set sort_area_size = 1000000;
Session altered.
SQL>
SQL> declare
2 CURSOR XCUR IS
3 select 123 from activities_values
4 order by free_value;
5 P NUMBER;
6 begin
7 open XCUR;
8 FETCH XCUR into P;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select * from v$sql_workarea where hash_value = 28529089;
-- altered result again, we have 2 rows
ADDRESS 511F06F0
HASH_VALUE 28529089
CHILD_NUMBER 1 <----------- this is the SECOND run
WORKAREA_ADDRESS 51C67470
OPERATION_TYPE SORT
OPERATION_ID 1
POLICY MANUAL
ESTIMATED_OPTIMAL_SIZE 5990400
ESTIMATED_ONEPASS_SIZE 1010688
LAST_MEMORY_USED 909312 <----------- see it's double now
LAST_EXECUTION 1 PASS
LAST_DEGREE 1
TOTAL_EXECUTIONS 1
OPTIMAL_EXECUTIONS 0
ONEPASS_EXECUTIONS 1
MULTIPASSES_EXECUTIONS 0
ACTIVE_TIME 1184246
MAX_TEMPSEG_SIZE
LAST_TEMPSEG_SIZE
------------------------------------
ADDRESS 511F06F0
HASH_VALUE 28529089
CHILD_NUMBER 0 <----------- this is the FIRST run
WORKAREA_ADDRESS 528327BC
OPERATION_TYPE SORT
OPERATION_ID 1
POLICY MANUAL
ESTIMATED_OPTIMAL_SIZE 6018048
ESTIMATED_ONEPASS_SIZE 1011712
LAST_MEMORY_USED 484352
LAST_EXECUTION 2 PASSES
LAST_DEGREE 1
TOTAL_EXECUTIONS 2
OPTIMAL_EXECUTIONS 0
ONEPASS_EXECUTIONS 0
MULTIPASSES_EXECUTIONS 2
ACTIVE_TIME 1232350
MAX_TEMPSEG_SIZE
LAST_TEMPSEG_SIZE
Your time is highly appreciate, as always. Again, I am just trying to clarify when (and why) it starts using manual when we have setup for auto.
April 04, 2003 - 6:22 am UTC
I'll have to ping you over to support on this one -- I cannot reproduce.
your init.ora seems to be set to auto and pga_aggregate_target is set.
ops$tkyte@ORA920> show parameter pga_agg
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 16777216
ops$tkyte@ORA920> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
ops$tkyte@ORA920> set autotrace traceonly
ops$tkyte@ORA920> select * from all_objects order by 1, 2,3, 4, 5, 6, 7, 8;
30515 rows selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
319 recursive calls
7 db block gets
148571 consistent gets
886 physical reads
120 redo size
2183437 bytes sent via SQL*Net to client
22873 bytes received via SQL*Net from client
2036 SQL*Net roundtrips to/from client
12 sorts (memory)
1 sorts (disk)
30515 rows processed
ops$tkyte@ORA920> select address from v$sql where sql_text like 'select * from all_objects order by %';
ADDRESS
--------
55867C18
1 row selected.
ops$tkyte@ORA920> @printtbl8 'select * from v$sql_workarea where address = "55867C18" '
ADDRESS : 55867C18
HASH_VALUE : 431721971
CHILD_NUMBER : 0
WORKAREA_ADDRESS : 55847FA8
OPERATION_TYPE : SORT
OPERATION_ID : 1
POLICY : AUTO <<<<==========
ESTIMATED_OPTIMAL_SIZE : 3898368
ESTIMATED_ONEPASS_SIZE : 859136
LAST_MEMORY_USED : 843776
LAST_EXECUTION : 1 PASS
LAST_DEGREE : 1
TOTAL_EXECUTIONS : 1
OPTIMAL_EXECUTIONS : 0
ONEPASS_EXECUTIONS : 1
MULTIPASSES_EXECUTIONS : 0
ACTIVE_TIME : 5772269
MAX_TEMPSEG_SIZE : 4194304
LAST_TEMPSEG_SIZE : 4194304
-----------------
PL/SQL procedure successfully completed.
Phil Quinn, April 09, 2003 - 4:58 am UTC
Good user-friendly explanation.
However, what is the 'PGA Advisory', and how do you use/access it ?
April 09, 2003 - 9:53 am UTC
You either query it (as I did -- the target advice view)
Or you use statspack and statspack tells you about it.
Or you use OEM and get a pretty chart about it.
Why Oracle is choosing ONEPASS Execution eventhough I have enough free space?
A reader, April 21, 2003 - 4:24 pm UTC
Here are the statistics from my server (9.2.0.1 version)
1. select name,
to_char(decode( unit,
'bytes', value/1024/1024,
value ),'999,999,999.9') value,
decode( unit, 'bytes', 'mbytes', unit ) unit
from v$pgastat
aggregate PGA target parameter 500.0 mbytes
aggregate PGA auto target 444.0 mbytes
global memory bound 25.0 mbytes
total PGA inuse 6.7 mbytes
total PGA allocated 35.9 mbytes
maximum PGA allocated 234.0 mbytes
total freeable PGA memory 9.8 mbytes
PGA memory freed back to OS 6,583.1 mbytes
total PGA used for auto workareas .0 mbytes
maximum PGA used for auto workareas 154.2 mbytes
total PGA used for manual workareas .0 mbytes
maximum PGA used for manual workareas .5 mbytes
over allocation count .0
bytes processed 35,829.3 mbytes
extra bytes read/written 2,480.2 mbytes
cache hit percentage 93.5 percent
2. SELECT
case when low_optimal_size < 1024*1024
then to_char(low_optimal_size/1024,'999999') ||
'kb <= PGA < ' ||
(HIGH_OPTIMAL_SIZE+1)/1024|| 'kb'
else to_char(low_optimal_size/1024/1024,'999999') ||
'mb <= PGA < ' ||
(high_optimal_size+1)/1024/1024|| 'mb'
end pga_size,
optimal_executions,
onepass_executions,
multipasses_executions
from v$sql_workarea_histogram
where total_executions <> 0
order by low_optimal_size
PGA_SIZE OPTIMAL_EXC ONEPASS MULTIPASSES
16kb <= PGA < 32kb 996881 0 0
32kb <= PGA < 64kb 34264 0 0
64kb <= PGA < 128kb 13749 0 0
128kb <= PGA < 256kb 2277 0 0
256kb <= PGA < 512kb 1487 0 0
512kb <= PGA < 1024kb 5465 0 0
1mb <= PGA < 2mb 554 0 0
2mb <= PGA < 4mb 350 0 0
4mb <= PGA < 8mb 549 0 0
8mb <= PGA < 16mb 168 0 0
16mb <= PGA < 32mb 9 23 0
32mb <= PGA < 64mb 1 9 0
64mb <= PGA < 128mb 0 12 0
128mb <= PGA < 256mb 0 4 0
Why the DB using OnePass (disk) execution even though I have enough free space (memory)?
The maximum PGA allocated was 234 Mb. It means (aggregate PGA auto Target – maximum PGA allocated, 444Mb – 234 Mb = 210 MB) the server has 210 Mb when it was in peck also.
Why it used disk operations when the execution size is > 16 mb?
How to find out the process/sql query that uses >16 Mb pga size?
Thanks
April 21, 2003 - 10:32 pm UTC
because a single process will NEVER use all memory -- it'll use a fraction of it (where the fraction is less then one)
Thanks, but still not clear :)
A reader, April 22, 2003 - 2:24 am UTC
Sorry for my dumb brain:) I guess that I was able to understand it partly. Can you throw some more light?
What do you mean by “(where the fraction is less then one)?
And you didnÂ’t answer my other question.
>>How to find out the process/sql query that uses >16 Mb pga size?
Is there any way to find out the queries/transaction that took >16 Mb PGA size in my example.
Thanks
April 22, 2003 - 7:45 am UTC
100/1 is a fraction. it is greater than 1
1/100 is a fraction. it is less than 1.
the max pga memory used by a SESSION will be some fraction (that is less than one) of the aggregate target. no single session would ever use it ALL
You can use temp_space in v$sql_plan to find queries that are estimated to exceed that. Since a single query plan is shared -- and on one run it might use 1m, the next 5m and it might be run a million times -- we do not track it at the exact sql level.
Thanks for your comments
A reader, April 22, 2003 - 2:23 pm UTC
one process takes 5% of pga_aggregate_target
Sravan, April 28, 2003 - 1:43 pm UTC
" no single session would ever use it ALL "
Correct. A single process uses upto 5% of the pga_aggregate_target. If it uses up all the 5%, then it does disk sorts(temp_space being used).
I believe there is a hidden parameter which controls this value for each process not to exceed 5% of the pga_aggregate_target.
On further investigation I realised that in my case it did use more than 5%. On further clarification, I learnt that though oracle controls the pga usage by single process, it can go over the value(5%) and its not a very hard limit.
Want to add to this, anyone ?
Sravan
ps: great site, Tom !!!
April 28, 2003 - 3:46 pm UTC
what case? this is the first followup from you as far as I can tell. no context here at all.
it is really a function of two hidden parameters, not just a n% number at work.
clarification on Over allocation count
Sravan, April 28, 2003 - 2:04 pm UTC
" Over allocation count "
I am trying to understand this concept. So, please bear with me.
So lets say, if A process uses more than a fraction(5%) of pga_aggregate_target, it uses the temp_space for a disk sort. Right ?
And if more than 20 such processes(each using 5%) are run at the same time, then oracle allocates more pga memory over and above the the pga_aggregate_target.
Am I correct in my understanding ?
Thanks again
Sravan
April 28, 2003 - 3:49 pm UTC
No, not really -- it is as I said:
...
Over allocation count (Oracle9iR2 and up): the number of times Oracle had to
allocate more PGA memory then the PGA_AGGREGATE_TARGET suggested. This
indicates the PGA target was set too small to accommodate the untunable (session
context, cursor caches, etc) memory plus the tunable component. This count
should be zero ideally. On the system I was using, we started the
PGA_AGGREGATE_TARGET at 25megabytes and increased it to 250megabytes and finally
settled in on 432megabytes at the suggestion of "the database".
....
we can control the sort/hash etc area sizes at run time. We have no control over these other untunable (by us at run time) things. It can be that the sum of these untunables + the workareas exceed the TARGET and that is an overallocation.
5 % ?
Sravan, April 28, 2003 - 9:23 pm UTC
Sorry Tom. My apologies about the "my case". I was not completely correct about the 5% either.
From metalink article Note:147806.1
" PGA_AGGREGATE_TARGET limits both the global PGA consumption and the size of a workarea i.e. the memory allocated to a single SQL operator is also limited to min(5% PGA_AGGREGATE_TARGET, 100MB) for serial operations, and to 30% PGA_AGGREGATE_TARGET/DOP for parallel operations (DOP=Degree of Parallelism). "
Sorry about the confusion !!
-Sravan
Disabled parameters
juancarlosreyesp@yahoo.com, May 02, 2003 - 4:01 pm UTC
Very interesting answer.
Tom, please
1) Which parameter should not be touched once you set
WORKAREA_SIZE_POLICY = AUTO
PGA_AGGREGATE_TARGET = xM
2) Under the previous setting, if you set one of that parameter does it affect or Oracle simply ignores them.
:) Thanks
May 02, 2003 - 4:06 pm UTC
1) not that they "shouldn't be" more that "won't affect anything"
*_size parameters
2) ignores it.
please more specific
juancarlosreyesp@yahoo.com, May 02, 2003 - 4:50 pm UTC
Thanks tom
Could you please be more specific
I did a select *_size and I got this
or did you want to say sort*_size
Thanks
select name from v$parameter where name like '%_size'
NAME shared_pool_size sga_max_size shared_pool_reserved_size large_pool_size java_pool_size java_max_sessionspace_size db_block_size db_keep_cache_size db_recycle_cache_size db_2k_cache_size db_4k_cache_size db_8k_cache_size db_16k_cache_size db_32k_cache_size db_cache_size global_context_pool_size create_bitmap_area_size bitmap_merge_area_size parallel_execution_message_size hash_area_size max_dump_file_size oracle_trace_collection_size object_cache_optimal_size sort_area_size sort_area_retained_size olap_page_pool_size
May 02, 2003 - 7:44 pm UTC
sort area
hash area
bitmap merge area
guess i should say "area%size"
great architectural discussion
Alberto Dell'Era, May 02, 2003 - 5:26 pm UTC
A reader, May 02, 2003 - 7:52 pm UTC
thanks :)
predicte pga_aggregate_target
A reader, May 21, 2003 - 9:23 pm UTC
To predicte pga_aggregate_target, if the parameter PROCCESSES=200. AS your advice, 1M for each proccesses, then we have:
pga_aggregate_target = 1*200=200M.
200M of the PGA is good for 200 concurrent users of a OLTP (very little on sorting)?
Thanks many
May 23, 2003 - 7:38 am UTC
it would be on the low end, but it would be a starting point and the pga advisor will help you adjust it up or down if needed.
A reader, June 04, 2003 - 4:13 pm UTC
Is there any side effects of setting pga_aggregate_target too high? Can it cause performance degradation as long as we ensure PGA+SGA is within real memory and does not page?
June 04, 2003 - 4:51 pm UTC
it'll affect plans -- it'll be like setting sort_area_size really high.
but if you set it to the size you would like to use given REAL memory (really available memory), that is what you want to do.
Excellent
Nasir Sarwar, June 04, 2003 - 5:32 pm UTC
When I started like this new 9i feature, I found out that an HP known "BUG" cause memory leakage when setting pga and work area policy to 'AUTO'. Though it is fixed in 9.2.0.2 and later, these patch set would not allow to install/de-install specific components at ease. Oracle mentioned that the new release will be 10i, so I think I will have to wait to get the benefit of pga and work area "AUTO" on HP. To test it, set pga like 25 MB and work area policy to 'AUTO', Log in from SQL*Plus on a machine other than the machine on which the HP-UX database resides many time till you hit ORA-27102: out of memory. Now unset pga (comment out) and work area policy to manual. Log in to the database as many time you like. You wll not hit ORA-27102.
Thanks
A reader, June 06, 2003 - 11:05 am UTC
I didn't understand your answer.
>>it'll affect plans -- it'll be like setting sort_area_size really high.
What are the disadvantages when setting this memory too high?
June 06, 2003 - 11:16 am UTC
say you have 100meg of free memory.
say you set sort_area_size to 20m
say you know you have 100 users.
say you know that 10 of them will be active concurrently and could be doing sorts.
you now have a system where you've given the users the OK to allocate 200m of ram at the same time -- but you only have 100meg of ram. swapping/paging heavily = in your future. that is not good.
why sort_area_size?
Reader, June 06, 2003 - 7:27 pm UTC
I thought if you set pga_aggregate_target and workarea_size_policy=auto then, no need to set sort_area_size. Oracle will auto manage allocating sort area memory to users dynamically depending upon the load/number of users etc. Is it correct?
I also thought oracle would ignore sort_area_size if i had set pga_aggregate_target? Please correct me. Thanks.
If I had set pga_aggregate_target and did not set workarea_size_policy=auto then, what Oracle would do? Thanks.
June 06, 2003 - 8:20 pm UTC
correct.
correct.
oracle would ignore pga_aggregate_target if workare_size_policy = manual and use the *_size settings instead., oracle will use pga_aggregate_target if it is auto and ignore the *_size settings.
workarea_size_policy=auto and query plan
Justin, June 10, 2003 - 1:13 am UTC
Is it possible that Oracle selects different plan depending on the available memory, something like: enough memory – hash join, not enough memory - nested loop?
June 10, 2003 - 8:28 am UTC
yes. just like dynamically changing the sort_area_size/hash_area_size would
Excellent!!!
Peter, August 04, 2003 - 6:57 am UTC
The gospel according to Tom...I see the light!
Thanks!!!
PGA information
bhavesh, November 04, 2003 - 9:00 am UTC
hi;
The article was very usefule to me. I have made some changes on the basis of article. Let's see how fast i get benifits
automatic PGA setting..
Nitin Kumar, December 24, 2003 - 11:16 am UTC
Hi Tom,
We are running 9.0.1.4 and I changed the pga_aggregate_target to 25 Mb.
This is what I see.
SYS@TSTPDB01 >ed
Wrote file afiedt.buf
1 SELECT name, value
2* FROM V$PGASTAT
SYS@TSTPDB01 >/
NAME VALUE
---------------------------------------- ----------
aggregate PGA auto target 10485760
global memory bound 1280
total expected memory 0
total PGA inuse 42614784
total PGA allocated 58347520
maximum PGA allocated 207346688
total PGA used for auto workareas 0
maximum PGA used for auto workareas 13384704
total PGA used for manual workareas 0
maximum PGA used for manual workareas 5666816
estimated PGA memory for optimal 0
maximum PGA memory for optimal 61865984
estimated PGA memory for one-pass 0
maximum PGA memory for one-pass 61865984
14 rows selected.
Since the maximum PGA used for auto workareas is more than aggregate PGA Auto target does it mean I should increase my pga_aggregate_target?
Thanx
December 24, 2003 - 11:25 am UTC
you know that the pga aggregate target is for the INSTANCE, not a session - so... 25m is teeny tiny!
see the review that contains:
We'll start with Oracle9iR1 techniques
above to get the advice on what to do.
Nitin Kumar, December 24, 2003 - 11:32 am UTC
Thanx for the quick response Tom.
1) How do I find the opitmal value for pga_aggregate_target in 9iR1.
2) When I run this query, I see this.
optimal workarea executions is 99%.
Is this not good or shall I set paga_aggregate_value high so that the workarea executions - multipass is close to 0.
SYS@TSTPDB01 >l
1 SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percent
age
2 FROM (SELECT name, value cnt, (sum(value) over ()) total
3 FROM V$SYSSTAT
4* WHERE name like 'workarea exec%')
SYS@TSTPDB01 >/
PROFILE CNT
---------------------------------------------------------------- ----------
PERCENTAGE
----------
workarea executions - optimal 127050
99
workarea executions - onepass 701
1
workarea executions - multipass 580
0
December 24, 2003 - 11:38 am UTC
session settings
A reader, January 03, 2004 - 3:14 pm UTC
Hi
In our DWH etl processes we give quite big hash_area_size for batch jobs, before each process we do
alter session set sort_area_size=64000000;
alter session set hash_area_size=64000000;
We are going to migrate to 9i and use this new automatic PGA thing. My question is if we use automatic PGA management will these "alter session " still be valid???
January 03, 2004 - 3:42 pm UTC
valid yes.
obeyed, or even looked at?
no.
if your workarea policy is auto, pga_aggregate_target is used.
if your workarea policy is manual, sort area, hash area are used.
You can set this at the session level for big batches if you like (the work area policy)
alter session
A reader, January 03, 2004 - 4:10 pm UTC
Hi
Normally when we do alter session these sort and hash parameters most of the times we modify CBO behaviour but these are necesary since a bigger hash* and sort* make batch jobs faster. If we use automatic PGA management for batch jobs and set a very high pga_aggregate_target value will Oracle be so clever and set a high sort and hash areas when there are lots of free memory in pga_aggregate_target? I mean if this is the case we are going to see batch jobs change their plan probably everyday and become unstable?
Our database is a CRM, OLTP, Batch and some Reporting mixed.
Anyone share their real world experience using automatic PGA management in Hybrid databases?
TIA
January 03, 2004 - 4:55 pm UTC
if there are few other users, and the pga aggregate target is set, getting 64m of SAS and HAS won't be an issue.
yes but
A reader, January 03, 2004 - 5:23 pm UTC
Hi
The problem is since if we are using AUTO pga management Oracle wont look at our alter session statements so how will Oracle assign a 64M when there are necessary? How can he know, obviously not. So I guess manual is the way to go for Batch Jobs and DWH
January 03, 2004 - 5:49 pm UTC
that is exactly the point.
the database looks at:
a) what you are asking to do
b) who else is asking to do things
c) how much ram you've said "i'm willing to dynamically allocate for the database"
and gives you as much as it can, without giving you so much that the other users would be starved.
it is an adaptable algorithm for memory allocation based on current workload. You tell us what you want to divvy up, and we do so.
from your last reply
A reader, January 07, 2004 - 7:00 am UTC
You said Oracle will look what we are asking for, how does he know that? I mean query plans are dependent on PGA settings (at least in 8i), I am not sure how can Oracle determine an optimum plan for a query if he does not know any PGA settings to calculate the costs for sort/hash/nested loop joins? I guess he takes pga_aggregate_target and subtracts the used memory and assuming the rest can be used for a query?
Also in one of your replies you said you tested with 6 or 7 queries which you know they need 6mb sort area size... How did you calculate that?!
January 07, 2004 - 8:25 am UTC
oracle DOES know the pga settings, it is simply called "pga_aggregate_target" in 9i instead of *_size.
which reply? then I can answer that (probably used all_objects with a known key size and a known number of rows and multiplied -- but refresh my memory and show me which reply that was and I'll clarify)
the reply which says
A reader, January 07, 2004 - 8:33 am UTC
It is now saying we could go down to 27mb! But, since we just reset that value
- we know we must wait a while - until some activity occurs - and query it
again. For example - I just hit the system with 30 processes I know will take
about 6-7meg of sort area each. After running that (and not much else), the
advice is:
January 07, 2004 - 8:42 am UTC
you use the "advisors" when you are running at "normal load"
if you let the machine sit idle for a while, it is going to say "don't need very much here"
if you monitor the machine when it is busy, it'll tell you "this is what you need at this load"
(seems sensible no? if you ask me how busy I am at 10pm at night, most of the time I'll say "not very busy at all". Ask me at 10am in the morning and most of the time I'll say "man, I'm jammed -- totally swamped"). So, what do I need to size my caloric intake, my stress levels, etc for? 10pm or 10am?)
Max session memory?
Christo Kutrovsky, January 07, 2004 - 9:24 am UTC
Tom,
I have tried to setup the PGA_AGGREGATE_TARGET instead of the *_size parameters, however I ran into a strange issue.
It was refusing to give mor than ~90 Mb of memory for sorting of a given session. Even thow I was setting PGA_AGGREGATE_TARGET to insane values like 16'000 Mb. Setting back to manual, and sort_area_size to 400 Mb, the same query used ~150 Mb of memory.
Is there such a hard-coded limit of ~90 Mb ?
January 07, 2004 - 6:11 pm UTC
the real question is - which performed better or more then good enough.
PGA settings and Transaction Monitors
A reader, January 07, 2004 - 4:34 pm UTC
Hi
I wonder what PGA management should we use in a environment where Transaction Monitor (such as Bea Tuxedo) is used or where a connection pool is used? Should we use Automatic or Manual? I am wondering because these two is very similar to MTS
January 08, 2004 - 11:25 am UTC
if you are using MTS connections -- in 9i, it will always be manual.
so, the question comes does to "which type of connections are you using"
Follow up for Tom
Christo Kutrovsky, January 07, 2004 - 6:46 pm UTC
> the real question is - which performed better or more then good enough
The One that was using automatic settings and used 90 Mb of memory for sorting also used ~80 Mb of temporary space and was running for about 150 seconds.
The One that used manual settings and used ~160 Mb of memory for sorting, ran for about 100 seconds.
The difference is significant in favor for manual settings.
I could probably reproduce the cases and paste here with full details.
January 08, 2004 - 12:41 pm UTC
yes there are limits to the amount of ram used by a session with pga_aggregate_target.
In my testing -- larger sort area sizes
a) sometimes go faster
b) sometimes go way slower (like a alot slower)
In most cases -- for ad-hoc queries, for DW type work -- pga_aggregate_target is what you are looking for.
In a controlled environment, like "i'm the only guy running this batch right now, no one else on" using fine tuned SAS/HAS might make sense.
But - consider what happens on your system when
a) you use pga_aggregate_target and have lots of users sorting (on my systems, the sort workarea is backed off, everyone runs as good as they can)
vs
b) using a fixed size SAS with lots of users sorting (on my systems, we swap and elapsed time goes through the roof)
that is the problem the pga aggregate target is solving -- what happens when you have a varying number of users over the course of the day -- do you size SAS for:
1) the fewest (meaning too big for peak and meaning really bad -- really bad performance then)
2) the average (meaning at small usages -- not near optimal, at large usage -- really bad -- only during average is it good)
3) the peak (meaning really small sizes all of the time)
.
Christo Kutrovsky, January 08, 2004 - 1:36 pm UTC
I wonder why this limit is not mentioned into the documenation. I am correct that it is 90 Mb?
I would like to use pga_aggregate_target, but the issue I am facing is the following.
The database is DW style, and has a connection pool. A lot if inactive sessions. Experimentally, i have discovered that oracle divides pga_aggregate_target to the number of sessions, and gives to a new session a maximum of the result. In my case, I have 20 sessions, and pga_target at 1 Gb. That's 50 Mb per session. My initial expectation were that non-sorting sessions would not be counted, and in this case more memory would have been available.
What would recomend in my situation?
1. Use a larger then expected pga_aggergate_target, to compensate for idle sessions
2. Use manual settings.
I understand, that any moment an idle session can start consuming memoring for sorting, but it's almost the same as making a new connection to the database, which will also cause memory re-distribution.
January 08, 2004 - 3:29 pm UTC
well, as with ALL things "auto", the details are left -out- (else, it would not be so auto anymore).
it is a % of the total pga which has also a cap and is subject to change and in fact already HAS changed.
It was funny -- on my system, I did the test and found that whilst the pga_aggregate_target used TEMP, it did so in a fashion that caused it to run 3 times faster then using a sort area size (that went to swap).....
use the 95/5 rule here -- 95% of the time (in 95% of the situations), pga aggregate is more then appropriate, better than good enough. in 5% of the cases, we may find it is not appropriate. You might be a case where you say "nope, i don't want auto-pilot, i prefer to DIY"
sort_area_size and optimal executions
A reader, March 19, 2004 - 11:58 am UTC
Tom,
you mentioned earlier in this thread the following :
-----
In Oracle9iR2 and up - there is another crucial view for measuring the effectiveness of the PGA workareas - that is the V$SQL_WORKAREA_HISTOGRAM view. This view breaks the PGA workarea sizes into discrete ranges (eg: executions that used between X and Y bytes of PGA workarea) and keeps counters of how many times:
o Operations were done in "optimal" mode, optimal being totally in memory
o Operations were done in "one pass" mode, some disk was used but used in a one pass mode - limited IO was performed
o Operations were done in "multi-pass" mode, the operation was very disk intensive
-----
I am on HP 9i with workarea_size_policy = MANUAL. I use SAS/HAS. I understand that the above statistics are gathered for WORK_AREA_POLICY=AUTO setting.
But If I use SAS/HAS, how I can find out the # of passes for my operation ? I am in the process of finding the optimal value for my sort_area_size
Thanks
March 19, 2004 - 2:05 pm UTC
you cannot, you would have to be using auto. else it is just "sorted to disk or did not sort to disk"
thanks
A reader, March 20, 2004 - 12:07 am UTC
Thanks Tom.
So, there is no alternate way to find out if query was sorted within sort_area_size OR spilled over sort_area_size and went to disk ?
Then how would we tune Sort_area_size value ?
March 20, 2004 - 10:00 am UTC
you use pga_aggregate_target and automatic workarea policys :)
either that or you monitor for sorts to disk and watch v$sort_segment/v$sort_usage to see what people are doing over time and up/down it as needed.
very hard to manually tune SAS - is there one perfect number for all users on your system? probably not, that is what the pga target is all about.
map()/unmap() for automatic PGA memory management only ?
Alberto Dell'Era, March 23, 2004 - 8:51 am UTC
> One last word about automatic PGA memory management. On operating systems that
> allow for it, the PGA memory is actually physically de-allocated,
> released back to the operating system. Instead of using traditional
> process heap memory allocated via the C routines malloc(),
> PGA workareas can be allocated via map() and unmap() memory calls instead.
Does this apply only to automatic PGA memory management, or to manual as well ?
tnx
Alberto
March 23, 2004 - 9:34 am UTC
only auto.
need help regarding pga tuning
reader, April 01, 2004 - 11:58 am UTC
I am wondering about steps to tune PGA_AGGREGATE_SIZE.
like fist query v$pgastat.
Which gives u the current values.
Then look for may be
V$SQL_WORKAREA_ACTIVE ?
But u get only active query statistics here?
I am little confused.
Can u give steps....to follow ?
I did check metalink note
Automatic PGA Memory Managment in 9i
Still not very clear abt steps.
April 01, 2004 - 12:08 pm UTC
use the pga summary advisor in statspack or OEM.
they both tell you "if you use this setting, this will be the expected outcome"
A reader, May 07, 2004 - 2:07 am UTC
Tom,
I read most of your reviews from this site on pga_aggregate_target/workarea_size_policy.
I came across a article, though i am not convinced on this --- Do you mind giving your thoughts in this?
.....Importantly, the AUTO setting is really only going to be of use in a DSS/Data Warehouse environment, because Users in OLTP environments tend not to run huge reports that require large sort_area_sizes. Putting it another way, the PGA consists of the sort_area_size, plus the stack space and the cursor area Œand in OLTP environments, the stack space and cursor areas are probably going to account for the major proportion of the PGA– which are the untunable parts of the PGA. Hence, the AUTO setting is likely not going to achieve much in such environments.
Thanks a lot
May 07, 2004 - 8:10 am UTC
they are saying "in pure oltp, we don't sort large sets, we don't hash join large things. therefore, we do not use large sort areas, large hash areas. therefore, setting to AUTO is the same as setting to MANUAL since the workareas will all be very small regardless"
It is not that is it "of no use" in OLTP. It is that you won't be seeing lots of different sized workareas since they are all small.
Basically -- it is 6 one way, 12 divided by 2 another. (eg: same net result at the end) with manual vs auto in an environment that doesn't really use differing sizes of sorts/hashes.
A reader, June 25, 2004 - 6:42 pm UTC
Tom,
I have set my PGA_AGGREGATE_TARGET to 750M and executed a query. The query took approximately 4 minutes. The sorts were done both in memory and disk(4 sorts). I increased the parameter value to 3Gig same performance with sorting in disk(2 sorts) and increased even upto 5 Gig. The response time is still same, the query still sorted in disk(2 sorts) and I felt there was no difference between 750M and 5Gig with regard to query response time.
Any comments on this.
Thanks,
June 26, 2004 - 1:25 pm UTC
You will only use upto about 5% of the pga_aggregate_target in any given session - it'll never give you the entire thing.
pga_aggregate_target is designed to make a multi-user, reporting type database "self tuning". It is assuming "multi-user" (eg: do not ever give all resources to a single individual). as the load goes up, the amount of memory given to you will go down.
If you have some extreme batch jobs -- for which you know they should use "everything you got", using an alter session to use manual and setting sort/hash area sizes is perfectly OK. For your day to day users -- where you cannot be fine tuning sort/hash area size by user or query or whatever -- pga_aggregate_target gives you the best overall "use of memory".
A reader, June 26, 2004 - 4:26 pm UTC
<quote>
If you have some extreme batch jobs -- for which you know they should use "everything you got", using an alter session to use manual and setting sort/hash area sizes is perfectly OK.
<quote>
I dont have my database in front of me now, but if I have workarea_size_policy=auto and pga_aggregate_target set wouldnt ALTER SESSION set *_area_size=N give me an error? And I wouldnt want to give a regular user ALTER SYSTEM privilege to change workarea_size_policy/pga_aggregate_target.
June 26, 2004 - 7:05 pm UTC
nope, do it all of the time. the workarea_size_policy is SESSION settable and you can set sort/hash area size regardless of its setting.
PGA CONCEPT IS CLEAR
FRIEND, June 27, 2004 - 8:13 am UTC
ONE OF THE BEST THREAD
Supporting data
A reader, June 29, 2004 - 7:02 pm UTC
I am taking a Oracle Univ 9iR2 Pref Tuning class and the instructor contradicts all your assertions about PGA_AGGREGATE TARGET. Lets take then one by one
<quote>
You will only use upto about 5% of the pga_aggregate_target in any given session - it'll never give you the entire thing.
<quote>
He said that on a single-user system, if the PGA_AGGREGATE_TARGET is set to, say, 1GB, the single user will get all of it! Can you prove otherwise?
<quote>
it is dynamic and not preallocated.
<quote>
Again, he said, that similar to the SGA, the PGA_AGGREGATE_TARGET is allocated in its entirety at instance startup. If it is dynamically modified (increased or decreased) with an ALTER SYSTEM, it takes effect right away.
<quote>
only if a request to the OS for memory fails would you get
an ora-4030
<quote>
This I tried myself, on a 5GB RAM Linux box (used in the training class), I set
alter system set pga_aggregate_target=1000G;
and it happily said 'System altered'. Sort of disproves that it is pre-allocated right there, right?
Thanks
June 29, 2004 - 8:16 pm UTC
what is this instructors name? seriously, i'd like to contact them. (email it to me, don't post it) tkyte@oracle.com
1) tell them to read <Note:147806.1>
<quote>
PGA_AGGREGATE_TARGET limits both the global PGA consumption and the size of a
workarea i.e.
the memory allocated to a single SQL operator is also limited
to min(5% PGA_AGGREGATE_TARGET, 100MB) for serial operations, and to
30% PGA_AGGREGATE_TARGET/DOP for parallel operations (DOP=Degree of Parallelism).</quote>
2) </code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96533/memory.htm#43234
stress the word DYNAMICALLY. your very simple (perfectly simple) test sort of proves that. are you swapping like mad? if not, hey -- guess what? it did not allocate it.
please, have him read the documentation.
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1158.htm#REFRN10165 <code>
the very definition stresses "target", this is a target -- one that might never be met, one that could well be exceeded.
it would be PHYSICALLY IMPOSSIBLE to pre-allocate it since the "p" in pga stands for PROCESS and the processes come and the processes -- well, they go. it is PROCESS memory.
Manual vs. auto
A reader, June 29, 2004 - 7:13 pm UTC
"nope, do it all of the time. the workarea_size_policy is SESSION settable and you can set sort/hash area size regardless of its setting"
Let me see if I understand this. Suppose I have
PGA_AGGREGATE_TARGET=500M
WORKAREA_SIZE_POLICY=AUTO
in my init.ora. All sessions will use auto PGA management.
1. I can ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL and then my init.ora *_area_size settings kick in. These can be further session-modified using ALTER SESSION
2. Even with WORKAREA_SIZE_POLICY=AUTO in effect, I can still ALTER SESSION my *_area_size settings and these override the auto PGA setting.
Is this correct? Thanks
June 29, 2004 - 8:06 pm UTC
1) correct.
2) incorrect. you need to be manual for them to take effect.
A reader, June 29, 2004 - 8:56 pm UTC
Well, you said
"the workarea_size_policy is SESSION settable and
you can set sort/hash area size *regardless* of its setting"
Thats what led me to conclude "Even with WORKAREA_SIZE_POLICY = AUTO in effect, I can still ALTER SESSION my *_area_size settings and these override the auto PGA setting"
Anyway, you are saying that for my *area_size settings to kick in (either init.ora ones or ALTER SESSION ones), I have to be in policy=manual and I can session-modify this setting.
Thanks, that clears it up.
June 29, 2004 - 9:27 pm UTC
you can set them -- they will be ignored, but you can set them without error.
Pre-allocate PGA target
A reader, June 29, 2004 - 9:58 pm UTC
"stress the word DYNAMICALLY. your very simple (perfectly simple) test sort of proves that. are you swapping like mad? if not, hey -- guess what? it did not allocate it"
Well, it is a little inconsistent w.r.t how the SGA is handled. Suppose I set some impossibly high numbers for some SGA components (db_cache_size, db_keep_cache_size, etc) that are more than the size of my physical RAM, the instance wont even start up. Same thing if I try to ALTER SYSTEM them.
So why does ALTER SYSTEM SET PGA_AGGREGATE_TARGET=1000G succeed on a machine having only 8GB RAM?
Thanks
June 29, 2004 - 10:06 pm UTC
because it is not allocated then, it is allocated as needed - session by session and freed session by session as it is done with it.
it is a target.
you are saying "Oracle, please dynamically allocate up to about 1000g of memory as you need it, try really hard to stay below that number, and be fair about it -- as the users go up, divvy out the memory in a fair fashion for all users"
you are NOT saying "Oracle go out right now and allocate 1000g of ram"
definitely not.
Proof
A reader, June 30, 2004 - 6:06 pm UTC
<quote>
PGA_AGGREGATE_TARGET limits both the global PGA consumption and the size of a workarea i.e. the memory allocated to a single SQL operator is also limited to min(5% PGA_AGGREGATE_TARGET, 100MB) for serial operations, and to
30% PGA_AGGREGATE_TARGET/DOP for parallel operations (DOP=Degree of Parallelism)
</quote>
Is there a way to verify the 5% number above? I tried the following
create table t as select rownum x from all_objects,all_objects where rownum<=2000000;
set autotrace traceonly
select * from t order by x desc;
and then tried to see my memory usage
select name,value from v$mystat a,v$statname b
where a.statistic#=b.statistic#
and name like '%memory%;
but didnt quite see how/where the 5% kicked in?
Help? Thanks
June 30, 2004 - 8:32 pm UTC
comments in bold:
ops$tkyte@ORA9IR2> @connect /
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select to_number(value)/1024/1024, 0.05*value/1024/1024
2 from v$parameter
3 where name like 'pga%';
TO_NUMBER(VALUE)/1024/1024 0.05*VALUE/1024/1024
-------------------------- --------------------
240 12
<b>target is 240 meg, 5% of that is about 12meg</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> column value new_val V
ops$tkyte@ORA9IR2> select a.name, b.value/1024/1024 value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'session pga memory max'
5 /
NAME VALUE
------------------------------ ----------
session pga memory max .382892609
<b>so far we are using about 4/10ths of a meg of page</b>
ops$tkyte@ORA9IR2> set autotrace traceonly statistics
ops$tkyte@ORA9IR2> select *
2 from (select id x
3 from big_table.big_table
4 where rownum <= 2000000
5 ) t
6 order by x desc;
999999 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
2102 consistent gets
1478 physical reads
0 redo size
14246864 bytes sent via SQL*Net to client
733825 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
999999 rows processed
<b>Note the sort to disk -- but also note:</b>
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select a.name, b.value/1024/1024, (b.value-&v)/1024/1024
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'session pga memory max'
5 /
old 1: select a.name, b.value/1024/1024, (b.value-&v)/1024/1024
new 1: select a.name, b.value/1024/1024, (b.value-.382892609)/1024/1024
NAME B.VALUE/1024/1024 (B.VALUE-.382892609)/1024/1024
------------------------------ ----------------- ------------------------------
session pga memory max 13.6953926 13.6953922
<b>that we used a maximum of 13.7meg of pga here -- now, some of that was for sorting (about 12meg) and some for other stuff -- but, we were contained in that about 12meg box. We know we wanted "more" (sort to disk!!!). We also know we got no where NEAR 240meg (proving I think that a single session will not take it all!!! -- we sorted to disk showing we ran out of space to sort in memory, we used 13.7meg of ram, we did not use 240meg of ram...) </b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect /
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter session set sort_area_size = 104857600;
Session altered.
ops$tkyte@ORA9IR2> alter session set workarea_size_policy= manual;
Session altered.
ops$tkyte@ORA9IR2> column value new_val V
ops$tkyte@ORA9IR2> select a.name, b.value/1024/1024 value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'session pga memory max'
5 /
NAME VALUE
------------------------------ ----------
session pga memory max .382892609
ops$tkyte@ORA9IR2> set autotrace traceonly statistics
ops$tkyte@ORA9IR2> select *
2 from (select id x
3 from big_table.big_table
4 where rownum <= 2000000
5 ) t
6 order by x desc;
999999 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2102 consistent gets
0 physical reads
0 redo size
14246864 bytes sent via SQL*Net to client
733825 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
999999 rows processed
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select a.name, b.value/1024/1024, (b.value-&v)/1024/1024
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'session pga memory max'
5 /
old 1: select a.name, b.value/1024/1024, (b.value-&v)/1024/1024
new 1: select a.name, b.value/1024/1024, (b.value-.382892609)/1024/1024
NAME B.VALUE/1024/1024 (B.VALUE-.382892609)/1024/1024
------------------------------ ----------------- ------------------------------
session pga memory max 29.1953926 29.1953922
ops$tkyte@ORA9IR2>
<b>that shows that sort wanted about 28meg of ram - (no sorts to disk see, all done in ram). So, what happens if we up the pga aggregate target:</b>
ops$tkyte@ORA9IR2> @connect /
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter system set pga_aggregate_target = 1006632960;
System altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select to_number(value)/1024/1024, 0.05*value/1024/1024
2 from v$parameter
3 where name like 'pga%';
TO_NUMBER(VALUE)/1024/1024 0.05*VALUE/1024/1024
-------------------------- --------------------
960 48
<b>we'll use upto 48meg</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> column value new_val V
ops$tkyte@ORA9IR2> select a.name, b.value/1024/1024 value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'session pga memory max'
5 /
NAME VALUE
------------------------------ ----------
session pga memory max .382892609
ops$tkyte@ORA9IR2> set autotrace traceonly statistics
ops$tkyte@ORA9IR2> select *
2 from (select id x
3 from big_table.big_table
4 where rownum <= 2000000
5 ) t
6 order by x desc;
999999 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2102 consistent gets
0 physical reads
0 redo size
14246864 bytes sent via SQL*Net to client
733825 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
999999 rows processed
<b>no sort to disk and</b>
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select a.name, b.value/1024/1024, (b.value-&v)/1024/1024
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name = 'session pga memory max'
5 /
old 1: select a.name, b.value/1024/1024, (b.value-&v)/1024/1024
new 1: select a.name, b.value/1024/1024, (b.value-.382892609)/1024/1024
NAME B.VALUE/1024/1024 (B.VALUE-.382892609)/1024/1024
------------------------------ ----------------- ------------------------------
session pga memory max 29.1953926 29.1953922
ops$tkyte@ORA9IR2>
<b>we used the same amount of ram as before</b>
so..... about 5%
Great
A reader, June 30, 2004 - 8:54 pm UTC
Tom, you are simply the best!
Really, I think I learn more from reading any _one_ of the topics on your website than from an entire week of a Oracle Univ class!
More on 5%
A reader, July 01, 2004 - 5:37 pm UTC
Going back to
<quote>
PGA_AGGREGATE_TARGET limits both the global PGA consumption and the size of a workarea i.e. the memory allocated to a single SQL operator is also limited to min(5% PGA_AGGREGATE_TARGET, 100MB) for serial operations, and to
30% PGA_AGGREGATE_TARGET/DOP for parallel operations (DOP=Degree of Parallelism)
</quote>
I see that it doesnt mention "session" anywhere above, just "single SQL operator". So this is what I did
create table t as select rownum x,a.* from all_objects a,all_objects b where rownum<=1000000;
create table t2 as select * from t;
set autotrace traceonly
select * from t,t2 where t.x=t2.x order by t.object_name desc;
The execution plan for this has a ORDER BY (for the object_name) and a MERGE JOIN for the 2 FTSs on T and T2
Now, when I follow the same steps as you did to verify the 5% in your last followup, I see that the number has gone up to 10% i.e. if my PGA_AGGREGATE_TARGET=100M, my session uses 10M!
This is interesting, so 5% is given to each *operation* that needs a workarea.
Questions:
1. Does the same semantic apply to workarea_size_policy=manual and *_area_size? I tried the following
alter session set workarea_size_policy=manual;
alter session set sort_area_size=10000000; -- 10M
Ran the same query above and now my 'session pga memory max' is 30M i.e. the equivalent of *3* sort operations. So, the manual mode seems to be considering each of the SORT(JOIN) operations involved in the MERGE JOIN as a separate operation!
Why is this?
2. In manual mode, is the *_area_size memory allocated as-needed or all at once? The reason I ask is that, in the above example, when I set sort_area_size to 100M, my session pga memory max went up to 132M!
Can you help me figure all this out?
Thanks, as always
July 01, 2004 - 8:19 pm UTC
1) yes, i should have used "per sort" as you can do more than one sort at a time. you can have multiple sort areas using manual as well.
it is just the way manual vs auto works.
2) memory is always allocated piece by piece as needed upto the limit. in auto, in manual. the memory grew as the sort grew, upto the limit you imposed.
the big difference with memory between auto and manual is in manual, the pga doesn't "shrink", in auto -- if the OS allows it, the pga will shrink.
Shrink
A reader, July 01, 2004 - 8:55 pm UTC
"the big difference with memory between auto and manual is in manual, the pga doesn't "shrink", in auto -- if the OS allows it, the pga will shrink"
Regarding shrinking, isnt this why the sort_area_retained_size was introduced in 8i? To do the shrinking after the sort operation was complete?
So, I guess the AUTO stuff is implemented under the covers by the kernel automatically setting the proper retained_size per session? Or is it more sophisticated than that?
"The more users, the less RAM they should use. The less users, the more RAM they should use"
Is this really true? Since each session is limited to a MAX of 5%/sort operation anyway, if the number of users grows, each user would get less than 5%? I guess I am having a little difficulty seeing the advantages of this auto stuff
Thanks
July 01, 2004 - 10:06 pm UTC
no, sort_area_retained_size lets sort_area_size-sort_area_retained_size be returned to your PGA for something else (another sort, a hash, a plsql table, whatever)
the auto stuff is totally different.
if you have 1,000 users, could they all use 5% and still have the database come anywhere close to keeping the amount of ram to the pga_aggregate_target? the 5% is an upper bound, you could use lots less.
Ok, lets say you set sort_area_size to 10m.
if you have 1000 sort requests, that would be 10,000 megs of ram. So, you would not set the sort area size to 10m, maybe to 1m.
But during the times you have just 10 sort requests, wouldn't it be nice to have 100m sort areas? the only way you get that is with a DBA sitting there adjusting sort area size up and down and up and down all day long -- as the load goes up and goes down.
that is what the auto stuff is about. got just a few users, give them what they need. load goes up, back off -- don't start swapping the OS like mad, give them what you got -- not swap space. load goes down, the ram they get goes up, load goes up, the ram they get goes down (without swapping the OS like mad).
that is what it is for -- it is the dba sitting there all day saying "ok, only 10 people -- set sort_area_size 100m, whoops -- 100 people, take it down to 10m, oh my -- 1000, dive dive dive...."
A reader, July 02, 2004 - 7:35 am UTC
"if you have 1,000 users, could they all use 5% and still have the database come anywhere close to keeping the amount of ram to the pga_aggregate_target? the 5% is an upper bound, you could use lots less"
Right, but I still dont understand the design decision behind 5%, seems like an arbitrary number. Suppose I have only a couple of users and my target is 1GB! Why not give these lucky folks everything you've got? As more users come in, you can bump it down anyway.
"that is what the auto stuff is about. got just a few users, give them what they need."
This is what I am saying. 5% is probably much lesser than what they need!
"that is what it is for -- it is the dba sitting there all day saying "ok, only 10 people -- set sort_area_size 100m, whoops -- 100 people, take it down to 10m, oh my -- 1000, dive dive dive...."
Your scenario doesnt compute! SORT_AREA_SIZE is NOT a system-wide dynamic (ALTER SYSTEM/IMMEDIATE) parameter. So, in your example above, if 100 people come in and start to sort, the DBA can do nothing but sit around helplessly!
Another question:
Suppose I follow the 80/20 guidelines for sizing agg_target as per the tuning guide and at a given time, the entire PGA_TARGET is being used. Now another session comes along and sets policy=manual and assigns a sort_area_size=100m. Would Oracle still try to fit this 100m in the PGA_AGG_TARGET or allocate this 100m _outside_ the currently allocated PGA?
Thanks
July 02, 2004 - 10:13 am UTC
every number is arbitrary at the end of the day. it could have been "42" or "12" or whatever.
AUTO is what it is -- AUTO. You are saying "do the best you can, be generic, don't make me think about it too much".
It was designed for the general purpose ad-hoc reporting/dss/DW instance where you have a huge differential in the number of users over time.
5% is 20 simultaneous things -- that is *tiny* today -- think about it, tiny.
I was using an analogy -- cannot you see that? AUTO *IS* the dba sitting there all day fine tuning this memory stuff as time goes on.
once you go manual, you go manual. the pga_aggregate_target is concerned with work area allocations -- that is the only thing we actually have control over.
_pga_max_size
Maurice, July 09, 2004 - 6:15 pm UTC
Hi Tom,
I'm working on DWH Project using Oracle9iR2. Our production servers has 20cpus and around 40Gb of ram. The pga_aggregate_target is set to 16GB and workarea_size_policy is set to AUTO.
The big problem we have is that from these 16GB of PGA only 5GB are used (based on V$PGASTAT). The reason for that is that Oracle never alocates more then 100MB of PGA to a serial or a parallel process. Increasing the usage of the PGA by increasing the DOP is not realy possible because we already have an average of 200 concurrent processes. Most of these processes don't need a lot of PGA, but there are around 30 processes which would need 200-300MB of PGA.
So I got 2 questions for you:
-why is there this 100mb limit? From my point of view it just doesnt make any sense in a dwh environnment.
-why is the parameter _pga_max_size not a public parameter?
Thanks for any feedback
Maurice
July 09, 2004 - 6:38 pm UTC
if you do not like the "auto" in the automatic -- you are free to use the manual approach.
pga_aggregate_target is a general purpose "best" solution. Is more than 100mb of memory in a various sort/hash phase more efficient than 100mb? Have you tried it out.
the auto stuff is to make it so that "memory utilization is 'fair' in a multi-user, dynamic environment"
For the 30 some odd processes that would like to "do it themselves", you can set the workarea policy to manual for them (alter session) and pick your own sort/hash area sizes.
also -- your 200 concurrent processes would be consuming more than 16gb of pga memory if they all hit 100mb
_ parameters are there because we don't want you setting them, they are things we think might need tweaking in extreme cases -- or aren't sure maybe if they need tweaking in real life, so they are hidden. they will come, they will go.
30% of PGA for parallel queries never used
A reader, July 11, 2004 - 12:44 pm UTC
Hi Tom,
Thanks for your quick feedback.
But there is one think I dont understand:
I have a server with 40Gb of RAM
I have set pga_aggregate_target to 16 GB
Based on the Oracle doc that means that I have a max of 5GB (30% of 16Gbytes) of PGA for one query running in parallel. The hidden parameter _smm_px_max_size confirmes me that I have 5033163Kbytes (30% of 16Gbytes) of PGA for parallel queries.
BUT, when I run a query in parallel (DOP 8)on that server with no other active sessions at the same time. I got only 100 MB allocated per parallel process? Why is that so? Why does Oracle not allow this parallel query to use all of these 5GB of RAM but only something like 1GB?
July 11, 2004 - 1:39 pm UTC
so, it is actually exceeding the 614'ish meg by a bit.
the sql operation (being run in parallel) is what is getting the ram, not processes.
Wonderful thread to read top to bottom
Scot, August 05, 2004 - 3:46 pm UTC
But tell me, one more time, why won't Oracle think that the pga agg TARGET is a DIRECTIVE instead of a TARGET? Why won't it grab it all like a DIRECTIVE should? Instead, it treats pga agg TARGET as if it were a TARGET!
And more importantly, why is it that if I have some special MANUAL batch job situation and want to control each aspect of memory MANUALLY, by MANUALLY setting them before, and then MANUALLY setting them back after, why doesn't Oracle automatically know about my MANUAL situation and let me use a size policy of AUTO, and then let me make MANUAL adjustments as needed?
August 05, 2004 - 8:33 pm UTC
well, it is called "target" after all?
you are telling Oracle -- "hey, i have this much real memory for you to use, please try to stay there so I can minimize paging/swapping"
but the target does not GRAB A THING. the target is "this is the upper bound for all sessions" (it would sort of stink if the first session to log in got all 15 gigawads of storage no?? you do want to spread it around a bit?)
we do that second paragraph entirely??? i'm thoroughly confused here -- your batch job would
a) say I want manual
b) say what it wants to do manually
?? or are you saying "geez, you should see by me saying sort_area_size = 43232432 you should set workarea_..... = manual"? if so, well, "sorry". you say auto to have these ignored, you say manual to have them obeyed - tis up to you.
Not what I intended...
Scot, August 24, 2004 - 9:36 am UTC
Sorry about that, I was in a really sarcastic mood the day I posted this. I had just read this thread from top to bottom, and was frustrated that you had to keep saying the same thing over and over again. I tried to use capital letters for sarcastic emphasis, but apparently did a poor job of it.
Can we override Workarea_size_policy by setting sort_area_size at session level
Arul, August 29, 2004 - 10:50 pm UTC
Hello Sir,
SQL> show parameter pga_
NAME TYPE VALUE
------------------------------------ ----------- ---------
pga_aggregate_target big integer 25165824
SQL> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ---------
workarea_size_policy string AUTO
SQL> alter session set sort_area_size=1000000;
Session altered.
SQL> select * from dba_objects order by 1, 2, 3;
So in above scenario,
1> Will optimizer override workarea_size_policy and manually use sort area size ?
2> Which view /column can we find that wether its has used sort_area_size or it went for workarea_size_policy and pga_aggregate_target ?
Please explain...
Thanx in Advance.
August 30, 2004 - 8:26 am UTC
if you set the worarea policy to manual, yes, then the old sort area/hash area sizes are used.
ops$tkyte@ORA9IR2> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
ops$tkyte@ORA9IR2> alter session set workarea_size_policy=manual;
Session altered.
ops$tkyte@ORA9IR2> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string MANUAL
ops$tkyte@ORA9IR2>
v$parameter tells you which is in effect for you.
Maurice, September 05, 2004 - 5:54 pm UTC
From the testings I have done I would say that the following statment of the Oracle Documentation is wrong:
the memory allocated to a single SQL operator is also limited to min(5% PGA_AGGREGATE_TARGET, 100MB) for serial operations, and to 30% PGA_AGGREGATE_TARGET/DOP for parallel operations (DOP=Degree of Parallelism)
Correct would be:
the memory allocated to a single SQL operator is also limited to min(5% PGA_AGGREGATE_TARGET, 100MB) for serial operations, and for parallel operations to min(30% PGA_AGGREGATE_TARGET/DOP, 5% PGA_AGGREGATE_TARGET, 100MB)
(DOP=Degree of Parallelism)
Also I dont understand why _pga_max_size is a hidden/undocumented parameter. For large DWH/DSS applications the default value of this parameter is totaly inadequate.
September 06, 2004 - 9:48 am UTC
well, i totally 100% disagree with your last statement. since there are simple, documented, accepted ways for batch processes (the only things in my experience you want playing with "give me all you got" logic) to get everything they want. But anyway.....
so, share with us the proof? not saying you are wrong on your formula, but hey -- "from the testings you have done", how'd you do the test, what did the test look like, how did you measure the outcome.
query cost and PGA_AGGREGATE size
amit poddar, November 02, 2004 - 6:38 pm UTC
Oracle calculates sorting and hash join costs based on sort_area_size and hash_area_size with Manual PGA setting.
My question is what values for sort_area_size and hash_area_size is used by oracle in costing queries with automatic PGA setting.
or it uses altogether different formulas for automatic PGA setting.
November 03, 2004 - 6:43 am UTC
it uses formulas based on the size of the pga aggregate target, number of sessions and so on. it is an undocumentation function of various inputs.
more clarification
amti poddar, November 03, 2004 - 7:46 am UTC
So if we move to automatic PGA
all the query plan (may) be effected
My question is how can we estimate what effect the query plans would have due to automatic PGA short of full testting
November 03, 2004 - 10:16 am UTC
yes, any time you change any optimizer related parameter - plans may change.
short of testing :) VBG.
PGA_AGGREGATE_TARGET and shared server
Prashant Mahajan, November 13, 2004 - 10:44 pm UTC
Hi Tom,
In Oracle9iR2, can automatic PGA Memory Management be done in shared server mode ie default server connections are 'SHARED'? If no, can you explain why?
If so, is *_SIZE parameter setting only way to control sort/hash memory used by sql operations in shared server mode?
Thanks
November 14, 2004 - 9:46 am UTC
No it cannot -- the why is "because it wasn't supported yet", in 10g, the answer is yes it can.
with shared server in 9i, you use the _size parameters, yes.
Is the set for pga_aggregate_target too low?
A reader, December 03, 2004 - 12:22 pm UTC
Tom,
One of our database is using MTS configuration, and the pga_aggregate_target is set to 100MB. I just queried the database and found following:
1 select name,
2 to_char(decode( unit,
3 'bytes', value/1024/1024,
4 value ),'999,999,999.9') value,
5 decode( unit, 'bytes', 'mbytes', unit ) unit
6* from v$pgastat
SQL> /
NAME VALUE UNIT
---------------------------------------- -------------- ------------
aggregate PGA target parameter 100.0 mbytes
aggregate PGA auto target 40.6 mbytes
global memory bound 5.0 mbytes
total PGA inuse 55.0 mbytes
total PGA allocated 88.1 mbytes
maximum PGA allocated 108.7 mbytes
total freeable PGA memory 8.6 mbytes
PGA memory freed back to OS 3,853.9 mbytes
total PGA used for auto workareas .1 mbytes
maximum PGA used for auto workareas 7.5 mbytes
total PGA used for manual workareas 1.2 mbytes
NAME VALUE UNIT
---------------------------------------- -------------- ------------
maximum PGA used for manual workareas 9.5 mbytes
over allocation count .0
bytes processed 329,676.7 mbytes
extra bytes read/written 212,372.5 mbytes
cache hit percentage 60.8 percent
16 rows selected.
1 select sn.name, st.value, ss.username
2 from v$sesstat st, v$statname sn, v$session ss
3 where sn.name like 'workarea%'
4 and st.statistic# = sn.statistic#
5* and st.sid = ss.sid and ss.username = 'EMERGE'
SQL> /
NAME VALUE USERNAME
-------------------------------------------------- ---------- ------------------------------
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
NAME VALUE USERNAME
-------------------------------------------------- ---------- ------------------------------
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 1514 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 270 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
NAME VALUE USERNAME
-------------------------------------------------- ---------- ------------------------------
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea memory allocated 0 EMERGE
workarea executions - optimal 72 EMERGE
workarea executions - optimal 8 EMERGE
workarea executions - optimal 12 EMERGE
workarea executions - optimal 8 EMERGE
workarea executions - optimal 8 EMERGE
workarea executions - optimal 8 EMERGE
workarea executions - optimal 8 EMERGE
workarea executions - optimal 12 EMERGE
NAME VALUE USERNAME
-------------------------------------------------- ---------- ------------------------------
workarea executions - optimal 8 EMERGE
workarea executions - optimal 163124 EMERGE
workarea executions - optimal 8 EMERGE
workarea executions - optimal 8 EMERGE
workarea executions - optimal 8 EMERGE
workarea executions - optimal 8 EMERGE
workarea executions - optimal 8 EMERGE
workarea executions - optimal 482 EMERGE
workarea executions - optimal 288 EMERGE
workarea executions - optimal 8 EMERGE
workarea executions - optimal 40 EMERGE
NAME VALUE USERNAME
-------------------------------------------------- ---------- ------------------------------
workarea executions - optimal 8 EMERGE
workarea executions - optimal 8 EMERGE
workarea executions - optimal 290 EMERGE
workarea executions - optimal 310 EMERGE
workarea executions - optimal 18 EMERGE
workarea executions - optimal 20 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
NAME VALUE USERNAME
-------------------------------------------------- ---------- ------------------------------
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 887 EMERGE
NAME VALUE USERNAME
-------------------------------------------------- ---------- ------------------------------
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 0 EMERGE
workarea executions - onepass 8 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
NAME VALUE USERNAME
-------------------------------------------------- ---------- ------------------------------
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 202 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
NAME VALUE USERNAME
-------------------------------------------------- ---------- ------------------------------
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
workarea executions - multipass 0 EMERGE
NAME VALUE USERNAME
-------------------------------------------------- ---------- ------------------------------
workarea executions - multipass 0 EMERGE
100 rows selected.
Does that indicate the memory set for pga_aggregate_target is way too low? The "cache hit percentage" is only 60.8%! If I increase the value will solve the hit ratio problem?
Thanks as always.
December 03, 2004 - 1:27 pm UTC
what version are you in?
in 9i -- pga_aggregate_target = meaningless for MTS (shared server) connections, the sort/hash_area_sizes are used.
in 10g it is.
so.... what release.
Re: Is the set for pga_aggregate_target too low?
A reader, December 03, 2004 - 2:04 pm UTC
The database is Oracle9i (9.2.0.5.0). The following are the related initial parameters:
SQL> show parameter area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 1048576
sort_area_size integer 524288
workarea_size_policy string AUTO
So should I set workarea_size_policy = manual and pga_aggregate_target=0; and then increase hash_area_size and sort_area_size to 100MB? Please advise. Thanks.
December 04, 2004 - 10:08 am UTC
you can set the workarea to whatever you want -- it is only used for dedicated servers in 9i, if you don't use them, it is not relevant.
Only you can answer the question "should I set the sort/hash areas to big things"
MTS (shared server) typically indicates a client server OLTP application. If they are sorting/hashing big time, you've got a problem (and it isn't the amount of memory).
do you sort to disk frequently.
Re: Is the set for pga_aggregate_target too low?
A reader, December 04, 2004 - 4:00 pm UTC
Tom,
I get a little confused about your responses.
1) You said the pgs_aggregate_target is meaningless for MTS configuration, why I can see some numbers in workarea executions - multipass = 202? Does it mean some dedicated sessions are still using PGA instead of sort_area_size even though the server connection is configured as MTS?
2) What does the result (cache hit percentage = 60.8 percent) of above query to v$pgastat mean for MTS configuration server? Bad hit ratio or meaningless?
3) I queried the v$sysstat and got the following:
SQL> select * from v$sysstat where name like '%sort%';
STATISTIC# NAME CLASS VALUE
---------- ------------------------------ ---------- ----------
245 sorts (memory) 64 2808994
246 sorts (disk) 64 2652
247 sorts (rows) 64 1991208502
There are total 2652 disk sorts happened since the database started up. This number is good or bad in comparison with 2808994 memory sorts? Should I increase the sort_area_size to eliminate the disk sorts or just keep the current value?
4) For shared server, the sort_area_size retained in the UGA in the SGA. Does it mean the UGA is in large pool? If yes, why do we need to configure both large_pool_size and sort_area_size for MTS? Will the large_pool_size covers the sort_area_size?
Please clarify those – I really get confused. Thanks.
December 04, 2004 - 8:17 pm UTC
1) you must be using dedicated at the same time then. I was responding simply to "i use MTS" -- meaning you were interested in tuning the MTS stuff.
I as a client can say "nope, no MTS for me, server=dedicated... local connections will be dedicated, there are many ways into that database of yours and dedicated server cannot be disabled.
2) workarea stuff isn't relevant for shared server in 9i. You are looking at the wrong stuff to look at shared server connections.
3) 0.09% of your sorts go to disk.
is that good bad or indifferent?
might be bad, might be good -- but it is as unanswerable as this question:
On my last car trip, I stopped at 500 red lights. Is that good, bad or just OK?
no matter what you answer, I'll give you a use case where your answer is wrong.
0.09% of the time you sort to disk.
If that always happens during a time sensitive batch job (that does 0.09% of your queries), that might be really really bad.
If that is "averaged over 99.9%" or your queries, that might be perfect.
4) uga is in large pool. the UGA is in the large pool. The sort area retained size is how much of the UGA might be used for retaining sorted data.
the sort area size isn't in the UGA, it is in the PGA (well, the SAS-SARS is really)...
The UGA contains lots of stuff -- how much bigger do you want it to be? that is why you control SAS and SARS -- if you set SAS = 100meg, SARS=100meg, your UGA might have 100meg of sort data in it. Set it to 1m and it'll have 1m.
pga_aggregate_target_size
friend, December 20, 2004 - 9:42 pm UTC
Golden article of oracle knowldge bank..
salute to TOM
very useful article!!
Menon, February 08, 2005 - 12:12 pm UTC
Tom
This is a basic question on changing
sort_area_size and hash_area_size at session level.
If you are using session pooling, you need to revert
it back to their original values after executing
the logic (since we want to change it only for this
session/request and want to reset the state of the
session before returning the session back to
the pool.)
My doubt is related to following scenario:
a. You grab a session
b. alter it to increase the *area*size
c. do your query/whatever that requires the new
parameters
d. alter the session back before returning to pool.
Won't this always result in a hard parse since you are
changing the session parameter which most likely may
affect your CBO plan? Since before returning it back,
you would always change it back to the older
settings. Or do we still retain the older plan in one
of the "child cursors" and reuse it for the same sort_area_size/hash_area_size setting?
If so that raises even more questions...
(Turns out this is a generic question for any change in
optimizer settings that change the plan)
I *think* I should be able to verify this myself
but I am not getting around to it and I am sure
you can answer me straightaway:)
Thanx!
February 09, 2005 - 1:29 am UTC
<quote>
Won't this always result in a hard parse since you are
changing the session parameter which most likely may
affect your CBO plan?
</quote>
only if you randomly set the sizes, if you consistently set them to 12345678 for example, they will all be the "same", one hard, N soft parses after that.
thanx!
Menon, February 09, 2005 - 8:49 am UTC
1. This means that the CBO is clever enough to
recognize that for the
optimizer settings a, b, c, e (for different paramters)
it needs to use child cursor number 3, correct?
2. Do you know where it stores this info (relating cursors
or queries to different CBO parameters)?
Thanx!
February 09, 2005 - 2:30 pm UTC
1) yes.
2) shared pool with the parsed query
Is pga_aggregate_target a hard upper limit?
Orlando Reyes, March 14, 2005 - 3:14 pm UTC
Hi Tom,
I see on this listing from above, and also on two of my databases, that the 'maximum PGA allocated' can be greater than the actual pga_aggregate_target ' parameter.
aggregate PGA target parameter 38.0 mbytes
aggregate PGA auto target 4.0 mbytes
global memory bound 1.9 mbytes
total PGA inuse 163.4 mbytes
total PGA allocated 182.6 mbytes
maximum PGA allocated 186.6 mbytes
I assume this happens when the parameter is set too low, but if due to the total RAM I have I cannot assign more memory to the this parameter, could I get and error from Oracle/OS running out of memory.
I though the 'pga_aggregate_target' was an upper limit and Oracle would never allocate more memory than that. So, if more users got connected, it would just divide the target we assigned into the number of users.
Now, if I make the target big enough maybe I will never have any issues, the problem is I might not have that much RAM.
Concrete example. I have 3 GB of RAM, I use 2 GB for SGA and 500 MB for pga_aggregate_target and the rest for OS, and this works fine for 400 users. So, how can I guarantee that if 800 users connect to the DB, Oracle is not going to try to allocate more memory for the PGA potentially running out, swapping or causing OS issues.
Is pga_aggregate_target a hard upper limit or not? if not how can I really control it? It true, how come/when Oracle allocates a lot more?
Please let me know you comments.
Thanks a bunch.
Orlando
March 14, 2005 - 3:19 pm UTC
it is a target, not a hard limit and can be exceeded for many reasons. The goal here is to adjust the sort/hash areas up and down in size during the course of the day -- using more when more ram is available and less when more users are on using it simultaneously.
You cannot guarantee that you will not get requests for more ram than you have physically installed.
pga aggregate target is used only to control workareas (sort/hash) -- not plsql memory for example. so if in plsql you fill up a big array -- we have *no* control over that.
Alberto Dell'Era, May 11, 2005 - 5:25 pm UTC
Clarification - when using workarea_size_policy=manual in 9.2.0.6 - you said that the plan for a statement is fixed at parse time, and so even if the number of sessions increase, and so their "allowed" workarea size decreases, the plan doesn't change (assuming it is not invalidated that is).
But, say that the "allowed" workarea size for each session was X Megabytes at parse time, and now it is X/n -- how much memory will each *execution* be able to allocate
a) (at most) X megabytes
b) (at most) X/n megabytes
(assume just a single sort step in the plan for brevity)
Ie - will "pga_aggregate_target and number of sessions" influence the *execution* environment of the statement, or only the *parse* one ?
Thanks in advance :)
Alberto
May 12, 2005 - 7:19 am UTC
As the number of concurrent users goes up, the aggregate amount of PGA memory levels off and each concurrent sort gets a smaller percentage of the aggregate target.
the execution plan stays the same
but the execution "enviorment" is different, less ram allocated to the sort.
pga_aggregate_target
JK Purtzer, June 07, 2005 - 4:07 pm UTC
excellent thread! The different cases brought up were interesting variations for the most part.
work areA
friend, June 15, 2005 - 11:25 pm UTC
If i dont set work are size policy and only setting pga parameter ????
June 16, 2005 - 3:42 am UTC
??? to you? what is the question.
(show parameter in sqlplus to see what the setting is)
pga_aggregate_target
long time reader, July 09, 2005 - 6:03 am UTC
Oracle 9i release 2 on windows
If my pga_aggregate_target is set to 600 MB, workarea_size_policy is set to auto, and no alters to override this occur, is it possible for anything to happen to make the PGA on my server grow to 700 MB or a few GB or any number significantly higher then then PGA target?
I know the target is just a target, so where is the true limit that assures me I won't get memory errors if it attempts to exceed the target and exceeds actual resources?
To rephrase: I have 3 GB of RAM on my windows machine that is available for the oracle process. 1.8 GB to the SGA, .6 GB to the pga, and ~600 mb unused for the oracle process, growth, etc.. Another DBA contends that this is not a safe setup as PGA (only in auto mode) can grow well beyond the target yielding memory errors. I say not possible, but double checking...
Any thoughts here would be appreciated.
July 09, 2005 - 9:09 am UTC
absolutely, definitely.
</code>
http://oracle.apress.com//betabooks/index.html <code>
(in the future, if that link doesn't take you anywhere, that means the book is published and the refered to chapter is in the book)
see the memory structures chapter.
but it is much "safer" than using manual mode!!!! because the thing that would take you over the top would be YOU allocating memory WE cannot control (example in that chapter). And if YOU used sort-area-size/hash-area-size we would not "back off" on our use of that if YOU start to use lots of pga memory WE cannot control.
It is easier to grow beyond the amount of pga you want to use in MANUAL mode than it is in AUTO mode. In Manual mode, the amount of memory to be used for sorting and hashing is a "constant" maximum. In Auto, it backs off as the amount of PGA memory used goes up.
PGA_AGGREGATE_TARGET still
long time reader, July 11, 2005 - 6:17 am UTC
still Oracle 9i on windows in dedicated server mode
_______________________________________
Tom, thanks for the great link. I have your first book and I look forward to getting your new book too. The information, examples, and demonstrations were great. My only question is why is this, a potentially disastrous situation, not outlined more clearly in the documentation in regards to the pga_aggregate_target in auto mode?
</code>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1158.htm#REFRN10165
states:
"When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET."
But we now know from your examples that this is not a guaranteed idea. The PGA can expand exponentially further than the PGA_AGGREGATE_TARGET. And one can get memory errors (4030/4031) while following this advice.
and from
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c08memor.htm#20461 <code>
we can read:
"Oracle tries to ensure that the total amount of PGA memory allocated across all database server processes never exceeds this target."
There is once again nothing here about how hard is "tries" or what such vague language really means.
Yes, I know much of this goes into the cloud of an "Auto" feature, but we are running production systems and need scientific certainty about how this will allocate and work. The PGA on a system needs to be kept in more of a certain box. And, in this situation, sort/hash operations should go to disk at reduced performance prior to failing with ORA memory errors from the PGA growing too big.
I think when you say 'is much "safer" than using manual mode' I think you really need to qualify that and say in many situations. It is a generalization (ROT); true for many, but not all.
It seems that while running under finite memory constraints the answer for many just may be to set workarea_size_policy to manual. In this case, if one knows the number of connections and the number and size of sql work areas that the system uses at peak load, then one could set the manual parameters and make a pretty good estimation of the PGA size. Then if an unusually heavy process comes in they can go to disk and all others will be fine. Yes, you lose some of the nice auto features, but you get more PGA control.
Thanks again for your time.
July 11, 2005 - 8:46 am UTC
but, umm, this is sooo much better than the setting of sort/hash/bitmap sizes as it actually TRIES to stay in the box you've assigned to it -- whereas the _SIZE things just keep on going and going and going and going.
... expand exponentially ... is a poor choice of words, it can get larger but there is no exponential kicker to it once you past the threshold or anything.
oracle will work hard to try and stay UNDER the target, but if your application allocates explicitly more memory than fits under the target - there is not much we can do (except back way way way off on the amount of ram we allocate to sorts and hashes - that is the ONLY thing we can do)
YOU have allocated YOURSELF this memory, out of our control. All we do is observe that and back off OUR allocations accordingly. if YOU take us over the top, there isn't anything we can do about that.
I would say it is much safer memorywise than sort_area_size/hash_area_size in all cases.
Auto:
You have lots of ram free, bigger sorts.
YOU start allocating lots of PGA we cannot control, they get smaller. We try to stay under the bar.
Manual:
You have lots of ram free, you get sort_area_size at most.
YOU start allocating lots of PGA we cannot control, they STAY THE SAME.
If you set to manual, the odds of you exceeding your goal is greatly increased, not the other way around.
Useful for OLTP ?
Des Browning, July 11, 2005 - 6:18 am UTC
Tom,
I've read that the PGA memory features are more for DSS/data warehouse applications rather than OLTP - which are the main systems I support. Obviously I can try using these features to see what effect they have, but what's your view on this ?
July 11, 2005 - 8:50 am UTC
if you don't sort/hash/bitmap merge - they do not come into play.
So, hash and bitmap -- don't come into play in OLTP.
that leaves SORT_area_size. the Auto stuff is good for OLTP in that regards. Few users -- bigger sort areas. Load increases - smaller sort areas so as to not exceed real memory on the machine (rather Oracle swaps a sort to disk than the OS swaps Oracle to disk).
the auto might not be applicable to large loads, index builds, DBA stuff (in DW or OLTP) as you know you want to use it ALL and not be friendly. The AUTO is designed to be friendly, to efficiently share this resource called RAM. With a big batch -- I might want to say "to heck with sharing, this is my machine for the next hour -- let me use it all" rather than be constrained to the % of the pga aggregate target it thinks I should have.
asktom - OLTP predominantly (except for my queries against the audit trail). pga_aggregate_target rocks and rolls (once I set it right, the default of 25m was a bit "small" you might say).
PGA in 9i
Des Browning, July 11, 2005 - 9:58 am UTC
Thanks for your speedy response.
Low cache hit percentage
The Human Fly, September 10, 2005 - 4:16 am UTC
Your explanation towards pga_aggregate_target is very good and easily understandble.
I have a strange problem with my database concerning pga_aggregate_target cache hit ratio.
I have given 984M as my PGA and when following is the output from v$pgastat
NAME VALUE UNIT
------------------------------ -------------- ------------
aggregate PGA target parameter 984.0 mbytes
aggregate PGA auto target 794.1 mbytes
global memory bound 49.2 mbytes
total PGA inuse 101.8 mbytes
total PGA allocated 190.8 mbytes
maximum PGA allocated 296.8 mbytes
total freeable PGA memory 8.8 mbytes
PGA memory freed back to OS 2,287.8 mbytes
total PGA used for auto .0 mbytes
workareas
maximum PGA used for auto 85.8 mbytes
workareas
total PGA used for manual .0 mbytes
workareas
maximum PGA used for manual .0 mbytes
workareas
over allocation count .0
bytes processed 32,006.6 mbytes
extra bytes read/written 85,046.7 mbytes
cache hit percentage 27.3 percent
My concerning points:
Why my cache hit percentage is only 20+ when my DB is not completely using my PGA? If you look at total PGA in use, its only 101 M and maximum is only 300+ M.
could you please shed some light on this?
Thanks
September 10, 2005 - 8:30 am UTC
given that setting, and that the algorithm allows you 5% of the pga_aggregate_target (or _smm_max_size) as a workarea for your session.
_smmm_max_size will default to about 50mb in this case (5% of the aggregate target) and a single sort in your session will use about 50% of that at most (in order for your first cursor to not allocate ALL of the memory targeted for your session).
So, it could just be that you sort things much larger than about 25MB - how big are your sorts
Suggest you use statspack during a peak processing time, it can be very useful to show you how big they are (the sorts) and such.
Maurice, September 14, 2005 - 7:24 am UTC
The Human Fly,
An easy way to use more PGA in a query would be to execute it in parallel.
Another way to do it, while still using Oracle's great PGA management, would be to modify some hidden parameters, but this is another story...
Steven Patenaude, September 20, 2005 - 6:23 pm UTC
How does this affect parallel operations when the database has PGA set to auto but a batch process sets it to manual?
I would assume the *_area_size settings would work like normal. However in another post ("Undocumented secrets...") you said
<quote>
In a serial session - I would definitely prefer using manual memory management
and setting sort area size/hash area size to what you want. If you want to
override, then override and use the documented supported way to do it.
In a batch job
alter session set workarea_size_policy = manual;
alter session set sort_area_size = .......;
alter session set hash_area_size = .........;
For the parallel stuff, to make it larger, we'd have to take a look at it on a
case by case basis. First, making sure it is even necessary - and second making
sure the global settings you are making are reasonable "24x7" - for everyone.
</quote>
That leads me to believe the parallel processes are going to be influenced by the PGA auto settings.
We have a vendor recommending setting everything to PGA manual, batch and online users. I'd like to have the online users have PGA auto and our batch processes use PGA manual.
Thanks!
September 21, 2005 - 1:43 pm UTC
The problem with the parallel processes is that the session settings you make don't necessarily "carry down" to the already running parallel execution servers.
are you using parallel execution?
can we use both manual and automatic workarea in shared server?
A reader, October 07, 2005 - 5:09 am UTC
Hi
We have an application which uses shared server, I wonder if I set up both automatic PGA and manual sort_area_size parameters will my
DEDICATED session use PGA
SHARED sessions use sort_area_size
?
Cheers
October 07, 2005 - 8:41 am UTC
A reader, October 07, 2005 - 2:18 pm UTC
Not sure whether this is already answered in this thread :
If you are using pga_aggregate_target at the instance level, can any individual user alter the %_area_size s for that session using
alter session set sort_area....
alter session set hash_area..
?
Will these 'alter session ..' have effect at session level, when automatic pga is in effect at the instance level ?
October 08, 2005 - 8:38 am UTC
yes, they will have effect at the session level IF that session is using manual PGA memory management.
Eg:
ops$tkyte@ORA10G> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
ops$tkyte@ORA10G> alter SESSION set workarea_size_policy = manual;
Session altered.
ops$tkyte@ORA10G> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string MANUAL
ops$tkyte@ORA10G>
Now, sort_area_size, hash_area_size are "in play" for that session - their settings are used.
Or, in 9i if you are using a shared server connection, then the *_size settings are used regardless as auto PGA memory management was not implemented for shared server connections in that release.
Bind variables
Deepak, October 21, 2005 - 5:52 am UTC
Hi Tom,
I read in a doc. that for dedicated server connections the PGA holds the cursor information that persists across multiple executions of the same statement such as, bind details, data type conversion, etc. The persistent area is de-allocated when the cursor is closed.
Before going through the doc. I knew that the cursor & other binding info. was stored in Library Cache area of the SGA.
If it is true then can a cursor in a dedicated server environment not be shared across multiple sessions?
Can anyone please elaborate on this.....
October 21, 2005 - 8:26 am UTC
How can you "know" two contradictory statements?
There is the shared part of the cursor (the v$sql type of stuff). The query, plan, etc - that is all shared in the shared pool.
then there is the non-shared part of the cursor. YOUR binds, where YOU are in processing that query. That is in YOUR memory which can either be in the PGA in dedicated server mode (in a process) or in the UGA in the SGA's large pool when using shared server connections - still private to a session.
Thanks
Deepak, October 21, 2005 - 9:28 am UTC
Thanks a lot Tom, I like the way you explain the things... Simply great...
v$sga_target_advice
Andrew, November 23, 2005 - 1:00 pm UTC
I understand v$pga_target_advice pretty well. I am now trying to make some sense out of v$sga_target_advice -- and dba_hist_sga_target_advice, which appears to be what its name implies.
Could you pleas help us understand how we might use v$sga_target_advice to help tune dataase performance. The documentation I have found is quite terse and is not of much help.
For instance ESTD_DB_TIME, ESTD_DB_TIME_FACTOR, and ESTD_PHYSICAL_READS are not well defined.
How would we use the information in the dynamic view and history table to better manage our databases?
Thanks.
November 23, 2005 - 7:57 pm UTC
estd = estimated. hopefully, estimated physical reads is somewhat self explanatory?
The db_time and db_time_factor estimates are to show "possible improvement in database response time".
ops$ora10gr2@ORA10GR2> select * from v$sga_target_advice;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
580 1 2170 1 422128
145 .25 3233 1.4897 819562
290 .5 2506 1.1549 573081
1160 2 2119 .9764 385783
725 1.25 2148 .9899 408324
870 1.5 2144 .9879 407100
1015 1.75 2143 .9874 406509
435 .75 2214 1.0201 427700
8 rows selected.
that shows the "estimated difference in time spent in the database given the proposed sga target size".
My database would run using 150% the time it does right now (guestimate) if I dropped the SGA TARGET from 580 to 145meg. It would using about 97/98% the time if I doubled it.
When workarea_size_policy = AUTO ...
Suvendu, November 24, 2005 - 5:40 am UTC
Hi Tom,
As you discussed when we are going for automatic PGA we need to consider both pga_aggregate_target=<minimum_value(24)>mb and workarea_size_policy=AUTO and Oracle ignores all *_area_size parameter.
So, here is my question:
Does Oracle still refers to these parameters when instance in AUTO PGA mode?
I got the following error at time of normal user login not using SYSDBA user,making all these parameters to zero values.
SQL> conn scott/tiger
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-00067: invalid value 0 for parameter bitmap_merge_area_size; must be at
least 65536
ORA-00604: error occurred at recursive SQL level 1
ORA-00067: invalid value 0 for parameter bitmap_merge_area_size; must be at
least 65536
Error accessing package DBMS_APPLICATION_INFO
Connected.
SQL> conn / as sysdba
Connected.
SQL>
Correct me, if I'm wrong.
Thanking you.
Regards,
Suvendu
November 24, 2005 - 8:55 am UTC
(in 9i, they are not ignored with shared server.....)
what is your version - and do you have these things set in your init.ora?
what sql is causing this?
Few more information...
Suvendu, November 24, 2005 - 9:43 am UTC
Hi Tom,
Thanks a lot your answer.
I'm sorry not provide all information. Here is all the sequence of statements I issued after getting your guidance. As statement saying I'm not issuing any statement to get all this errors, simple when connecting to the normal user and also it connecting with errors.
Please, once again correct me on my understanding.
Regards,
Suvendu
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Nov 24 19:39:41 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> show parameter bitmap
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 65536
create_bitmap_area_size integer 8388608
SQL> show parameter work
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 25165824
SQL> show parameter shared_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer 20
shared_server_sessions integer 165
shared_servers integer 1
SQL> alter system set bitmap_merge_area_size=0 scope=spfile;
System altered.
SQL> alter system set create_bitmap_area_size=0 scope=spfile;
System altered.
SQL> alter system set shared_servers=0 scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> show parameter shared_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer 20
shared_servers integer 0
SQL> show parameter work
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 25165824
SQL> show parameter bitmap
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 0
create_bitmap_area_size integer 0
SQL>
SQL> conn scott/tiger
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-00067: invalid value 0 for parameter bitmap_merge_area_size; must be at
least 65536
ORA-00604: error occurred at recursive SQL level 1
ORA-00067: invalid value 0 for parameter bitmap_merge_area_size; must be at
least 65536
Error accessing package DBMS_APPLICATION_INFO
Connected.
SQL> select * from emp order by 1,2,3,4;
select * from emp order by 1,2,3,4
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-00067: invalid value 0 for parameter bitmap_merge_area_size; must be at
least 65536
ORA-00067: invalid value 0 for parameter bitmap_merge_area_size; must be at
least 65536
SQL>
November 24, 2005 - 10:31 am UTC
so are you using shared server --
select server, count(*) from v$session group by server;
what is the output of that from the session getting the error.
when SHARED_SERVERS=0....
Suvendu, November 25, 2005 - 5:25 am UTC
Hi Tom,
Here is the statement from Document which says about shared server at chapter 5 Managing Oracle Processes(Admin Guide).
<quote>
If you set SHARED_SERVERS to 0, Oracle terminates all current servers when they become idle and does not start any new servers until you increase SHARED_SERVERS. Thus, setting SHARED_SERVERS to 0 may be used to effectively disable shared server.
</quote>
So, my understanding when SHARED_SERVERS=0 then database is running in DEDICATED configuration mode. And all these test case in with a test database, there is no more connection. And here is the output of your said statement:
SQL> select server, count(*) from v$session group by server;
SERVER COUNT(*)
--------- ----------
DEDICATED 11
SQL> select username, server from v$session;
USERNAME SERVER
------------------------------ ---------
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
DEDICATED
SYS DEDICATED
11 rows selected.
SQL>
Once again, correct me if I'm wrong.
Thanking you.
Regards,
Suvendu
November 25, 2005 - 10:19 am UTC
do you have logon triggers.
Is this information is valid one?
Suvendu, November 25, 2005 - 10:35 am UTC
Hi Tom,
Is this information will help to move further? Else I have not created any such trigger explictly.
Please, guide me to do next if any.
Thanking you.
Regards,
Suvendu
SQL> show user
USER is "SYS"
SQL> select owner, trigger_name, triggering_event, action_type, status from all_trigg
where triggering_event like '%LOG%'
OWNER TRIGGER_NAME TRIGGERING_EVENT
------------------------------ ------------------------------ ------------------
SYS SYS_LOGOFF LOGOFF
SYS SYS_LOGON LOGON
2 rows selected
November 25, 2005 - 11:06 am UTC
someone created them, lets look at them.
No such use full information.....
Suvendu, November 25, 2005 - 12:06 pm UTC
Hi Tom,
This is the content of these triggers. I think these are default by database. I checked out in other databases too, where is same information and the procedures contents are in encrypted form.
Regards,
Suvendu
SQL> select trigger_body from all_triggers where trigger_name='SYS_LOGON'
TRIGGER_BODY
----------------------------------------
logon_proc;
1 row selected
SQL> select text from all_source where name='LOGON_PROC'
TEXT
--------------------------------------------------------------------------------
procedure logon_proc wrapped
0
abcd
abcd
.
.
.
1
4
0
16
0 0 0 0
1 0 1
0
1 row selected
SQL> select trigger_body from all_triggers where trigger_name='SYS_LOGOFF'
TRIGGER_BODY
----------------------------------------
logoff_proc(lt.getSid);
1 row selected
SQL> select text from all_source where name='LOGOFF_PROC'
TEXT
--------------------------------------------------------------------------------
procedure logoff_proc wrapped
0
abcd
abcd
abcd
.
.
.
2 1 0
17 1 0
10 1 0
9 1 0
1 0 1
0
1 row selected
November 25, 2005 - 1:50 pm UTC
Nope, I don't have them - I've not heard of them. I believe they are "yours"
sys@ORA10GR2> l
1 select trigger_name, triggering_event, action_type, status
2 from dba_triggers
3* where owner = 'SYS'
sys@ORA10GR2> /
TRIGGER_NAME TRIGGERING_EVENT ACTION_TYPE STATUS
------------------------------ -------------------- ----------- --------
AW_DROP_TRG DROP PL/SQL ENABLED
NO_VM_DROP DROP PL/SQL DISABLED
NO_VM_DROP_A DROP PL/SQL DISABLED
NO_VM_CREATE CREATE PL/SQL DISABLED
NO_VM_ALTER ALTER PL/SQL DISABLED
AURORA$SERVER$STARTUP STARTUP CALL DISABLED
AURORA$SERVER$SHUTDOWN SHUTDOWN CALL DISABLED
CDC_ALTER_CTABLE_BEFORE ALTER PL/SQL DISABLED
CDC_CREATE_CTABLE_AFTER CREATE PL/SQL DISABLED
CDC_CREATE_CTABLE_BEFORE CREATE PL/SQL DISABLED
CDC_DROP_CTABLE_BEFORE DROP PL/SQL DISABLED
OLAPISTARTUPTRIGGER STARTUP PL/SQL ENABLED
OLAPISHUTDOWNTRIGGER SHUTDOWN PL/SQL ENABLED
13 rows selected.
when SHARED_SERVERS=0....
A Reader, November 25, 2005 - 2:14 pm UTC
Tom,
That person has nothing to do with those 2 triggers.
I checked our database. It's there on all environments from Dev to Prod.
They are created when installing Oracle Workspace Manager. Please refer
$ORACLE_HOME/rdbms/admin/owmctrg.plb file.
Could these two triggers are causing ORA-00604, ORA-00067??
SQL> l
1 select owner, trigger_name, triggering_event, action_type, status from
2 dba_triggers
3* where triggering_event like '%LOG%'
SQL> /
OWNER TRIGGER_NAME TRIGGERING_EVENT
------------------------------ ------------------------------ ------------------------------------------------------
SYS SYS_LOGOFF LOGOFF
SYS SYS_LOGON LOGON
November 25, 2005 - 3:46 pm UTC
10.2
/*
*
*
We are dropping logon/logoof triggers ON DATABASE as it impacts a
critical path. The old code for the triggers can be looked up from the
OVM_MAIN_SOLARIS_030819 label
so, looks like I was looking in 10g.
but, no, I think if you look, you'll find they are disabled.
I turned on sql_trace=true for the instance and found it is this:
ops$tkyte@ORA9IR2> exec dbms_output.disable
BEGIN dbms_output.disable; END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00067: invalid value 0 for parameter bitmap_merge_area_size; must be at
least 65536
In fact, it seems to be "many things plsql"
ops$tkyte@ORA9IR2> create or replace procedure p as begin null; end;
2 /
create or replace procedure p as begin null; end;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-00067: invalid value 0 for parameter bitmap_merge_area_size; must be at least 65536
It looks like something in the plsql layer is checking this parameter and finding it invalid.
so, it looks like a parameter validation routine is getting invoked
<b>Updated shortly after...</b>
I sent this along to Jonathan Lewis knowing he might be interested and he wrote back:
.....
select * from dba_triggers where trigger_name = 'SYS_LOGON'
works okay.
select /*+ all_rows */ * from dba_triggers where trigger_name = 'SYS_LOGON'
ORA-00067 etc....
It looks like an optimizer code path complaining
...............
makses sense - it could well be the CBO complaining about invalid parameter settings - anything that tripped the CBO might run into this.
It is not that the CBO was *using* it, it was just doing a santity check on values.
Does not seem to reproduce in 10gr2 in any case.
A reader, January 26, 2006 - 12:44 pm UTC
Hi Tom,
I have setup the second database from our production database.
I have gathered schema statistics too.
Only one of the schema scripts gives the performance degradition.
I compare the execution plan of the query between both production database and the newly setup database. Both same.
Also cost values in New DB is much less than production database.
But when I execute the query, it takes very long time.
in production => 6 min
in new database => >1 hr
I have noticed for sorting it takes considerably time.
But following parameters on the two database are same:
------------------------------------------------------
workarea_size_policy string AUTO
sort_area_retained_size integer 131072
sort_area_size integer 134217728
and the temp tablespaces and temp files are same.
So could you please tell me, what else could be the reason for this performance degration?
January 27, 2006 - 8:08 am UTC
if the plans are the same
and the data is the same
then you would need to share with us the tkprof reports of both showing us that the plans are the data, the data is the same but the runtimes are very much different.
something is *not* the same, we just need to discover what that is.
A reader, January 26, 2006 - 1:24 pm UTC
I wants to add some more information to above question:
I ran the following query:
select * from v$session_longops where username='FONDSRPT' and last_update_time > sysdate-1 order by last_update_time;
SID SERIAL# OPNAME
---------- ---------- --------------------
TARGET
----------------------------------------------------------------
TARGET_DESC SOFAR TOTALWORK
-------------------------------- ---------- ----------
UNITS START_TIM LAST_UPDA TIME_REMAINING
-------------------------------- --------- --------- --------------
ELAPSED_SECONDS CONTEXT
--------------- ----------
MESSAGE
--------------------------------------------------------------------------------
USERNAME SQL_ADDR SQL_HASH_VALUE QCSID
--------------- -------- -------------- ----------
FONDSRPT 11A45C04 1354897888 0
9 36 Sort Output
676 676
Blocks 26-JAN-06 26-JAN-06 0
366 0
Sort Output: : 676 out of 676 Blocks done
FONDSRPT 11BBAE80 2649139773 0
9 36 Sort Output
512 676
Blocks 26-JAN-06 26-JAN-06 72
225 0
Sort Output: : 512 out of 676 Blocks done
FONDSRPT 11A45C04 1354897888 0
32 4577 Sort Output
41643 388
Blocks 26-JAN-06 26-JAN-06
22 0
Sort Output: : 41643 out of 388 Blocks done
FONDSRPT 1294BC88 512926114 0
Elapsed: 00:00:00.02
19:10:23 SQL>
So what does that means => Sort Output: : 41643 out of 388 Blocks done
Any problem with my database setup, which cause for performance degration?
thanks
January 27, 2006 - 8:12 am UTC
only look at rows where time_remaining > 0 - else if time remaining is zero, the row "is done, not being used, not useful, garbage, whatever you want to call it"
low values maximum PGA used for auto workareas/ total PGA used for auto workareas
Kumar, February 20, 2006 - 7:53 pm UTC
This is the output from v$pgastat
NAME VALUE UNIT
----------------------------------- -------------------- ------------
aggregate PGA target parameter 125.0 mbytes
aggregate PGA auto target 106.6 mbytes
global memory bound 6.3 mbytes
total PGA inuse 6.7 mbytes
total PGA allocated 13.5 mbytes
maximum PGA allocated 144.8 mbytes
total freeable PGA memory 1.4 mbytes
PGA memory freed back to OS 41,282.2 mbytes
total PGA used for auto workareas .0 mbytes
maximum PGA used for auto workareas 4.2 mbytes
total PGA used for manual workareas .0 mbytes
maximum PGA used for manual workareas .0 mbytes
over allocation count .0
bytes processed 29,122.4 mbytes
extra bytes read/written .0 mbytes
cache hit percentage 100.0 percent
Can you please explain why the maximum PGA used for auto workareas and total PGA used for auto workareas have such low values?
The application accesses data thru the Packages/procedures only.
Thanks in advance
February 21, 2006 - 7:35 am UTC
you are not sorting and hashing large amounts of data I would conclude.
consider:
ops$tkyte@ORA9IR2> connect / as sysdba
Connected.
ops$tkyte@ORA9IR2> startup force
ORACLE instance started.
Total System Global Area 152114296 bytes
Fixed Size 451704 bytes
Variable Size 117440512 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
ops$tkyte@ORA9IR2>
<b>upon startup, we expect these counters to be reset so:</b>
ops$tkyte@ORA9IR2> connect /
Connected.
ops$tkyte@ORA9IR2> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 1073741824
ops$tkyte@ORA9IR2> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from v$pgastat where name like '% auto work%';
NAME VALUE UNIT
-------------------------------------------------- ---------- ------------
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 0 bytes
<b>we see they are. now, let us do a sort, but not close out the cursor</b>
ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2> declare
2 l_rec all_objects%rowtype;
3 begin
4 open :x for
5 select *
6 from all_objects
7 order by 1,2,3,4,5,6,7,8,9,10,11,12;
8 fetch :x into l_rec;
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select * from v$pgastat where name like '% auto work%';
NAME VALUE UNIT
-------------------------------------------------- ---------- ------------
total PGA used for auto workareas 3842048 bytes
maximum PGA used for auto workareas 3842048 bytes
<b>Now we have 3.8mb IN USE (sort is still "happening", we haven't finished the query yet) and 3.8mb as our max.... In the following, I set termout off and printed x to exhaust and close the cursor</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from v$pgastat where name like '% auto work%';
NAME VALUE UNIT
-------------------------------------------------- ---------- ------------
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 3842048 bytes
<b>and then we have 0 IN USE (query over, no more workarea for us) and 3.8mb is still the "max"</b>
ops$tkyte@ORA9IR2> declare
2 l_rec all_objects%rowtype;
3 begin
4 open :x for
5 select *
6 from (select * from all_objects union all select * from all_objects )
7 order by 1,2,3,4,5,6,7,8,9,10,11,12;
8 fetch :x into l_rec;
9 end;
10 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select * from v$pgastat where name like '% auto work%';
NAME VALUE UNIT
-------------------------------------------------- ---------- ------------
total PGA used for auto workareas 7520256 bytes
maximum PGA used for auto workareas 7520256 bytes
<b>and when we double the sort - we double the max and so on...</b>
PGA HIST
Kumar, February 21, 2006 - 10:17 pm UTC
What you say seems right as output below confirms that. But why total PGA used for auto and manual work areas is showing zero? It should be a cumulative value for the isntance.
PGA_SIZE OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
------------------------------------------------------------- ------------------ ------------------ ----------------------
8kb <= PGA < 16kb 62789 0 0
16kb <= PGA < 32kb 2388 0 0
32kb <= PGA < 64kb 590 0 0
64kb <= PGA < 128kb 1014 0 0
128kb <= PGA < 256kb 8 0 0
512kb <= PGA < 1024kb 2316 0 0
1mb <= PGA < 2mb 4391 0 0
2mb <= PGA < 4mb 1184 0 0
Thanks
February 22, 2006 - 8:34 am UTC
umm, maybe you are not doing anything right now.
sort_area_size
A reader, April 03, 2006 - 11:44 am UTC
Tom,
If both parameters sort_area_size and pga_aggretate_area are set for Oracle instance, will oracle simple ignore the sort_area_size setting (work_area_size_policy are set to auto)?
April 04, 2006 - 9:43 am UTC
no.
if ( db_version >= 10gR1 )
then
if ( auto memory management is being used )
then
sort area size is not used
else
sort area size is used
end if
else -- db_version < 10gr1
if ( shared server connection )
then
sort area size is used
else
if ( auto memory management is being used )
then
sort area size is not used
else
sort area size is used
end if
end if
end if
Also, you can change the memory management policy via alter session to flip flop between auto and sort area sizing as well.
So, you cannot really say it will be "ignored"
Clarification of the sort area size
Sri, April 20, 2006 - 5:06 pm UTC
Tom,
Excellent input as always. One small clarificatrion. In the beginning of the chain you mention "..... with only 2 users 50Mb Sort size may be sufficient. With 500 users it may not be appropriate"...
Why would the sort area size be dependent on number of users. Isn't the sort area size specified, per process or per session basis . More the number of users it may put more load on the server memory. But should the sort area size be in anyway dependent on number of users? Am I missing something here ?
April 20, 2006 - 5:48 pm UTC
it could be that two users doing concurrent sorts fits
but that 500 users - doing concurrent sorts - it doesn't fit (you blow out the memory on the machine).
The point is -> more users => more sort areas concurrently allocated (probably)
less users => less sort areas concurrently allocated (probably)
as load goes up, sort area size should decrease in order to not exceed physical ram on the machine. with a FIXED sort area size, that cannot happen.
pga_aggregate_target
raaj, April 21, 2006 - 5:36 am UTC
Hi,Tom
Thanks a lot.
i have gone through entire questionare and your answers as well learned a lot from this article and made the changes. on my database.
raaj
confirmation
A reader, April 25, 2006 - 4:32 am UTC
I have read the thread and based on that just want
to confirm my understandings. I am using
Oracle version 9i Rel 2 in dedicated server mode.
Q1) If
work_area_size_policy=AUTO and
pga_aggregate_target=4000MB and
whatever the other parameters are
Then which of the following is correct:-
a) Each sort operation( not users/sessions) cannot
get more than 200MB (i.e 5 percent of 4000MB).
b) Each sort operation( not users/sessions) cannot
get more than 100MB
(i.e minimum(5 percent of 4000MB, 100MB)).
Q2) If
work_area_size_policy=MANUAL and
pga_aggregte_targer=4000MB and
sort_area_size=1000MB and
what ever the other parameters are
Then each sort operation (not sessions/users) cannot get
more than 1000MB, right?
Q3) copy/paste from documentation.
Oracle9i Database Performance Tuning Guide and Reference
Release 2 (9.2)
Part Number A96533-02
Chapter 14
Memory Configuration and Use
Section
Configuring the PGA Working Memory
".....
To enable automatic PGA memory management, you have to set the initialization parameter PGA_AGGREGATE_TARGET, as
described in the following section.
....."
and then in the following section there is nothing mentioned
about work_area_size_policy parameter, I thought that
automatic management is enabled throught work_area_size_policy, but the document is saying pga_aggregate_target.????
April 25, 2006 - 5:47 am UTC
1) in 9i it would be the value of _smm_max_size, the _smm_max_size undocumented
parameter defaults to least( 0.05 * pga_aggregate_target, _pga_max_size/2 )
and _pga_max_size defaults to 200mb.
2) we would use sort area size, yes.
3) </code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1222.htm#1025384 <code>
if you set pga_aggregate_target, then work area policy defaults to AUTO, you don't need to set workarea_size_policy, we'll default it to auto.
A reader, April 25, 2006 - 5:41 am UTC
copy/paste from documentation.
Oracle9i Database Performance Tuning Guide and Reference
Release 2 (9.2)
Part Number A96533-02
Chapter 14
Memory Configuration and Use
Section
Considerations with SORT_AREA_SIZE
".....
Remember that there is a point after which increasing the SORT_AREA_SIZE no longer provides a performance benefit."
Please tell me what point is that (could be)? Normaly,
the greater the RAM you have for sorting the greater
the performance.
Thanks
April 25, 2006 - 6:11 am UTC
well, if you increase sort_area_size beyond the amount of ram you actually need (eg: you need 1meg to sort, but you set it to 256meg) you won't see any benefit would be one way to read that.
or, if you increase sort area size to such a point that you start to exceed physical ram on the box.
(rather than cut and paste, be nicer just to point to the documentation on otn.oracle.com so we can read it in full)
pga target and work area
A reader, April 25, 2006 - 6:14 am UTC
Sorry not clear,
This is very confusing, default value of a parameter
depends on another parameter?
For e.g.
First I set workarea_size_policy=MANUAL then
if pga_aggregate_target is set to some value other than
0 then according to documentation workara_size_policy will
be automatically set to AUTO? Does it mean
if we want MANUAL behaviour, we MUST set pga_target to zero? and then set workarea_size_policy=AUTO?
April 25, 2006 - 6:20 am UTC
did you click on the link - yes the default depends on another parameter being set or not - MANY of the parameters are this way. specifically for workarea policy:
<quote>
Default value
If PGA_AGGREGATE_TARGET is set, then AUTO
If PGA_AGGREGATE_TARGET is not set, then MANUAL
</quote>
NO, the documentation does not say anything remotely similar to what you state - it says "the default value...", defaults being the value assigned when you don't assign one yourself.
If you chose to OVERRIDE the default setting by setting workarea_policy yourself, you will have overridden the default.
If you want manual, regardless of anything else, you would set it manual - hence overriding any DEFAULT setting.
DEFAULT means when we dont set value
A reader, April 25, 2006 - 6:32 am UTC
Sorry if I am missing some very basic concept, but,
Yes, I have clicked the link and becuase of that this question came in my mind, because there are only two values
available for workarea_size_policy (AUTO MANUAL), we cannot
set workarea_size_policy = NULL or BLANK, and for someone
to assign DEFUALT value to workarea_size_policy , it must
be NULL or BLANK. I mean there will always be a value
SET for workarea_size_policy then how can someone assign
it DEFAULT value.
Thanks alot.
April 25, 2006 - 8:38 am UTC
no, default means if you do not set a value at all (you do not put it in your init.ora or your spfile), then we DEFAULT it's value for you.
IF you put it in, you are giving a value, the default is no longer used.
to assign the "default", you do not supply ANYTHING.
the second you supply something, you are overriding the default.
how to hash(memory) and hash(disk)
A reader, April 25, 2006 - 10:00 am UTC
I have the following query for cheking
how many sorts were in memory and how many used
disk sort.
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('sorts (memory)', 'sorts (disk)');
But how can I see that how man hash were in memory
and how many hash in disk.
Thanks
sorts in a single sql query
A reader, April 26, 2006 - 7:32 am UTC
If I am not using Oracle parallel feature and
I am the only user in the database connected,
and I execute a single SQL query which require more
than one sort and hash, will these sorts/hashes be
done in parallel or 1 after another?
April 26, 2006 - 8:17 am UTC
unless you use parallel query, everything in your session is "sequential". the only way to get "parallel" is "parallel query"
but i think it is doing parallel
A reader, April 27, 2006 - 2:31 am UTC
Ok, I executed an SQL query and it quicky returned
me an error "out of process memory", my sort area=1GB
hash area=1GB , work area policy is MANUAL and pga aggregate
target = 4GB. I have 16 GB of physical total ram in my
machine and I know there are no other applications running
on the system, and I am the only database user connected to the database. If say there are alltogather 30 sorts/hashes
required by this single query, then first SORT will get 1GB, get completed , free the memory , then next SORT get
1GB RAM, get completed, free the memory, .... and so on
the query should complete successfuly. But I am surprised
that why I got "out of process memory error"
can you please help.
April 27, 2006 - 2:58 pm UTC
.. work area policy is MANUAL and pga aggregate
target = 4GB ...
so basically, the pga_aggregate_target is not relevant....
sonds like you have ulimit set on your server and ulimit is such that processes cannot allocate the amount of ram you have said they can (eg: we try to allocate 1gb or so (can have more than one sort area at a time, can have MANY MANY of them in fact))....
so we try to allocate 1g but you said "sorry, ulimit says 500mb at most, you lose"
or, maybe you are windows with a single process and are 32bit - in which case the 16 gig is a pipe dream, doesn't exist.
Even 32-bit Oracle on Unix will have limits for each PGA
Roderick, April 28, 2006 - 1:33 am UTC
For example 32-bit Oracle on Solaris only lets a single PGA grow to about 420MB before the PGA runs into the SGA in the process virtual address space. 64-bit Oracle on Unix has a bigger address space for both PGA and SGA to live in.
"out of process memory"
A reader, April 28, 2006 - 2:02 am UTC
I am using AIX 64 bit, and my OS user has unlimited
ulimit.
"You said there can be many many sorts going on at a time",
but remember I said I am the only user connected to the
database and I only executed one single SQL query with
out using parallel feature.
OR you mean to say that a single query may have multiple
SORT/HASH requirement and these SORT/hashes can be done
concurrently/parallel even though the paralle feature
is not used?
April 28, 2006 - 2:14 am UTC
A single query can use more than one sort area. They are used sequentially - no parallel processing - but could exist simultaneously (as we move the data from one to the other for example)
But - how did you prove that the Oracle server process (which runs in an entirely different environment from you) has ulimit unlimited - are you sure.
confirmed
A reader, April 28, 2006 - 2:38 am UTC
I have confirmed from my system administrator, and
he says that the OS user through which oracle runs
has unlimited access.
You mean there can be multiple sort/hash areas
allocated to one single SQL query at a single point in time but the function
of sorting/hashing is going on in only one of them?
Also , is the sort_area_retained_size is effected by
the workarea_size_policy= AUTO or MANUAL?
Thanks
April 28, 2006 - 2:46 am UTC
a single query may have more than one sort area, yes.
I would work this with support if the admin is sure of what they say.
sort area retained size is used when using manual memory management, not during automatic memory management.
support?
A reader, April 28, 2006 - 3:01 am UTC
Is it something abnormal if the OS user throught which
oracle is running has unlimited access?
April 28, 2006 - 3:41 am UTC
If you are 64bit, and you have 16gb of ram and the ulimits are not set and you are the only user and there is plenty of free memory and you are running but one query
then, you would not expect the ora-4030
as you said
A reader, April 28, 2006 - 3:55 am UTC
Ok, but as you said in response to my previous questions
that there might be multiple sort/hash
areas allocated at one time, so there might be that
my single SQL query have alltogather 30 SORT/HASH , each require 1GB and more than 16 of them have to be done concurrently
(i.e. they have to be allocated at one point in time),
this will definatly exceed 16gb physical ram and therefore
the error 4030
April 28, 2006 - 12:17 pm UTC
no, not 30 - more like "2"
64-bit Oracle AIX + 1GB sort_area_size + ORA-4030 = call support
Roderick, April 28, 2006 - 4:25 am UTC
You may be on version that has bugs in memory allocations. If so, setting an undocumented parameter
_use_realfree_heap = false
may provide a workaround. But I think patches may be available as well depending on if a bug actually is at play here.
???
A reader, April 29, 2006 - 5:33 am UTC
"no, not 30 - more like "2""
Tom, I completely cannot understand what you mean?
Can you please ellaborate it
Thank you very much for your help
April 30, 2006 - 4:50 am UTC
You said "so my single sql could have 30 sort/hash..."
and I said "No, not 30, more like 2"
You have a serial query here, we might have a sort area (sort area 1) that needs to be re-sorted a different way into sort area 2. When that is done, sort area 1 isn't really necessary anymore, so away it goes and you are back down to 1 sort area.
So "more like 2"
how many sort areas at one time
A reader, April 30, 2006 - 5:50 am UTC
So this means (in my case) there can be atmost 2 sort
areas allocated at any one point in time, so atmost
2GB, therefore ora-04030 should not appear, right?
April 30, 2006 - 1:53 pm UTC
given what you have said (if your description of what is going on at the time is accurate)
and given that what your SA has said (that ulimit is not set in the environment of the Oracle backend processes)
Then yes, you should not be hitting this - but remember, I've said "you really ought to be in touch with support" as well as Roderick (who actually is with support...) said basically the same :)
Bug 3130972
A reader, May 03, 2006 - 7:32 am UTC
I have searched metalink for this problem and here
is what I found.
Bug 3130972
Description
The realfree allocator on Unix systems imposes a cap at 1Gb
of memory per process. This fix relaxes that limit as
in some cases it is desirable to allow a process to use
over 1Gb of private memory. If the limit is reached an
ORA-4030 occurs even though the system may have plenty
of free memory available and can map that memory into the
process address space.
(The realfree allocator is used on Unix systems where
PGA_AGGREGATE_TARGET is in use)
Workaround:
Configure the system to allow less than 1Gb of memory
per process to avoid ORA-4030 errors.
PGA allocation - ORA-4030
Adrian Mos, June 07, 2006 - 2:45 pm UTC
Hi Tom,
IÂ’m using 10.2.0.2 on a Windows 2003. It is a development box and IÂ’m the only one using it.
These are my PGA stats:
1 aggregate PGA target parameter 600.0 mbytes
2 aggregate PGA auto target 491.2 mbytes
3 global memory bound 100.0 mbytes
4 total PGA inuse 57.4 mbytes
5 total PGA allocated 110.1 mbytes
6 maximum PGA allocated 438.1 mbytes
7 total freeable PGA memory .0 mbytes
8 process count 35.0
9 max processes count 47.0
10 PGA memory freed back to OS .0 mbytes
11 total PGA used for auto workareas 3.1 mbytes
12 maximum PGA used for auto workareas 5.7 mbytes
13 total PGA used for manual workareas .0 mbytes
14 maximum PGA used for manual workareas .5 mbytes
15 over allocation count .0
16 bytes processed 2,114.8 mbytes
17 extra bytes read/written .0 mbytes
18 cache hit percentage 100.0 percent
19 recompute count (total) 21,336.0
IÂ’m using JAXP API to run XSLT Transformation (SAXON parser) on large XML files (15MB) and IÂ’m getting these errors:
ORA-04030: out of process memory when trying to allocate 753120 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 16384308 bytes (joxp heap,f:OldSpace)
You said :
o aggregate PGA auto target: amount of tunable PGA memory the system can use. This is the memory left over for sorts, hash joins, bitmap operations and the like. The difference between this and the PGA target parameter (93.9 megabytes in this example) represents parts of the PGA that are not tunable such as cursor areas, PLSQL and/or Java memory, context information for the session and the like.
I suspect that I’m running out of this “not tunable” part of PGA. Since there is plenty of PGA still available when it crashes, what pool area from PGA I’m maxing out? Is there a way to manually set this “not tunable” area? If I’m on a wrong track please advice.
Another thing I canÂ’t explain is the total memory consumption by Oracle. Windows reports that oracle.exe uses 1.65 GB of memory usage (1.74 GB of Virtual Memory). My SGA is set to 500 MB. If I add to that 110 MB of allocated PGA it doesnÂ’t get near that 1740 MB reported by Windows. What is all that extra memory used for?
June 07, 2006 - 3:44 pm UTC
have you monitored the pga in use by session via v$sesstat?
Your max pga was much larger than 110
PGA allocation - ORA-4030
Adrian Mos, June 09, 2006 - 8:23 am UTC
Yes, I had. It was showing 140MB for a while and then, right before it crashed, it showed 190MB for that process. Either way the numbers don't add up. I don't know what limit I'm reaching and I don't know why Windows reports a usage of about 700MB RAM more than Oracle it's suppose to be using.
Thank you
June 09, 2006 - 8:43 am UTC
share with me the queries you used to measure memory used by Oracle from within Oracle itself.
PGA allocation - ORA-4030
Adrian Mos, June 09, 2006 - 10:28 am UTC
SELECT
name,
TO_CHAR(DECODE( unit,'bytes', value/1024/1024, value ),'999,999,999.9') value,
DECODE( unit, 'bytes', 'mbytes', unit ) unit
FROM v$pgastat
For the session stats I'm using the Sessions window in PL/SQL Developer.
June 09, 2006 - 1:21 pm UTC
I mean sga size and everything.
show me how your arrive at the "total total"
v$pgastat shows you "right now", not "at the most"
and immediately following a 4030, there is typically 'a lot less allocated then just a second ago' since, well, the session failed and didn't need the memory anymore.
PGA allocation - ORA-4030
Adrian Mos, June 09, 2006 - 2:38 pm UTC
OK. About the total amount of memory reported by Windows: my math was (too) simple, I was using the “Total SGA Size” from EM (500 MB) and the “total PGA allocated” from v$pgastat. The sum was 1GB smaller that what OS reported. But after your last follow up I started looking at v$sga and v$sgastat and I noticed that “Variable Size” in v$sga was 1.5 GB and the sum(bytes) from v$sgastat was 504MB. I was expecting that these values will be the same. But then I remembered that I set “Maximum SGA Size” in EM to 1.5 GB to be able to dynamically play with the “Total SGA Size”. I didn’t realize at the time that in order to be able to dynamically play with “Total SGA Size” Oracle actually grabs 1.5 GB from OS even though it only uses 504 MB (the size of my “Total SGA Size”).
Now, about running out of memory when running XSLT Transformation: I have to wait until the end of the day to run isolated tests. I’ll also get “Maximum SGA Size” in sync with “Total SGA Size”. Releasing that 1 GB of memory might solve my problem. I’ll try to capture the memory usage more closely.
IÂ’ll come back with test results.
Memory Consumption too high
Hitesh Bajaj, November 21, 2006 - 5:27 am UTC
Hi Tom,
We have a database server having 16G of RAM and we are running warehouse operations. We are loading data via SQL*ldr process via remote connections
In order to have better throughput for hash joins where in we scan million(s) of rows I was thinking of allocating more memory to the pga_aggregate_target
but the memory consumption on this box is already full to 16G.
The SGA has been configured as
SQL>show sga;
Total System Global Area 7667187712 bytes
Fixed Size 778796 bytes
Variable Size 1081351636 bytes
Database Buffers 6553600000 bytes
Redo Buffers 31457280 bytes
which various init.ora parameters contributor to the memory consumers specified as:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 752M
db_block_buffers integer 800000
db_block_size integer 8192
use_indirect_data_buffers boolean TRUE
large_pool_size big integer 256M
_PX_use_large_pool boolean TRUE
pga_aggregate_target big integer 800M
java_pool_size big integer 16M
log_buffer integer 31457280
open_cursors integer 500
open_links integer 10
db_writer_processes integer 8
log_archive_max_processes integer 8
aq_tm_processes integer 0
job_queue_processes integer 18
processes integer 1000
parallel_max_servers integer 64
parallel_min_percent integer 0
parallel_min_servers integer 8
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 4
08:39:13 up 32 days, 19:55, 5 users, load average: 0.17, 0.11, 0.15
262 processes: 261 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 99.7%
cpu00 0.3% 0.1% 0.0% 0.0% 0.0% 0.3% 99.0%
cpu01 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 100.0%
cpu02 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 100.0%
cpu03 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 100.0%
Mem: 16304524k av, 16149328k used, 155196k free, 0k shrd, 69848k buff
1230012k actv, 3333840k in_d, 270020k in_c
Swap: 16776952k av, 1484k used, 16775468k free 13832588k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
7543 oracle 15 0 1595M 1.6G 1587M S 0.0 10.0 4:02 0 oracleadmw (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
2045 oracle 15 0 1598M 1.6G 1586M S 0.0 10.0 216:30 3 ora_p000_admw
2049 oracle 15 0 1599M 1.6G 1586M S 0.0 10.0 217:23 3 ora_p002_admw
2065 oracle 15 0 1597M 1.6G 1585M S 0.0 10.0 233:40 1 ora_p005_admw
2047 oracle 15 0 1599M 1.6G 1586M S 0.0 10.0 216:59 0 ora_p001_admw
2051 oracle 15 0 1597M 1.6G 1586M S 0.0 10.0 217:43 2 ora_p003_admw
2057 oracle 15 0 1599M 1.6G 1586M S 0.0 10.0 252:54 2 ora_p004_admw
2069 oracle 15 0 1598M 1.6G 1585M S 0.0 10.0 243:47 1 ora_p007_admw
2067 oracle 15 0 1596M 1.6G 1583M S 0.0 9.9 232:10 0 ora_p006_admw
2019 oracle 15 0 1583M 1.5G 1577M S 0.0 9.9 19:33 2 ora_dbw0_admw
2031 oracle 15 0 1583M 1.5G 1577M S 0.0 9.9 17:56 2 ora_dbw6_admw
2025 oracle 15 0 1583M 1.5G 1577M S 0.0 9.9 18:52 0 ora_dbw3_admw
2033 oracle 15 0 1583M 1.5G 1577M S 0.0 9.9 19:56 2 ora_dbw7_admw
2023 oracle 15 0 1583M 1.5G 1577M S 0.0 9.9 18:44 1 ora_dbw2_admw
2029 oracle 15 0 1583M 1.5G 1577M S 0.0 9.9 19:28 1 ora_dbw5_admw
2027 oracle 15 0 1583M 1.5G 1577M S 0.0 9.9 19:07 0 ora_dbw4_admw
2021 oracle 15 0 1583M 1.5G 1577M S 0.0 9.9 18:07 3 ora_dbw1_admw
2039 oracle 15 0 1397M 1.4G 1392M S 0.0 8.7 3:09 1 ora_smon_admw
14734 oracle 15 0 1343M 1.3G 1339M S 0.0 8.4 0:00 1 oracleadmw (LOCAL=NO)
2186 oracle 15 0 1282M 1.2G 1256M S 0.0 8.0 4:27 0 ora_mmon_admw
9023 oracle 15 0 1190M 1.2G 1186M S 0.0 7.4 93:37 0 ora_q001_admw
22627 oracle 15 0 1157M 1.1G 1152M S 0.0 7.2 0:00 2 oracleadmw (LOCAL=NO)
22545 oracle 15 0 1157M 1.1G 1151M S 0.0 7.2 0:00 0 oracleadmw (LOCAL=NO)
16284 oracle 15 0 1150M 1.1G 1146M S 0.0 7.2 0:00 0 oracleadmw (LOCAL=NO)
17428 oracle 16 0 1147M 1.1G 1143M S 0.0 7.2 0:00 1 oracleadmw (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
6993 oracle 15 0 1134M 1.1G 1127M S 0.0 7.1 0:20 2 ora_j000_admw
16023 oracle 16 0 1094M 1.1G 1089M S 0.0 6.8 0:01 1 oracleadmw (LOCAL=NO)
12179 oracle 15 0 1092M 1.1G 1087M S 0.0 6.8 0:00 2 oracleadmw (LOCAL=NO)
2166 oracle 15 0 1086M 1.1G 1067M S 0.0 6.7 20:01 1 ora_arc0_admw
2170 oracle 15 0 1086M 1.1G 1067M S 0.0 6.7 19:20 1 ora_arc2_admw
2176 oracle 15 0 1086M 1.1G 1067M S 0.0 6.7 18:12 1 ora_arc5_admw
2174 oracle 15 0 1086M 1.1G 1067M S 0.0 6.7 3:12 3 ora_arc4_admw
2180 oracle 15 0 1086M 1.1G 1067M S 0.0 6.7 20:08 3 ora_arc7_admw
2168 oracle 15 0 1086M 1.1G 1067M S 0.0 6.7 3:16 1 ora_arc1_admw
2172 oracle 15 0 1086M 1.1G 1067M S 0.0 6.7 19:21 3 ora_arc3_admw
15887 oracle 16 0 1079M 1.1G 1074M S 0.0 6.7 0:01 0 oracleadmw (LOCAL=NO)
16673 oracle 15 0 1078M 1.1G 1072M S 0.0 6.7 0:00 0 oracleadmw (LOCAL=NO)
16769 oracle 15 0 1078M 1.1G 1072M S 0.0 6.7 0:00 2 oracleadmw (LOCAL=NO)
11013 oracle 15 0 1077M 1.1G 1072M S 0.0 6.7 0:00 1 oracleadmw (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
8531 oracle 15 0 1076M 1.1G 1071M S 0.0 6.7 0:00 3 oracleadmw (LOCAL=NO)
16237 oracle 15 0 1075M 1.0G 1071M S 0.0 6.7 0:00 0 oracleadmw (LOCAL=NO)
16032 oracle 15 0 1074M 1.0G 1069M S 0.0 6.7 0:00 0 oracleadmw (LOCAL=NO)
15890 oracle 16 0 1074M 1.0G 1069M S 0.0 6.7 0:00 1 oracleadmw (LOCAL=NO)
15434 oracle 15 0 1073M 1.0G 1069M S 0.0 6.7 0:00 3 oracleadmw (LOCAL=NO)
15430 oracle 15 0 1073M 1.0G 1069M S 0.0 6.7 0:00 3 oracleadmw (LOCAL=NO)
18694 oracle 15 0 1073M 1.0G 1069M S 0.0 6.7 0:00 0 oracleadmw (LOCAL=NO)
15128 oracle 15 0 1073M 1.0G 1069M S 0.0 6.7 0:00 0 oracleadmw (LOCAL=NO)
All commands "oracleadmw (LOCAL=NO)" are remote connections and they seems not to get released even if there is no activity on the box. I mapped these PID to
the V$session table
1 Select Status from v$session Where Paddr in (select Addr from V$process Where spid in (7543,
2 14734,
3 22627,
4 22545,
5 16284,
6 17428,
7 16023,
8 12179,
9 15887,
10 16673,
11 16769,
12 11013,
13 8531,
14 16237,
15 16032,
16 15890,
17 15434,
18 15430,
19 18694,
20 15128)
21* )
dwuser@ADMW>/
STATUS
--------
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
INACTIVE
Having known that the dedicated connection to the box consumes roughly max 2M per session connection, the database memory consumption is too high in any case.
The consumption of each process is nearly 1.5G according to the RSS column of the top monitor.
Why the memory consumption is too high while making dedicated connection(s) or I am off track. But When I disconnect from those session the memory released is
insignificant.
Please guide us!
Regards,
November 22, 2006 - 3:23 pm UTC
each process has parts of the sga attached, top is useless here.
You have lots of free memory - 8gb used by the SGA, probably 1gb or less by the dedicated servers (given the 800mb pga)
and linux/unix's will typically use all other free memory for a unix file system cache (which it will free as processes need it)
top is not showing you anything you can really use here.
Perfect, as always
Philippe Lefèvre, November 22, 2006 - 12:40 pm UTC
Thank You Tom, your review was perfect for me. I have been asked to review the oracle parameters for a customer, and I had a doubt about their PGA_AGGREGATE_TARGET value (which I found too small).
Memory consumption
Hitesh Bajaja, November 26, 2006 - 5:30 am UTC
Hi tom,
Just to check whether the free memory is available in plenty, I did try to increase the shared_pool_size for my testing purposes as Orcle will try to acquire it from OS at the same time, but it failed to do so complaining the the free memory is not sufficient to grow poll beyond.
Total System Global Area 7667187712 bytes
Fixed Size 778796 bytes
Variable Size 1081351636 bytes
Database Buffers 6553600000 bytes
Redo Buffers 31457280 bytes
dwuser@ADMW>! free -m
total used free shared buffers cached
Mem: 15922 15755 166 0 99 13436
-/+ buffers/cache: 2219 13703
Swap: 16383 1 16381
dwuser@ADMW>show parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_shared_pool_reserved_min_alloc big integer 4000
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 48M
shared_pool_size big integer 752M
shared_server_sessions integer
shared_servers integer 0
sys@ADMW>alter system set shared_pool_size = 1G scope = memory;
alter system set shared_pool_size = 1G scope = memory
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
Where is the memory going? I think we are wasting the proper usage of 7G Memory but doesn't understand Where it has got itself allocated.
Please see if you can help us a little more to explore in details so that the root cuase can be found.
November 26, 2006 - 9:39 am UTC
you'd have to shrink something before growing that.
Memory Consumption too high
hitesh bajaj, November 27, 2006 - 12:35 am UTC
Hi Tom,
<You have lots of free memory - 8gb used by the SGA, probably 1gb or less by the
dedicated servers (given the 800mb pga)>
On the basis of this statement I tried to enlarge my shared_pool but Oracle did refuse it since the free available memory to the OS was less than what we were asking shared pool to expand.
It is difficult to understand where the left out memory is being used up.
November 27, 2006 - 7:48 am UTC
huh?
To Hitesh
Roderick, November 29, 2006 - 10:52 pm UTC
You will not be able to dynamically increase your shared_pool_size withouth shrinking another pool unless you had set sga_max_size parameter to something larger than what is already being allocated to the shared_pool, large_pool, java_pool, and buffer cache. Since you are using db_block_buffers instead of db_cache_size, you will not be able to shrink the SGA memory claimed by the buffer cache.
The most likely way under your current situation to allocate additional memory from the OS to be used for the SGA (and for the shared pool) is to make appropriate changes to the init.ora / spfile and then bounce the database instance.
Question
PRS, February 03, 2007 - 6:41 pm UTC
Tom,
We have a 3rd party data warehouse application with database size 2TB. We are using ORACLE 9.2.0.7(64 bit).
We are having Sun E2900 server,96GB RAM,12 DUAL Core CPU.
Our WORKAREA_SIZE_POLICY set to AUTO and PGA_AGGREAGE_TARGET set to 2GB. Then rebuild of indexes on market data (50 Million rows per table, total 3 tables) were taking 45 minutes. When we increased it from 2 to 5GB,
rebuild of index performance is approved drastically and it is now 22 minutes.
We calculate statistics 5% of the database tables. This is a third party application which kicks off in parallel the statistics of each table using DBMS_STATS package. We have 3 tables each day 50 million rows being inserted into it and performance of the statistics job is taking more time. It runs for 3 hrs and the server is under utilized in terms of CPU and memory.
We do see some iowait at that time. I do not know internally what oracle does.
We know that we can decrease the sample from 5% to 2% and it will take less time.
I have two questions here.
1) Can we increase PGA_AGGREGATE_TARGET to 10GB? So statistics calculation job may go faster.
2) Are there any other parameters may require tunig to have the database statistics calculation go fast?
Thanks,
PRS
February 03, 2007 - 7:54 pm UTC
do you have 10gb?
Answer to your question
PRS, February 04, 2007 - 1:34 pm UTC
Yes. We have 96GB RAM, Sun Server E2900 with 12 dual core CPU (Total 24 CPU). Our SGA is only 2GB. So we can infact go upto 20 to 30GB for PGA. But my question is it worth to increase it to so high?
February 05, 2007 - 7:29 am UTC
you seem to be definitely showing that in your case "yes it could"
use the pga advisor in statspack or AWR if you have that. It'll help you see how many operations are done purely in memory and what the optimum setting to get things done entirely in memory might be for your system
pga related columns in v$process
VLS, March 06, 2007 - 10:27 am UTC
Hi Tom,
Excellent Stuff on PGA.
I joined v$process and v$session to find out top sessions using huge pga memory. I joined v$process.addr to v$session.paddr to get the result and for one such sid, got the following result :
SID PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
---------- ------------ ------------- -----------
1908 13381054 13546894 69645686 <---
163 20939398 32382878 32384070
I am most concerned about the first session. When I get the total of these three columns for all te sessions, it comes as : (These figures are in GB)
TOTAL_USED_MEM is total sum of PGA_USED_MEM
TOTAL_ALLOC_MEM is total sum of PGA_ALLOC_MEM and
TOTAL_PGA is total sum of PGA_MAX_MEM
TOTAL_USED_MEM TOTAL_ALLOC_MEM TOTAL_PGA TOTAL_SESSION
-------------- --------------- ---------- -------------
20 23 26 4107
What does this output say ?
Though, I have my pga_aggregate_target set to 2gb, I feel, that my pga overgrows to 26 GB and hence need to optimize this setting.
Physical RAM on my server is around 40GB and hence, when this value exceeds 28GB, I have observed major performance issues.
I am planning to add some more memory to the server to eliminate this performance issue.
Please correct me if I am wrong.
Regards
VLS
March 06, 2007 - 11:20 am UTC
you wrote the query and you are asking me what the output says???
Why do you feel that? do you KNOW that? or is it just a feeling?
In continuation to my previous post
VLS, March 06, 2007 - 12:10 pm UTC
Hi Tom,
Sorry for not clarifying on what I wanted.
I wanted to know the meaning of pga columns in v$process. For a particular sid, pga_max_mem shows upto 69m. The total for all sessions comes out to 26gb. Does it mean that the total PGA max memory could have grown upto 26gb ?
Even the sum of value from v$sesstat and v$statname where name like '%memory% shows almost the same.
Regards
VLS
March 06, 2007 - 12:28 pm UTC
"could have" - yes
"did" - no
that is the max in use. If the select max(that column) shows 69m, then all you can say is that at some point AT LEAST 69mb of memory was used.
If you select sum(that column), you have a rather meaningless number. The max values all happened (probably) at different times. So, at 1pm I used 50m, at 1:30pm you used 50m. The max memory in use at any point in time was 50m - but sum would say "100m"
use v$pgastat to analyze pga memory usage
One server to many databases
Tom Evert, March 28, 2007 - 11:06 am UTC
I hope this is a related question.
Our super server environment runs between 10 and 20 databases on each of two servers. I have been hesitant to set PGA because I would like to set it at the server level which is not possible (9208) rather than the database level. While I understand that Oracle will manage the process memory more efficiently within a database by using PGA, I see PGA memory being unavailable to the other databases on the server when those other databases may be heavily utilized and require more process memory. The way I see it I'd need to purchase additional memory to ensure that each database has sufficient PGA to handle peak workloads which I don't need today.
I have looked for info on setting PGA in this type of environment and not found a thing in almost four years. We don't use MTS. Can you offer any comment?
March 28, 2007 - 12:26 pm UTC
you are using pga memory regardless.
you either are setting sort/hash area size
or you are using pga_aggregate_target
(or both...)
having 10/20 databases per host makes it impossible to tune. having more than one database per host does actually.
I can comment that you need to consolidate these into a single instance - then tuning becomes feasible.
Until then, you are stuck with guessing at how much each instance should get and letting them have it.
the meaning of MAX_TEMPSEG_SIZE/LAST_TEMPSEG_SIZE in v$sql_workarea
deba, April 30, 2007 - 11:07 am UTC
Hi Tom,
I would like to know the meaning of columns LAST_TEMPSEG_SIZE and MAX_TEMPSEG_SIZE in v$sql_workarea . Is it in bytes ? Does it mean that it uses temporary tablespace ? I can find out the rows where LAST_TEMPSEG_SIZE is null but MAX_TEMPSEG_SIZE has some value. What is the meaning of this row then ? Could you please explain what are the meaning of these 2 columns in details ?
Thanks
Deba
April 30, 2007 - 3:25 pm UTC
Any change in 10g?
A reader, July 02, 2007 - 4:43 pm UTC
In Note 147806.1,
PGA_AGGREGATE_TARGET limits both the global PGA consumption and the size of a
workarea i.e. the memory allocated to a single SQL operator is also limited
to min(5% PGA_AGGREGATE_TARGET, 100MB) for serial operations, and to
30% PGA_AGGREGATE_TARGET/DOP for parallel operations (DOP=Degree of Parallelism).
Any change in 10g regarding 5%/100M limitations?
Thanks.
July 03, 2007 - 10:26 am UTC
yes, there was. and there will be incremental - undocumented refinements to this over time.
A reader, August 14, 2007 - 7:56 pm UTC
show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- -----
workarea_size_policy string AUTO
show parameter pga_aggregate_target
NAME TYPE VALUE
------------------------------------ ----------- -----
pga_aggregate_target big integer 700M
CREATE TABLE x NOLOGGING AS
SELECT * FROM all_objects;
INSERT INTO x
SELECT * FROM x;
INSERT INTO x
SELECT * FROM x;
INSERT INTO x
SELECT * FROM x;
INSERT INTO x
SELECT * FROM x;
INSERT INTO x
SELECT * FROM x;
INSERT INTO x
SELECT * FROM x;
commit;
SELECT COUNT(*) FROM x;
COUNT(*)
----------
2524864
SELECT object_type, status, COUNT(*)
FROM (
SELECT *
FROM (
SELECT /* THE QUERY */
row_number() OVER (PARTITION BY object_type, object_name
ORDER BY created DESC) rn,
object_type, object_name, status
FROM x
)
WHERE rn = 1
)
GROUP BY object_Type, status;
ERROR at line 9:
ORA-04030: out of process memory when trying to allocate 8208 bytes (sort subheap,sort key)
alter system set workarea_size_policy = manual scope = memory;
show parameter sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------
sort_area_size integer 524288
-- ----------------------------------------------------------------------------
-- Run the same query again.
-- ----------------------------------------------------------------------------
SELECT object_type, status, COUNT(*)
FROM (
SELECT *
FROM (
SELECT /* THE QUERY */
row_number() OVER (PARTITION BY object_type, object_name
ORDER BY created DESC) rn,
object_type, object_name, status
FROM x
)
WHERE rn = 1
)
GROUP BY object_Type, status;
OBJECT_TYPE STATUS COUNT(*)
------------------- ------- ----------
FUNCTION VALID 195
INDEXTYPE VALID 6
OPERATOR VALID 51
JAVA CLASS VALID 15879
SEQUENCE VALID 102
XML SCHEMA VALID 24
PACKAGE BODY VALID 1
PROCEDURE VALID 24
LOB VALID 60
SYNONYM INVALID 19
CONSUMER GROUP VALID 2
JOB CLASS VALID 1
LIBRARY VALID 15
WINDOW GROUP VALID 1
TABLE VALID 384
EVALUATION CONTEXT VALID 1
JAVA RESOURCE VALID 766
SYNONYM VALID 18793
PACKAGE VALID 249
INDEX VALID 646
VIEW VALID 1272
WINDOW VALID 2
TYPE VALID 956
SCHEDULE VALID 1
select name,
to_char(decode( unit,
'bytes', value/1024/1024,
value ),'999,999,999.9') value,
decode( unit, 'bytes', 'mbytes', unit ) unit
from v$pgastat;
NAME VALUE UNIT
--------------------------------------------- -------------- -------
aggregate PGA target parameter 700.0 mbytes
aggregate PGA auto target 574.3 mbytes
global memory bound 100.0 mbytes
total PGA inuse 61.9 mbytes
total PGA allocated 82.7 mbytes
maximum PGA allocated 124.4 mbytes
total freeable PGA memory .0 mbytes
process count 120.0
max processes count 123.0
PGA memory freed back to OS .0 mbytes
total PGA used for auto workareas .0 mbytes
maximum PGA used for auto workareas 80.0 mbytes
total PGA used for manual workareas .0 mbytes
maximum PGA used for manual workareas 1.0 mbytes
over allocation count .0
bytes processed 14,617.0 mbytes
extra bytes read/written 12,395.6 mbytes
cache hit percentage 54.1 percent
recompute count (total) 400,514.0
SELECT
case when low_optimal_size < 1024*1024
then to_char(low_optimal_size/1024,'999999') ||
'kb <= PGA < ' ||
(HIGH_OPTIMAL_SIZE+1)/1024|| 'kb'
else to_char(low_optimal_size/1024/1024,'999999') ||
'mb <= PGA < ' ||
(high_optimal_size+1)/1024/1024|| 'mb'
end pga_size,
optimal_executions,
onepass_executions,
multipasses_executions
from v$sql_workarea_histogram
where total_executions <> 0
order by low_optimal_size
/
PGA_SIZE OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
------------------------- ------------------ ------------------ ----------------------
2kb <= PGA < 4kb 266394 0 0
4kb <= PGA < 8kb 234 0 0
8kb <= PGA < 16kb 5163 0 0
16kb <= PGA < 32kb 284 0 0
32kb <= PGA < 64kb 87 0 0
64kb <= PGA < 128kb 712 1 0
128kb <= PGA < 256kb 261 0 0
256kb <= PGA < 512kb 233 28 0
512kb <= PGA < 1024kb 7493 0 0
1mb <= PGA < 2mb 55 0 2
2mb <= PGA < 4mb 50 2 4
4mb <= PGA < 8mb 6 32 0
8mb <= PGA < 16mb 14 5 0
16mb <= PGA < 32mb 2 0 0
64mb <= PGA < 128mb 0 2 6
128mb <= PGA < 256mb 0 18 6
256mb <= PGA < 512mb 0 10 0
select
trunc(pga_target_for_estimate/1024/1024)
pga_target_for_estimate,
to_char(pga_target_factor * 100,'999.9') ||'%'
pga_target_factor,
trunc(bytes_processed/1024/1024) bytes_processed,
trunc(estd_extra_bytes_rw/1024/1024) estd_extra_bytes_rw,
to_char(estd_pga_cache_hit_percentage,'999') || '%'
estd_pga_cache_hit_percentage,
estd_overalloc_count
from v$pga_target_advice;
PGA_TARGET_FOR_ESTIMATE PGA_TAR BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_ ESTD_OVERALLOC_COUNT
----------------------- ------- --------------- ------------------- ----- --------------------
87 12.5% 36 0 100% 0
175 25.0% 36 0 100% 0
350 50.0% 36 0 100% 0
525 75.0% 36 0 100% 0
700 100.0% 36 0 100% 0
840 120.0% 36 0 100% 0
979 140.0% 36 0 100% 0
1120 160.0% 36 0 100% 0
1260 180.0% 36 0 100% 0
1400 200.0% 36 0 100% 0
2100 300.0% 36 0 100% 0
2800 400.0% 36 0 100% 0
4200 600.0% 36 0 100% 0
5600 800.0% 36 0 100% 0
Based on the above output of the system views, it does not seem like the first query should not be able to obtain enough memory to do the sorting. What could be the problem?
August 20, 2007 - 12:57 pm UTC
you didn't describe the OS at all.
this is an ora-4030, out of PROCESS MEMORY (from the OS)
all you proved here was "if you use 0.5 mb max for sorting, you did not fail given your current workload, further given my 700mb pga target and my current workload and any other OS restrictions (like ulimits, hitting the max memory on 32bit windows, whatever...) - we fail to be able to allocate that much memory"
you ran out of OS memory.
A reader, August 21, 2007 - 5:05 pm UTC
The OS is Windows 32 bit. There are no other major applications running on the server. What other info would you need from me in order to troubleshoot this? Thanks.
August 22, 2007 - 12:34 pm UTC
32bit windows, you probably ran out of memory (single process, very limited addressing)
You do not say what your SGA was sized to.
ensure SGA+PGA is very much less than 1.7gb total.
A reader, August 22, 2007 - 5:30 pm UTC
Looks like sga + pga is way beyond 1.7 GB. Is that why?
SQL> show sga
Total System Global Area 1652555776 bytes
Fixed Size 1292144 bytes
Variable Size 595593360 bytes
Database Buffers 1048576000 bytes
Redo Buffers 7094272 bytes
SQL> show parameter pga_aggregate_target
NAME TYPE VALUE
------------------------------------ ----------- -----
pga_aggregate_target big integer 700M
Can you explain why it worked when the workarea_size_policy was set to MANUAL, causing sort_area_size to be used? I ran the test in a single user environment.
August 23, 2007 - 10:53 am UTC
yes.
if you use a fixed size maximum sort area, you limited the amount of memory your sort could take (1/2 mb)
with the pga aggregate target you set, the maximum allowable sort area was some 70times larger.
so, it worked with manual because you limited the amount of ram to 0.5 mb for the sort and with automatic you said "please go much higher, thanks"
and you couldn't go that much higher.
You have allocated almost all addressable memory for the oracle.exe process (a single process that all of your connections live in) to the SGA in a 32bit windows environment.
Get that SGA way down.
pga_aggregate_target in shared server....
aman...., May 07, 2008 - 11:04 pm UTC
Hi sir,
The PGA_AGGREGATE_TARGET as I understand it makes more sense and benefit when we are in teh dedicated server where all have their own PGA to work upon.How this parameter works (if it does) in the Shared Server where the PGA(UGA) is shared among all the users and there is no dedicated PGA for anyone?In the case, it does work than how the allocations are done for the users in the case their workareas go beyond the limits and need more memory to make the over alloaction count under optimal or one-pass executions?
I searched in the Performance Tuning guide but except for the setting guidelines, nothing else I could find about the difference of this param for dedicate/shared server mode.
Thanks and regards
Aman....
May 08, 2008 - 3:36 am UTC
in 9i, shared server did not use pga_aggregate_target.
in 10g, it does, the shared server can dynamically allocate pga for sorting and hashing, use it, and then free it.
So, in 9i, remember that you still use the *_area_size parameters for shared server connections and the dedicated servers can use pga aggregate target
and in 10g, both can use the pga_aggregate_target equally.
pga_aggregate_target for RAC
A reader, June 21, 2009 - 11:30 am UTC
Hi Tom,
We have a 10 node cluster with 7G physical memory configured for pga_aggregate_target at each node and this is a big data warehouse, where in we have many multiple jobs/reports run at any point of time
I have some doubts which I would like you to address:
1.We use parallel processing for doing ETL transformations which inculde LOAD as SELECT, Hash Joins, SORTS and Rebuilding of Bitmaps, Bitmap Merge which essentially uses PGA. Based on the article 30% of PGA_AGGREGATE_TaRGET/DOP is what the slave get is it we are saying that we cannot run more than 4 concurrent users with Parallel operations.
eg. If we have 100 M configured as PGA_AGGREGATE_TARGET and we do Select /*+ Parallel(x 4) */ id, count(*) from big_table x group by Id;
30% of 100 M = 30M allocated to this co-ordinator which internally allocates 7 M to each slave.
If I have 4 concurrent sessions running like this I will exhaust my PGA_AGGREGATE_TARGET. How will the 5th session work??
2. All tables in our warehouse are configured with DEGREE default and INSTANCES default so any SQL operation work across instances with slaves working for the co-ordinator at all instances. How does the memory allocation happen considering this case.
Is it 70G * 30% = 21G allocated to this process. I think this is totally non-scaleable solution. OR is it the instance at which the query co-ordinator is running ie. 7G * .30 = 2G allocated to this server processes.
3.Can you please give the audience an example of how a parallel operation work as you stated above and proved 5% in case of a serial process.
Thanks
June 21, 2009 - 2:19 pm UTC
1) the memory is backed off depending on concurrent workload - the more you give us to do, the less everyone gets - the formulas are not hard and fast, they are not fixed, they are designed to TRY to NOT exceed (stress 'try') your target. The more you do, the less everyone gets.
2) see #1, the more you do concurrently, the less everyone gets. The various internal, undocumented init.ora parameters would control the MAXIMUM your parallel session would get and it will not use it all at once - it does not pre-allocate it - it is an upper limit and the upper limit might be X at time T0 and Y at time T1 and X again at time T2. It depends.
3) do the same thing I did, do it in parallel, you don't need me for that, you already have the techniques for measuring everything.
And remember the numbers *change* from release to release, version to version, that is why it is called "automatic", we vary (fine tune) the implementation over time. This was just a way to see what it was in that release and to show how you could measure it yourself.
How about you do it for the "audience"?
mfz, January 26, 2010 - 12:35 pm UTC
In the metalink , I see the "system health information" as "Automatic PGA memory management is not being used " for this system.
But here are the parameters . It is automatic . What does this mean ?
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Jan 26 13:29:11 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 13120M
memory_target big integer 13120M
pga_aggregate_target big integer 0
sga_target big integer 0
January 29, 2010 - 2:45 pm UTC
ops$tkyte%ORA11GR2> show parameter workarea
NAME TYPE VALUE
------------------------------------ ----------- -----
workarea_size_policy string AUTO
what is the output of that.
also, what do you mean by:
In the metalink , I see the "system health information" as "Automatic PGA memory management is not being used " for this system. where in metalink does it talk about *your* database specifically?
PGA SIZE
sam, February 03, 2010 - 3:21 pm UTC
Tom:
Do you agree that I have undersized PGA at 25 M for OLTP system with 400 processes and 100 average concurrent processes.
Shall i increase that to 75 M by adjusting the PGA_AGGREGATE_TARGET
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 25165824
SQL> select name,
2 to_char(decode( unit,
3 'bytes', value/1024/1024,
4 value ),'999,999,999.9') value,
5 decode( unit, 'bytes', 'mbytes', unit ) unit
6 from v$pgastat
7 /
NAME VALUE UNIT
---------------------------------------------------------------- -------------- ------------
aggregate PGA target parameter 24.0 mbytes
aggregate PGA auto target 4.0 mbytes
global memory bound 1.2 mbytes
total PGA inuse 43.9 mbytes
total PGA allocated 77.0 mbytes
maximum PGA allocated 84.9 mbytes
total freeable PGA memory 1.4 mbytes
PGA memory freed back to OS 1,966.3 mbytes
total PGA used for auto workareas .0 mbytes
maximum PGA used for auto workareas 3.9 mbytes
total PGA used for manual workareas .0 mbytes
NAME VALUE UNIT
---------------------------------------------------------------- -------------- ------------
maximum PGA used for manual workareas .0 mbytes
over allocation count 13,455.0
bytes processed 1,817.4 mbytes
extra bytes read/written 583.4 mbytes
cache hit percentage 75.7 percent
16 rows selected.
SQL> SELECT
2 case when low_optimal_size < 1024*1024
3 then to_char(low_optimal_size/1024,'999999') ||
4 'kb <= PGA < ' ||
5 (HIGH_OPTIMAL_SIZE+1)/1024|| 'kb'
6 else to_char(low_optimal_size/1024/1024,'999999') ||
7 'mb <= PGA < ' ||
8 (high_optimal_size+1)/1024/1024|| 'mb'
9 end pga_size,
10 optimal_executions,
11 onepass_executions,
12 multipasses_executions
13 from v$sql_workarea_histogram
14 where total_executions <> 0
15 order by low_optimal_size
16 /
PGA_SIZE OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
------------------------------------------------------------- ------------------ ------------------
8kb <= PGA < 16kb 35174 0 0
16kb <= PGA < 32kb 1047 0 0
32kb <= PGA < 64kb 230 0 0
64kb <= PGA < 128kb 234 0 0
128kb <= PGA < 256kb 430 0 0
256kb <= PGA < 512kb 138 0 0
512kb <= PGA < 1024kb 845 0 0
1mb <= PGA < 2mb 61 16 1
2mb <= PGA < 4mb 0 3 1
16mb <= PGA < 32mb 0 19 0
10 rows selected.
SQL> select
2 trunc(pga_target_for_estimate/1024/1024)
3 pga_target_for_estimate,
4 to_char(pga_target_factor * 100,'999.9') ||'%'
5 pga_target_factor,
6 trunc(bytes_processed/1024/1024) bytes_processed,
7 trunc(estd_extra_bytes_rw/1024/1024) estd_extra_bytes_rw,
8 to_char(estd_pga_cache_hit_percentage,'999') || '%'
9 estd_pga_cache_hit_percentage,
10 estd_overalloc_count
11 from v$pga_target_advice;
PGA_TARGET_FOR_ESTIMATE PGA_TAR BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_ ESTD_OVERALLOC_COUNT
----------------------- ------- --------------- ------------------- ----- --------------------
12 50.0% 1829 2730 40% 175
18 75.0% 1829 2730 40% 175
24 100.0% 1829 542 77% 172
28 120.0% 1829 542 77% 160
33 140.0% 1829 541 77% 153
38 160.0% 1829 541 77% 138
43 180.0% 1829 541 77% 102
48 200.0% 1829 537 77% 28
72 300.0% 1829 534 77% 0
96 400.0% 1829 534 77% 0
144 600.0% 1829 534 77% 0
PGA_TARGET_FOR_ESTIMATE PGA_TAR BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_ ESTD_OVERALLOC_COUNT
----------------------- ------- --------------- ------------------- ----- --------------------
192 800.0% 1829 534 77% 0
12 rows selected.
SQL> SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
2 ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
3 ESTD_OVERALLOC_COUNT
4 FROM v$pga_target_advice;
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
12 40 154
18 40 154
24 76 151
29 76 139
34 76 132
38 76 117
43 76 81
48 76 21
72 76 0<<<------------------------MY PGA should be here
96 76 0
144 76 0
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
192 76 0
12 rows selected.
SQL> select name c1,count c2,decode(total, 0, 0, round(count*100/total)) c3
2 from
3 (
4 select name,value count,(sum(value) over ()) total
5 from
6 v$sysstat
7 where
8 name like 'workarea exec%'
9 );
Workarea
Profile Count Percentage
----------------------------------- ------------ ----------
workarea executions - optimal 35,892 100
workarea executions - onepass 36 0
workarea executions - multipass 2 0
3 rows selected.
February 04, 2010 - 12:04 pm UTC
... I have undersized PGA at 25 M for OLTP system with 400
processes and 100 average concurrent processes.
...
Sam,
You've told me before words to the effect of:
"my DBA said"
This is something for me and your DBA to discuss perhaps. You would not be setting this.
PGA
A reader, February 04, 2010 - 4:53 pm UTC
Mr. Tom:
That changed. He was told to do what i say now.
Can you take a quick look and tell me if i am correct or wrong. The above stats tell me i need 75 M.
Looking at the advice here, it seems the cache hit ratio is stable between 32 M and above.
Can you tell me what shall i go with. still 32 M is tiny. Based on my reading you assign 1M per connection or PGA should be 20% of total SGA (840 M).
SQL> select * from v$pga_target_advice;
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT
----------------------- ----------------- --- --------------- ------------------- ------------------
12582912 .5 ON 5278094336 5815005184 48 485
18874368 .75 ON 5278094336 5815005184 48 485
25165824 1 ON 5278094336 1132362752 82 480
30198784 1.2 ON 5278094336 1132362752 82 456
35231744 1.4 ON 5278094336 1125935104 82 427
40264704 1.6 ON 5278094336 1122611200 82 395
45297664 1.8 ON 5278094336 1088203776 83 302
50331648 2 ON 5278094336 1074486272 83 115
75497472 3 ON 5278094336 1042620416 84 0
100663296 4 ON 5278094336 1042620416 84 0
150994944 6 ON 5278094336 1042620416 84 0
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT
----------------------- ----------------- --- --------------- ------------------- ------------------
201326592 8 ON 5278094336 1033214976 84
February 04, 2010 - 6:39 pm UTC
oh my, why?
if you have the ram, listen to the advisors and do what they recommend.
yes, it seems that raising the pga aggregate target would have a slight small effect on you. It could remove the 36 one pass and 2 multipass sorts out of 36,000 sorts.
good luck.
PGA
A reader, February 04, 2010 - 7:22 pm UTC
Tom:
You make it sound like you never met a DBA that does not understand memory performance tuning or oracle internal architecture!
that is good because now i understand how this whole things works from SGA/PGA/shared pool/buffer cache, etc.
I will go with the advisor and double PGA to 50meg.
You usally recommend 1 meg per process and then monitor it.
oracle recommeds 20% of the total SGA.
Richard Niemiec in oracle database 10g performance tuning (page 155) sized PGA at 512 M for a 100 users small system (up to 25 G bytes). Kind of strange?
Thanks for your advice.
February 07, 2010 - 11:33 pm UTC
... You usally recommend 1 meg per process and then monitor it. ...
where did I write that?
... Richard Niemiec in oracle database 10g performance tuning (page 155) sized PGA
at 512 M for a 100 users small system (up to 25 G bytes). Kind of strange?
....
why? If you understand it, explain why you think that is strange, don't just say "kind of strange?", write "I think it is kind of strange BECAUSE"
and then I'll either agree with you, disagree with you or more likely say "it depends - consider (a) and then (b)"
PGA
A reader, February 04, 2010 - 7:24 pm UTC
Tom:
I forgot to say they reboot the instance weekly for cold backup.
Are those stats based on Sunday reboot? that would not make sense. i would need to create temp tables and store the weekly stats to get a better picture.
February 07, 2010 - 11:34 pm UTC
use statspack/awr reports for a REPRESENTATIVE period of time.
pga
A reader, February 08, 2010 - 10:12 am UTC
Tom:
your 2nd comment on this thread
<<<I would start with 500 meg for the pga_aggregate_target -- 1+ meg/connection to start.>>>
For richard niemiec sizing of 512 M it is strange because I have similar size OLTP system running with 25M bytes and the pga advisor says i can take it up a little.
512 M is 20 times the size i have.
set configuration oracle 9i
Bekka Plond, September 26, 2010 - 8:34 pm UTC
I have windows server 2003 32-bit with RAM 16 GB, how to set configuration database oracle 9i R2 for the best performance , i want database use memory 10 GB
Thank a lot for your advice dan helps
Bekka Plond
September 27, 2010 - 12:03 pm UTC
... i want database use memory 10
GB ...
32bit, 10gb, oxymoron. That won't work. It cannot use that much really. You'd want a 64bit platform for that.