The Persistence of Memory Issues

1
The Persistence of Memory (Issues)

• Brian HitchcockOCP 8, 8i, 9i DBA
• Sun Microsystems
• brian.hitchcock_at_sun.com
• brhora_at_aol.com

NoCOUG
Brian Hitchcock April 2, 2004
Page 1
2
Whats 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

3
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 8i to 9i

4
Relevant Memory Structures

• SGA in memory
• Db buffer cache (db_block_buffers 8i,
  db_cache_size 9i)
• Redo log buffers (log_buffer)
• Shared pool
• Dictionary cache
• Library cache
• Control structures
• PGA in memory
• Sort area
• Shared pool synonymous with library cache

5
Memory Structures
Physical Memory of DB Machine
SGA
Db Buffer Cache
Redo Log Buffer
OS etc.
Shared Pool
PGA
Individual Sessions

6
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

7
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.

8
Shared Pool Structure
Shared_pool_size
Shared Pool
Dictionary Cache
Library Cache
Reserved Pool
Shared_pool_reserved_size
_shared_pool_reserved_size_min_alloc
9
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

10
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

11
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

12
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 cant do much about the SQL
• Some init.ora parameters may help
• Force sharing, cache cursors etc.

13
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

14
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

15
What About Too Small?

• Performance
• Less scan time
• Cant hold all SQL executing at any one time
• Lots of flushes
• Flushing takes time, holds the latch
• Performance suffers

16
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

17
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 havent been to the perfect world
• Brochures look nice!

18
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

19
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

20
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

21
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?

22
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 shouldnt be
  made
• Not sent to alert log, select request_failures
  from vshared_pool_reserved

23
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

24
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 )

25
STATSPACK Shared Pool

• STATSPACK report
• Latch sleep breakdown
• Shared pool
• Library cache
• Row object cache
• Dictionary cache stats
• Pct misses (
• Library cache activity
• Pct misses (very low)
• Shared pool advisory (9i)

26
9i Improvements

• Vshared_pool_advice (9.2)
• Output seen in STATSPACK report
• Shared pool advisory
• Similar to vdb_cache_advice
• Shows possible benefit of larger shared pool

27
Shared Pool Details 8i vs 9i

• Shared_pool_size
• Static in 8i
• Dynamic in 9i
• Must also set sga_max_size
• Defaults to current sga size
• Shared_pool_reserved_size
• 8i, static, default 10 shared pool, min 5000
  bytes
• 9i, static, default 5 shared pool, min 4400
  bytes
• Shared_pool_reserved_size_min_alloc
• Hidden parameter in 8i, 9i
• Default to 4000 bytes (both 8i, 9i)

28
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

29
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 300mb shared pool
• App vendor recommends 400mb shared pool
• True optimization takes a lot of time

30
Real World Case 1a

• ORA-04031 errors
• Not sent to alert log
• Vendor app doesnt trap oracle error
• App users report app error
• Cant connect
• App users dont see oracle error
• Only DBA can see if any have occurred
• Select request_failures from vshared_pool_reserve
  d
• This delayed finding root cause of cant login

31
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 2gb shared pool
• No performance problems seen

32
Who Is Right?

• It depends
• Different customers
• Different priorities
• Many apps dont need supreme db performance
• Large shared pool may cause long scan times
• If overall app performance doesnt suffer
• Who cares?
• Time spent carefully tuning may be wasted

33
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
• Assumes steady state under load
• Row cache objects latch waits
• Need for larger dictionary cache

34
Sorting

• Needed when
• Creating index
• SQL that uses
• Distinct
• Order by
• Group by
• Involves multiple passes
• Merge results

35
Whats 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

36
Sorting Structure
PGA
Sort_area_size

Individual Sessions
37
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

38
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

39
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

40
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

41
STATSPACK -- Sorting

• Instance activity stats
• Sorts (disk)
• Sorts (memory)
• Sorts (rows)
• Tablespace IO stats
• File IO stats

42
Sorting Issues

• Similar to shared pool
• Cant 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
• Cant increase sort_area_size without worrying
  about total physical memory

43
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

44
Sorting in 9i

• 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

45
Sorting in 9i

• 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

46
9i Sorting Structure
PGA
Pga_aggregate_target

Individual Sessions
47
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?

48
Real World Case

• Details
• Load test 800 users
• Sort_area_size 2M
• Some sorts going to disk
• Increase sort_area_size?
• 800x2m 1.6gb
• Too much memory!
• Cant change sort_area_size
• Live with performance impact of sorts to disk

49
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

50
Summary

• Shared pool, sorting
• Hard to tune perfectly
• Need to monitor over time and adjust
• Sorting
• 9i features very good
• Pga_aggregate_target
• More memory helps
• Even if some sorting still goes to disk
• Load testing can make you tune incorrectly

51
Political Commentary

• Shared pool, sorting
• 8i to 9i, more hidden
• 10g even more automated
• Expertise may not be valuable long-term
• Traditional DBA
• Automation bigger threat than
• Outsourcing
• Off-shoring