|
Oracle SQL Tuning Training
Course
© 2007-2012
by Burleson Consulting
* See how initialization parameters
affect SQL optimization.
* Understand how to read
an execution plan.
* Learn the importance of schema statistics.
* Learn how to apply "global" changes to tune
many SQL statements at once.
* See how to locate missing indexes.
* See how to use the library cache to find
SQL tuning opportunities.
Understand how to prioritize your SQL tuning
efforts.
* Learn how external system statistics improve SQL
execution.
* See how to evaluate and test SQL execution speed.
* Learn the internal table join methods.
* Learn to detect unnecessary large-table full-table scans.
* See how to quickly identify sub-optimal SQL.
* Use hints to change SQL execution plans.
* Tune SQL with stored outlines.
* See how the SQLTuning advisor and SQLAccess advisor recommends SQL tuning
opportunities.
* Use the v$ internal views for Oracle SQL tuning.
|
|
Advanced Oracle
SQL tuning is an intensive three-day, four-day, or five-day course
designed to provide Oracle professionals with an in-depth
understanding of Oracle SQL tuning and how to formulate and tune
SQL statements for optimal performance. Burleson Consulting instructors offer decades of real world DBA
experience in Oracle features, and they will share their secrets in
this intense Oracle SQL tuning training.
Optional SQL
Tuning book:
This course is designed for
practicing Oracle DBA's and Systems Administrators professionals who have basic experience with SQL
statements. Prior experience with Oracle
is not required, but experience using SQL with a relational
database is highly desirable.
The goal of the SQL tuning class is to
provide a comprehensive toolkit to allow the DBA to quickly locate and tune a
database workload.
|
This course was designed by Donald K.
Burleson, an acknowledged leader in Oracle database
administration. Author of more than 20 database books,
Burleson was chosen by Oracle Press to write five authorized
editions, including Oracle High-Performance SQL
tuning. Burleson Consulting instructors
offer decades of real world DBA experience in Oracle features, and
they will share their secrets in this intense Oracle SQL
tuning training.
Learning
Objectives
By the end of this course the student
will be able to tune advanced SQL queries including correlated
subqueries and outer joins. The student will also become familiar
with all of the major SQL tuning techniques for Oracle, including
global parameter and statistics changes, adding missing indexes and adjusting
optimizer statistics. The student will also see the internals of the Oracle optimizers, and
see proven techniques for tuning Oracle SQL statements for optimal
performance.
Advanced Oracle SQL Tuning
Three Day Syllabus
© 2012 by Donald K.
Burleson
Note: You choose the specific
SQL tuning topics based on your specific tuning requirements.
|
Day 1: Introduction to
SQL Optimization
1
- Internal processing of SQL statements
Parsing SQL
Syntax
SQL Semantic Analysis
Generating the execution plan
Using
optimizer plan stability
Using the v$sql view
Using the v$sql_plan
view
Exercise Query the library cache
2 Optimizer Statistics
Purpose of statistics
Types of statistics (table, column, system)
Histogram statistics
Dynamic
sampling
using dbms_stats
Exporting/importing statistics
Statistics
management
Optional exercise dbms_stats to analyze a table and index
3 Optimizer modes and goals
Management issues with system-wide optimization
Different modes of SQL
optimization
Bi-modal databases
Rule-based optimization
Cost-based
optimization
All rows optimization
First_rows optimization
Optional
exercise display and change optimizer_mode
4 - Table joining internals
Sort-merge joins
Nested Loop joins
Hash joins
STAR
joins
Bitmap joins
Optional Exercise Change table join techniques &
evaluate performance
5 SQL Tuning and
full-table scans
Basics of file I/O
Sequential reads
vs. scattered reads
When full scans are best
RAM caching in the SGA
Automating table caching
Solid State Disks
Tracking full-scans over time
with AWR
|Optional exercise - Run plan9i.sql
6 Oracle parallel query and parallel DML
Parallel and SMP processing
Parallel query optimal degree
Parallel query
management (system, session, statement)
Parallel DML
Parallel parallelism
DAY 2 Execution plan internals
7 - Locating poor execution plans
Evaluating
Large-table, full-table scans
Index Usage Analysis
Reports on
system-wide SQL execution
Exercise run plan91.sql
8 - Altering SQL execution plans
Using hints
Changing the system-wide optimizer mode
Changing
optimizer mode for specific statements
Re-writing SQL queries
Table join order evaluation
Using the ordered hint
Exercise Add hints
to queries
|
|
9 Tracing SQL execution
Internals
Using and interpreting explain plan
The
10046 trace event
Using TKPROF
Using SQL*Trace
Using set autotrace in
SQL*Plus
Exercise Create a plan table and view SQL plans
10 Tuning SQL with hints
Optimizer directives
Scope of hints (session-level, statement level)
Broad hints (optimizer mode) vs. narrow hints
Good Hints vs. bad hints
Using the ORDERED and LEADING HINTS
Forcing index usage
Using hints in
subqueries
Optional exercise change an execution plan with a
hint
11 - Oracle Index Optimization
B-Tree indexes
Bitmap Indexes
Function-based Indexes
Clustered indexes
Index-only tables
Exercise Create a multi-column
index
12 Tuning Oracle sorting
When a sort is invoked (order by, group by, etc.)
Sorting with indexes vs
internal sorting
Detecting disk sorts
Sorted Hash Clusters
DAY 3 Advanced SQL
Tuning Topics
13 Monitoring SQL
Performance
Measuring end-to-end response time
Measuring SQL throughput
Using v$session_longops
Optional Exercise
Monitor SQL (v$sql and dba_hist_sqlplan)
14 Data compression and SQL Performance
Types of Oracle
compression
Compression and disk space
Effect of compression on full-scan
behavior
15 Tuning with materialized
views and temporary objects
Materialized views
Global
temporary tables
Using scalar subqueries (WITH clause)
Simplifying complex
SQL with temporary objects
Optional Exercise Create a materialized view
16 Tuning with partitioning
Types of partitioning
Sub-partitioning
Partition-aware SQL performance
17 Tuning with the SPA
The history of automated advisors
The SQLAccess and SQLTuning advisors
Recommending missing indexes
Recommending materialized views
The future
for automatic SQL Tuning
18 - Expert SQL
Tuning Secrets
Materialized Views to pre-aggregate and
pre-joins
Row re-sequencing
Adding indexes
|
Advanced Oracle SQL Tuning Class
Five Day Syllabus
© 2011 by Burleson Consulting
Note:
This is the continuation of the three day class with an emphasis on expert SQL
Tuning Training for experienced developers and DBA's.
|
Day 4
Advanced Topics
19 Tuning with
cardinality feedback
Understanding cardinality
How Oracle estimate cardinality
Cardinality and table join order
Changing cardinality
20 Tuning
subqueries (Ch 14)
Subquery Tuning and SQL
Types of SQL
Subqueries
Tuning Guidelines for
Subqueries
Avoiding SQL
Subqueries
Subqueries in the where Clause
In vs. exists
Subqueries
Same
Results, Different Syntax and Plans
Non-correlated
subquery:
Outer Join:
Correlated Subquery:
Tuning Scalar
Subqueries
Scalar Subquery
Performance
Removing Subqueries for Fast SQL Performance
Using Global
Temporary Tables to Improve SQL Speed
Removing Subqueries Using
the with Clause
Internals of Temporary
Tables
Row Management of Temporary Tables
Remove subqueries
with global temporary tables
Correlated vs.
Non-correlated Subqueries
Tuning Correlated
Subqueries
Automatic Rewriting not exists Subqueries
Automatic Rewriting
exists Subqueries
Rewriting Non-equality Correlated Subqueries
Rewriting exists
Subqueries with the rank Function
Subquery Hint Tuning
Subquery Tuning with
Index Hints
Tuning Subqueries
With the push_subq Hint
Table Anti-Join Hints
The merge_aj Hint
SQL Tuning With the
hash_aj Hint
21
- Time-series SQL Tuning
estat-bstat reports
Statspack
ASH
AWR Reports
Using linear regression
Identifying signatures
22 - Advanced
Boolean expressions
Sequence of SQL Predicates
Ordering clauses in WHERE statements
CASE statement
Using the ordered_predicates hint
Optional exercise: Change predicate order
23
Instance-level SQL workload tuning
Optimizer parameters
PGA parameters
Hidden parameters
Detecting changing workloads
Changing parameters to match workloads
24
Row re-sequencing for SQL performance (Book Ch 11)
Using clustering
factor
Using CTAS with
ORDER BY
Using sorted
hash clusters
Using
IOTs
Reorganizing
Tables for High Performance
Faster SQL with
Database Reorganizations
Tuning SQL Access
with clustering_factor
Tuning SQL with
Cluster Tables
Multi-table Index Cluster Tables
Single-table Index
Cluster Tables
Managing Row Chaining
in Oracle
A Summary of Object
Tuning Rules
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|
DAY 5 Advanced SQL Tuning Topics
25
Tuning Object-oriented SQL
Object Oriented
Oracle SQL
The SQL Impedance Mismatch
SQL Object Extension
Performance
Performance of Oracle
Object-oriented SQL
Repeating Data Items in
Relational Tables
Inside Varray Tables
Execution Plans for
varray Tables
Oracle Nested tables
Nested Tables and SQL
Performance
ADTs and Oracle SQL
Abstract Datatypes
and SQL Performance
26
Troubleshooting SQL
Troubleshooting Problem SQL
The Holistic Approach
to SQL Tuning
Troubleshooting Oracle SQL
Bugs
What is Bad SQL?
Identifying Problem
SQL
Troubleshooting with
v$sql_plan
SQL Troubleshooting
with v$sql_plan_statistics
Finding indexing opportunities
27
Oracle DML Tuning
DML Tuning is not for neophytes
Oracle DML tuning
Optimizing Oracle SQL
insert performance
High Impact insert
Tuning Techniques
Tips for batch inserts
Low-impact insert
techniques (% and % faster)
Tuning insert speed with the nologging option
Reverse key indexes
and insert performance
Blocksize and insert
performance
Oracle Delete Tuning
Oracle Update Tuning
High impact update
techniques (over % faster):
Low-impact techniques
(between % and % faster)
Using bulk binds for
PL/SQL updates
Oracle subquery factoring
(with clause) for DML
28 - SQL Tuning
with Views (Chapter 10)
Inside Oracle Views
Tuning SQL with Views
Benefits of Oracle
Views
Drawbacks in Tuning
SQL that Contains Views
Abusing Views in Oracle SQL
Merging Views and SQL
Tuning
Predicate Pushing with Views
Combining Hints and Views
Oracle In-line Views
Tips for Tuning SQL
with Views
SQL
Tuning with Materialized Views
Materialized Views
and Automatic SQL Query Rewrite
A Case Study in Materialized Views
Conclusions on
Materialized Views for SQL Tuning
29 - Tuning
Distributed SQL (Chapter 8)
Distributed Database
Technology
Coordinating
Distributed Databases
Distributed SQL Table Joins
The remote-to-remote
Distributed Join
The local-to-remote
distributed join
Troubleshooting Distributed
Oracle SQL
Performance Issues
with Distributed Queries
Creating Cross-database
Execution Plans
Determining the
Driving Site and Driving Table for Cross-database Queries
The Problem of Remote
Joins
Overview of Distributed SQL Tuning
Sorting and
Distributed SQL
Parallelism and Distributed
Queries
Using Views for Distributed
SQL
Tuning with the
driving_site Hint
Forcing Partition
Pruning on Distributed SQL
Tuning distributed
DDL
30 - Expert
SQL Tuning Secrets (Summary and review)
Proactive SQL tuning with
linear regression techniques
Detecting problems before they impact end-users
Materialized Views to pre-aggregate and pre-joins
Row re-sequencing
Adding indexes
Time series signature
analysis
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|
Please
note that while the knowledge gained from this Oracle training may
be valuable when preparing for OCP exams, the
content of this course is not for Oracle Certification, including
the Certified Professional (OCP) or Oracle Certified Associate
(OCA) programs.
This is a BC Oracle
SQL Tuning training course (c) 2012
|
|
|
|
| |
|
|
|
|
|
