List of Figures

---

Introduction

Figure 1: The Steps in Tuning an Oracle Installation

Figure 2: Using Static Generic Entities

Figure 3: A Simple Query Plan from EXPLAIN PLAN

Chapter 1: The Relational Database Model

Figure 1.1: 1st Normal Form

Figure 1.2: 1st Normal Form Rows for Figure 1.1

Figure 1.3: 2nd Normal Form

Figure 1.4: 2nd Normal Form Rows for Figure 1.3

Figure 1.5: 3rd Normal Form

Figure 1.6: 3rd Normal Form Rows for Figure 1.5

Figure 1.7: A More Concise Form of the Rows in Figure 1.6

Figure 1.8: 4th Normal Form

Figure 1.9: 4th Normal Form Rows for Figure 1.8

Figure 1.10: 5th Normal Form

Figure 1.11: 5th Normal Form Rows for Figure 1.10

Chapter 2: Tuning the Relational Database Model

Figure 2.1: Static Generic Entities

Figure 2.2: A Hierarchy of Relational Entities

Figure 2.3: A Hierarchy of Relational Entities Using Integer Keys

Figure 2.4: A Hierarchy of Relational Entities Using Unique Integer Identifier Keys

Figure 2.5: Avoiding Locking on Foreign Keys

Figure 2.6: An Object-Like Many-to-Many Join Resolution Entity

Figure 2.7: A Traditional Relational Version of Figure 2.6

Figure 2.8: 4th Normal Form Denormalization

Figure 2.9: 3rd Normal Form Denormalization

Figure 2.10: The Employees Schema

Figure 2.11: 2nd Normal Form Denormalization

Figure 2.12: Duplicating Individual Columns Between Entities

Figure 2.13: Summary Columns in Parent Entities

Chapter 3: Different Forms of the Relational Database Model

Figure 3.1: A Purist's Form of the Employees Schema

Figure 3.2: The Object versus the Relational Model

Figure 3.3: The Run Time Objects

Figure 3.4: The Employees Schema

Chapter 4: A Brief History of Data Modeling

Figure 4.1: The History and Evolution of Data Modeling

Figure 4.2: A Hierarchical Model for Company Employees

Figure 4.3: A Network Model for Company Employees

Figure 4.4: A Relational Model for Company Employees

Figure 4.5: The Object versus the Relational Model

Figure 4.6: The Evolution of the Relational Databases

Figure 4.7: The History of Oracle Database

Chapter 5: What is SQL?

Figure 5.1: DML Versus DDL

Figure 5.2: The Employees Schema

Figure 5.3: Separating the Employee Table into Manager and Employee

Figure 5.4: The MERGE Command

Figure 5.5: COMMIT versus ROLLBACK

Figure 5.6: Locking Rows Before Transaction Completion

Chapter 6: The Basics of Efficient SQL

Figure 6.1: Row Counts of Accounts Schema Tables

Chapter 7: Common Sense Indexing

Figure 7.1: An Oracle Database BTree Index

Figure 7.2: A Bitmap Index

Figure 7.3: A Skewed BTree Index

Figure 7.4: A Reverse Key Index

Figure 7.5: A Compressed Composite-Column Index

Figure 7.6: A Function-Based Index

Chapter 8: Making SQL Efficient in Oracle Database

Figure 8.1: An Example Histogram

Figure 8.2: A Skewed BTree Index

Figure 8.3: Passing a ROWID Pointer from an Index to a Table

Chapter 10: Tuning SQL with Oracle Enterprise Manager

Figure 10.1: The Index Tuning Wizard

Figure 10.2: Select the Application Type

Figure 10.3: Select Schemas to Analyze

Figure 10.4: The Index Tuning Wizard Makes Suggestions

Figure 10.5: SQL Analyze and the Analysis Stage

Figure 10.6: SQL Analyze Single SQL Statement Assessment and Testing

Figure 10.7: SQL Analyze SQL Text Display

Figure 10.8: SQL Analyze Query Plan

Figure 10.9: SQL Analyze Index Recommendations

Figure 10.10: SQL Analyze Query Plan Assessment

Figure 10.11: SQL Analyze Virtual Indexing, Hints, and Tuning

Figure 10.12: Comparing Different Versions of an SQL Statement

Figure 10.13: An ALL_ROWS Optimizer Query Plan

Figure 10.14: A FIRST_ROWS Optimizer Query Plan

Figure 10.15: Oracle Expert Recommendations for the Accounts Schema

Chapter 11: Installing Oracle and Creating a Database

Figure 11.1: Dedicated and Shared Servers Architecture

Figure 11.2: Choosing the Appropriate Database Template

Figure 11.3: Options and Examples when Creating a Database

Figure 11.4: More Optional Database Features

Figure 11.5: Choosing Dedicated or Shared Servers

Figure 11.6: Oracle Database Memory Buffer Parameters

Figure 11.7: The Sort Buffer

Figure 11.8: Creation of Tablespaces and Datafiles

Figure 11.9: Creation of Redo Logs

Figure 11.10: Physical Datafile Default Structure

Chapter 12: Tuning Oracle Database File Structures

Figure 12.1: Oracle Instance Memory Cache Buffers

Figure 12.2: Oracle Instance Process and Memory Layers

Figure 12.3: The Oracle Database and the Oracle Instance

Figure 12.4: Relationships Between Oracle Database Files

Figure 12.5: Oracle Database Physical and Logical Structure

Chapter 13: Object Tuning

Figure 13.1: Nonparallel Versus Parallel Processing

Chapter 14: Low-Level Physical Tuning

Figure 14.1: Block Fixed Header

Figure 14.2: Block Table Directory

Figure 14.3: Block Row Directory

Figure 14.4: Block Free Space

Figure 14.5: Block Row Data

Figure 14.6: Accounts Schema Current Row and Block Numbers

Figure 14.7: Changes to Block Structure Space Usage

Figure 14.8: Changes to Block Structure Concurrency

Chapter 16: Tuning Network Usage

Figure 16.1: Dedicated Versus Shared Servers

Chapter 17: Oracle Partitioning and Parallelism

Figure 17.1: Physical Partitioning

Chapter 21: Tools and Utilities

Figure 21.1: Defining Events for Detection and Resolution

Figure 21.2: Monitoring and Managing Locking

Figure 21.3: TopSessions Monitoring in a Specified Order

Figure 21.4: TopSQL Statements

Figure 21.5: The Performance Overview Tool

Figure 21.6: The Performance Manager Main Screen

Figure 21.7: Latch Get/Miss Rates Drilldown

Figure 21.8: Latch Analysis

Figure 21.9: After Execution of Tablespace Analysis

Figure 21.10: What Should be Reorganized and Why

Figure 21.11: After Reorganization and Coalescence on the INDX Tablespace

Figure 21.12: Spotlight on Oracle Main Monitoring Screen

Figure 21.13: Spotlight SGA View

Figure 21.14: Various Spotlight Views

Figure 21.15: The Windows Performance Monitor

Figure 21.16: This is a Very Busy Server

Chapter 22: Tuning with the Wait Event Interface and STATSPACK

Figure 22.1: Tools for Drilling into the Oracle Database Wait Event Interface

Figure 22.2: The System Aggregation Layer of the Oracle Database Wait Event Interface

Figure 22.3: Isolating Segments using Event Parameters

Figure 22.4: Session-Level Event and Wait Views

Figure 22.5: Hooking Wait Events to Sessions

Figure 22.6: Find SQL Code for Sessions

Figure 22.7: Drilling Down into Latches

Figure 22.8: Database Health Overview Chart– Database Memory Portion

Figure 22.9: Database Health Overview Chart–Database Memory Portion

Figure 22.10: Top Objects and TopSQL

Appendix A: Sample Databases

Figure A.1: Employees Schema ERD Version One

Figure A.2: Employees Schema ERD Version Two

Figure A.3: Accounts Schema ERD Denormalized Version

Figure A.4: Accounts Schema ERD Normalized Version

---