Introduction to SQL Tuning
- Intro to the class
- History of SQL
- Evolution of SQL
- Exercise - declarative SQL
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
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
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
- Table joining internals
- Sort-merge joins
- Nested Loop joins
- Hash joins
- STAR joins
- Bitmap joins
- Exercise – Change table join techniques & evaluate performance
- 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
Execution plan internals
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
Exposing execution plans
- Evaluating Large-table, full-table scans
- Index Usage Analysis
- Reports on system-wide SQL execution
- Exercise – run autotrace options
- 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
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
Oracle Index Optimization
- B-Tree indexes
- Bitmap Indexes
- Function-based Indexes
- Clustered indexes
- Index-only tables
- Exercise – Create a function-based index
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
Advanced SQL Tuning Topics
Monitoring SQL Performance
- Measuring end-to-end response time
- Measuring SQL throughput
- Using v$session_longops
- Optional Exercise – Run plan9i.sql
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
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
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
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
Advanced Optimizer Statistics
- Histograms
- Exporting/importing statistics
- Statistics management
- Exercise – Analyze schema and tables
Advanced Topics
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
Tuning with partitioning
- Types of partitioning
- Sub-partitioning
- Partition-aware SQL performance
Time-series SQL Tuning
- estat-bstat reports
- Statspack
- ASH
- AWR Reports
- Using linear regression
- Identifying signatures
- Exercise: Analyze a AWR report
- 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
- Optimizer Statistics
- Purpose of statistics
- Types of statistics (table, column, system)
- Histogram statistics
- Dynamic sampling
- using dbms_stats
- Exporting/importing statistics
- Exercise: Use 11g extended statistics
Row re-sequencing for SQL performance
- Using clustering factor
- Using CTAS with ORDER BY
- Using sorted hash clusters
- Using IOT’s
- 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
