|
Advanced Oracle SQL Tuning Training
Course
© 2000 - 2018
by Burleson Corporation
* 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.
|
|
Our advanced Oracle
SQL tuning training 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 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 SQL tuning course is designed for
practicing Oracle DBAs 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 SQL tuning 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 Corporation 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 SQL tuning 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
© 2000 - 2018 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-1 Introduction to SQL Tuning
Intro to the class
History of SQL
Evolution of SQL
Exercise - declarative SQL
1-2 - 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
1-3 Optimizer Statistics
Purpose of statistics
Types of statistics (table, column,
system)
Histogram statistics
Dynamic sampling
using dbms_stats
Exporting/importing statistics
Statistics management
Exercise gather
system stats
1-4 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
Exercise
display and change optimizer_mode
1-5 - Table joining internals
Sort-merge
joins
Nested Loop joins
Hash joins
STAR joins
Bitmap joins
Exercise Change table join techniques & evaluate performance
1-6 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
Exercise Query v$sql
DAY 2
Execution plan internals
2-1 Oracle parallel
query and parallel DML
Parallel and SMP processing
Parallel
query optimal degree
Parallel query management (system, session, statement)
Parallel DML
Parallel parallelism
Exercise: Run a parallel query
2-2 Exposing execution plans
Evaluating
Large-table, full-table scans
Index Usage Analysis
Reports on
system-wide SQL execution
Exercise run autotrace options
2-3 - 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
Optimizer costing models
2_4 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
Exercise change an execution plan with a hint
2_5 - Oracle Index Optimization
B-Tree indexes
Bitmap Indexes
Function-based Indexes
Clustered indexes
Index-only tables
Exercise Create a function-based index
2_6 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
Exercise: Force two sort methods
|
|
DAY
3 Advanced SQL Tuning Topics
3_1 Monitoring
SQL Performance
Measuring end-to-end response time
Measuring
SQL throughput
Using v$session_longops
Optional Exercise Run plan9i.sql
3_2 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 & 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
3_3 Tuning with materialized views
and
temporary objects
Materialized views
Global temporary
tables
Using scalar subqueries (WITH clause)
Simplifying complex SQL with
temporary objects
Exercise Re-write complex query using temporary tables
and WITH clause
3_4 Tuning subqueries
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
Internals of 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
Exercise: Tune an anti-join
3_5 Troubleshooting bad 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
Exercise: Find top SQL hogs
3_6
Advanced Optimizer Statistics
Histograms
Exporting/importing
statistics
Statistics management
Exercise Analyze schema and tables
|
Advanced Oracle SQL Tuning Class
Five Day Syllabus
© 2000 - 2018 by Burleson Corporation
Note:
This is the continuation of the three day class with an emphasis on expert SQL
Tuning Training for experienced developers and DBAs.
|
Day 4 Advanced Topics
4_1 - Tuning Distributed SQL
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
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
4_2 Tuning with partitioning
Types of partitioning
Sub-partitioning
Partition-aware SQL performance
4_3
- Time-series SQL Tuning
estat-bstat reports
Statspack
ASH
AWR Reports
Using linear regression
Identifying signatures
Exercise: Analyze a AWR report
4_4 - Advanced WHERE clause tuning
Sequence of SQL Predicates (ordered_predicates)
Ordering clauses in WHERE statements
CASE statement
Using the ordered_predicates hint
Optional exercise: Change predicate order
4_5 Optimizer Statistics
Purpose of statistics
Types of statistics (table, column, system)
Histogram statistics
Dynamic sampling
using dbms_stats
Exporting/importing statistics
Exercise: Use 12c extended statistics
4_6 Row
re-sequencing for SQL performance
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
Managing Row Chaining in Oracle
A Summary of Object
Tuning Rules
|
DAY 5 Advanced SQL Tuning Topics
5_1 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
and SQL Performance
ADTs and Oracle SQL
Abstract Datatypes
and SQL Performance
Exercise: Test performance of an abstract
datatype
5_2 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
5_3 Data compression and SQL Performance
Types of Oracle compression
Compression and disk space
Effect of compression on full-scan behavior
5_4 - SQL Tuning
with 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
Exercise: Tuning an
in-line view
5_5 Tracing execution Plans
Using TKPROF
Using the 10046 trace event
Exercise run TKPROF
5_6 - Expert SQL Tuning Secrets
|
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) 2000- 2018
|
|
|
|
| |
|
|
|
|
|
