Basic Tuning Diagnostics

• Describe the time model

• Explain wait events

• Diagnose performance problems using V$ views

Using Automatic Workload Repository

• Analyze database performance using AWR reports

Using Metrics and Alerts

• Monitor performance using metric thresholds and alerts

Using Baselines

• Use AWR baselines to monitor performance

Using AWR-Based Tools

• Diagnose performance issues using ADDM

• Diagnose performance issues using ASH reports

Monitoring Real-Time Database Operation

• Implement Real-Time Database Operation Monitoring

Monitoring Applications

• Configure and use services to monitor database performance

Identifying Problem SQL Statements

• Interpret execution plans

• Use formatted output of SQL Trace or the optimizer trace to identify poorly performing SQL statements

Influencing the Optimizer

• Explain how statistics influence the optimizer

• Configure parameters to influence the optimizer

Reducing the Cost of SQL Operations

• Use Automatic Segment Space Management to optimize space management

• Use table compression

• Diagnose and tune space related issues

Using SQL Performance Analyzer

• Use SQL Performance Analyzer to test the impact of database changes

SQL Performance Management

• Manage optimizer statistics

• Use SQL Profiles and Advisors to tune SQL statements

• Use SQL Plan Management to tune SQL statements

Using Database Replay

• Use Database Replay to check impact of system change on performance

Tuning the Shared Pool

• Diagnose and resolve hard and soft parsing problems

• Diagnose and resolve performance issues related to the shared pool

Tuning the Buffer Cache

• Diagnose and resolve performance issues related to the buffer cache

Tuning PGA and Temporary Space

• Diagnose and resolve performance issues related to PGA

• Diagnose and resolve performance issues related to temporary tablespace

Using Automatic Memory

• Use and tune Automatic Shared Memory Management

• Use and tune Automatic Memory Management

Tuning I/O

• Diagnose database I/O issues

• Configure Automatic Storage Management (ASM) for maximum availability

Using the Database Resource Manager

• Understand and configure the Database Resource Manager