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