Transcript Oracle Tuning Considerations

Oracle Tuning
Considerations
Agenda


Why Tune ?
Ways to Improve Performance
Hardware
 Software
 Application Design



Monitoring and Improving
Performance
Top 10 Mistakes of Oracle
Systems
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)
Ways to Improve Performance



Hardware Component
Software Component
Application Design
Hardware




CPU
Memory
I/O Subsystem
Network
Software




Managing the User Interface
Implementing Business Logic
Managing User Requests and
Resource Allocation
Managing Data and
Transactions
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
Deploying New Applications

Rollout Strategies
Big Bang Approach
 Trickle Approach


Performance Checklist
Performance Checklist
1.
2.
3.
4.
5.
Allow for growth
Block Size and Optimizer
Set minimal number of
initialization parameters
Manage Block Contention
Optimal SQL Statements and
Resource Usage
Performance Checklist
6.
7.
8.
9.
10.
Connection Management
(Middleware and Programs)
Efficient Cursors
Migrated Objects are Consistent
Establish a Baseline set of Stats
from DB and OS (Statspack or
OEM)
Anticipate the First Bottleneck –
follow the Oracle Performance
Improvement Method
Monitoring and Improving
Performance

Statistics
OS
 Database
 Application


Oracle Performance
Improvement Method
Statistics

Importance of Statistics

OS Stats
CPU
 Virtual Memory
 Disk
 Network

Statistics

Database Stats
Buffer Cache
 Shared Pool
 Wait Events


Application Stats
Statistics Gathering Tools

OS
UNIX
CPU
sar, vmstat, mpstat,
iostat
Memory sar, vmstat
Disk
sar, iostat
Network netstat
Statistics Gathering Tools

Database
Statspack
 Oracle Enterprise Manager (EM)
 Bstat/Estat Scripts

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
Oracle Performance Improvement
Method
7.
8.
Has the change had the
desired effect ?
Repeat last 3 steps
Top 10 Mistakes of Oracle
Systems
1.
2.
3.
4.
5.
6.
Bad Connection Management
Bad Use of Cursors and
Shared Pool
Getting Database I/O Wrong
Redo Log Setup Problems
Serialization of Data Blocks in
the Buffer Cache
Long Full Table Scans
Top 10 Mistakes of Oracle
Systems
7.
8.
9.
10.
In Disk Sorting
High Amounts of Recursive
(sys) SQL
Schema Errors and Optimizer
Problems
Use Of Nonstandard
Initialization Parameters