Sumário Itens Encontrados: 341Part I: Foundations 1Chapter 1: Performance Problems 3Do You Need to Plan Performance? 3Requirements Analysis 4Analysis and Design 5Coding and Unit Testing 6Integration and Acceptance Testing 7Designing for Performance 8Lack of Logical Database Design 8Implementing Generic Tables 8Not Using Constraints to Enforce Data Integrity 9Lack of Physical Database Design 9Not Choosing the Right Data Type 9Not Using Bind Variables Correctly 10Not Using Advanced Database Features 10Not Using PL/SQL for Data-Centric Processing 11Performing Unnecessary Commits 11Steadily Opening and Closing Database Connections 11Do You Have Performance Problems? 11System Monitoring 12Response-Time Monitoring 12Compulsive Tuning Disorder 12How Do You Approach Performance Problems? 13Business Perspective vs. System Perspective 13Cataloging the Problems 14Working the Problems 14On to Chapter 2 17Chapter 2: Key Concepts 19Selectivity and Cardinality 19What Is a Cursor? 21Life Cycle of a Cursor 21How Parsing Works 24Shareable Cursors 25Bind Variables 29Reading and Writing Blocks 40Instrumentation 42Application Code 43Database Calls 44On to Part 2 48Part II: Identification 49Chapter 3: Analysis of Reproducible Problems 51Tracing Database Calls 51SQL Trace 52Structure of the Trace Files 65Using TRCSESS 67Profilers 67Using TKPROF 68Using TVD$XTAT 79Profiling PL/SQL Code 89Using DBMS_HPROF 89Using DBMS_PROFILER 96Triggering the Profilers 101On to Chapter 4 101Chapter 4: Real-Time Analysis of Irreproducible Problems 103Analysis Roadmap 104Dynamic Performance Views 105OS Statistics 106Time Model Statistics 107Wait Classes and Wait Events 109System and Session Statistics 113Metrics 114Current Sessions Status 115Active Session History 117SQL Statement Statistics 126Real-time Monitoring 126Analysis With Diagnostics and Tuning Pack 131Database Server Load 131System Level Analysis 132Session Level Analysis 137SQL Statement Information 139Analysis Without Diagnostics Pack 142Database Server Load 142System Level Analysis 143Session Level Analysis 147SQL Statement Information 147On to Chapter 5 149Chapter 5: Postmortem Analysis of Irreproducible Problems 151Repositories 151Automatic Workload Repository 152Performing Configuration 152Taking Snapshots 154Managing Baselines 154Statspack 156Performing Installation 157Configuring the Repository 157Taking and Purging Snapshots 158Managing Baselines 160Analysis With Diagnostics Pack 160Analysis Without Diagnostics Pack 161On to Part 3 166Part III: Query Optimizer 167Chapter 6: Introducing the Query Optimizer 169Fundamentals 169Architecture 172Query Transformations 173Count Transformation 174Common Sub-Expression Elimination 174Or Expansion 175View Merging 176Select List Pruning 177Predicate Push Down 178Predicate Move Around 180Distinct Placement 181Distinct Elimination 181Group-by Placement 181Order-By Elimination 182Subquery Unnesting 183Subquery Coalescing 183Subquery Removal Using Window Functions 184Join Elimination 185Join Factorization 185Outer Join to Inner Join 186Full Outer Join 187Table Expansion 187Set to Join Conversion 188Star Transformation 189Query Rewrite with Materialized Views 189On to Chapter 7 190Chapter 7: System Statistics 191The dbms_stats Package 191What System Statistics Are Available? 192Gathering System Statistics 194Noworkload Statistics 194Workload Statistics 195Choosing Between Noworkload Statistics and Workload Statistics 198Restoring System Statistics 199Working with a Backup Table 200Logging of Management Operations 200Impact on the Query Optimizer 202On to Chapter 8 206Chapter 8: Object Statistics 207The dbms_stats Package 207What Object Statistics Are Available? 209Table Statistics 210Column Statistics 211Histograms 213Extended Statistics 226Index Statistics 230Statistics for Partitioned Objects 232Gathering Object Statistics 233Target Objects 235Gathering Options 239Backup Table 244Configuring the dbms_stats Package 245The Legacy Way 245The Contemporary Way 246Working with Global Temporary Tables 248Working with Pending Object Statistics 250Working with Partitioned Objects 251Challenges 251Incremental Statistics 254Copying Statistics 256Scheduling Object Statistics Gathering 257The 10g Way 257The 11g and 12c Way 259Restoring Object Statistics 261Locking Object Statistics 262Comparing Object Statistics 265Deleting Object Statistics 267Exporting, Importing, Getting, and Setting Object Statistics 268Logging of Management Operations 269Strategies for Keeping Object Statistics Up-to-Date 270On to Chapter 9 271Chapter 9: Configuring the Query Optimizer 273To Configure or Not to Configure 273Configuration Road Map 274Set the Right Parameter! 276Query Optimizer Parameters 276PGA Management 291On to Chapter 10 297Chapter 10: Execution Plans 299Obtaining Execution Plans 299The EXPLAIN PLAN Statement 299Dynamic Performance Views 303Automatic Workload Repository and Statspack 305Tracing Facilities 307The dbms_xplan Package 311Output 311The display Function 316The display_cursor Function 322The display_awr Function 323Interpreting Execution Plans 325Parent-Child Relationship 325Types of Operations 328Stand-Alone Operations 328Iterative Operations 331Unrelated-Combine Operations 331Related-Combine Operations 333Divide and Conquer 342Special Cases 345Adaptive Execution Plans 348Recognizing Inefficient Execution Plans 353Wrong Estimations 353Restriction Not Recognized 355On to Part 4 356Part IV: Optimization 357Chapter 11: SQL Optimization Techniques 359Altering the Access Structures 360How It Works 360When to Use It 361Pitfalls and Fallacies 361Altering the SQL Statement 361How It Works 361When to Use It 363Pitfalls and Fallacies 363Hints 363How It Works 363When to Use It 370Pitfalls and Fallacies 370Altering the Execution Environment 372How It Works 372When to Use It 375Pitfalls and Fallacies 375Stored Outlines 375How It Works 375When to Use It 385Pitfalls and Fallacies 385SQL Profiles 387How It Works 387When to Use It 401Pitfalls and Fallacies 402SQL Plan Management 402How It Works 403When to Use It 417Pitfalls and Fallacies 417On to Chapter 12 418Chapter 12: Parsing 419Identifying Parsing Problems 419Quick Parses 420Long Parses 425Solving Parsing Problems 427Quick Parses 427Long Parses 433Working Around Parsing Problems 433Cursor Sharing 434Server-Side Statement Caching 436Using Application Programming Interfaces 438PL/SQL 439OCI 442JDBC 443ODP.NET 445PHP 446On to Chapter 13 447Chapter 13: Optimizing Data Access 449Identifying Suboptimal Access Paths 449Identification 449Pitfalls 452Causes 454Solutions 454SQL Statements with Weak Selectivity 459Full Table Scans 459Full Partition Scans 461Range Partitioning 461Hash and List Partitioning 474Composite Partitioning 475Design Considerations 477Full Index Scans 479SQL Statements with Strong Selectivity 482Rowid Access 482Index Access 484Single-table Hash Cluster Access 526On to Chapter 14 528Chapter 14: Optimizing Joins 529Definitions 529Join Trees 529Types of Joins 534Restrictions vs. Join Conditions 537Nested Loops Joins 538Concept 538Two-Table Join 539Four-Table Join 541Buffer Cache Prefetches 542Merge Joins 544Concept 544Two-Table Join 545Four-Table Join 548Work Areas 549Hash Joins 555Concept 555Two-table Joins 556Four-Table Joins 557Work Areas 559Index Joins 560Outer Joins 561Choosing the Join Method 562First-Rows Optimization 562All-Rows Optimization 562Supported Join Methods 562Parallel Joins 563Partition-wise Joins 563Full Partition-wise Joins 563Partial Partition-wise Joins 566Star Transformation 568On to Chapter 15 574Chapter 15: Beyond Data Access and Join Optimization 575Materialized View 575How It Works 576When to Use It 596Pitfalls and Fallacies 596Result Caching 597How It Works 597When to Use It 604Pitfalls and Fallacies 604Parallel Processing 605How It Works 605When to Use It 636Pitfalls and Fallacies 636Direct-Path Insert 641How It Works 641When to Use It 644Pitfalls and Fallacies 644Row Prefetching 645How It Works 645When to Use It 650Pitfalls and Fallacies 650Array Interface 650How It Works 650When to Use It 654Pitfalls and Fallacies 654On to Chapter 16 654Chapter 16: Optimizing the Physical Design 655Optimal Column Order 655Optimal Datatype 657Pitfalls in Datatype Selection 657Best Practices in Datatype Selection 660Row Migration and Row Chaining 662Migration vs. Chaining 662Problem Description 664Problem Identification 664Solutions 665Block Contention 665Problem Description 666Problem Identification 666Solutions 670Data Compression 673Concept 673Requirements 675Methods 675Part V: Appendix 679Bibliography 681Index 687