distance checker make sure you see these select a value which is visible from last rows at 18 this...
TRANSCRIPT
![Page 1: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/1.jpg)
Distance checker
Make sure you see these
select a value which is visible from last rows at 18This is a positive highlighted valueThis is a negative highlighted value
select a value which is visible from last rows at 14This is a positive highlighted valueThis is a negative highlighted value
![Page 2: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/2.jpg)
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
![Page 3: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/3.jpg)
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
![Page 4: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/4.jpg)
SGA
Oracle memory model
P1
P2
P3
P4PGA
SORT
HASH
PGABIG
SORT
cursorspl/sql var.
cursorspl/sql var.
PGA Sortcursors
pl/sql var.
PGA Hashcursorspl/sql var.
![Page 5: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/5.jpg)
Virtual memory mapping
P1
P2
SGA
32/64 bit addressing space
PGA Sortcursors
pl/sql var.
PGA Hashcursorspl/sql var.
0 gb 1 gb 2 gb 3 gb 4 gb
![Page 6: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/6.jpg)
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
![Page 7: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/7.jpg)
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)
![Page 8: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/8.jpg)
Sorting response time
Response time
Memory used
Optimal
1 pass
![Page 9: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/9.jpg)
Crossing optimal => 1 passSQL> select count(*) from (select * from TBLSESSION t where rownum<= 920000
order by ses_ip);…Executed in 27.559 seconds
SQL> select count(*) from (select * from TBLSESSION t where rownum<= 930000 order by ses_ip);
…Executed in 27.82 seconds
SQL> select count(*) from (select * from TBLSESSION t where rownum<= 940000 order by ses_ip);
…Executed in 35.451 seconds
SQL> select count(*) from (select * from TBLSESSION t where rownum<= 950000 order by ses_ip);
…Executed in 36.652 seconds
![Page 10: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/10.jpg)
Crossing optimal => 1 passselect 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--------- ---- -- ----- ----- ------ ------- --- --- --- ---- ------ ----- -----<=920000 SORT 3 96.5 3.28 85.78 OPTIMAL 1 1 0 0 26.62 <=930000 SORT 3 97.56 3.3 86.72 OPTIMAL 1 1 0 0 26.91 <=940000 SORT 3 87.28 3.13 87.27 1 PASS 1 0 1 0 34.32 80 80<=950000 SORT 3 88.22 3.15 87.27 1 PASS 1 0 1 0 35.53 80 80
![Page 11: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/11.jpg)
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
![Page 12: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/12.jpg)
Affecting sort memory needs Adding a fixed string:SQL> select count(*) from (select t.*,'extra' from TBLSESSION
t where rownum<=920000 order by ses_ip);…Executed in 33.398 seconds
Narrowing the select listSQL> select count(*) from (select ses_id, ses_start,
ses_refkey from TBLSESSION t where rownum<= 950000 order by ses_ip);
…Executed in 26.868 seconds
![Page 13: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/13.jpg)
Switching to multi-pass
Multipass is a long way down the road, compared to optimal => 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
![Page 14: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/14.jpg)
Top N queries
Remember top N queries?SQL> select count(*) from
(select * from TBLSESSION t where rownum<=950000 order by ses_ip )where rownum <=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
![Page 15: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/15.jpg)
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
![Page 16: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/16.jpg)
![Page 17: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/17.jpg)
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
![Page 18: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/18.jpg)
Automatic PGA
Now to the “automatic” word
![Page 19: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/19.jpg)
![Page 20: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/20.jpg)
![Page 21: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/21.jpg)
Global memory manager
Updates the “global memory bound” every 3 seconds
Reacts to over allocation by lowering the bound further
![Page 22: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/22.jpg)
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
![Page 23: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/23.jpg)
![Page 24: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/24.jpg)
Drift
A drift of 10% is allowed When the limit is passed, a “foreground”
bound recompute occurs
![Page 25: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/25.jpg)
![Page 26: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/26.jpg)
Monitoring PGA activity
Overview
Details
Advisors
![Page 27: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/27.jpg)
Monitoring - Overview SQL> 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
![Page 28: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/28.jpg)
Monitoring - Overview – no bytesselect name,round(value/1024/1024,1) as Mb from v$pgastat where unit = 'bytes';
NAME MB-------------------------------------- ---------aggregate PGA target parameter 2000aggregate PGA auto target 1779.7global memory bound 100total PGA inuse 22.5total PGA allocated 126.7maximum PGA allocated 538.7total freeable PGA memory 4PGA memory freed back to OS 2065058.6total PGA used for auto workareas 0maximum PGA used for auto workareas 106.3total PGA used for manual workareas 0maximum PGA used for manual workareas 0.5bytes processed 507256.6extra bytes read/written 2777.6
![Page 29: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/29.jpg)
Monitoring – getting the details 1
When the query is runningv$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
![Page 30: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/30.jpg)
Monitoring – getting the details 2 When it has finished (or for another query)
v$sql_workareaselect 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_Lfrom 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
![Page 31: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/31.jpg)
Monitoring – getting the details 3 You can link workareas to steps from the EX.PLAN
v$sql_planselect rpad(' ', depth*3)||operation||' '||options||nvl2(object_name, ' -> ','')||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 -> TAG_SYM 4 INDEX RANGE SCAN -> TS_VSID (1) 5 "VSID"=:1 TABLE ACCESS BY INDEX ROWID -> VEN_TAG 6 INDEX UNIQUE SCAN -> VT_TAG_PK (1) 7 "TAG"="TAG" VIEW 8 HASH JOIN 9 ROWID=ROWID INDEX FAST FULL SCAN -> VI_NAME_PK 10 INDEX FAST FULL SCAN -> VI_VID_UK 11
![Page 32: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/32.jpg)
Monitoring – getting the details 4Summaries – overall per workarea size
v$sql_workarea_histogramSELECT LOW_OPTIMAL_SIZE/1024/1024 low_mb,(HIGH_OPTIMAL_SIZE+1)/1024/1024 high_mb, optimal_executions,onepass_executions,multipasses_executionsFROM v$sql_workarea_histogramWHERE total_executions != 0and (low_optimal_size/1024/1024 >= 8 or total_executions > 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
![Page 33: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/33.jpg)
Monitoring – getting the details 5
Summaries – finding the bad onesselect 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
![Page 34: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/34.jpg)
The Advisors – been really lazy
Similar to v$db_cache_advice v$pga_target_adviceShows estimate temp IO usage
v$pga_target_advice_histogram can be used to estimate opt/1pass/multi executions for different workarea sizes
![Page 35: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/35.jpg)
v$pga_target_adviceselect 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…
![Page 36: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/36.jpg)
![Page 37: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/37.jpg)
To make it work
And now what you need to make it work
![Page 38: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/38.jpg)
Documented parameters
pga_aggregate_target that’s it
![Page 39: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/39.jpg)
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
![Page 40: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/40.jpg)
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
![Page 41: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/41.jpg)
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 !
![Page 42: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/42.jpg)
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
![Page 43: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/43.jpg)
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
![Page 44: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/44.jpg)
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
![Page 45: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/45.jpg)
Monitoring – The adjusted systemSQL> select name,round(value/1024/1024,1) as Mb from v$pgastat where unit = 'bytes';
NAME MB--------------------------------------- ----------aggregate PGA target parameter 8000aggregate PGA auto target 7139.4global memory bound 4882.8total PGA inuse 67.4total PGA allocated 2940.5maximum PGA allocated 9344.4total freeable PGA memory 2783.5PGA memory freed back to OS 2590908.6total PGA used for auto workareas 0maximum PGA used for auto workareas 6903.9total PGA used for manual workareas 0maximum PGA used for manual workareas 0.5bytes processed 13542884extra bytes read/written 1034611.9
![Page 46: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/46.jpg)
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 tempfrom 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
![Page 47: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/47.jpg)
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 tempfrom 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
![Page 48: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/48.jpg)
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 tempfrom 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
![Page 49: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/49.jpg)
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_memfrom 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
![Page 50: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/50.jpg)
Checking doubts on the OS side I don’t believe that, that’s just oracle telling us/home/oracle> 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/oracle> 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
![Page 51: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/51.jpg)
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
![Page 52: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/52.jpg)
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
![Page 53: 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](https://reader035.vdocuments.us/reader035/viewer/2022062619/5516b029550346f0208b522a/html5/thumbnails/53.jpg)
The End
Thank you,
Questions?
Christo Kutrovsky
The Pythian Group
2005 Feb http://www.pythian.com/documents/Working_with_Automatic_PGA.ppt