Sumário Itens Encontrados: 631PART I Introduction 11 Introduction to Oracle 32 Understanding Terms 213 What Is a Well-Tuned System? 314 Tuning Methodology 415 Benchmarking 516 Performance Monitoring Tools 737 Performance Engineering Starts at the Design Stage 81PART II Tuning the Server 898 What Affects Oracle Server Performance? 919 Oracle Instance Tuning 9710 Performance Enhancements 13911 Tuning the Server Operating System 16712 Operating System-Specific Tuning 17713 System Processors 20514 Advanced Disk I/O Concepts 21315 Disk Arrays 225PART III Configuring the System 24316 OLTP System 24517 Batch Processing System 26518 Decision Support System 28519 Data Warehousing System 30320 BLOB System 32321 The Oracle Parallel Server System 33922 Optimal Backup and Recovery 34923 Miscellaneous Configurations 367PART IV Tuning SQL 39124 What Is a Well-Tuned SQL Statement? 39325 Using EXPLAIN PLAN and SQL Trace 40326 Tuning SQL Statements 41927 Using the Oracle Optimizer 43728 Using Procedures, Functions, and Packages 44929 Providing for Data Integrity and Triggers 46130 Using Hints 47531 Introducing SQL Development Tools 48932 Miscellaneous SQL Topics 501ivOracle Performance Tuning & OptimizationPART V Tuning the Client 51333 What Affects Client Performance? 51534 Tuning the Client System 52535 Using GUI Builders 53336 Using Middleware Products 555PART VI Tuning the Network 56337 What Affects Network Performance? 56538 Tuning the Network Components 573PART VII References 579A Review of Tuning Guidelines 581B Quick Reference 595C Flowcharts 603D Glossary 607E Oracle Tuning Parameters 619F of the CD-ROM 645Index 649The Database 4The Physical Layer 4The Logical Layer 5The Oracle Instance 8The Oracle Memory Structure 8System Global Area (SGA) 9Program Global Area (PGA) 10Processes 10How Transactions Work 12Oracle Products 13Oracle RDBMS Products 13Oracle Workgroup Server 15Personal Oracle for Windows 16Oracle Development Tools 16Oracle Applications 17Oracle Services 18Summary 19Terms 22RDBMS Functionality 26Checkpoint 26Logging and Archiving 26Business Models 27OnLine Transaction Processing (OLTP) 27Batch Processing 27Decision Support 28Data Warehousing 28Binary Large Objects (BLOBs) 28Unit Conversions 28Powers of 10 29Storage Units 29Summary 30Client/Server Computing 32The Client or Front-End Machines 33The Server 33The Network 35Client/Server Checklist 35Host-Based Computing 36The Front-End Application 36The Database 36Terminal-Based Checklist 37Batch Computing 38Batch-Processing Checklist 39Exceptions 39Multimedia Systems 39Shipping Systems 39Summary 40Goals 42Throughput 42Response Time 42Connectivity 43Fault Tolerance 43Load Time 44Tuning Methodology 44Examine the Problem 45Determine the Problem 47Determine the Solution and Set Goals 48Test the Solution 49Analyze the Results 50Summary 50Introduction to Benchmarking 52Industry Standard Benchmarks 52The Transaction Processing Performance Council (TPC) 53TPC Rules and Regulations 55Results 56Benchmarks 57Publication Benchmarks 69Custom Benchmarks 70Writing Your Own Benchmark 70Summary 72Oracle Tools 75SQL*DBA Monitor 76Server Manager 76Oracle SNMP Agents 76SQL Trace 76EXPLAIN PLAN 77OS Tools 77Third-Party Tools 78Real-Time Monitors 79Threshold Monitors 79Summary 80Design Stage 82Database Layout 82Indexes and Clusters 83Application Design 83Hardware Sizing 83Network Considerations 84Performance Tuning after the System Is Built 84Tuning the Client 85Tuning the Server 85Tuning the Network 85Summary 86System Bottlenecks 92Finding the Bottleneck 92Removing the Bottleneck 93System Tuning 93Tuning RDBMS Resources 93Tuning OS Resources 94Tuning Hardware Resources 94Other Tuning Factors 95System Limitations 95Summary 95Tuning Memory 98Tuning the Operating System 99Tuning the Private SQL and PL/SQL Areas 100Tuning the Shared Pool 100Tuning the Buffer Cache 107Tuning the I/O Subsystem 108Understanding Disk Contention 109Identifying Disk Contention Problems 110Solving Disk Contention Problems 111Reducing Unnecessary I/O Overhead 117Migrated and Chained Rows 117Dynamic Extensions 118PCTFREE and PCTUSED Command Options 119A Review of I/O Reduction Techniques 122Tuning Rollback Segments 123Understanding How Rollback Segments Work 123Tuning Rollback Segments 126Review of Rollback Segment Tuning 130Checking for Latch Contention 130Redo Log Buffer Contention 130Redo Log Buffer Latch Contention 131Tuning Checkpoints 133Optimizing Archiving 134Adjusting the Effect of Archiving 135Optimizing Sorts 135Minimizing Free List Contention 136Summary 137Block Size 140Clusters 141Direct-Write Sorts 143Fragmentation 144Hash Clusters 146When To Hash 147Indexes 148Index Types 149How the Oracle Index Works 149What To Index 150Multiblock Reads 152Multiblock Writes 152Parallel Query Option 153Parallel Query Processing 153Direct-Write Sorts 158Parallel Index Creation 159Parallel Loading 159Parallel Recovery 160Parallel Server Option 161Spin Counts 164Summary 164Goals 168Processes 169Memory 170I/O 170Direct or Synchronous I/O 172Asynchronous I/O 172Miscellaneous 173Post-Wait Semaphore 173Scheduling and Preemption 173Cache Affinity 174Summary 174NetWare 178Architectural Overview 178Tuning Considerations 179Windows NT 183Architectural Overview 183Tuning Considerations 185OS/2 188Architectural Overview 188Tuning Considerations 188UNIX 191Architectural Overview 192Tuning Considerations 192Summary 203Overview of Computer Architecture 206CPU and Cache 206CPU Design 207CISC Processors 207RISC Processors 208Multiprocessor Systems 209SMP Systems 209MPP Systems 209CPU Cache 210System Memory Architecture 210Virtual Memory System 211Bus Design 211Summary 212Disk Operation 214Seek Time 216Rotational Latency 217Data Transfer Rate 217Queue Time 218Disk Performance 219Random I/Os 220Sequential I/Os 220Summary 223How Does a Disk Array Work? 226Software Array 227Hardware Array 227RAID Technology 229RAID-0 230RAID-1 230RAID-2 231RAID-3 231RAID-4 232RAID-5 232Fault-Tolerance Concerns 233No Data Protection 233Full Data Protection 234Partial Data Protection 234Configuring RAID for RDBMS Performance 235Isolate Sequential I/Os 236Distribute Random I/Os 237Size the Volume Properly 238Configure for the Disk Array 240RAID Comparison 240Summary 241Characteristics of the OLTP System 246Data Access Patterns 246System Load 247Goals 248Design Considerations 249Physical Data Layout 250Hardware Considerations 253Tuning Considerations 253Oracle Tuning 254Server OS Tuning 255Enhancements 256Oracle Parallel Server Option 257Hardware Enhancements 257Performance Verification 260What To Test in the RDBMS 261What To Test in the OS 261Benchmarks 262Summary 262Characteristics of the Batch Processing System 266Data Access Patterns 267System Load 267Goals 268Design Considerations 270Physical Data Layout 270Hardware Considerations 274Tuning Considerations 274Oracle Tuning 274Server OS Tuning 276Enhancements 277Parallel Query Option 277Oracle Parallel Server Option 278Hardware Enhancements 279Performance Verification 281What To Test in the RDBMS 281What To Test in the OS 282Benchmarks 282Summary 283Characteristics of a DSS System 287Data Access Patterns 287System Load 288Goals 289Design Considerations 290Physical Data Layout 291Hardware Considerations 294Tuning Considerations 294Oracle Tuning 294Server OS Tuning 295Enhancements 296Parallel Query Option 297Oracle Parallel Server Option 297Hardware Enhancements 298Performance Verification 300What To Test in the RDBMS 301What To Test in the OS 301Benchmarks 301Summary 302Characteristics of a Data Warehouse 304Data Access Patterns 305System Load 306Goals 307Design Considerations 308Physical Data Layout 308Fault Tolerance Consideration 311Hardware Considerations 312Tuning Considerations 312Oracle Tuning 312Server OS Tuning 314Enhancements 315Parallel Query Option 316Oracle Parallel Server 316Hardware Enhancements 317Performance Verification 319What To Test in the RDBMS 320What To Test in the OS 320Benchmarks 320Summary 321Characteristics of BLOBs 324Data Access Patterns 324System Load 324Goals 325Design Considerations 327Physical Data Layout 328Hardware Considerations 331Tuning Considerations 331Oracle Tuning 332Server OS Tuning 333Enhancements 333Hardware Enhancements 334Performance Verification 335What To Test in the RDBMS 336What To Test in the OS 336Benchmarks 336Summary 337Oracle Parallel Server Architecture 340Design Considerations 343Design Goals 343System Design 346Tuning the Parallel Server System 346Summary 348RDBMS Operational Review 351Backup Process 351Recovery Process 351Characteristics of the Oracle Backup Process 352Cold (Offline) Backup 352Hot (Online) Backup 352Data Access Patterns During Backup 353System Load During Backup 353Backup Goals 353System Design Considerations 354Cold Database Backup 354Hot Database Backup 355Tuning Considerations 358System Enhancements To Improve Backup Performance 359CPU Enhancements 359I/O Enhancements 359Network Enhancements 360Split Up the Backup 360Performance Verification 362What To Test in the RDBMS 362What To Test in the OS 363Summary 365Financial Systems 368System Characteristics 369Design and Tuning Hints 369Enhancements 372Replicated Systems 374System Characteristics 374Design and Tuning Hints 375Distributed Systems 377System Characteristics 378Design and Tuning Hints 378TextServer 3.0 Systems 379System Characteristics 379Design and Tuning Hints 380Enhancements 381Oracle Office Systems 382System Characteristics 383Design and Tuning Hints 383WebServer Systems 386System Characteristics 386Design and Tuning Hints 387Enhancements 389Summary 390How To Identify Badly Formed SQL Statements 394Transaction Processing 395SQL Statement Processing 397Cursor Creation 398Statement Parsing 399Query Processing 400Bind Variables 401Statement Execution 401Parallelization 401Fetch Rows To Be Returned 401Summary 402SQL Trace 404SQL Trace Initialization 404Controlling SQL Trace 405SQL Trace Functionality 406TKPROF Functionality 407Interpreting SQL Trace 409The EXPLAIN PLAN Command 414EXPLAIN PLAN Initialization 414Invoking EXPLAIN PLAN 415Extracting EXPLAIN PLAN Results 416Registering Applications 417Summary 418Tuning an Existing Application 420Problem Analysis 420Tuning the Application 422Designing a New Application 426Indexes 426Clusters 430Hash Clusters 431Packages, Procedures, and Functions 432Optimization Approaches 433Discrete Transactions 435Summary 436How the Optimizer Works 438How To Specify an Optimization Mode 438Optimization Methods 439Rule-Based Approach 440Cost-Based Approach 441Using the ANALYZE Command 441How To Run the ANALYZE Command 442Data Dictionary Statistics 445Hints 447Summary 447Review of the Library Cache 450Procedures and Functions 452Procedures 453Functions 453How Procedures and Functions Operate 454How To Create Stored Procedures and Stored Functions 456How To Replace Procedures and Functions 457Packages 457Summary 459Integrity Constraints 462Referential Integrity 462Integrity Constraints 465Using Constraints 466Triggers 469Using Triggers 469Using Alerts 470Creating Triggers 470Viewing Triggers 471Audit Trails 472Serial Reads 473Summary 473Implementing Hints 476Hint Syntax 477Hint Errors 477Using Multiple Hints 478Hints 478Optimization Approaches 478Access Methods 481Parallel Query Hints 485Summary 487Database Design Tools 490Oracle Designer/2000 490Third-Party Tools 492Application Development Tools 494Oracle Tools 494Third-Party Tools 495Analysis Tools 496Oracle Mission Control 496Third-Party Tools 497Summary 499Table Sequences 502Creating Sequences 502Tuning Sequences 503Using Sequences 503Using Cached Sequences for Primary Key Values 504Join Performance 505Equijoin 506Self Join 506Cartesian Product 506Outer Join 507Tuning Joins for Throughput 507Tuning Joins for Response Time 507Locking 508What Is Locking? 508Serializable Reads 508Using Locks 509Array Processing 510Using VARCHAR2 instead of CHAR 510Summary 511What Is a Client Machine? 516The Traditional Computing Model 516The Network Computing Model 517The GUI/Server Model 517The Client/Server Model 519Two-Tiered and Three-Tiered Models 520Two-Tiered System 520Three-Tiered System 521Client Bottlenecks 522Network Performance 523Application Performance 523Presentation Performance 524Client Hardware Performance 524Summary 524Windows NT 527Tuning Memory 52716-bit Applications 527I/O Performance 528Microsoft Windows 3.1 and Windows for Workgroups 3.11 528Memory 528Network 528Microsoft Windows 95 52932-Bit Support 529Memory 530Network 530Oracle Support 530UNIX 530Memory 531Network 531Hardware 531Summary 532Tuning the Application 534First-Generation Graphical Application Development Tools 534Modern Graphical Application Development Tools 535How To Test and Improve Automatically Generated SQL Statements 535Oracle Tools 536Developer/2000 536Power Objects 544Third-Party Tools 546Delphi from Borland 547ReportSmith 548SQLWindows from Gupta 550PowerBuilder from Powersoft 552Summary 553What Is Middleware? 556Two-Tiered System Architecture 556Three-Tiered System Architecture 557Application Servers 558How To Tune the Application Server 559Transaction Monitor (TM) 559What Is a TM? 559When To Use a Transaction Monitor 561Tuning the TM and System 561Summary 562Network Architecture 566Hardware Components 566Summary 571Software Tuning 574NetWare 574Windows NT 575OS/2 575UNIX 575Oracle Tuning 575Network Design 576Bandwidth Considerations 576Segmenting the Network 577Bridges, Routers, and Hubs 577Summary 578RDBMS Tuning 582SGA 582Performance Enhancements 583OS Tuning 588OS Tuning Goals 589OS Features 589I/O Tuning 590System Design 590Application Tuning 591Client Tuning 592Network Tuning 593Oracle Instance Tuning 596Library Cache 596Data Dictionary Cache 596Database Buffer Cache 597Physical I/O Usage 597Chained Rows 599Recursive Calls 599Rollback Segment Contention 599Dynamic Rollback Growth 600Redo Log Buffer Contention 600Redo Latch Contention 601Sort Performance 601Free List Contention 602Problem-Solving Methodology 604User-Transaction Profile 605SQL Statement Processing 605The Oracle Optimizer 605Performance Parameters 620Parallel Query Option Parameters 626Analysis Tool Parameters 627General Parameters 629Multithreaded Server Parameters 637Distributed Option Parameters 638Parallel Server Parameters 639Security Parameters 641Trusted Oracle7 Parameters 642National Language Support Parameters 643SQL Scripts 646Chapter 9 646Chapter 10 646Chapter 16 647Chapter 25 647Chapter 27 647Chapter 28 648Chapter 29 648Chapter 32 648