Coming Soon ...

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2012-09-30 01:31:02

Share to 

Oracle SQL Optimizing and Tuning Technologies   ORACLE SQL OPTIMIZING AND TUNING TECHNOLOGIES 1 Preface 7 Abstraction 8   SECTION 1 EXECUTION PLAN 10 Chapter 1. Generate and Display Execution Plan 10 1.1 Gnerate Execution Plan 11 1.2 Display Execution Plan 12 1.2.1 Using SQL Query 12 1.2.2 Using DBMS_XPLAN 13 1.2.3 Using AUTOTRACE 19 1.2.4 Other Approaches 20   Chapter 2. Read Execution Plan 22 2.1 Basic Information in Execution Plan 22 2.2 Internal Functions and Execution Plan Operations 23 2.3 Data In Exectuion Plan 25 2.4 Operations in Execution Plan 26 2.4.1 Statement(DDL&DML) Operations 27 2.4.2 Access Path Operations 30 2.4.3 Bitmap Operations 46 2.4.4 Sort Operations 51 2.4.5 Join Operations 55 2.4.6 Hierarchy Query Operations 68 2.4.7 View Operations 71 2.4.8 Data Set Operations 72 2.4.9 Partitions Operations 75 2.4.10 Parallel Query Operations 82 2.4.11 Aggregate Operations 95 2.4.12 Analytic Operations 96 2.4.13 SQL Model Operation 99 2.4.14 Data and Object Management Operations 102 2.4.15 Others 105 2.5 Other Information 111 2.5.1 Query Block and Alias 111 2.5.2 Outline Data 111 2.5.3 Peeked Binds 112 2.5.4 Remote SQL Information 113 2.5.5 Note 113   SECTION 2 SQLOPTIMIZING TECHNOLOGIES 113 Chapter 3. Query Transformation 114 3.1 Heuristic Transformation 115 3.1.1 Simple View Merge 115 3.1.2 Subquery Unnesting 119 3.1.3 Push Subquery 124 3.1.4 Old Join Predicate Push-Dowd 126 3.1.5 Filter Push-Down 128 3.1.6 Predicate Move Around 129 3.1.7 OR Expansion 130 3.1.8 Materialized View Re-write 131 3.1.9 Set Join Conversion 132 3.1.10 Gernate Predicate from Constraint 133 3.1.11 Star Transformation 134 3.1.12 Order By Elimination 136 3.1.13 Distinct Elimination 137 3.1.14 Table Elimination 137 3.1.15 Subquery Coalesce 140 3.1.16 Common Subexpression elimination 141 3.1.17 Count Convertion 142 3.1.18 Evaluation of Expressions and Conditions 143 3.1.19 Aggregate Subquery Elimination 148 3.1.20 Distinct Aggregate Transformation 149 3.1.21 select list pruning，SLP 151 3.1.22 Distinct Elimination 151 3.1.23 Distinct Push Down 152 3.1.24 Grouping Set To Union 153 3.1.25 Grouping Set Rewrite 153 3.1.26 Group Pruning 154 3.1.27 Outer Join Elimination 156 3.1.28 Native Full Outer Join 156 3.1.29 Lateral View 158 3.2 Cost Based Transformationb 161 3.2.1 Complex View Merging 161 3.2.2 Join Predicate Push-Down 162 3.2.3 Predicate Pull Up 164 3.2.4 Group By Placement 165 3.2.5 Table Expansion 166 3.2.6 Join factorization 167 3.2.7 Distinct Placement 169 3.2.8 With Subquery Tranformation 170   Chapter 4. Statistics Data 170 4.1 System Statistics Data 171 4.1.1 Gather System Statistics Data 171 4.1.2 Manager System Statistics Data 173 4.1.3 Flexible Management 177 4.1.4 Manage Parameters 178 4.1.5 Manage Preferences 180 4.2 Object Statistics Data 183 4.2.1 Table Statistics Data 183 4.2.2 Index Statistics Data 184 4.2.3 Column Statistics Data 184 4.2.4 Extended Statistics Data 185 4.2.5 Manage Object Statistics Data 186 4.2.6 Pedning Statistics Data 196 4.3 Object Statistics Data Gathering Analysis 197 4.3.1 Table Statistics Data Gathering and Calculating 198 4.3.2 Column Statistics Data Gathering and Calculating 200 4.3.3 Histogram Gathering and Calculating 205 4.3.4 Index Statistics Data Gathering and Calculating 216   Section 5. Execution Plan Cost Estimate 218 5.1 Optimizer Cost Model 219 5.2 Fundamental  Cost Formula 220 5.3 Calculate Selectivity 222 5.3.1 Single Filter Condition 222 5.3.2 Bind Variable without Peeking 222 5.3.3 Peeked Binds without Histogram 223 5.3.4 Calculate with Histogram 223 5.3.5 Combined Filter 227 5.4 Cost of Multiple Data Block Read Operations 228 5.4.1 Cost Model 228 5.4.2 Calculate IO Cost 230 5.4.3 Calculate CPU Cost 232 5.4.4 Calculate Other Data 238 5.4.5 Cost Calculation Demo of Full Table Scan 238 5.5 Cost of Parallel Query 247 5.5.1 Calculate IO Cost 247 5.5.2 Calculate CPU Cost 249 5.6 Cost of Single Data Block Read Operations 249 5.6.1 Calculate IO Cost 250 5.6.2 Calculate CPU Cost 253 5.6.3 Cost Calculation Demo of Single Data Block Read Operation 255 5.7 Cost of Sort Operations 259 5.7.1 Pass Data to Disk or Not 259 5.7.2 Calculate IO Cost 260 5.7.3 Calculate CPU Cost 261 5.7.4 Cost Calculation Demo of Sort 262 5.8 Cost of Join Operations 266 5.8.1 Join Selectivity 266 5.8.2 Calculate Nested-Loop Join Cost 267 5.8.3 Calculate Sort-Merge Join Cost 272 5.8.4 Calculate Hash Join Cost 275 5.9 Cost of Prarllel Join Operations 282 5.9.1 Calculate IO Cost 283 5.9.2 Calculate CPU Cost 284 5.9.3 Cost Calculation Demo of Prarllel Join Operation 284   SECTION 3 SQL TUNING TECHNOLOGIES 288 Chapter 6. SQL Run-Time Performance Statistics Data 288 6.1 Performance Statistics Data 288 6.1.1 session logical reads 288 6.1.2 consistent gets 289 6.1.3 consistent gets direct 289 6.1.4 consistent gets from cache 290 6.1.5 consistent changes 291 6.1.6 db block gets 291 6.1.7 db block gets direc 292 6.1.8 db block gets from cache 293 6.1.9 db block changes 293 6.1.10 physical reads 294 6.1.11 physical reads cache 294 6.1.12 physical reads cache prefetch 295 6.1.13 physical reads direct 295 6.1.14 physical writes 295 6.1.15 physical writes from cache 295 6.1.16 physical writes direct 295 6.1.17 sorts (memory) 295 6.1.18 sorts (disk) 296 6.1.19 sorts (rows) 297 6.1.20 recursive calls 297 6.2 Run-Time Performance Analysis 297 6.2.1 Logical Reads Analysis 297 6.2.2 Physical Reads Analysis 333 6.2.3 Recursive Calls 394   Chapter 7. Oracle Tuning Technologies 395 7.1 Stored Outline 396 7.1.1 What is Stored Outline 396 7.1.2 Generate Stored Outline 397 7.1.3 Manage Stored Outline 399 7.1.4 Use Stored Outline 400 7.2 SQL Plan Management 402 7.2.1 Generate and Add Plan Baseline 402 7.2.2 Evolve Historical Execution Plan 413 7.2.3 Optimizer Choose Execution Plan from Plan Baseline 414 7.3 Oracle SQL Tuning Advisor 416 7.3.1 Create Tuning Task 416 7.3.2 Parametes of SQL Advisor 417 7.3.3 SQL Tuning Analysis 418 7.4 SQL Performance Analyzer 429 7.4.1 The Procedure of Performance Analyzing 429 7.4.2 Demostration SQL Performance Analyzing 431 7.5 SQL Access Advisor 433 7.5.1 Procedure of the Advisor Advise New Index 433 7.5.2 Use SQL Access Advisor 437   Chapter 8. Quick SQL Tuning 443 8.1 Check Statistics Data 443 8.2 Identify Potential Problem from Execution Plan 446 8.2.1 Multiple Execution Plan 446 8.2.2 Note from Execution Plan 449 8.2.3 High Risk Operations 450 8.2.4 Predicate Information 454 8.2.5 Check Outline Data and Optimizing Environment 456 8.3 Physical Design Tuning 456 8.3.1 Index 456 8.3.2 Partition 457 8.3.3 Materialized View 458 8.3.4 Constratins 458   Appendix: SQL HINT Usage and Real Cases 458 Not End 535