Day 1 morning: Tuning the Oracle Data access
layer
This section takes an in-depth look into the
concepts and skills needed to tune Oracle with SQL Students will learn about the
broader issues of tuning with SQL, and then delve into tuning with the
Optimizers, the Explain Plan Utility, Hints, and other tuning techniques. After
completing this course, students will be able to tune all Oracle SQL and take
the actions required to ensure optimal SQL performance.
In conjunction with the other four courses in the
Oracle Performance Tuning Certification Series, this course helps
students prepare for Oracle Certified Professional Exam number 1Z0-014, Oracle:
Performance Tuning, part of the Oracle DBA certification track.
Learning Objectives - After completing
this course, students will be able to:
- Define the major features
of Oracle SQL
- Tune using ANSI extensions
- Tune using the Explain
Plan Utility
- Understand SQL Optimizer
modes
- Tune using SQL hints
This area will examine all of the SQL
within the Oracle library cache and determine the best opportunities for tuning
of SQL. The focus on the SQL tuning will be to tune the most frequently
executed statements first, and move down the list as time permits. The areas
for SQL tuning will be to optimize execution plans by removing unnecessary
full-table scans, fully utilizing indexes, and implementing advanced hints to
derive the optimal execution plan for each query. This may also involve
replacing native SQL with PL/SQL procedures. Tuning SQL statements using Oracle
hints will be explained and techniques for testing SQL in a separate instance
to determine the optimal execution plan and minimal run time will be explored.
-
Cost-based vs. Rule-based optimization
See the relative benefits of cost-based vs rule based optimization and see
when to use rule and cost hints.
-
Tuning with indexes
Learn a technique for identifying unnecessary full-table scans.
-
Tuning sub-queries
See the optimal execution plans for subqueries with the IN clause.
-
Tuning with hints
See the techniques for using the use_hash, use_aj and other important
Oracle tuning hints.
-
Tuning with Parallel query - This
section describes how parallel query works, and how to best provide
parallelism for Oracle.
Day 1 afternoon: Tuning the external
environment
This section will teach students about external
influences on Oracle performance, how to measure them, and how to ensure that
the external environment is properly configured for Oracle. Students will learn
how to take advantage of all the UNIX-based tools for monitoring, configuring,
and tuning the external environment. The course also covers tuning issues
related to the operating system, tuning for effective CPU usage, reorganizing
tables to improve I/O performance and more.
The next section will focus on
identifying and correcting external influences including RAM memory shortages,
disk I/O bottlenecks, and CPU shortages. The external environment will be
monitored using the vmstat and iostat utilities. This section will discuss the
iterations of recommendations relating to environmental tuning such as OS
dispatching priorities, swap space allocation, and other OS-specific issues.
-
vmstat utility
Interpreting the runque, page-in and the CPU metrics.
-
iostat utility
Using the iostat utility to determine disk I/O access patterns and I/O
bottlenecks.
This section will also includes
an easy method for capturing environmental statistics inside Oracle tables for
long-term analysis.
In conjunction with the other four courses in the
Oracle Performance Tuning Certification Series, this course helps
students prepare for Oracle Certified Professional Exam number 1Z0-014, Oracle
Performance Tuning, part of the Oracle DBA certification track.
Learning Objectives - After completing
this course, students will be able to:
- Define the major features
of Oracle SQL
- Tune using ANSI extensions
- Tune using the Explain
Plan Utility
- Understand SQL Optimizer
modes
- Tune using the rule-based
optimizer
- Tune using the cost-based
optimizer
- Tune using SQL hints
This section takes an in-depth look into the
memory internals of the Oracle SGA. Students will learn how to identify and
diagnose Oracle memory performance problems, including problems in the library
cache, data buffer cache and data dictionary cache. Once students complete the
second course, theyll be able to handle any SGA-related slowdown, and will have
a set of pre-written SQL scripts that they can use to quickly identify
performance problems.
This area will examine the
behavior of the Oracle instance and look into the behavior of the Oracle
background process and the SGA memory. The topics for this section will include
iterations of recommendations for changes to init.ora parameters, changes to
redo log and rollback segments, and changes of physical file locations on disk.
Oracle Instance Tuning
This section will also explore
how to measures changes to the SGA performance and how to document the
performance improvements.
-
Redo Logs This
section to introduce the performance characteristics of the Oracle online
and archived redo logs.
-
UNDO (Rollback) segments
This section explores the interaction between the Oracle rollback segments
and the performance of the Oracle database.
-
Data Buffer management
- Buffer management issues will include the performance of Oracle with
various values of db_block_size and the proper settings for
db_block_buffers. This section will also look at allocation of the DEFAULT,
KEEP and RECYCLE pools.
-
Shared pool issues
This section will discuss the management of the components of the shared
pool with a focus on the management of activity in the library cache.
-
Sorting issues
This section will describe the management of the sort_area_size parameter
and how to determine the optimal setting for disk versus memory sorting.
-
Default SQL optimizer
mode This section will explore the optimizer_mode initialization
parameter and how the default parameter can effect the database performance.
-
Miscellaneous
Initialization parameters Other init.ora parameters such as
cursor_space_for_time will be discussed.
-
Concurrency Management
- This will describe Oracle's serialization and locking schemes and show how
to manage locks.
Day 3: Oracle SQL tuning for ERP systems
(references are to pages in Floss textbook)
Internal processing of SQL statements
Parsing SQL Syntax - (page 53-57)
SQL Semantic Analysis - (page 56)
Generating the execution plan - (page 181-185)
Using optimizer plan stability - (page 309-332)
Using the v$sql view - (page 57)
Using the v$sql_plan view
Exercise Query the library cache
Viewing SQL execution Internals
Using and interpreting explain plan - (page 310)
Using TKPROF - (page 205)
Using SQL*Trace - (page 200)
Using set autotrace in SQL*Plus
Exercise Create a plan table and view SQL plans
Altering SQL execution plans
Using hints - (page 87, 277)
Changing the system-wide optimizer mode - (page 92)
Changing optimizer mode for specific statements - (page 10)
Re-writing SQL queries - (page 54, 314)
Table join order evaluation - (page 88)
Using the ordered hint - (page 249)
DBA tuning for SQL Optimization
Inside the library cache (page 222-229)
Avoiding SQL re-parsing - (page 223)
Cursor Sharing - (page 54-55)
Optimizer Plan Stability (stored outlines) - (page 310-317)
Parallel DML - (page 441)
Parallel SQL and partitions - (page 517)
SQL and the data warehouse
SQL Aggregate Functions
Grouping in several levels
Grouping and NULLS
CUBE and ROLLUP
Performance and grouping
Exercise Using rollup and cube
Tuning SQL sub-queries
Correlated subqueries - (page 342)
Non-correlated subqueries - (page 342)
Replacing subqueries with joins - (page 385)
Exercise Tune a complex query
Expert SQL Tuning Secrets
Materialized Views to pre-aggregate and pre-join - (page 54)
Adding indexes - (page 62)
