1 / 8

Most Common Oracle System-Level Tuning Opportunities March2009

Most Common Oracle System-Level Tuning Opportunities March2009. Jeff Lippe Master Solution Architect. My Background: 24 Years with Hewlett-Packard, Retired 2007 (great package) Last 20 years focused solely on Performance Analysis Tuning & Capacity Planning

dean
Download Presentation

Most Common Oracle System-Level Tuning Opportunities March2009

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Most Common Oracle System-Level Tuning Opportunities March2009 Jeff Lippe Master Solution Architect

  2. 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. Who Is Jeff Lippe?

  3. 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 Top Tuning Area #1: SGA Memory Allocation

  4. 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 Top Tuning Area #2: Multi-Block Read Count

  5. 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 Top Tuning Area #3: IO Access Alternatives

  6. 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 Top Tuning Area #4: Array Layout and ALUA

  7. 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 Top Tuning Area #5: Locality Domains

  8. 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 Top Tuning Area #6: Virtualization considerations

More Related