Oracle Tuning Considerations

1
Oracle Tuning Considerations
2
Agenda

• Why Tune ?
• Ways to Improve Performance
• Hardware
• Software
• Application Design
• Monitoring and Improving Performance
• Top 10 Mistakes of Oracle Systems

3
Why Tune ?

• The speed of computing might be wasting valuable
  human time (users waiting for response)
• Enable your system to keep-up with the speed
  business is conducted
• Optimize hardware usage to save money (companies
  are spending millions on hardware)

4
Ways to Improve Performance

• Hardware Component
• Software Component
• Application Design

5
Hardware

• CPU
• Memory
• I/O Subsystem
• Network

6
Software

• Managing the User Interface
• Implementing Business Logic
• Managing User Requests and Resource Allocation
• Managing Data and Transactions

7
Application Design

• Design decisions involved in building
  applications
• Simplicity in Application Design
• Data Modeling
• Table and Index Design
• Using Views
• SQL Execution Efficiency
• Implementing the Application
• Trends in Application Development

8
Deploying New Applications

• Rollout Strategies
• Big Bang Approach
• Trickle Approach
• Performance Checklist

9
Performance Checklist

• Allow for growth
• Block Size and Optimizer
• Set minimal number of initialization parameters
• Manage Block Contention
• Optimal SQL Statements and Resource Usage

10
Performance Checklist

1. Connection Management (Middleware and Programs)
2. Efficient Cursors
3. Migrated Objects are Consistent
4. Establish a Baseline set of Stats from DB and OS
   (Statspack or OEM)
5. Anticipate the First Bottleneck follow the
   Oracle Performance Improvement Method

11
Monitoring and Improving Performance

• Statistics
• OS
• Database
• Application
• Oracle Performance Improvement Method

12
Statistics

• Importance of Statistics
• OS Stats
• CPU
• Virtual Memory
• Disk
• Network

13
Statistics

• Database Stats
• Buffer Cache
• Shared Pool
• Wait Events
• Application Stats

14
Statistics Gathering Tools

• OS

UNIX
CPU sar, vmstat, mpstat, iostat
Memory sar, vmstat
Disk sar, iostat
Network netstat
15
Statistics Gathering Tools

• Database
• Statspack
• Oracle Enterprise Manager (EM)
• Bstat/Estat Scripts

16
Oracle Performance Improvement Method

1. Feedback from Users
2. Full Set of Stats (OS, DB, Apps)
3. Sanity-Check the OS
4. Top 10 Most Common Mistakes
5. Conceptual Model on the System using Symptoms
6. Remedy Actions of the Anticipated Problem

17
Oracle Performance Improvement Method

1. Has the change had the desired effect ?
2. Repeat last 3 steps

18
Top 10 Mistakes of Oracle Systems

1. Bad Connection Management
2. Bad Use of Cursors and Shared Pool
3. Getting Database I/O Wrong
4. Redo Log Setup Problems
5. Serialization of Data Blocks in the Buffer Cache
6. Long Full Table Scans

19
Top 10 Mistakes of Oracle Systems

1. In Disk Sorting
2. High Amounts of Recursive (sys) SQL
3. Schema Errors and Optimizer Problems
4. Use Of Nonstandard Initialization Parameters