Introduction to Oracle Database
Performance Tuning Tools
By Ahmed Baraka
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Objectives
In this lecture, you should learn how to perform the following:
• Describe in high level Oracle database performance tuning tools
• Describe types of performance tuning statistics
• Set the statistics level
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Performance Tuning Tools
• V$ views and data dictionary views
• Automatic Workload Repository (AWR)
• Statspack reports
• Automatic Database Diagnostic Monitor (ADDM)
• Performance tuning advisors
• alertlog file
• Trace files
• Enterprise Manager Cloud Control and EM Database Express
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Types of Performance Tuning Statistics
Performance Tuning statistic types:
• Cumulative statistics: mostly have no meaning without the time factor
- Time model
- Wait events
• Metrics: statistic rates
• Sampled statistics
- ADDM, AWR, Active Session History (ASH)
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Setting Statistics Level
• STATISTICS_LEVEL controls the level of collecting database and
operating system statistics.
Value Description
BASIC Most important performance statistics are not collected
TYPICAL All major statistics are collected
ALL Timed operating system statistics and execution plan statistics are added
• Can be altered at the system and session levels
• To know which parameter is affected by setting this parameter, query
the view V$STATISTICS_LEVEL
Oracle© Database Performance Tuning - a course by Ahmed Baraka
Summary
In this lecture, you should have learnt how to perform the following:
• Describe in high level Oracle database performance tuning tools
• Describe types of performance tuning statistics
• Set the statistics level
Oracle© Database Performance Tuning - a course by Ahmed Baraka
