Transcript Phase 1 Performance Data Collection Phase 2 DB Architecture

Introduction to DB Tuning
Presented by Jae-won Lee
Tuning Definition and Effects
 Tuning ?

Increasing customer satisfaction by improving DB performance, reducing
costs, and improving capability
Customer Satisfaction
Performance
-Stable Operation
-Performance Reporting
-Application Effects
Analysis
-Performance Data
Monitoring
Center for E-Business Technology
Costs
-Storage Save
-CPU Usage
Reduction
Copyright  2006 by CEBT
Capability
-Guaranteeing Tuning
Capability
-Strengthening
Competitiveness
IDS Lab. Seminar - 2
Tuning Methodology
 Ratio Based Analysis

Tuning database which is based on Hit Ratio such as Database Buffer Cache
Hit Ratio or Library Cache Hit Ratio

Recently, due to the improved H/W, this method is not used well
 Session Wait Event Based Analysis

Tuning database by collecting performance data (primarily, Session Wait
Event) with data dictionary view

This method focused on simple database wait event (not OS wait)
 User Response Time Based Analysis

This complements Session Wait Event based Analysis

Based on a formula, Response time = CPU time + Wait time, this method
also tunes CPU time compared to Session Wait Event based Analysis
Center for E-Business Technology
Copyright  2006 by CEBT
IDS Lab. Seminar - 3
Tuning Process
Phase 1
Performance
Data Collection
Collecting
Wait Event
Phase 2
DB Architecture
Tuning
Segment Tuning
Phase 3
Application
Tuning
Application
Tuning
Statics of
Resource
Usage
Parallel
Processing
Collecting
Access
Path
SQL Tuning
Collecting
Inefficient SQL
Phase 4
Effect Analysis
Effect Analysis
Table
Architecture
Tuning
Index Tuning
SGA Tuning
Center for E-Business Technology
Copyright  2006 by CEBT
IDS Lab. Seminar - 4