Základní info
Popis kurzu
Co se naučíte
This course is designed to give the student a firm foundation in the art of SQL tuning. The participant learns the necessary knowledge and skills to effectively tune SQL against the Oracle9i Server. Students gain a thorough conceptual understanding of the Rule-Based and Cost-Based Optimizer. The course uses a series of challenge-level workshops, allowing students to "play, discover, and learn" at their own level and pace. The students learn to use the Oracle diagnostic tools and facilities: EXPLAIN, SQL Trace and TKPROF, SQL*Plus AUTOTRACE. Students also learn to influence the behavior of the Optimizer by changing the physical schema and modifying SQL statement syntax.
Posluchači
Technical Consultant
Cíle kurzu
Learn where SQL tuning fits in an overall tuning methodology
Use the diagnostic tools to gather information about SQL statement processing
Describe alternative methods of accessing data
Influence the physical data model so as to avoid performance problems
Describe the basic steps in processing SQL statements
Learn Rule-Based Optimizer (RBO) and Cost-Based Optimizer (CBO) behavior
Describe the causes of performance problems
Influence the optimizer behavior
Témata kurzu
Following a Tuning Methodology
Describing the Causes of Performance Problems
Identifying Performance Problems
Using a Tuning Methodology
Listing the Steps to Tune SQL
SQL Statement Processing
Describe the basic steps involved in processing a SQL statement
Monitor the use of shared SQL areas
Write SQL statements to take advantage of shared SQL areas
Understand how to use the CURSOR_SHARING parameter
Use automatic PGA memory management
EXPLAIN and AUTOTRACE
Using the EXPLAIN PLAN Command
Identifying the AUTOTRACE Syntax
Interpreting EXPLAIN Output
Interpreting AUTOTRACE Statistics
SQL Trace and TKPROF
Invoking the SQL Trace Facility
Setting Up Appropriate Initialization Parameters
Formatting Trace Files with TKPROF
Interpreting the Output of the TKPROF Command
Cost-Based Optimization Versus Rule-Based Optimization
Identifying Rule-Based Optimization
Identifying Cost-Based Optimization
Understanding the Cost of an Execution Plan
Indexes and Basic Access Methods
Identifying Row Access Methods
Creating B*-Tree Indexes
Understanding B*-Tree Index Access and Index Merging
Gathering Statistics
Using the DBMS_STATS Package
Identifying Table, Column, and Index Statistics
Building Histograms
Using the ANALYZE Command
Influencing the Optimizer
Setting Up Appropriate Initialization Parameters
Using the ALTER SESSION Command
Using Hints
Sorting and Joining
Sorting Guidelines
Using Top-N SQL
Nested Loops JoinsSort/Merge Joins
Outer Joins
Star Joins
Hash Joins
Optimizer Plan Stability
Purpose and Benefits of Optimizer Plan Stability
Creating Stored Outlines
Using the OUTLN_PKG Package
Advanced Indexes
Creating Bitmapped Indexes
Creating Function-Based Key Indexes
Optimizing Star Joins with Star Transformation
Materialized Views and Temporary Tables
Using the CREATE MATERIALIZED VIEW Syntax
Utilizing Query Rewrites
Creating and Using Temporary Tables
Alternative Storage Techniques
Creating Index-Organized Tables
Creating Index Clusters
Creating Hash Clusters
SQL Analyze
Using SQL Analyze for SQL Tuning
