O1033 – Oracle 11g Performance Tuning
Enquire/Book this course
Trained over 60000 delegates
Course delivered by industry expert instructors
Highly competitive pricing
Course Description
The course is designed to introduce students to the full functional range of features and technologies within the Oracle 11g RDBMS needed to optimise and tune SQL statements. This is done through explanation of the different access methods involved and practical based teaching to control the optimal retrieval paths chosen by Oracle.
Where necessary, this involves explanation of Oracle™s internal mechanisms, and the supporting hierarchical structures required by a Relational Database.
A practical hands-on course, Oracle 11g SQL Tuning is an essential skill for all Oracle 11g developers and users to ensure that they maximise the performance of the data retrieval components of their business systems.
Target Student:
Oracle Analyst/Programmers
Oracle Technicians
Oracle DBA™s
Oracle Developers
Prerequisites:
Oracle SQL
Oracle PL/SQL (recommended)
Delivery Method: Instructor led, group-paced, classroom-delivery learning model with structured hands-on activities.
Course Content
Introduction
Cause of Performance Problems
Setting Performance Goals
The Tuning Cycle
ORACLE Architecture
Logical Storage Structures
Physical Structures Memory
Structures
The Shared Pool
Processes
Some Administration
Terminology
Design
Data Design Phase
Data Model Design
Online Transaction Processing
Decision Support Systems
Multi-purpose Applications
Optimizing SQL
SQL Processing
Physical Retrieval of Data
Full Table Scan versus Index
Reads
Performance Diagnostic Tools
Explain Plan
SQL Trace Facility
TKPROF Autotrace
Join Methods
Sort/Merge Joins
Nested Loops
Hash Joins
Hash Join Example Data Access
Methods
Indexes
Basic Indexes
B-Tree Indexes
Bitmap Index
Comparing B-Tree and Bitmap
Indexes
Reverse Key Index Index-Organised Tables Invisible index
Creating Monitoring and
Maintaining Indexes.
Automatic SQL Tuning
Query Optimizer Modes Types of Tuning Analysis
Automatic workload repository SQL Tuning Advisor SQL Tuning Sets Top SQL
Identify high-load SQL Dynamic Performance views
SQL Performance Analyzer
Describe SQL Performance Analyzer process and benefits
Use SQL Performance Analyzer
The Optimizer
The ORACLE Optimizer SQL statement parsing Initialisation parameters Rule Based Optimizer
Cost Based Optimizer Rule / Cost Comparisons Choosing an Approach Multiple WHERE Clauses Using
Indexes for Sorts Multiple Table Joins Disabling Indexes
Hints.
Sharing SQL Statements
Sharing Cursors
Adaptive Cursors Sharing
Other SQL Tuning Tips
Gathering Statistics
Analyzing Statistics
DBMS_STATS
Automatic Optimizer Statistics
Collection
Histograms
How to Generate Histograms
Statistics
SQL Plan Management
SQL outlines
SQL profiles
SQL Access Advisor
Set up and use SQL plan baseline
Advanced Tuning
Star Queries
Materialized Views
Refreshing Views
Materialized View Logs
SQL Result Cache
Temporary Tables
