Cost-based Oracle Fundamentals

by Lewis, Jonathan

• ISBN13: 9781590596364
• ISBN10: 1590596366
• eBook ISBN(s): 9781430200871
• Format: Paperback
• Copyright: 2005-11-07
• Publisher: Apress
• More Book Details
• Purchase Benefits

Summary

The cost-based optimiser is simply a piece of code that contains a model of how Oracle databases work. By applying this model to the statistics about your data, the optimiser tries to efficiently convert your query into an executable plan. Unfortunately, the model can't be perfect, your statistics can't be perfect, and the resulting execution plan may be far from perfect. In Cost- Based Oracle Fundamentals, the first book in a series of three, Jonathan Lewis one of the foremost authorities in this field describes the most commonly used parts of the model, what the optimiser does with your statistics, and why things go wrong. With this information, you'll be in a position to fix entire problem areas, not just single SQL statements, by adjusting the model or creating more truthful statistics.

Table of Contents

Foreword	xiii
About the Author	xv
About the Technical Reviewers	xvii
Acknowledgments	xix
Introduction	xxi
CHAPTER 1 What Do You Mean by Cost?	1	(8)
Optimizer Options	1	(1)
So What Is the Cost?	2	(3)
Transformation and Costing	5	(2)
WYSIWYG?	7	(1)
Summary	8	(1)
Test Cases	8	(1)
CHAPTER 2 Tablescans	9	(32)
Getting Started	9	(5)
Onwards and Upwards	14	(11)
Effects of Block Sizes	14	(2)
CPU Costing	16	(6)
The Power of CPU Costing	22	(3)
The BCHR Is Dead! Long Live the BCHR!	25	(3)
Parallel Execution	28	(3)
Index Fast Full Scan	31	(3)
Partitioning	34	(5)
Summary	39	(1)
Test Cases	40	(1)
CHAPTER 3 Single Table Selectivity	41	(20)
Getting Started	41	(3)
Null Values	44	(1)
Using Lists	45	(5)
10g Update	49	(1)
Range Predicates	50	(5)
10g Update	54	(1)
Two Predicates	55	(3)
Problems with Multiple Predicates	58	(1)
Summary	59	(1)
Test Cases	60	(1)
CHAPTER 4 Simple B-tree Access	61	(26)
Basics of Index Costing	61	(2)
Getting Started	63	(18)
Effective Index Selectivity	66	(1)
Effective Table Selectivity	67	(1)
clustering_factor	67	(2)
Putting It Together	69	(2)
Extending the Algorithm	71	(7)
The Three Selectivities	78	(3)
CPU Costing	81	(3)
Loose Ends	84	(1)
Summary	85	(1)
Test Cases	85	(2)
CHAPTER 5 The Clustering Factor	87	(28)
Baseline Example	87	(14)
Reducing Table Contention (Multiple Freelists)	90	(4)
Reducing Leaf Block Contention (Reverse Key Indexes)	94	(2)
Reducing Table Contention (ASSM)	96	(3)
Reducing Contention in RAC (Freelist Groups)	99	(2)
Column Order	101	(3)
Extra Columns	104	(2)
Correcting the Statistics	106	(6)
The sys_op_countchg() Technique	106	(5)
Informal Strategies	111	(1)
Loose Ends	112	(1)
Summary	112	(1)
Test Cases	113	(2)
CHAPTER 6 Selectivity Issues	115	(36)
Different Data Types	115	(7)
Date Values	116	(1)
Character Values	116	(2)
Daft Data Types	118	(4)
Leading Zeros	122	(2)
Deadly Defaults	124	(2)
Discrete Dangers	126	(4)
10g Update	130	(1)
Surprising sysdate	130	(2)
Function Figures	132	(2)
Correlated Columns	134	(7)
Dynamic Sampling	137	(2)
Optimizer Profiles	139	(2)
Transitive Closure	141	(3)
Constraint-Generated Predicates	144	(4)
Summary	148	(1)
Test Cases	148	(3)
CHAPTER 7 Histograms	151	(30)
Getting Started	151	(6)
Generic Histograms	157	(5)
Histograms and Bind Variables	157	(3)
When Oracle Ignores Histograms	160	(2)
Frequency Histograms	162	(7)
Faking Frequency Histograms	166	(1)
Warning to Fakers	167	(2)
"Height Balanced" Histograms	169	(6)
The Arithmetic	172	(3)
Data Problems Revisited	175	(4)
Daft Datatypes	175	(3)
Dangerous Defaults	178	(1)
Summary	179	(1)
Test Cases	180	(1)
CHAPTER 8 Bitmap Indexes	181	(26)
Getting Started	181	(9)
The Index Component	186	(2)
The Table Component	188	(2)
Bitmap Combinations	190	(8)
Low Cardinality	192	(3)
Null Columns	195	(3)
CPU Costing	198	(2)
Interesting Cases	200	(5)
Multicolumn Indexes	200	(1)
Bitmap Join Indexes	201	(1)
Bitmap Transformations	202	(3)
Summary	205	(1)
Test Cases	205	(2)
CHAPTER 9 Query Transformation	207	(58)
Getting Started	208	(3)
Evolution	211	(1)
Filtering	211	(23)
Filter Optimization	215	(2)
Scalar Subqueries	217	(7)
Subquery Factoring	224	(6)
Complex View Merging	230	(2)
Pushing Predicates	232	(2)
General Subqueries	234	(18)
Subquery Parameters	236	(1)
Categorization	237	(6)
Semi-Joins	243	(3)
Anti-Joins	246	(2)
Anti-join Anomaly	248	(1)
Nulls and Not In	249	(1)
The ordered Hint	250	(2)
Star Transformation Joins	252	(6)
Star Joins	258	(2)
The Future	260	(2)
Summary	262	(1)
Test Cases	263	(2)
CHAPTER 10 Join Cardinality	265	(42)
Basic Join Cardinality	265	(6)
Biased Joins	269	(2)
Join Cardinality for Real SQL	271	(3)
Extensions and Anomalies	274	(14)
Joins by Range	275	(1)
Not Equal	276	(2)
Overlaps	278	(2)
Histograms	280	(3)
Transitive Closure	283	(5)
Three Tables	288	(3)
Nulls	291	(4)
Implementation Issues	295	(4)
Difficult Bits!	299	(2)
Features	301	(2)
An Alternative Viewpoint	303	(1)
Summary	304	(1)
Test Cases	305	(2)
CHAPTER 11 Nested Loops	307	(12)
Basic Mechanism	307	(5)
Worked Example	312	(2)
Sanity Checks	314	(4)
Summary	318	(1)
Test Cases	318	(1)
CHAPTER 12 Hash Joins	319	(34)
Getting Started	319	(16)
The Optimal Hash Join	323	(2)
The Onepass Hash Join	325	(6)
The Multipass Hash Join	331	(4)
Trace Files	335	(4)
Event 10104	336	(2)
Event 10053	338	(1)
Headaches	339	(2)
Traditional Costing	339	(1)
Modern Costing	340	(1)
Comparisons	341	(6)
Multitable Joins	347	(3)
Summary	350	(1)
Test Cases	350	(3)
CHAPTER 13 Sorting and Merge Joins	353	(50)
Getting Started	353	(17)
Memory Usage	359	(1)
CPU Usage	360	(4)
sort_area_retained_size	364	(1)
pga_aggregate_target	365	(3)
Real I/O	368	(2)
Cost of Sorts	370	(5)
10053 trace	371	(4)
Comparisons	375	(4)
Merge Joins	379	(8)
The Merge Mechanism	379	(5)
A Merge Join Without the First Sort	384	(1)
The Cartesian Merge Join	385	(2)
Aggregates and Others	387	(11)
Indexes	392	(1)
Set Operations	393	(5)
Final Warning	398	(1)
Summary	399	(1)
Test Cases	400	(3)
CHAPTER 14 The 10053 Trace File	403	(50)
The Query	404	(1)
The Execution Plan	405	(1)
The Environment	406	(1)
The Trace File	407	(42)
Parameter Settings	407	(4)
Query Blocks	411	(1)
Stored Statistics	412	(2)
Single Tables	414	(2)
Sanity Checks	416	(1)
General Plans	416	(1)
Join order[1]	417	(6)
Join order[2]	423	(1)
Join order[3]	424	(1)
Join order[4]	424	(1)
Join order[5]	425	(4)
Join order[6]	429	(1)
Join order[7]	429	(4)
Join order[8]	433	(2)
Join order[9]	435	(1)
Join order[10]	435	(1)
Join order[11]	436	(3)
Join order[12]	439	(2)
Join order[13]	441	(2)
Join order[14]	443	(1)
Join order[15]	444	(1)
Join order[16]	444	(1)
Join order[17]	445	(2)
Join order[18]	447	(2)
Join Evaluation Summary	449	(2)
Test Cases	451	(2)
APPENDIX A Upgrade Headaches	453	(12)
dbms_stats	453	(1)
Frequency Histograms	454	(1)
CPU Costing	455	(1)
Rounding Errors	455	(1)
Bind Variable Peeking	455	(1)
Nulls Across Joins	456	(1)
B-tree to Bitmap Conversions	456	(1)
Index Skip-Scans	456	(1)
AND-Equal	456	(1)
Index Hash Join	457	(1)
In-List Fixed	457	(1)
Transitive Closure	458	(1)
sysdate Arithmetic Fixed	458	(1)
Indexing Nulls	459	(1)
pga_aggregate_target	459	(1)
Sorting	460	(1)
Grouping	460	(1)
Sanity Checks	460	(1)
Going Outside the Limits	460	(1)
Type Hacking	460	(1)
optimizer_mode	461	(1)
Descending Indexes	461	(1)
Complex View Merging	461	(1)
Unnest Subquery	461	(1)
Scalar and Filter Subqueries	461	(1)
Parallel Query Changes x 2	462	(1)
Dynamic Sampling	462	(1)
Temporary Tables	462	(1)
Dictionary Stats	462	(3)
APPENDIX B Optimizer Parameters	465	(10)
optimizer_features_enable	465	(2)
The 10053 Trace File	467	(6)
v$sql_optimizer_env	473	(2)
INDEX	475