Distance checker : Distance checker Make sure you see these
select a value which is visible from last rows at 18
This is a positive highlighted value
This is a negative highlighted value
select a value which is visible from last rows at 14
This is a positive highlighted value
This is a negative highlighted value
Working with Automatic PGA : Working with Automatic PGA All graphs are taken from the document 'SQL Memory Management in Oracle9i' from Benoît Dageville and Mohamed Zait from Oracle Corporation Christo Kutrovsky
The Pythian Group
2005 Feb
What is PGA ? : What is PGA ? Program Global Area
i.e. Process Global Area
Why is PGA different from SGA
What goes into PGA
Sort
Hash
PL/SQL variables and cursor memory
Oracle memory model : Oracle memory model P1 P2 P3 P4 PGA SORT HASH PGA BIG
SORT cursors
pl/sql var. cursors
pl/sql var.
Virtual memory mapping : Virtual memory mapping P1 P2 32/64 bit addressing space 0 gb 1 gb 2 gb 3 gb 4 gb
The need for more memory : The need for more memory To make things go faster !
The fast = true parameter
Not really
More memory has different effects on sorts and hash joins
Effects on Sorting : Effects on Sorting Sorting has ONLY 3 modes
optimal or cache (all in memory)
1 pass (the entire result set is written once to disk)
multi-pass (the entire result set is written multiple times to disk)
Sorting response time : Sorting response time Response time Memory used Optimal 1 pass
Crossing optimal => 1 pass : Crossing optimal =andgt; 1 pass SQLandgt; select count(*) from (select * from TBLSESSION t where rownumandlt;= 920000 order by ses_ip);
…
Executed in 27.559 seconds
SQLandgt; select count(*) from (select * from TBLSESSION t where rownumandlt;= 930000 order by ses_ip);
…
Executed in 27.82 seconds
SQLandgt; select count(*) from (select * from TBLSESSION t where rownumandlt;= 940000 order by ses_ip);
…
Executed in 35.451 seconds
SQLandgt; select count(*) from (select * from TBLSESSION t where rownumandlt;= 950000 order by ses_ip);
…
Executed in 36.652 seconds
Crossing optimal => 1 pass : Crossing optimal =andgt; 1 pass select substr(sql_text,57,15) as sql, operation_type as op, operation_id as id, policy,
round(estimated_optimal_size/1024/1024,2) as e_opt, round(estimated_onepass_size/1024/1024,2) as e_one,
round(last_memory_used/1024/1024,2) as l_mem, last_execution as last,
total_executions as tot, optimal_executions as opt, onepass_executions as one, multipasses_executions as mult,
round(active_time/1000000,2) as sec, round(max_tempseg_size/1024/1024,2) as tmp_m, round(last_tempseg_size/1024/1024,2) as tmp_L
from v$sql_workarea swa, v$sql sq
where swa.address = sq.address and swa.hash_value = sq.hash_value
and sql_text like 'select count(*) from ( select * from TBLSESSION%‘ order by sql;
SQL OP ID E_OPT E_ONE L_MEM LAST TOT OPT ONE MULT SEC TMP_M TMP_L
--------- ---- -- ----- ----- ------ ------- --- --- --- ---- ------ ----- -----
andlt;=920000 SORT 3 96.5 3.28 85.78 OPTIMAL 1 1 0 0 26.62
andlt;=930000 SORT 3 97.56 3.3 86.72 OPTIMAL 1 1 0 0 26.91
andlt;=940000 SORT 3 87.28 3.13 87.27 1 PASS 1 0 1 0 34.32 80 80
andlt;=950000 SORT 3 88.22 3.15 87.27 1 PASS 1 0 1 0 35.53 80 80
Other nuances of SORTs : Other nuances of SORTs When sorting, you are sorting the ENTIRE result set. Thus you need as much memory as ALL your select columns
So yes, by adding an extra field (even a fixed string) you could just pass the limit and switch to one-pass sort
select * is even worse
Affecting sort memory needs : Affecting sort memory needs Adding a fixed string:
SQLandgt; select count(*) from (select t.*,'extra' from TBLSESSION t where rownumandlt;=920000 order by ses_ip);
…
Executed in 33.398 seconds
Narrowing the select list
SQLandgt; select count(*) from (select ses_id, ses_start, ses_refkey from TBLSESSION t where rownumandlt;= 950000 order by ses_ip);
…
Executed in 26.868 seconds
Switching to multi-pass : Switching to multi-pass Multipass is a long way down the road, compared to optimal =andgt; 1 pass
TYPE POLICY SID A_SEC WSIZE EXP ACT MAX PASSES TEMP
---- ------ --- ------ ------ ----- ------ ------ ------- ----
SORT AUTO 299 813.77 77.98 77.98 78.01 87.27 1 6750
Top N queries : Top N queries Remember top N queries?
SQLandgt; select count(*) from (select * from TBLSESSION t
where rownumandlt;=950000 order by ses_ip )
where rownum andlt;=1000;
…
Executed in 11.597 seconds
TYPE POLICY SID A_SEC WSIZE EXP ACT MAX PASSES TEMP
---- ------ ---- ------ ----- ----- ---- ----- ------ ----
SORT AUTO 336 11.42 0.21 0.21 0.21 0.21 0
Effect on HASH joins : Effect on HASH joins Hash joins have the same 3 modes: optimal (cache, in memory), 1 pass and multi-pass
Hash joins benefit from additional memory between optimal and one-pass execution
That is, according to Oracle, in my testing there was no measurable benefit
Slide16 :
Nuances HASH joins : Nuances HASH joins If the optimizer estimates it will go above the bound, it will use temp right away
The estimated value is 're-learned' from the last execution
Thus a 2nd execution, without any changes, may not use temp
If the 'estimate' is bypassed, then it will write to temp as needed, same way as a sort
Automatic PGA : Automatic PGA Now to the 'automatic' word
Slide19 :
Slide20 :
Global memory manager : Global memory manager Updates the 'global memory bound' every 3 seconds
Reacts to over allocation by lowering the bound further
Some facts : Some facts Oracle’s SQL operators can adjust the memory consumption in mid-execution
Sorts that switch from optimal to 1-pass will release the extra memory immediately (depending on disk speed)
Background jobs processes will not free-up the virtual memory, if sufficient memory is available
Slide23 :
Drift : Drift A drift of 10% is allowed
When the limit is passed, a 'foreground' bound recompute occurs
Slide25 :
Monitoring PGA activity : Monitoring PGA activity Overview
Details
Advisors
Monitoring - Overview : Monitoring - Overview SQLandgt; select * from v$pgastat;
NAME VALUE UNIT
-------------------------------------- ---------- --------
aggregate PGA target parameter 2097152000 bytes
aggregate PGA auto target 1866138624 bytes
global memory bound 104857600 bytes
total PGA inuse 23664640 bytes
total PGA allocated 132822016 bytes
maximum PGA allocated 564828160 bytes
total freeable PGA memory 4194304 bytes
PGA memory freed back to OS 2165370847 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 111509504 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 529408 bytes
over allocation count 0
bytes processed 5318923366 bytes
extra bytes read/written 2912501760 bytes
cache hit percentage 99.45 percent
Monitoring - Overview – no bytes : Monitoring - Overview – no bytes select name,round(value/1024/1024,1) as Mb from v$pgastat where unit = 'bytes';
NAME MB
-------------------------------------- ---------
aggregate PGA target parameter 2000
aggregate PGA auto target 1779.7
global memory bound 100
total PGA inuse 22.5
total PGA allocated 126.7
maximum PGA allocated 538.7
total freeable PGA memory 4
PGA memory freed back to OS 2065058.6
total PGA used for auto workareas 0
maximum PGA used for auto workareas 106.3
total PGA used for manual workareas 0
maximum PGA used for manual workareas 0.5
bytes processed 507256.6
extra bytes read/written 2777.6
Monitoring – getting the details 1 : Monitoring – getting the details 1 When the query is running v$sql_workarea_active
select operation_type as type, policy, sid, round(active_time/1000000,2) as a_sec,
round(work_area_size/1024/1024,2) as wsize, round(expected_size/1024/1024,2) as exp,
round(actual_mem_used/1024/1024,2) as act,round(max_mem_used/1024/1024,2) as max,
number_passes as passes, round(tempseg_size/1024/1024,2) as temp from v$sql_workarea_active;
TYPE POLICY SID A_SEC WSIZE EXP ACT MAX PASSES TEMP
---- ------ --- ----- ----- ----- ----- ----- ------ ----
SORT AUTO 299 284.5 54.73 54.73 53.75 87.27 1 3015
Monitoring – getting the details 2 : Monitoring – getting the details 2 When it has finished (or for another query) v$sql_workarea
select operation_type as op, operation_id as id, policy,
round(estimated_optimal_size/1024/1024,2) as e_opt, round(estimated_onepass_size/1024/1024,2) as e_one,
round(last_memory_used/1024/1024,2) as l_mem, last_execution as last,
total_executions as tot, optimal_executions as opt, onepass_executions as one, multipasses_executions as mult,
round(active_time/1000000,2) as sec, round(max_tempseg_size/1024/1024,2) as tmp_m, round(last_tempseg_size/1024/1024,2) as tmp_L
from v$sql_workarea where address = '0000000381E23CF0' and hash_value = '1505362365';
OP ID POLICY E_OPT E_ONE L_MEM LAST TOT OPT ONE MULT SEC TMP_M TMP_L
---------- -- ------ ----- ----- ----- ------- ---- ---- --- ---- ---- ----- -----
SORT 1 AUTO 0.02 0.02 0.02 OPTIMAL 227 227 0 0 0.02
HASH-JOIN 2 AUTO 0.63 0.63 0.34 OPTIMAL 227 227 0 0 0.02
HASH-JOIN 9 AUTO 0.88 0.88 1.14 OPTIMAL 146 146 0 0 0.01
Monitoring – getting the details 3 : Monitoring – getting the details 3 You can link workareas to steps from the EX.PLAN v$sql_plan
select rpad(' ', depth*3)||operation||' '||options||nvl2(object_name, ' -andgt; ','')||object_name||decode(search_columns,0,NULL,' ('||search_columns||')') as OP,cost, cardinality as CARD, bytes, id as 'id',access_predicates as 'ACCESS', filter_predicates as filter,round(temp_space/1024/1024) as TMP_MB,partition_start ||nvl2(partition_start, ' - ', '')||partition_stop as P, partition_id, other, other_tag, cpu_cost, io_cost, distribution, object_owner, parent_id,optimizer from ( select * from V$SQL_PLAN where address = hextoraw('0000000381E23CF0') and hash_value = '1505362365' and child_number = 0) t connect by prior id = parent_id start with id = 0 order by id, position;
OP id ACCESS TMP_MB
---------------------------------------------------- -- --------- ------
SELECT STATEMENT 0
SORT ORDER BY 1
HASH JOIN 2 'VID'='VID'
NESTED LOOPS 3
TABLE ACCESS INDEX ROWID -andgt; TAG_SYM 4
INDEX RANGE SCAN -andgt; TS_VSID (1) 5 'VSID'=:1
TABLE ACCESS BY INDEX ROWID -andgt; VEN_TAG 6
INDEX UNIQUE SCAN -andgt; VT_TAG_PK (1) 7 'TAG'='TAG'
VIEW 8
HASH JOIN 9 ROWID=ROWID
INDEX FAST FULL SCAN -andgt; VI_NAME_PK 10
INDEX FAST FULL SCAN -andgt; VI_VID_UK 11
Monitoring – getting the details 4 : Monitoring – getting the details 4 Summaries – overall per workarea size v$sql_workarea_histogram
SELECT LOW_OPTIMAL_SIZE/1024/1024 low_mb,(HIGH_OPTIMAL_SIZE+1)/1024/1024 high_mb, optimal_executions,onepass_executions,multipasses_executions
FROM v$sql_workarea_histogram
WHERE total_executions != 0
and (low_optimal_size/1024/1024 andgt;= 8 or total_executions andgt; optimal_executions)
;
LOW_MB HIGH_MB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
------ ------- ------------------ ------------------ ----------------------
2 4 3073 0 0
4 8 2033 0 0
8 16 402 0 0
16 32 1501 55 0
32 64 147 2295 0
64 128 0 374 0
128 256 0 28 0
256 512 0 3 0
512 1024 0 16 0
1024 2048 0 1 0
Monitoring – getting the details 5 : Monitoring – getting the details 5 Summaries – finding the bad ones
select operation_type as op, operation_id as id, policy,
round(estimated_optimal_size/1024/1024,2) as e_opt, round(estimated_onepass_size/1024/1024,2) as e_one,
round(last_memory_used/1024/1024,2) as l_mem, last_execution as last,
total_executions as tot, optimal_executions as opt, onepass_executions as one, multipasses_executions as mult,
round(active_time/1000000,2) as sec, round(max_tempseg_size/1024/1024,2) as tmp_m, round(last_tempseg_size/1024/1024,2) as tmp_L
from v$sql_workarea where max_tempseg_size is not null;
OP ID POLICY E_OPT E_ONE L_MEM LAST TOT OPT ONE MULT SEC TMP_M TMP_L
--------- -- ------- ----- ----- ----- ------ --- --- --- ---- ----- ----- -----
HASH-JOIN 4 AUTO 47.91 4.09 36.03 1 PASS 158 0 158 0 90.99 45 40
The Advisors – been really lazy : The Advisors – been really lazy Similar to v$db_cache_advice v$pga_target_advice Shows estimate temp IO usage
v$pga_target_advice_histogram can be used to estimate opt/1pass/multi executions for different workarea sizes
v$pga_target_advice : v$pga_target_advice select round(pga_target_for_estimate/1024/1024) as est_mb,pga_target_factor as factor,
round(bytes_processed/1024/1024) as p_mb,round(estd_extra_bytes_rw/1024/1024) as extra_mb,
estd_pga_cache_hit_percentage as hit_ratio,estd_overalloc_count as est_over from v$pga_target_advice;
EST_MB FACTOR P_MB EXTRA_MB HIT_RATIO EST_OVER
------ ------ ------ ---------- ---------- ----------
125 0.125 446362 768675 37 9797
250 0.25 446362 293602 60 2075
500 0.5 446362 181392 71 0
750 0.75 446362 176313 72 0
1000 1 446362 136979 77 0
1200 1.2 446362 136391 77 0
1400 1.4 446362 136391 77 0
1600 1.6 446362 136391 77 0
1800 1.8 446362 136391 77 0
2000 2 446362 136391 77 0
…
Slide36 :
To make it work : To make it work And now what you need to make it work
Documented parameters : Documented parameters pga_aggregate_target
that’s it
Before we continue : Before we continue The following slides will show undocumented parameters
Do not use them, unless instructed by Oracle support
Bugs you should be aware off
Patches/Bugs that are required : Patches/Bugs that are required 2805580 - corruption with auto PGA, 9.2.0.4
3130972 - allow more then 1gb per workarea 9.2.0.5
3586008 - the above 2 (as they conflict)
Check with Oracle what is required for your patch level
Maximum workarea size : Maximum workarea size The maximum PGA workarea is hard limited to 5% of pga_aggregate_target or a max of 100Mb
Can be controlled with _smm_max_size
Value is in KB !
Maximum PGA per process : Maximum PGA per process One process can have many workareas
Max total size can be controlled with _pga_max_size
Value is in bytes, default 200Mb
CAUTION: virtual address space limitation on 32 bit platforms
So what’s possible? : So what’s possible? Put really BIG sorts in memory !
A single workarea is still limited at 4gb
In addition due to steps-allocations, it’s spilling much earlier, around 3.5 gb
A process however is not limited to 4gb, at least on 64 bit system
The system : The system Solaris with 64 bit Oracle
9.2.0.5
32 Gb RAM
16 Gb SGA
pga_aggregate_target = 8000m
_pga_max_size = 8000000000
_smm_max_size = 5000000
Monitoring – The adjusted system : Monitoring – The adjusted system SQLandgt; select name,round(value/1024/1024,1) as Mb from v$pgastat where unit = 'bytes';
NAME MB
--------------------------------------- ----------
aggregate PGA target parameter 8000
aggregate PGA auto target 7139.4
global memory bound 4882.8
total PGA inuse 67.4
total PGA allocated 2940.5
maximum PGA allocated 9344.4
total freeable PGA memory 2783.5
PGA memory freed back to OS 2590908.6
total PGA used for auto workareas 0
maximum PGA used for auto workareas 6903.9
total PGA used for manual workareas 0
maximum PGA used for manual workareas 0.5
bytes processed 13542884
extra bytes read/written 1034611.9
A big sort 1 - running : A big sort 1 - running select operation_type, policy, sid, round(active_time/1000000,2) as a_sec,
round(work_area_size/1024/1024,2) as wsize, round(expected_size/1024/1024,2) as exp, round(actual_mem_used/1024/1024,2) as act,
round(max_mem_used/1024/1024,2) as max, number_passes as passes, round(tempseg_size/1024/1024,2) as temp
from v$sql_workarea_active;
OPE POLICY SID A_SEC WSIZE EXP ACT MAX PASSES TEMP
--- ------ ---- ------- -------- -------- -------- -------- ------- -----
SOR AUTO 26 148.66 3589.75 3589.75 3108.88 3108.88 0
A big sort 2 - spilling : A big sort 2 - spilling select operation_type, policy, sid, round(active_time/1000000,2) as a_sec,
round(work_area_size/1024/1024,2) as wsize, round(expected_size/1024/1024,2) as exp, round(actual_mem_used/1024/1024,2) as act,
round(max_mem_used/1024/1024,2) as max, number_passes as passes, round(tempseg_size/1024/1024,2) as temp
from v$sql_workarea_active;
OPER POLICY SID A_SEC WSIZE EXP ACT MAX PASSES TEMP
---- ------ --- ------ ------- ----- ------- ------- ------ ----
SORT AUTO 26 246.38 3589.75 46.41 3589.75 3589.75 0 1100
A big sort 3 - spilled (first fetch) : A big sort 3 - spilled (first fetch) select operation_type, policy, sid, round(active_time/1000000,2) as a_sec,
round(work_area_size/1024/1024,2) as wsize, round(expected_size/1024/1024,2) as exp, round(actual_mem_used/1024/1024,2) as act,
round(max_mem_used/1024/1024,2) as max, number_passes as passes, round(tempseg_size/1024/1024,2) as temp
from v$sql_workarea_active;
OPER POLICY SID A_SEC WSIZE EXP ACT MAX PASSES TEMP
---- ------ --- ------ ----- ---- ---- ------- ------ ----
SORT AUTO 26 378.28 3.28 3.28 3.52 3589.75 1 2800
Is it seriously using 3.5gb of ram? : Is it seriously using 3.5gb of ram? Oh yes, even more !
select round(pga_alloc_mem/1024/1024,1) as alloc_mb, round(pga_used_mem/1024/1024,1) as used_mb, round(pga_max_mem/1024/1024,1) as max_mb, pid, spid, serial#, program, traceid, background, pga_freeable_mem
from v$process order by alloc_mb desc
ALLOC_MB USED_MB MAX_MB SPID PROGRAM PGA_FREEABLE_MEM
-------- ------- ------ ------ ------------------------ ------
4787.8 4780.1 4787.8 16291 oracle@test (TNS V1-V3) 0
10.9 0.2 10.9 4320 oracle@test (DBW0) 0
5.3 5.1 5.3 19355 oracle@test (TNS V1-V3) 0
5.3 5.1 5.3 19318 oracle@test (TNS V1-V3) 0
4.2 4.1 4.2 19312 oracle@test (NSV0) 0
1.4 1.3 1.4 16251 oracle@test (TNS V1-V3) 65536
1 0.4 1 16241 oracle@test (TNS V1-V3) 524288
Checking doubts on the OS side : Checking doubts on the OS side I don’t believe that, that’s just oracle telling us
/home/oracleandgt; pmap -xs 16291|head
16291: oracleprod (LOCAL=NO)
Address Kbytes RSS Anon Locked Pgsz Mode Mapped File
0000000100000000 2272 2272 - - 8K r-x-- oracle
0000000100238000 104 96 - - - r-x-- oracle
…
/home/oracleandgt; pmap -xs 16291|tail
7FFFFFFF7FE00000 176 176 - - 8K r-x-- ld.so.1
7FFFFFFF7FF2C000 16 16 16 - 8K rwx-- ld.so.1
FFFFFFFF7FFE0000 128 128 128 - 8K rw--- [ stack ]
---------------- ---------- ---------- -------- --------
total Kb 21931752 21904656 4907016 16961536
The above values are in Kb. ~16 gb sga + ~5 gb anonymous memory, which is PGA = total 21 gb resident in use
Not used doesn’t mean returned : Not used doesn’t mean returned select round(pga_alloc_mem/1024/1024,1) as alloc_mb, round(pga_used_mem/1024/1024,1) as used_mb, round(pga_max_mem/1024/1024,1) as max_mb, pid, spid, username, serial#, program, round(pga_freeable_mem/1024/1024,1) as freeable_mb
from v$process order by alloc_mb desc;
ALLOC_MB USED_MB MAX_MB PID SPID USERNAME SERIAL# PROGRAM FREEABLE_MB
-------- ------- ------ --- ----- -------- ------- ------------- -----------
4100 3.7 4787.8 25 16291 oracle 2 … (TNS V1-V3) 4096
10.9 0.2 10.9 3 4320 oracle 1 … (DBW0) 0
5.3 5.1 5.3 18 19355 oracle 10 … (TNS V1-V3) 0
Wrap-up – what can be achieved : Wrap-up – what can be achieved Use more memory for sorting/hashing
Yet controlled automatically to avoid swapping
Resulting in improved response time and less disk IO
Resulting in more IO capacity available for other tasks
Basically fast = true
The End : The End Thank you,
Questions?
kutrovsky@pythian.com Christo Kutrovsky
The Pythian Group
2005 Feb http://www.pythian.com/documents/Working_with_Automatic_PGA.ppt