Oracle Database Performance Tuning

Oracle Database Performance Tuning Training in Delhi or Online (Advanced) 

• PRE Requisites: Knowledge of Oracle Database Administration (with RAC)
• Covers Multiple Scenarios encountered, approach followed to troubleshoot and resolve Performance Issues including SQLs for quick diagnosis.

Approach to a Performance Problem

• Type of User Complains
• Scope of the Problem
• Circumstances which led to the Problem
• Issue Verification
• Data Collection

Information Gathering : Sessions, Statistics , Wait Events and interpret multiple waits. Analysing the OS command output 

• Basic Tuning Tools (Prac)
• Monitoring tools overview 
• Enterprise Manager V$ Views, 
• Statistics and Metrics 
• Wait Events
• Alert Log
• OS Tools and commands for CPU and Memory : top , vmstat , free , iostat
• Tracking CPU Utilization, High CPU Consuming Processes and Sessions
• User Trace Files
• Monitor Sessions and Processes at Runtime

Information Gathering : Using AWR Reports and Advisories 

• Diagnosis of Server Memory Issues
• Tracking CPU Utilization, High CPU Consuming Processes and Sessions
• AWR Reports for Single Instance / RAC 
• Reading the AWR Report
• Comparing the AWR Report
• Comparative Performance Analysis with AWR Baselines 
• Automatic Workload Repository Baselines
• Hang Analyze to diagnose hanging and performance problems
• Automatic Maintenance Tasks 
• ADDM Performance Monitoring 
• Active Session History: Overview

Information Gathering : SQL Tuning with Multiple Examples for Perf. Tuning Approach
Tracing SQL and Tracing Sessions 
Influencing the Optimizer 

• Functions of the Query Optimizer, Selectivity, Cardinality and Cost & Changing Optimizer Behavior 
• Checking Statistics
• Gathering the execution Plan
• Tracing a Database Session , Using TKPROF to analyze a trace File
• Interpreting the Execution Plan Output through several examples
• Collecting Schema, table and index statistics (dbms_stats)
• Using Hints, Optimizer Statistics & Extended Statistics 
• Controlling the Behavior of the Optimizer with Parameters 
• Enabling Query Optimizer Features & Influencing the Optimizer Approach 
• Optimizing SQL Statements, Access Paths & Choosing an Access Path 
• Join & Sort Operations 
• Reducing the Cost
• Creating a SQL Performance Analyzer Task 
• SQL Advisor
• Auditing Requirements

Diagnosing Locks and Latch Issues and Rectification, Tuning SGA , PGA and Temporary Space Issues 

• Understanding Locks
• Detect enqueue contention
• Inter Instance Locks for RAC
• Understanding Latches and Performance Issues on account of Latches
• Diagnose and Resolve Latch Issues
• Tuning the Shared Pool
• Library Cache locks and Invalidations
• Row Cache Locks
• latch Buffer Cache Chains and Hot Blocks
• Tuning the Buffer Cache
• Automatic Shared Memory Management
• SGA and Buffer Cache Considerations for RAC
• Diagnose PGA memory issues
• Size the PGA memory
• Diagnose temporary space issues

Checkpoint and Redo Tuning and Instance Recovery,Tuning Block Space Usage 

• Diagnose checkpoint and redo issues
• Implement Fast Start MTTR target (including RAC perspective)
• Implement multiple database writers
• Tune the redo chain
• Size the redo log file
• How to Speed Instance Recovery for Oracle Real Application Cluster (RAC)
• What should be the size of the log buffer
• Diagnose table fragmentation
• Reorganise Tables, Indexes
• Reorganize Tablespaces to remove Fragmentation

Considerations for RAC Performance abd Best Practices 

• RAC Design Considerations & Network and Storage Considerations
• Clusterware and Grid Infrastructure Considerations
• RAC Database Considerations
• Oracle RAC Services for better Utilization
• Considerations for RAC Performance
• Tracing Services in RAC
• RAC Related Waits – gc wait events 
• 11g ADDM for Oracle Real Application Cluster (RAC)
• RACcheck 
• Collect Diagnostic Data in 11gR2 RAC Using DiagCollection.pl Script
• Understanding RAID Levels and Impact
• ASM Disk organization