The Persistence of Memory Issues Brian Hitchcock OCP
The Persistence of Memory (Issues) Brian Hitchcock OCP 8, 8 i, 9 i DBA Sun Microsystems brian. hitchcock@sun. com brhora@aol. com No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 1
What’s The Issue? Ÿ Shared pool – – What is it? How does it work? How to monitor and tune it? What can go wrong? Ÿ Sorting – Same four questions Ÿ Validity of load testing No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 2
Why Not Cover All of SGA? Ÿ Too much to discuss Ÿ Shared pool – – Caused problems for me recently Very different behavior from buffer cache Ÿ Sorting – – Source of recent performance problem Major change in management 8 i to 9 i No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 3
Relevant Memory Structures Ÿ SGA in memory – – – Db buffer cache (db_block_buffers 8 i, db_cache_size 9 i) Redo log buffers (log_buffer) Shared pool Ÿ Dictionary cache Ÿ Library cache Ÿ Control structures Ÿ PGA in memory Ÿ Sort area Ÿ Shared pool synonymous with library cache No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 4
Memory Structures Physical Memory of DB Machine SGA Db Buffer Cache Shared Pool Redo Log Buffer OS etc. PGA Individual Sessions… … No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 5
Shared Pool Ÿ What is it? – – Part of SGA (shared global area) Stores SQL and PL/SQL that is being executed Ÿ After parsing, SQL stored in shared pool – Allows reuse of SQL and PL/SQL Ÿ Prevents need to re parse same SQL – Controlled by Ÿ Shared_pool_size No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 6
Shared Pool init. ora Parameters Ÿ Shared_pool_size – Overall size of shared pool Ÿ Shared_pool_reserved_size – Part reserved for large memory allocations Ÿ Shared_pool_reserved_size_min_alloc – – Minimum size of large memory allocations Abbreviated SPRS_min_alloc for my sanity Ÿ Dedicated server processes assumed – MTS different, PGA allocations made in SGA etc. No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 7
Shared Pool Structure Shared_pool_size Shared Pool Dictionary Cache Library Cache Reserved Pool Shared_pool_reserved_size _shared_pool_reserved_size_min_alloc No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 8
What Does Library Cache Do? Ÿ SQL or PL/SQL to be executed must be parsed Ÿ Once parsed, is stored in shared pool Ÿ If same SQL or PL/SQL is executed again, it can be used from shared pool without parsing Ÿ Parsing is very compute intensive Ÿ Storing parsed SQL, PL/SQL reduces parsing – Improves performance No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 9
How Does It Work? Ÿ SQL to be executed – – Scan shared pool looking for free memory in library cache Need enough contiguous free memory to store the SQL statement If not found Ÿ Flush all unused, unpinned memory and merge Ÿ Scan again If still not found Ÿ Issue ORA 04031 error Ÿ “Unable to allocate % bytes of shared memory Ÿ SQL is not executed No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 10
Implications Ÿ Shared pool – – Large enough to allocate memory for all SQL requests that will execute at any one time Over time, becomes fragmented When scanning, if larger than needed piece found Ÿ Broken into size needed and small free segment Ÿ Over time, lots of small pieces Ÿ Over time, very few big contiguous pieces If large memory allocation requested Ÿ May not have any large pieces available Ÿ May not be able to flush enough to create large piece No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 11
Shared SQL Ÿ Before tuning shared pool – – – Is application SQL shared? Bind variables? PL/SQL packages? Ÿ Vendor apps – – – May or may not be good about this You can’t do much about the SQL Some init. ora parameters may help Ÿ Force sharing, cache cursors etc. No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 12
Why Not Just Make It Larger? Ÿ Performance – – Even if all SQL in shared pool is not in use Must scan all of it looking for free space If not enough found, flush, merge, scan again Shared pool has single shared pool latch Ÿ Longer scan time holds latch longer Ÿ Other requests wait longer Ÿ Latch wait shows up in STATSPACK wait events No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 13
Shared Pool Latches Ÿ Shared pool latch – Serializes memory allocations in library cache Ÿ Library cache latch – Serializes access to objects in library cache Ÿ Row cache objects latch – Latch for dictionary cache Ÿ Latch waits for any of these – – More shared SQL? Investigate shared pool size No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 14
What About Too Small? Ÿ Performance – – – Less scan time Can’t hold all SQL executing at any one time Lots of flushes Flushing takes time, holds the latch Performance suffers No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 15
So, What To Do? Ÿ Shared pool size tradeoff – Smaller Ÿ Faster to scan before flushing Ÿ More flushes Ÿ Less SQL stored in parsed form, more parsing – Bigger Ÿ Slower to scan Ÿ Fewer flushes Ÿ Slower to flush Ÿ More SQL stored in parsed form, less parsing No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 16
In Perfect World Ÿ All SQL reused Ÿ All SQL fits in shared pool Ÿ Shared pool reaches steady state – – No flushes No 04031 errors No one tries to execute large on off SQL at random intervals Everyone is happy Ÿ I haven’t been to the perfect world – Brochures look nice! No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 17
In The Real World Ÿ Some, perhaps most SQL is reused Ÿ Some is not – – Large SQL statements request large memory allocations Shared pool flushed Ÿ Free up memory of SQL not in use Ÿ Merge small pieces of memory – Frequently used SQL (not currently in use) Ÿ Must be re parsed and reloaded Ÿ Slows performance No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 18
The Perfect World II Ÿ Assuming there will be some large SQL once in a while – – – Reserved pool Portion of shared pool for large allocations Only large allocations go here Prevents disruption of shared pool Prevents smaller requests fragmenting reserved pool No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 19
Reserved Pool? Ÿ For allocation request bigger than SPRS_min_alloc – – – If not enough free space in shared pool Allocation goes to reserved pool Scan reserved pool looking for enough memory If not found, flush unused, merge, scan again If not found, 04031 error Normal shared pool not disturbed Ÿ No flushing Ÿ No reloading of frequently used SQL No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 20
But Really… Ÿ If you think about it… Ÿ Shared pool needs to be big enough – – – Over longest time between db restarts Max number of simultaneous users Each user submitting the largest SQL Ÿ Reserved pool needs to hold – Same criteria as shared pool but for allocations larger than SPRS_min_alloc Ÿ Is this really possible? No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 21
How To Tune Shared Pool? Ÿ Monitor free space in shared pool – – – Large free space may not be a good thing Ÿ Memory that is never used Ÿ Many small pieces, no large pieces Ideally Ÿ Small amount of free space during max load Monitor ORA 04031 errors Ÿ None, or very few, very infrequently Ÿ Only caused by large requests that shouldn’t be made Ÿ Not sent to alert log, select request_failures from v$shared_pool_reserved No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 22
Tuning Shared Pool Ÿ If shared pool – – Has free space over time No ORA 04031 errors Ÿ Consider reducing shared pool Reduce scan time – Only if latch waits are an issue Ÿ STATSPACK report Ÿ Start large and reduce if needed – My opinion No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 23
STATSPACK -- Shared Pool Ÿ STATSPACK report – Load profile Ÿ Parses Ÿ Hard parses (low %) – Instance efficiency percentages Ÿ Soft parses (high %) – Shared pool statistics Ÿ Memory usage (high %) Ÿ SQL with executions >1 (high %) Ÿ Memory for SQL w/exec >1 (high %) No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 24
STATSPACK – Shared Pool Ÿ STATSPACK report – Latch sleep breakdown Ÿ Shared pool Ÿ Library cache Ÿ Row object cache – Dictionary cache stats Ÿ Pct misses (< 2%) – Library cache activity Ÿ Pct misses (very low) – Shared pool advisory (9 i) No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 25
9 i Improvements Ÿ V$shared_pool_advice (9. 2) Ÿ Output seen in STATSPACK report – Shared pool advisory Ÿ Similar to v$db_cache_advice Ÿ Shows possible benefit of larger shared pool No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 26
Shared Pool Details 8 i vs 9 i Ÿ Shared_pool_size – – Static in 8 i Dynamic in 9 i Ÿ Must also set sga_max_size Ÿ Defaults to current sga size Shared_pool_reserved_size Ÿ 8 i, static, default 10% shared pool, min 5000 bytes Ÿ 9 i, static, default 5% shared pool, min 4400 bytes Shared_pool_reserved_size_min_alloc Ÿ Hidden parameter in 8 i, 9 i Ÿ Default to 4000 bytes (both 8 i, 9 i) No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 27
Bigger Reserved Pool? Ÿ To increase – – Reserved pool taken from shared pool size Must increase both Ÿ Shared pool, shared_pool_size Ÿ Reserved pool, shared_pool_reserved_size No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 28
Real World Case 1 Ÿ Priority: must not waste memory – – – Start testing with very small shared pool Lots of ORA 04031 errors Slowly increase shared pool until errors stop Many days spent Running with 300 mb shared pool App vendor recommends 400 mb shared pool Ÿ True optimization takes a lot of time No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 29
Real World Case 1 a Ÿ ORA 04031 errors – – – Not sent to alert log Vendor app doesn’t trap oracle error App users report app error Ÿ Can’t connect – – App users don’t see oracle error Only DBA can see if any have occurred Ÿ Select request_failures from v$shared_pool_reserved – This delayed finding root cause of “can’t login” No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 30
Real World Case 2 Ÿ Priority: must not waste time – – – – Existing app, running for months Suddenly throws lots of ORA 04031 errors No time, just increase shared pool Happens twice Problem may be fixed in 8174 patch Finally running with 2 gb shared pool No performance problems seen No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 31
Who Is Right? Ÿ It depends Ÿ Different customers Ÿ Different priorities Ÿ Many apps don’t need supreme db performance Ÿ Large shared pool may cause long scan times – – If overall app performance doesn’t suffer Who cares? Ÿ Time spent carefully tuning may be wasted No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 32
What About Dictionary Cache? Ÿ Ignored so far – – – Part of shared pool Caches system table info Not much to tune Ÿ Make larger by increasing shared pool size – STATSPACK report shows Ÿ Dictionary cache stats Ÿ Pct misses should be < 2% Ÿ Assumes steady state under load – Row cache objects latch waits Ÿ Need for larger dictionary cache No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 33
Sorting Ÿ Needed when – – Creating index SQL that uses Ÿ Distinct Ÿ Order by Ÿ Group by Ÿ Involves multiple passes – Merge results No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 34
What’s The Issue? Ÿ Sorting done – – In memory up to sort_area_size On disk in temporary tablespace (TEMP) Ÿ Sorts to disk much slower – Contends with other disk activity Ÿ Want all sorting done in memory – More memory helps even if some sorting still goes to disk No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 35
Sorting Structure PGA Sort_area_size … Individual Sessions… No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 36
Sorting In Memory Ÿ How much memory needed? – Level 1 (least memory) Ÿ Many multi pass sorts to disk – Level 2 (more memory) Ÿ More memory than level 1 Ÿ Only single pass sorts to disk – Level 3 (most memory) Ÿ Lots more memory Ÿ No sorts to disk of any kind – Level 2 may be best option No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 37
Sorting In Memory Ÿ For dedicated server processes (non MTS) – – When user SQL needs to sort Memory allocated in PGA Ÿ Up to sort_area_size – After sort completes Ÿ Memory above sort_area_size_retained released for reuse by same process Ÿ Sort_area_size_retained defaults to sort_area_size Ÿ Memory released to OS after process completes No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 38
Sorting Issues Ÿ Lots of users, lots of sorting – Total memory needed is large Ÿ Worst case Ÿ Max number users x sort_area_size – Users can alter session to increase sort area size Ÿ Restrict “alter session” privilege? – Total memory needed Ÿ Could be very large Ÿ Could cause swapping No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 39
Tune Sorting? Ÿ STATSPACK report – – – Sorts to memory, disk TEMP tablespace I/O stats Want all sorts in memory Ÿ Increase sort_area_size – – – Reduce sorts to disk Reduce TEMP I/O Monitor total memory used No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 40
STATSPACK -- Sorting Ÿ Instance activity stats – – – Sorts (disk) Sorts (memory) Sorts (rows) Ÿ Tablespace IO stats Ÿ File IO stats No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 41
Sorting Issues Ÿ Similar to shared pool – – – Can’t really predict how much sorting will happen at any given time SQL that requires large sort area will interfere with all other SQL in the database Can’t increase sort_area_size without worrying about total physical memory No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 42
Sorting For Specific Sessions Ÿ Instead of tuning for all sessions – – – Identify sessions that always need large sorts Those sessions use alter session to assign larger sort_area_size All other sessions use smaller sort_area_size No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 43
Sorting in 9 i Ÿ Instead of sort_area_size Ÿ Pga_aggregate_target – – Set limit on total PGA for all users Any user sort can use all of this PGA limit Maximum memory for sorting controlled Makes tuning much simpler Ÿ Increase pga_aggregate_target Reduce multiple pass sorts to disk Reduce single pass sorts to disk Reduce TEMP I/O No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 44
Sorting in 9 i Ÿ Pga_aggregate_target – – – Workarea_size_policy must be auto (default) When set, *_area_size parameters ignored Dynamic Ÿ Pga_target_advice – – Show benefit of increased pga target Makes tuning easier No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 45
9 i Sorting Structure PGA Pga_aggregate_target … Individual Sessions… No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 46
Real World Case Ÿ App users login – – Sorting Load test Ÿ Max users Ÿ Want to run test quickly Ÿ Ramp up user logins fast – – – Login SQL causes large sort Stresses sort_area_size Will we ever see this in production? No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 47
Real World Case Ÿ Details – – Load test 800 users Sort_area_size 2 M Some sorts going to disk Increase sort_area_size? Ÿ 800 x 2 m = 1. 6 gb Ÿ Too much memory! Ÿ Can’t change sort_area_size – Live with performance impact of sorts to disk No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 48
Load Testing? Ÿ Was it valid? – – Done quickly Simulate worst load possible Ÿ 800 users – Does this simulate real world experience? Ÿ Will 800 users ever connect in this time frame? – – Causes strain on TEMP tablespace Sort area size tuned for 800 users at once Ÿ Could be bigger for smaller number of users No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 49
Summary Ÿ Shared pool, sorting – – Hard to tune perfectly Need to monitor over time and adjust Ÿ Sorting – 9 i features very good Ÿ Pga_aggregate_target – More memory helps Ÿ Even if some sorting still goes to disk Ÿ Load testing can make you tune incorrectly No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 50
Political Commentary Ÿ Shared pool, sorting – – – 8 i to 9 i, more hidden 10 g even more automated Expertise may not be valuable long term Ÿ Traditional DBA – Automation bigger threat than Ÿ Outsourcing Ÿ Off shoring No. COUG www. brianhitchcock. net Brian Hitchcock April 2, 2004 Page 51
- Slides: 51