Oracle Performance Tuning for 10gR2
Regular price
34.500 KD
inc. VAT
Couldn't load pickup availability
Table of contents
- Cover
- Copyright Pageiv
- Contentsvii
- Prefacexxiii
- Introductionxxix
- Part I: Data Model Tuning1
- Chapter 1. The Relational Database Model3
- 1.1 The Formal Definition of Normalization3
- 1.2 A Layperson’s Approach to Normalization9
- 1.3 Referential Integrity25
- Chapter 2. Tuning the Relational Database Model27
- 2.1 Normalization and Tuning27
- 2.2 Referential Integrity and Tuning28
- 2.3 Optimizing with Alternate Indexes44
- 2.4 Undoing Normalization47
- Chapter 3. Different Forms of the Relational Database Model73
- 3.1 The Purist’s Relational Database Model73
- 3.2 Object Applications and the Relational Database Model75
- Chapter 4. A Brief History of Data Modeling81
- 4.1 The History of Data Modeling81
- 4.2 The History of Relational Databases85
- 4.3 The History of the Oracle Database86
- 4.4 The Roots of SQL87
- Part II: SQL Code Tuning89
- Chapter 5. What Is SQL?91
- 5.1 DML and DDL91
- 5.2 Transaction Control110
- 5.3 Parallel Queries113
- Chapter 6. The Basics of Efficient SQL115
- 6.1 The SELECT Statement117
- 6.2 Using Functions145
- 6.3 Pseudocolumns155
- 6.4 Comparison Conditions158
- Chapter 7. Advanced Concepts of Efficient SQL167
- 7.1 Joins167
- 7.2 Using Subqueries for Efficiency179
- 7.3 Using Synonyms191
- 7.4 Using Views191
- 7.5 Temporary Tables198
- 7.6 Resorting to PL/SQL199
- 7.7 Object and Relational Conflicts206
- 7.8 Replacing DELETE with TRUNCATE208
- Chapter 8. Common-Sense Indexing209
- 8.1 What and How to Index209
- 8.2 Types of Indexes213
- 8.3 Types of Indexes in Oracle Database214
- 8.4 Tuning BTree Indexes237
- 8.5 Summarizing Indexes253
- Chapter 9. Oracle SQL Optimization and Statistics255
- 9.1 What Is the Parser?256
- 9.2 What Is the Purpose of the Optimizer?257
- 9.3 Rule-Based versus Cost-Based Optimization261
- Chapter 10. How Oracle SQL Optimization Works281
- 10.1 Data Access Methods281
- 10.2 Sorting333
- 10.3 Special Cases337
- Chapter 11. Overriding Optimizer Behavior Using Hints347
- 11.1 How to Use Hints347
- 11.2 Hints: Suggestion or Force?350
- 11.3 Classifying Hints352
- 11.4 Influencing the Optimizer in General353
- 11.5 10g Naming Query Blocks for Hints363
- Chapter 12. How to Find Problem Queries367
- 12.1 Tools to Detect Problems367
- 12.2 EXPLAIN PLAN368
- 12.3 SQL Trace and TKPROF377
- 12.4 TRCSESS392
- 12.5 Autotrace394
- 12.6 Oracle Database Performance Views for Tuning SQL396
- Chapter 13. Automated SQL Tuning409
- 13.1 Automatic Gathering of Statistics410
- 13.2 The AWR and the ADDM411
- 13.3 Automating SQL Tuning421
- Part III: Physical and Configuration Tuning427
- Chapter 14. Tuning Oracle Database File Structures429
- 14.1 Oracle Database Architecture and the Physical Layer429
- 14.2 Tuning and the Logical Layer440
- 14.3 Automating Database File Structures456
- Chapter 15. Object Tuning459
- 15.1 Tables459
- 15.2 Indexes467
- 15.3 Index-Organized Tables and Clusters471
- 15.4 Sequences472
- 15.5 Synonyms and Views472
- 15.6 The Recycle Bin473
- Chapter 16. Low-Level Physical Tuning475
- 16.1 What Is the High-Water Mark?475
- 16.2 Space Used in a Database476
- 16.3 What Are Row Chaining and Row Migration?477
- 16.4 Different Types of Objects478
- 16.5 How Much Block and Extent Tuning?479
- 16.6 Choosing Database Block Size479
- 16.7 Physical Block Structure481
- 16.8 Extent Level Storage Parameters493
- Chapter 17. Hardware Resource Usage Tuning497
- 17.1 Tuning Oracle CPU Usage497
- 17.2 How Oracle Database Uses Memory509
- 17.3 Tuning Oracle I/O Usage540
- Chapter 18. Tuning Network Usage549
- 18.1 The Listener549
- 18.2 Network Naming Methods553
- 18.3 Connection Profiles557
- 18.4 Shared Servers560
- Chapter 19. Oracle Partitioning and Parallelism569
- 19.1 What Is Oracle Partitioning?569
- 19.2 Tricks with Partitions586
- 19.3 Endnotes587
- Part IV: Tuning Everything at Once589
- Chapter 20. Ratios: Possible Symptoms of Problems591
- 20.1 Database Buffer Cache Hit Ratio592
- 20.2 Table Access Ratios603
- 20.3 Index Use Ratio606
- 20.4 Dictionary Cache Hit Ratio607
- 20.5 Library Cache Hit Ratios607
- 20.6 Disk Sort Ratio608
- 20.7 Chained Rows Ratio609
- 20.8 Parse Ratios610
- 20.9 Latch Hit Ratio611
- 20.10 Ratios in the Database Control612
- Chapter 21. Wait Events615
- 21.1 Idle Events616
- 21.2 Significant Events620
- 21.3 Wait Events in the Database Control667
- Chapter 22. Latches669
- 22.1 What Is a Latch?669
- 22.2 The Most Significant Latches676
- Chapter 23. Tools and Utilities685
- 23.1 Oracle Enterprise Manager685
- 23.2 The Database Control709
- 23.3 Spotlight729
- 23.4 Operating System Tools729
- 23.5 Other Utilities and Tools733
- Chapter 24. The Wait Event Interface739
- 24.1 What Is a Bottleneck?739
- 24.2 Detecting Potential Bottlenecks740
- 24.3 What Is the Wait Event Interface?741
- 24.4 Oracle Database Wait Event Interface Improvements760
- 24.5 Oracle Enterprise Manager and the Wait Event Interface762
- 24.6 The Database Control and the Wait Event Interface765
- Chapter 25. Tuning with STATSPACK771
- 25.1 Using STATSPACK771
- Appendices799
- A. Sample Databases799
- B. Sample Scripts831
- C. Syntax Conventions839
- D. Installing Oracle 9i Database841
- E. Sources of Information879
- Index881
Tuning of SQL code is generally cheaper than changing the data model. Physical and configuration tuning involves a search for bottlenecks that often points to SQL code or data model issues. Building an appropriate data model and writing properly performing SQL code can give 100%+ performance improvement. Physical and configuration tuning often gives at most a 25% performance increase.
Gavin Powell shows that the central theme of Oracle10gR2 Performance Tuning is four-fold: denormalize data models to fit applications; tune SQL code according to both the data model and the application in relation to scalability; create a well-proportioned physical architecture at the time of initial Oracle installation; and most important, mix skill sets to obtain the best results.
* Fully updated for version 10gR2 and provides all necessary transition material from version 9i
* Includes all three aspects of Oracle database tuning: data model tuning, SQL & PL/SQL code tuning, physical plus configuration tuning
* Contains experienced guidance and real-world examples using large datasets Emphasizes development as opposed to operating system perspective
Gavin Powell shows that the central theme of Oracle10gR2 Performance Tuning is four-fold: denormalize data models to fit applications; tune SQL code according to both the data model and the application in relation to scalability; create a well-proportioned physical architecture at the time of initial Oracle installation; and most important, mix skill sets to obtain the best results.
* Fully updated for version 10gR2 and provides all necessary transition material from version 9i
* Includes all three aspects of Oracle database tuning: data model tuning, SQL & PL/SQL code tuning, physical plus configuration tuning
* Contains experienced guidance and real-world examples using large datasets Emphasizes development as opposed to operating system perspective