SQL Tuning
SQL Tuning
Start Date | 11th April 2018 |
Course Code | SS18-35 |
Venue | Dublin |
Non-member Full Fee | €0.00 |
Duration | 3 Days |
Location | Dublin |
Provider | Neueda |
Network Member Subsidised Fee | €0.00 |
Course expired
Programme Overview
This course aims to cover the following topics:
- Introduction to SQL Tuning
- Optimizer Fundamentals
- Bind Variables
- Displaying Execution Plans
- Reading and interpreting Execution Plans
- Optimizer Access Paths
- Optimizer Joins
- Other Optimizer Operators
- SQL Tuning Techniques
Who should attend
This programme is aimed at those using SQL and requiring advanced knowledge.
Course Content
Introduction to SQL Tuning
- Brief Introduction to Statement Processing
- SQL Tuning Session
- What Is Bad SQL?
- SQL Tuning Strategies
Optimizer Fundamentals
- Components of the Optimizer
- Query Transformer
- Estimator: Selectivity and Cardinality
- Estimator: Cost and Cost Components
- Plan Generator
- How Statistics affect plans
- Best practice for gathering statistics
- Controlling the Behaviour of the Optimizer
Bind Variables
- Cursor Sharing and Different Literal Values
- Cursor Sharing and Bind Variables
- Bind Variable Peeking
- The CURSOR_SHARING Parameter
- Forcing Cursor Sharing
- Adaptive Cursor Sharing (ACS)
- Interacting with Adaptive Cursor Sharing
- Adaptive Cursor Sharing: Views
Displaying Execution Plans
- What Is an Execution Plan?
- Viewing Execution Plans
- The EXPLAIN PLAN Command
- Displaying from PLAN_TABLE
- Using the V$SQL_PLAN View
- DBMS_XPLAN.DISPLAY_CURSOR
- Links between important Dynamic Performance Views
- AUTOTRACE
- Automatic Workload Repository (AWR)
- SQL Monitoring
- DBMS_SQLTUNE output to HTML/Text
Reading and Interpreting Execution Plans
- Explain the execution plan
- Interpreting a Serial Execution Plan
- Interpreting a Parallel Execution Plan
- Evaluating Estimates and Actuals
- Adaptive Plans/ Optimizations
- Reading Adaptive Query plans
- Understanding the key data in dynamic performance views
- Understanding Statistics
- SQL Hints and how hints can affect plans
Optimizer Access Paths
- Row Source Operations
- Main Structures and Access Paths
- Full Table Scan
- ROWID Scan
- Sample Table Scans
- Indexes: Overview
- Normal B*‐tree Indexes
- Index Scans
- Index Unique Scan
- Index Range Scan
- Index Range Scan: Descending
- Index Range Scan: Function-Based Index
- Full Index Scan
- Fast Full Index Scan
- Skip Scan
- Index Join Scan
- Composite Indexes
- B*-tree Indexes and Nulls
- Using Indexes: Index-Organized Table Scans
- Bitmap Indexes
- Bitmap Operations
- Bitmap Join Index
- Invisible Indexes
- Guidelines for Managing Indexes
Optimizer Joins
- Join Methods
- Nested Loops Join
- Sort-Merge Join
- Hash Join
- Cartesian Join
- Join Methods in Summary
- Join Types: Equijoins and Nonequi Joins
- Join Types: Outer Joins
- Join Types: Semijoins
- Join Types: Antijoins
Other Optimizer Operations
- Sorting Operators
- Buffer Sort Operator
- Inlist Iterator
- View Operator
- Min/Max and First Row Operators
- FILTER Operations
- SET Operations
- Result Cache
SQL Tuning Techniques
- Overview of writing efficient SQL
- Table Design?
- Index Usage
- Transformed Index
- Data Type Mismatch
- NULL usage
- Tune the ORDER BY Clause
- Retrieve a MIN/MAX value
- Correlated Subquery verses Inline Views
- UNION and UNION ALL
- Avoid Using HAVING Inappropriately
- Tune the BETWEEN Operator
- Tune a Star Query
- Tune the Join Order
- Test for Existence of Rows
- LIKE \'%STRING\'
- Use Caution When Managing Views
- Join Column and Index
- Ordering Keys for Composite Index
- Bitmap Join Index
- Tune Complex Logic
- Writing Combined SQL Statement
- Write a Multitable INSERT Statement
- Using Temporary Table
- Using the WITH Clause
- Using Materialized Views
- Partition Pruning
- Using a Bind Variable
- Functions in the WHERE clause
- Using Scalar Subqueries
Certification
N/A
About the Trainer
Helen Robertson – Helen is a highly resourceful consultant with valuable and varied experience, gained in the database industry, within a world leading technology corporations. Able to use own initiative and work as part of a team. Excellent technical, communication, analytical and problem solving skills. Helen develops and delivers training courses for courses covering all aspects and versions of SQL (including SQL Tuning), PL/SQL and Application Express, delivers all Oracle certified courses in SQL (including SQL Tuning), PL/SQL, Application Express (all versions), SQL Tuning, New Features for Developers (all database versions).