|
CHAPTER
1: Introduction to Oracle SQL Tuning
Introduction to Oracle SQL
The origin of relational SQL
The declarative nature of SQL
When SQL sucks
Who tunes SQL?
Understanding SQL Tuning
The top-down approach to Oracle tuning
The Goals of SQL Tuning
Roadblocks to SQL Tuning
Successful SQL Tuning Methods
Tuning by Simplifying SQL Syntax
Causes of poor Oracle performance
Optimizing Oracle SQL Execution
Watching the SQL WHERE clause
Remove unnecessary large-table full table
scans
Setting SQL Optimization Goals
The Persistent SQL System
The Dynamic SQL System
Holistic Oracle Tuning
Dealing with Time Constraints
Best Practices for SQL Optimization
Maintaining SQL Infrastructure
Encapsulating SQL for Fast Performance
Pinning SQL into Packages
How Hardware Affects SQL Tuning
Techniques
The changing landscape of Oracle SQL
Tuning
A Release-centric Approach to SQL
Optimization
g SQL Tuning Enhancements
Operational SQL enhancements in g
Automating g SQL Tuning with Plan
Baselines
Tuning When the SQL Cannot Be Touched
The g Flash Cache and SQL tuning
External Tables and SQL
Defining an External Table
Internals of External Table SQL
Conclusion
CHAPTER 2:
The Oracle Cost-based SQL Optimizer
Inside the SQL optimizer
Tuning with the rule-based SQL optimizer
Why the rule hint is still popular in
Oracle g
Costs and the CBO
The steps for executing a SQL statement
Creating a SQL executable
Optimizer behavior and parameters
The optimizer_mode parameter
The optimizer and where clause order
High throughput vs. fast response time
Optimizing SQL for multiple workloads
The effect of optimizer_mode on SQL
execution plans
Optimizer mode and query performance
Oracle parameters that influence the CBO
The g opt_param hint
Using optimizer_index_cost_adj
Changing CBO SQL Optimizer Parameters
Changing Optimizer Parameters at the User
Level
Measuring I/O Costs for Scattered and
Sequential Reads
Understanding CBO Statistics
Extended Optimizer Statistics
Oracle Optimizer Costing
Oracle Dynamic Sampling
The Dynamic Sampling Hint
Sub-Optimal Table Join Order
Managing Schema Statistics with
dbms_stats
Getting top-quality statistics for the
CBO
Statistics Enhancements for Oracle g and
Beyond
Testing New CBO Statistics
Avoiding Monday Morning Mayhem
Tuning SQL with Histograms
Automating Histogram Creation with
dbms_stats
Histogram Improvements in g
g New Features for dbms_stats
Oracle Workload Statistics and SQL
Performance
External Costing with the Optimizer
Fixing CBO Statistics
Repairing Obsolete CBO Statistics
Gathering
Setting the SQL Optimizer Cost Model
Turning on CPU Costing
Solid State Oracle and SQL tuning
CHAPTER 3 :
Oracle SQL Execution Plans
Inside SQL Execution Details
The Obsolete Explain Plan Syntax
Why the Explain Plan Gives Wrong Details!
Wrong Plans When Not Using explain plan
Using the SQL*Plus autotrace Utility
Using Explain Plan
Using utlxpls.sql to See SQL Explain
Plans
The Oracle dbms_xplan Utility
Introduction to SQL Tracing
Using Trace Analyzer to Trace SQL
Executing Trace Analyzer
Using tkprof to Trace SQL
Viewing RAM Usage for SQL Statements
Costs and the Cost-based Optimizer
SQL Tuning and Execution Statistics
Learning the Sequence of Explain Plan
Steps
Ordering the Sequence of Execution Plan
Steps
Explain Plan Supplemental Information
Inside SQL Table Joining
The Nested Loops Join
Interrogating SQL Execution Plans
Tracing SQL Execution History
Using SQL Profiles
Swapping execution plans
Swapping SQL Profiles
Changing SQL Execution Plans with Hints
When to Use an Oracle Hint
Automatic Query Rewrite with Hints
When Hints Are Ignored
Why is My Hint Ignored?
Syntax Error Hints are Ignored
Incompatible Hints are Ignored
Hints With Bad Parameters are Ignored
CHAPTER 4:
Oracle SQL Join Internals
Oracle Table Join Types
Equi-Join
The Outer Join Operator
The anti-join operator
The Oracle Semi-Join
Oracle ISO
Table Syntax
ISO outer
join Syntax
Oracle Internal Machinations for SQL
Oracle Physical Join Types
The sort merge join
Oracle nested loops joins
When the optimizer fails to choose nested
loops joins
The Oracle hash Join
Displaying Hash RAM usage for execution
plan steps
Hash Partitioning and Hash Joining
Tuning Oracle Hash Joins
Parallelizing Hash Joins
The Oracle g Full Hash Join
Interrogating SQL Table Join Methods
Verifying optimal Join techniques
Evaluating Oracle Physical Join
Performance
Oracle Table Join Optimization
Optimal Table Join Order and Transitive
Closure
SQL Tuning with the ordered Hint
SQL Tuning with the ordered_predicates
Hint
How is SQL join cardinality estimated?
Tuning with the cardinality hint
Enabling Dynamic Sampling of Statistics
Sampling Table Scans
SQL Tuning with the dynamic_sampling hint
The star transformation join
Requirements Star Transformation Join
Types of Index Access Methods
Inside the index range scan
Inside the index fast full scan
CHAPTER 5:
Tuning Oracle Full Table Scans
Inside the Full-table Scan
The Types of Full Scan Operations
Full Scan I/O is Cheaper Than Index I/O
When a Large-table Full-table Scan is
Evil
Full Table Scans and Stripe Size
SQL Result Set Size and Full Table Scans
Unnecessary Large-table Full-table Scans
NULL Values and Index Access
Never Replace a NULL Value for Index
Access
Avoiding Full Scans on SQL with a not
equals Clause
Full Table Scans and the like Clause
Oracle case insensitive searches
Using Oracle*Text Indexes with the SQL
like Clause
Index Resynchronization
Remove Unnecessary Large-table Full-table
Scans
Locating Full-scan Operations
Tuning Large-table Full-table Scans
Tuning Small-table Full-table Scans
KEEP Pool Assignment Rules
Scripts for Automating KEEP Pool
Assignment
Another KEEP Pool Script Approach
Sizing the KEEP Pool
Conclusion
CHAPTER 6:
Oracle SQL Tuning with Indexes
SQL Tuning with Indexes
The Types of Oracle indexes
The Oracle b-tree Index
Creating a b-tree Index
Tuning SQL with Bitmapped Indexes
Distinct Key Values and Bitmap Indexes!
Bitmap Compression, Row Clustering, and
Column Cardinality
Exceptions to the Rule! High Cardinality
Bitmap Indexes
Combining Bitmaps:
The Bitmap Merge Operation
SQL Tuning with Bitmap Join Indexes
How Bitmap Join Indexes Work
Bitmap Join Index Example
Exclusions for Bitmap Join Indexes
When Oracle SQL Chooses the Wrong Index
Beware of the Fast Fix
Forcing Index Usage
Oracle Does Not Use My Index
Using nls_date_format with Date Indexes
Managing Complex Date Comparisons in SQL
Using the months_between Date Function
Using the add_months Date Function
Using the last_day Date Function
Using the next_day Date Function
Using the round Date Function
Using the trunc Date Function
Index Usage and Built-in Functions
Finding BIFs
Tuning SQL with FBIs
Using case Statements with an FBI
Indexing on Complex Functions
Statistics and FBIs
Conclusions on FBIs
SQL tuning with Regular Expression
Indexes
Indexing on Regular Expressions
Case Sensitive Searches with Indexes
SQL Tuning with Oracle*Text Indexes
Oracle*Text Index Resynchronization
Tuning SQL with Index Organized Tables
Testing New Oracle Indexes
Testing SQL Workloads with Invisible
Indexes
Monitoring Index Usage
Monitoring for Index Range Scans
Monitoring SQL Workload Activity
Verifying Optimal Index Usage
Finding Indexing Opportunities
Find SQL that Uses Sub-optimal ndexes
Finding SQL with Excessive I/O
Finding sub-optimal SQL in the library
cache
Finding Index Opportunities in AWR
Locating Un-used Indexes
Finding Unused Indexes in Oracle i and
Earlier
Finding Unused Indexes in Oracle i
Finding Unused indexes in Oracle g and
Beyond
Dropping Un-used Indexes
Locating Infrequently Used Indexes
The Problem of Too Many Indexes
Determining Which Index to Delete
Large Multi-column Indexes
Row Clustering and SQL Performance
Index Reorganization and SQL Performance
When Rebuilding Indexes May Help SQL
Performance
When Rebuilding Indexes Will Hurt
Performance
Choosing Candidates for Index Maintenance
Index Hints
The Index Hint
The index_join Hint
The and_equal Hint
The index_asc Hint
The no_index Hint
The index_desc Hint
The index_combine Hint
The index_ffs Hint
|
CHAPTER 7:
RAM and SQL Tuning
RAM usage and SQL Tuning
SQL processing and RAM
Sizing the PGA
SQL Tuning and Automatic Memory
Management
Manual RAM allocation vs. AMM
Hidden parameters for Oracle PGA regions
Sizing PGA for Fast SQL
Important Caveats in PGA Management
Sizing PGA for Hash Joins
Viewing RAM Usage for Hash Joins in SQL
A Case Study in Invoking Hash Joins
The g Full Hash Join
Using the hash group by Plan
When Hash Joins Fail
Monitoring RAM Operations for SQL
Viewing RAM Usage for Individual
Processes
Viewing Individual RAM Work Areas
Tracking Hash Joins
CHAPTER 8:
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
Overview of Distributed SQL Tuning
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
CHAPTER 9:
Tuning Parallel SQL Execution
Parallel query speeds up full-table scans
A Brief History of Parallel Query
Parallel Sorting
Parallel Query and I/O Buffering
Tuning Parallel Join Operations
Tuning Parallel Sort Merge Joins
Tuning Parallel Hash Joins
How Oracle Parallel Query Works
Parallel Hints
The Parallel Hint
The pq_distribute Hint
The noparallel Hint
Invoking Parallel Query
Single Instance Oracle Parallel Query
Oracle RAC and Inter-Instance Parallelism
Finding the Optimal Degree of Parallelism
Checking CPU Count
Parallel Query for Distributed Instances
Oracle Parallel Query Parameters
The fast_start_parallel_rollback
Parameter
The parallel_max_servers Parameter
The log_buffer Parameter
The db_block_lru_latches Parameter
Oracle g R Parallel Parameters
Parallel DML: update, merge and delete
Monitoring Oracle Parallel Query
Bottlenecks in Oracle Parallel Query
Finding Disk Enqueues
Finding CPU Slowdowns
Parallel Query Execution Plans
Conclusion
CHAPTER 10:
SQL Tuning with Views
Inside Oracle 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
Conclusions on Materialized Views for SQL
Tuning
CHAPTER 11:
Tuning SQL with Object Reorganization
Reorganizing Tables for High Performance
Faster SQL with Database Reorganizations
Tuning SQL Access with clustering_factor
Tuning SQL with Cluster Tables
Multi-table Index Cluster Tables
Single-table Index Cluster Tables
Managing Row Chaining in Oracle
A Summary of Object Tuning Rules
Oracle Index Maintenance
Not All Indexes Benefit from Rebuilding
When Should Indexes Be Rebuilt?
CHAPTER 12:
Tuning SQL Sort Operations
The History of Data Sorting
Sort at Query Time or Sort After
Retrieval?
Tuning with External Sorts
Internals of Oracle Sorting
Managing Oracle Sorting
Avoiding Disk Sorts
Super Sizing SQL Sort areas
Monitoring Disk Sorts
CHAPTER 13:
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
Nested Tables and SQL Performance
ADTs and Oracle SQL
Abstract Datatypes and SQL Performance
CHAPTER 14:
Tuning SQL 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
Using Global Temporary Tables to Improve
SQL Speed
Removing Subqueries Using the with Clause
Internals of Temporary Tables
Row Management of Temporary Tables
Remove subqueries with global temporary
tables
Correlated vs. Noncorrelated 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
CHAPTER 15:
Troubleshooting 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
CHAPTER 16:
SQL Tuning & the Library Cache
The Library Cache and Oracle SQL
Performance
All About Parsing
All About Cursors
Reusing SQL Inside the Library Cache
Place All SQL Inside Stored Procedures
Use Host Variables
Tuning the Library Cache
Tuning shared_pool_size
The Library Cache Hit Ratio
Using open_cursors
Using session_cached_cursors
Using cursor_space_for_time
Using cursor_sharing
Oracle Cursor Sharing Enhancements
Using g Adaptive Cursor Sharing
Conclusion
CHAPTER 17:
Time Series SQL Tuning
Proactive Time-Series SQL Tuning
SQL Trending with STATSPACK and AWR
The dba_hist_sqlstat Table
The dba_hist_sql_plan Table
Viewing Table and Index Access with AWR
Tracking SQL nested loop joins
Tracking Full-Scan SQL Over Time
Counting Index Usage Inside SQL
Tracking SQL Sort Activity
Tracking SQL Library Cache Hard Parses
CHAPTER 18:
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 Tuning
Oracle 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
CHAPTER 19:
Oracle SQL Tuning Advisors
Towards Automated SQL Tuning
Oracle’s Automatic SQL Tuning Approach
The Goals of Holistic SQL Tuning
Decision Support and Expert Systems
Technology
Inside the g SQL Performance Analyzer
Comparing the SPA Results
Gathering the SQL Tuning set
Oracle guided workflow screen
Using the Oracle SQLTuning Advisor
Using SQLTuning Advisor Session
Oracle Automatic Database Diagnostics
Monitor
CHAPTER 21:
Oracle SQL Advanced Analytics
Inside SQL Analytics
Creating Custom SQL Extensions
Extending SQL with PL/SQL Functions
Mathematic Functions in SQL
Binary Functions in SQL
Sign Functions
Rounding and Truncating Functions
SQL Modulo Functions
SQL Functions to Search and Modify
Strings
Regular Expression Functions
SQL Conversion Functions
SQL NLS Functions
National Character Set
Logical Functions with True or False
Values
SQL Null Functions
|
