Oracle Performance Tuning

Whalen E.

This book is designed to enhance the skills of the Oracle DBA by giving them knowledge and examples on how to improve their system’s performance. Readers will learn how to: understand how to use trace files, the alert log file and event sets to assist with tuning; become familiar with the new features of Oracle9i.; understand and be able to use utilities and dynamic performance views to assist with tuning; understand how to tune applications using index structures and how to perform SQL statement tuning.; tune SQL Statements Efficiently; read Alert Logs and Trace Files; tune the Shared Pool; tune the Buffer Cache; use Multiple Buffer Caches; tune a Multi-Threaded Server; configure and tune the storage subsystem that Oracle is using, and use Oracle in a SAN environment.

---

Table of contents :
Oracle Performance Tuning and Optimization……Page 1
Copyright……Page 2
Overview……Page 3
Contents……Page 5
Foreword……Page 21
Acknowledgments……Page 22
About the Author……Page 23
Introduction……Page 24
What’s in this Book?……Page 25
How To Use this Book……Page 27
Part I Introduction……Page 28
Ch 1 Introduction to Oracle……Page 30
Ch 2 Understanding Terms……Page 48
Ch 3 What Is a Well-Tuned System?……Page 58
Ch 4 Tuning Methodology……Page 68
Ch 5 Benchmarking……Page 78
Ch 6 Performance Monitoring Tools……Page 100
Ch 7 Performance Engineering Starts at the Design Stage……Page 108
Part II Tuning the Server……Page 116
Ch 8 What Affects Oracle Server Performance?……Page 118
Ch 9 Oracle Instance Tuning……Page 124
Ch 10 Performance Enhancements……Page 166
Ch 11 Tuning the Server Operating System……Page 194
Ch 12 Operating System-Specific Tuning……Page 204
Ch 13 System Processors……Page 232
Ch 14 Advanced Disk I/O Concepts……Page 240
Ch 15 Disk Arrays……Page 252
Part III Configuring the System……Page 270
Ch 16 OLTP System……Page 272
Ch 17 Batch Processing System……Page 292
Ch 18 Decision Support System……Page 312
Ch 19 Data Warehousing System……Page 330
Ch 20 BLOB System……Page 350
Ch 21 The Oracle Parallel Server System……Page 366
Ch 22 Optimal Backup and Recovery……Page 376
Ch 23 Miscellaneous Configurations……Page 394
Part IV Tuning SQL……Page 418
Ch 24 What Is a Well-Tuned SQL Statement……Page 420
Ch 25 Using EXPLAIN PLAN and SQL Trace……Page 430
Ch 26 Tuning SQL Statements……Page 446
Ch 27 Using the Oracle Optimizer……Page 464
Ch 28 Using Procedures, Functions, and Packages……Page 476
Ch 29 Providing for Data Integrity and Triggers……Page 488
Ch 30 Using Hints……Page 502
Ch 31 Introducing SQL Development Tools……Page 516
Ch 32 Miscellaneous SQL Topics……Page 528
Part V Tuning the Client……Page 540
Ch 33 What Affects Client Performance?……Page 542
Ch 34 Tuning the Client System……Page 552
Ch 35 Using GUI Builders……Page 560
Ch 36 Using Middleware Products……Page 582
Part VI Tuning the Network……Page 590
Ch 37 What Affects Network Performance?……Page 592
Ch 38 Tuning the Network Components……Page 600
Part VII References……Page 606
Appendix A Review of Tuning Guidelines……Page 608
Appendix B Quick Reference……Page 622
Appendix C Flowcharts……Page 630
Appendix D Glossary……Page 634
Appendix E Oracle Tuning Parameters……Page 646
Appendix F Contents of the CD-ROM……Page 672