- Knowledge of Oracle SQL and PL/SQL
- Knowledge of Oracle Database 12c.
Course Duration: 30 Hours
Course Highlights:
- Oracle Database Architecture
- Performance Tuning Tools
- Oracle Optimizer
- SQL Tuning with Activities
- Data Loading and Monitoring
- AWR,ADDM,ASH Report Analysis
- Database Storage Objects
- Advisories
- Instance / memory tuning
- Best Architectural Practices
Required Prerequisites:
Target Audience:
- Oracle DBA who to learn Oracle Database Performance Tuning in detail
Course Content
Section 1: Oracle Database Architecture
Memory Architecture
Physical Architecture
Logical Architecture
Installation of Oracle Database 12c on OEL
Section 2: Performance Tunning Tools
Explain plan of SQL statements
Tracing SQL execution
tkprof for trace analysis
AUTOTRACE in SQL*
V$ dynamic performance views
OEM
AWR, ADDM and ASH reports
SQL tuning advisor and baselines
Section 3: Oracle Optimizer
Basics of Optimizer
Understanding behaviour
Histograms and Bind Peeking
Access paths
Interpreting JOINS
Optimizer statistics
Hints
Manage Optimizer
Section 4: SQL Tuning Activity - 1
Possible SQL issues
SQL Tuning methods and Tools
Pro-active SQL Tuning
Reactive SQL Tuning
Section 5: SQL Tuning Activity - 2
SQL tuning examples (Continue)
Creating SQL tuning sets and baselines
Tuning JOINS
Sorting and Grouping activities
PL/SQL tuning notes
Parallelism in SQL
Section 6: Data Loading and Monitoring
Creating SCHEMAS, TABLES
Preparing Environment
Load Data-1
Monitor using OEM & Alert log
Load Data-2
Monitor using V$ views
Section 7: AWR , ADDM , ASH Report Analysis
Generate AWR reports
Analysis & Problem Identification
Fixing Performance Issue
AWR reports and baselines
ADDM reports
ASH report
Section 8 : Database Storage Objects
Storage options
Oracle Partitioning
Partitioning Examples
Indexes Overview
B-Tree Indexes & Examples
BitMap Indexes & Examples
Index Organized Tables & Examples
Section 9 : Advisories
BAD SQL tracing & Analysis
SQL tuning Advisor
STS (SQL Tuning Set) with SQL tuning advisor
SQL Access advisor
Implementing SQL Plan management
Fragmentation and Data distribution
Section 10 : Instance / Memory Tuning
Shared pool tuning – Latch/Mutex, Library cache, Cursors
Shared pool tuning – Parses, Result cache, Data dictionary
Buffer cache tuning
PGA tuning and Sort area
Automatic memory management ( AMM ) – Concepts
Real time Activity – PGA Tuning
Section 11: Best Architectural Practices
Important parameters
Storage considerations
Memory size planning
High Availability – Best practices
Maintenance
Top WAIT Events
Application analysis
Pro-active monitoring approach