|
|
|
1 | (4) |
|
1.1 Aims and Target Groups |
|
|
2 | (1) |
|
1.2 An Overview of the Book |
|
|
2 | (1) |
|
|
|
3 | (2) |
|
2 Some Thoughts on the Term "SQL Tuning" |
|
|
5 | (6) |
|
2.1 SQL Tuning: Definitions and Objectives |
|
|
5 | (1) |
|
|
|
6 | (5) |
|
|
|
6 | (2) |
|
|
|
8 | (1) |
|
2.2.3 Database Administrator |
|
|
9 | (2) |
|
3 Minimum Minimorum on the Subject of the "Execution Plan" |
|
|
11 | (14) |
|
3.1 Can You Read Execution Plans? |
|
|
11 | (1) |
|
3.2 Some Important Details |
|
|
11 | (11) |
|
3.2.1 Sections of the Execution Plan |
|
|
12 | (8) |
|
3.2.2 Optimizer Estimations and Costs |
|
|
20 | (1) |
|
|
|
20 | (2) |
|
|
|
22 | (3) |
|
4 Approaches to Formal SQL Tuning |
|
|
25 | (6) |
|
4.1 The Objective: Effective SQL Tuning |
|
|
25 | (1) |
|
4.2 The Principle: Elimination of "Brakes" in the Execution Plan |
|
|
26 | (1) |
|
4.3 The Method: Analysis of Runtime Statistics in the Execution Plan |
|
|
26 | (1) |
|
4.4 The Main Criterion: The Runtime Statistic "Cardinality" |
|
|
27 | (1) |
|
4.5 The Procedure: An Iterative Process |
|
|
28 | (1) |
|
4.6 The Guideline: Tuning Without Changing the SQL Statement |
|
|
28 | (3) |
|
5 Bottlenecks in the Execution Plan |
|
|
31 | (42) |
|
5.1 "Local" Problems in the Execution Plan |
|
|
31 | (17) |
|
|
|
31 | (4) |
|
5.1.2 A Nonselective Index |
|
|
35 | (3) |
|
5.1.3 An Index with a Large Clustering Factor |
|
|
38 | (1) |
|
|
|
39 | (4) |
|
5.1.5 Nested Loop Join Instead of Hash Join and Vice Versa |
|
|
43 | (5) |
|
5.2 "Global" Problems in the Execution Plan |
|
|
48 | (22) |
|
5.2.1 Formal Rules for Changing the Table Order in a Join |
|
|
48 | (5) |
|
5.2.2 Joins with a Low Number of Hits |
|
|
53 | (11) |
|
5.2.3 Joins with a Large Hit Quantity |
|
|
64 | (6) |
|
|
|
70 | (3) |
|
6 Procedure of Formal SQL Tuning |
|
|
73 | (6) |
|
7 Practical Experience with Formal SQL Tuning |
|
|
79 | (14) |
|
|
|
79 | (8) |
|
7.1.1 Statistics on Problem Categories |
|
|
80 | (1) |
|
7.1.2 A Small Synthetic Test Case in Respect of a Nonselective Index |
|
|
81 | (2) |
|
|
|
83 | (4) |
|
|
|
87 | (6) |
|
7.2.1 The First Practical Example |
|
|
88 | (2) |
|
7.2.2 The Second Practical Example |
|
|
90 | (3) |
|
|
|
93 | (4) |
| Appendix: Application of the Formal Principle for the Analysis of Performance Problems After an Oracle Migration |
|
97 | (12) |
| Literature |
|
109 | |
Lisainfo e-raamatute kohta