Introdução a Oracle Performance e Tuning

Neste treinamento, você irá aprender a utlizar as features de tuning do Oracle database 11g como SQL Tuning Advisor, SQL Access Advisor, relatório AWR (Automátic Workload Repository) e ADDM (Automatic Database Diagnostic Monitor) além de aprender a empregar as melhores práticas no processo de tuning em banco de dados. Este treinamento foca o processo de tuning do ponto de vista do DBA.

Os capítulos apresentados são introdutórios ao Tuning. Em alguns casos não consigo demonstrar na prática pois não tenho um ambiente pronto para isso, mas as técnicas são as mesmas para você usar em seu ambiente.

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