Database Performance Tuning and Optimization: Using Oracle / Edition 1

by Sitansu S. Mittra

Overview

Examples abound in database applications of well-formulated queries running slowly, even if all levels of the database are properly tuned. It is essential to address each level separately by focusing first on underlying principles and root causes, and only then proposing both theoretical and practical solutions.

"Database Performance Tuning and Optimization" comprehensively addresses each level separately by focusing first on underlying principles and root causes, and then proposes both theoretical and practical solutions using Oracle 8i examples as the RDBMS. The book combines theory with practical tools (in the form of Oracle and UNIX shell scripts) to address the tuning and optimization issues of DBAs and developers, irrespective of whether they use Oracle.

Topics and features:

* An integrated approach to tuning by improving all three levels of a database (conceptual, internal, and external) for optimal performance

* Balances theory with practice, developing underlying principles and then applying them to other RDBMSs, not just Oracle

* Includes CD-ROM containing all scripts and methods utilized in the book

* Coverage of data warehouses provides readers much needed principles and tools for tuning large reporting databases

* Coverage of web-based databases

* Appendix B shows how to create an instance and its associated database and all its objects

* Provides useful exercises, references, and Oracle 8i and select 9i examples

Based on nearly two decades of experience as an Oracle developer and DBA, the author delivers comprehensive coverage of the fundamental principles and methodologies of tuning and optimizing database performance. Database professionals and practitioners with some experience developing, implementing, and maintaining relational databases will find the work an essential resource. It is also suitable for professional short courses and self-study purposes.

Product Details

ISBN-13:	9780387953939
Publisher:	Springer New York
Publication date:	12/13/2002
Series:	Springer Professional Computing
Edition description:	2003
Pages:	489
Product dimensions:	7.01(w) x 10.00(h) x 0.28(d)

Table of Contents

Preface
Scope
Overview of the Book
Part 1: Chapters 1-3
Part 2: Chapters 4-10
Part 3: Chapters 11-12
Appendices A-E
Part 1 Methodology
1 Database Application Development
Outline
Overview of the Chapter
1.1 1970s Era for Software Technology
1.2 Role of Database in SDLC and SMLC
1.3 Enterprise Modeling
1.5. Physical Database Design
1.6 Database Implementation
1.7 Database Maintenance
1.8 Naming Guidelines for Database Objects
Key Words
References and Further Reading
Exercises
2 Performance Tuning Methodology
Outline
Overview of the Chapter
2.1 Three Levels of a Database
2.2 Optimization at Each Level
2.3 Process and Metric for Performance Tuning
Key Words
References and Further Reading
Exercises
3 Tuning Conceptual Level of a Database
Outline
Overview of the Chapter
3.1 Three Versions of Conceptual Level
3.2 Performance Issues at Conceptual Level
3.3 Denormalization of Conceptual Level
3.4 Optimal Indexing of Tables
3.5 Integration of Views into Queries
3.6 Partitioning of Tables and Indices
3.7 Data Replication
Key Words
References and Further Reading
Exercises
Part 2 Oracle Tools for Tuning and Optimization
4 Internal Level of an Oracle Database
Outline
Overview of the Chapter
4.1 Components of the Internal Level
4.2 Oracle Instance
4.3 Oracle Database
4.4 Tuning Methodology for Internal Level
4.5 Oracle Data Dictionary
4.6 V$ Views and X$ Tables for Dynamic Performance Monitoring
4.7 Initialization Parameters for Oracle Database
Key Words
References and Further Reading
Exercises
5 Tuning of Disk Resident Data Structures
Outline
Overview of the Chapter
5.1 Disk Resident Data Structures
5.2 Performance Tuning of Disk Resident Data Structures
5.3 Baseline of Disk Resident Data Structures
5.3.3 Segments with Information on Extents
5.4 Changes to Database Schema
5.5 Data Block Structure
5.6 Used Space Fragmentation at Segment Level
5.7 Severity of Free Space Shortage
5.8 Free Space Fragmentation at Tablespace Level
5.9 Row Chaining and Row Migration in Tables
5.10 Performance Tuning of Rollback Segments
Key Words
References and Further Reading
Exercises
6 Tuning of Memory Resident Data Structures
Outline
Overview of the Chapter
6.1 Memory Resident Data Structures
6.2 Performance Tuning of Memory Resident Data Structures
6.3 Data Block Buffers
6.4 Redo Log Buffer
6.5 Shared SQL Pool
6.6 Background Processes
6.7 Tuning the Memory
6.8 Tuning the CPU
6.9 Pinning Packages in Memory
6.10 Latching Mechanism for Access Control
Key Words
References and Further Reading
Exercises
7 Oracle Utility for Tuning and Optimization
Outline
Overview of the Chapter
7.1 Scope of Oracle Utilities
7.2 UTLBSTAT and UTLESTAT Utilities
7.3 Location and Function of the Utility Scripts
7.4 Procedure for Running the Utilities
7.5 Analysis of Performance Report from UTLBSTAT/ESTAT
7.6 Comprehensive Tuning Plan for Internal Level
7.7 Performance Tracking
7.8 Tuning Activities
Key Words
References and Further Reading
Exercises
8 Optimization of the External Level of a Database
Outline
Overview of the Chapter
8.1 Contents of the External Level
8.2 Principles of Query Optimization
8.3 Query Optimization in Oracle
8.4 Optimal Indexing Guidelines
8.5 Methodology for Optimal Indexing
8.6 Implementation of Indices in Oracle
8.7 Tools for Tracking Query Performance
Key Words
References and Further Reading
Exercises
9 Query Tuning and Optimization under Oracle 8i
Outline
Overview of the Chapter
9.1 Oracle Query Performance
9.2 Query Tuning in Oracle: General Principles
9.3 Query Tuning in Oracle: Cost Based Optimizer
9.4 Query Tuning in Oracle: Rule Based Optimizer
9.5 Tuning of Join Queries
9.6 Statistical Forecasting for Tracking Performance
Key Words
References and Further Reading
Exercises
10 Special Features of Oracle 8i and a Glimpse into Oracle 9i
Outline
Overview of the Chapter
10.1 Scope of the Chapter
10.2 Evolution of Oracle through Versions 8 and 8i
10.3 Partitioning of Tables and Indices
10.4 Materialized Views
10.5 Defragmentation via Local Tablespace Management
10.6 LOB Datatype versus LONG Datatype
10.7 Multiple Buffer Pools in the SGA
10.8 Query Execution Plan Stability via Stored Outlines
10.9 Index Enhancements
10.10 Query Rewrite for Materialized Views
10.11 Online Index Creation, Rebuild, and Defragmentation
10.12 ANALYZE versus DBMS_STATS
10.13 Optimization of Top_N Queries
10.14 Glimpse into Oracle 9i
Key Words
References and Further Reading
Part 3 Contemporary Issues
11 Tuning the Data Warehouse at All Levels
Outline
Overview of the Chapter
11.1 Advent of Data Warehouse
11.2 Features of Data Warehouse
11.3 Design Issues of Data Warehouse
11.4 Structure of a Data Warehouse
11.5 Proliferation from Data Warehouse
11.6 Metadata
11.7 Implementation and Internal Level
11.8 Data Loading in Warehouse
11.9 Query Processing and Optimization
Key Words
References and Further Reading
Exercises
12 Web Based Database Applications
Outline
Overview of the Chapter
12.1 Advent of Web Based Applications
12.2 Components of Web Based Applications
12.3 Oracle Application Server (OAS)
12.4 Database Transaction Management under OAS
12.5 Oracle Internet Application Server (iAS)
12.6 Performance Tuning of Web Based Databases
12.7 Tuning of Internal Level
12.8 Tuning of External Level
Key Words
References and Further Reading
Exercises
Appendices
Appendix A Sizing Methodology for Tables, Indices, and Tablespaces
in Oracle 8i
Outline
Overview of the Appendix
A1. Transition from Logical to Physical Database Design
A2. Space Usage via Extents
A3. Algorithms for Sizing Tables, Indices, and Tablespaces
A4. Inclusion of STORAGE Clauses at Table and Index Levels
A5. Sizing Methodology
A6. Sizing of RBS, SYSTEM, TEMP, and TOOLS Tablespaces
Key Words
References and Further Reading
Appendix B Instance and Database Creation
Outline
Overview of the Appendix
B1. Preparation
B2. Instance Startup
B3. Database Creation
B4. Creation of Users, Roles, Privileges, and Schema
B5. Miscellaneous Informational Items
Appendix C Instance and Database Removal
Outline
Overview of the Appendix
C1. Preparation
C2. Locating the Components
C3 Removing the Components
C4. Verification
Appendix D Database Refresh with Transportable Tablespaces
Outline
Overview of the Appendix
D1. Database Refresh Process
D2. Detailed Methodology with Scripts
D3. Time Estimates
D4. Internal Consistency
Key Words
Appendix E Mathematical Foundation of Relational Databases
Outline
Overview of the Appendix
E1. Foundation Pillars of Relational Database Systems
E2. Relation
E3. Functional Dependency
E4. Query Languages
E5. Relational Algebra for Prescriptive Query Languages
E 6. Primitive and Derived Operations in Relational Algebra
E7. Closure Property for Relational Algebra
E8. Relational Calculus for Descriptive Query Languages
E9. Tuple Relational Calculus
E10. Domain Relational Calculus
E11. Equivalence Theorem for Algebra and Calculus
E12. Data Structures for Search Algorithms
E13. Linear Linked List
E14. Search Tree
E15. Hash Table
E16. Performance Metrics
Key Words
References and Further Reading
Index