Most Common Oracle System-Level Tuning Opportunities March2009

1
Most Common Oracle System-Level Tuning
Opportunities March2009

• Jeff Lippe
• Master Solution Architect

2
Who Is Jeff Lippe?

• My Background
• 24 Years with Hewlett-Packard, Retired 2007
  (great package)
• Last 20 years focused solely on Performance
  Analysis Tuning Capacity Planning
• I typically review over 100 environments a year
  after 20 years I have completed over 2,000
  assessment of UNIX / Oracle environments
• After the first couple hundred analyses, you
  begin to indentify the most common issues that
  impact Oracle / UNIX environments. After a 1,000
  you should really grasp how Oracle interacts with
  UNIX.
• Achieving optimal performance is a moving
  target.  Each release of Oracle, UNIX, and disk
  subsystems can require adjustments to best
  practice tuning recommendations.
• There are hundreds of performance metrics. 
  Focusing on the key metrics is important.  Even
  more important is knowing the thresholds values
  for the metrics and how to view the metrics
  together (UNIX and Oracle) to provide a holistic
  view of the environment.
• Access to HP internal factory resources provides
  and invaluable level of understanding of the
  inner workings of UNIX and how Oracle operates in
  the UNIX environment.

3
Top Tuning Area 1 SGA Memory Allocation

• SGA memory allocation can have the largest
  overall impact on the database performance
• (2) Primary memory-intensive components
• Shared Pool
• Buffer cache
• Shared Pool memory requirements vary
  significantly based on application workloads
• 300 MB to700 MB is almost always sufficient
• Many environments size the shared pool over 1 GB,
  wasting physical memory
• A large Buffer Cache is critical to avoiding IO
  bottlenecks. Key sizing considerations
• Try to achieve a 98 to 99.5 buffer hit rate
• For IO intensive environments, the physical IO
  rate should be in the 2,500 to 3,500 physical IOs
  per seconds
• Very few environments exceed 5,000 IOs per second
  when properly tuned
• Design for a large SGA buffer cache and a small
  to moderate sized UNIX file cache

4
Top Tuning Area 2 Multi-Block Read Count

• The db_file_multiblock_read_count (MBRC)
  determines the number of blocks to be read in a
  single read system call when performing table
  scans and index range scans
• Common Values for Oracle 9i
• 8 for transactional environments
• 32 for data warehouse / reporting environments
• Concerns of setting the MBRC large
• Can influence the optimizer to favor table scans
• Can pollute the buffer cache and impact buffer
  quality
• With VxFs, IO sizes of 256 KB or more can trigger
  direct IO
• Oracle will use readv system call when the MBRC
  is less than 32
• readv direct IO scattered reads broken up
  into single block reads horrible performance
• Common Values for Oracle 10g
• 8 for transactional environments
• 32 for data warehouse / reporting environments
• Best value default
• Oracle will divide 1 MB by the block size to
  determine the MBRC
• This will result in very efficient 1 MB scattered
  reads without negatively impacting the optimizer

5
Top Tuning Area 3 IO Access Alternatives

• Data Access Alternatives
• File systems when well tuned can provide
  comparable performance as ASM or Raw
• Raw a well designed raw implementation will
  provide comparable performance as ASM
• ASM can provide excellent performance however,
  DBA / storage administrator / SA will relinquish
  some control
• Common best practices
• File systems
• Use direct IO with Oracle 10g along with the MBRC
  set to default
• Validate file system block size is optimal. For
  example, with VxFs
• All table space file systems should be 8k
• Redo archive file system should be 1K
• Raw
• Use some type of host-based striping to evenly
  distribute the IO across the HBAs and physical
  disks within the array
• ASM
• Make sure the ASM stripe unit is optimal for
  virtualized arrays. Small ASM stripe sizes
  (including the default) can result in non-optimal
  IO performance on virtualized arrays

6
Top Tuning Area 4 Array Layout and ALUA

• The virtualized array
• HP EVA is an example. Netapps may have similar
  functionality. The virtualized array can evenly
  distribute IO across available disks providing
  optimal performance with minimal system
  management effort.
• Host based striping can still provide a benefit
  to distribute the IO across HBAs and to provide
  multiple IO queues that are common with multiple
  LUNs
• Conventional RAID Array
• RAID-1 with larger disks is often preferable to
  RAID-5
• Using host-based striping in conjunction with
  different RAID groups can provide optimal
  performance
• Mixing redo logs with table space files is a good
  practice
• Archive logs should be placed in separate RAID
  groups to avoid impacting IO service times during
  writing of archive logs
• ALUA Asymmetric Logical Unit Access
• Automatically detects the set of LUN paths to the
  optimized target port groups
• Only uses these LUN paths for I/O transfer
  according to the I/O load balancing policy set
  for the disk devices
• LUN paths to non-optimized LUN ports are put in
  standby state

7
Top Tuning Area 5 Locality Domains

• Oracle 10g automatically detects if there are
  multiple locality domains (ccNUMA architecture)
  when there are multiple locality domains, Oracle
  will
• Create (1) Shared memory segment for the shared
  pool
• One buffer cache for each locality domain
• Will logically attach each shadow process to a
  buffer cache and will attempt to perform all IO
  through this buffer cache
• Can result in a fragmented cache that reduces
  buffer quality
• There is one database writer for each buffer
  cache
• Unless the server has be architected to take
  advantage of the ccNUMA architecture (using local
  memory and workload distribution techniques to
  align common workloads to the same locality
  domain) performance can suffer
• In practice the enhancements for ccNUMA
  architectures almost always degrade performance
• ccNUMA features can be disabled by
• _enable_NUMA_optimization FALSE
• _db_block_numa 1

8
Top Tuning Area 6 Virtualization considerations

• Virtualization allows multiple logical servers to
  operate concurrently on a single physical server
  where each Virtual Machine (VM) appears to have
  its own view of the hardware
• While virtualized environments have many
  advantages, there are some performance
  implications
• A VM may be distributed across locality domains,
  resulting in inefficient memory latency
• Oracle relies heavily on the SGA
• SGA memory access is greatly enhanced by large
  pages which significantly shorten the path
  length by optimal use of TLB entries
• Virtualization can slice and dice the SGA into
  smaller pages, resulting in degraded TLB-hit
  rates, significantly reducing the performance
  characteristics of Oracle workloads