How is this Book Organized?

 < Day Day Up > 



This book is divided into three separate parts. The three parts cover relational data model tuning in Part I, SQL code tuning in Part II, and finally physical and configuration tuning in Part III.

The object in this book is to cover all aspects of tuning and Oracle installation. Each chapter focuses on a very particular aspect of tuning Oracle Database. As you read through each part, each chapter will build on the previous chapter, digging deeper step by step.

Part I. Data Model Tuning

Chapters 1 and 2 introduce and offer tuning solutions for relational data modeling. Chapter 3 looks at alternative data modeling methodologies and Chapter 4 provides some historical background.

Chapter 1. The Relational Database Model

Introduces Normalization and Referential Integrity.

Chapter 2. Tuning the Relational Database Model

Tuning using efficient Normalization, Referential Integrity indexing, alternate indexing, Denormalization, and some useful tricks.

Chapter 3. Different Forms of the Relational Database Model

This chapter provides a contrast of relational, object, and object- relational concepts.

Chapter 4. A Brief History of Data Modeling

This is a brief history of relational data modeling and the roots of SQL.

Part II. SQL Code Tuning

The format in Part II is to first describe and then offer solutions and tools to help find those solutions. Chapters 5 and 6 present SQL and describe how to tune SQL code without having to dig into the depths of the Oracle Optimizer. Chapter 7 looks at indexing. Chapter 8 digs into the Oracle Optimizer. Chapters 9 and 10 present tools used to help find SQL code performance problems. Solving as opposed to finding SQL code problems is found in Chapters 6, 7, and 8.

Chapter 5. What is SQL?

A brief description of SQL and the basics that Oracle SQL can accomplish. Everything is explained in detail and using practical examples.

Chapter 6. The Basics of Efficient SQL

This chapter is the most important chapter in Part II SQL Code Tuning. This chapter will teach step by step how to write properly tuned SQL code. Robust realistic data sets and Oracle Database schemas are used to prove all aspects of tuning. Some tables in schemas are in excess of 1 million rows. There is plenty of data to tune with.

Tip 

The number of rows in tables varies as the book progresses.

Chapter 7. Common Sense Indexing

Teaches how to build indexes properly, building on tuning of SQL code taught in Chapter 6. Indexing and index types are analyzed, explained, and compared for speed in relation to various database activities.

Chapter 8. Making SQL Efficient in Oracle Database

Covers very deep level tuning of SQL code in Oracle Database with emphasis on using the Optimizer, without wasting time on too many nitty-gritty details. This chapter describes how the Oracle SQL code Optimizer functions and what can be done to influence the Optimizer. If you have read, understood, and implemented the advice and pointers in Chapters 6 and 7, you may never need to understand the Optimizer on this level.

Tip 

Chapter 6 should be read prior to reading Chapter 8.

Chapter 9. How to Find Problem Queries

Describes the INS and OUTS of Optimizer query plans using EXPLAIN PLAN, Tracing, and TKPROF. Additional details covering Oracle V$ performance views for SQL code tuning are also provided.

Chapter 10. Tuning SQL with Oracle Enterprise Manager

A brief picture of SQL code tuning tools contained within Oracle Enterprise Manager.

Part III. Physical and Configuration Tuning

As in Part II, the format in Part III is to first describe and then offer solutions and tools to help find those solutions. Chapters 11 to 17 describe how to build cleanly structured Oracle installations. Chapters 18 to 20 present symptoms and indicators of problems. Chapter 21 present tools which can be used to solve problems and Chapter 22 offers up bottleneck tuning methods using the Oracle Wait Event Interface and STATSPACK.

Chapter 11. Installing Oracle and Creating a Database

Shows how to create a cleanly structured Oracle installation and database.

Chapter 12. Tuning Oracle Database File Structures

Tuning Oracle Database File Structures encompasses both the physical and logical layers of tuning from the Oracle Instance and Oracle Database file system layers through to logical layers and tablespaces.

Chapter 13. Object Tuning

Object Tuning covers database objects such as tables, indexes, sequences, and synonyms. There are numerous tweaks to be made to various database objects to tune for performance.

Chapter 14. Low-Level Physical Tuning

This chapter covers physical block and extent structures, and how to build physical structure from the ground up.

Chapter 15. Hardware Resource Usage Tuning

Hardware resource usage tuning is introduced, covering tuning CPU usage, Oracle Database memory cache buffers, and finally tuning of I/O activity.

Chapter 16. Tuning Network Usage

This chapter covers the Listener, network naming methods, and shared server configuration. There are numerous ways in which Oracle network connectivity can be tuned for better performance, depending on requirements.

Chapter 17. Oracle Partitioning and Parallelism

Oracle Partitioning allows for breaking up of large tables into separate objects, which can have SQL code executed on them in parallel for excellent performance improvements.

Chapter 18. Ratios: Possible Symptoms of Problems

Traditionally Oracle Database practitioners use large numbers of ratios to assess and provide pointers as to what and where to tune. The secret is to not tune the ratios themselves. Use the ratios as indicators of and pointers to potential problems.

Chapter 19. Wait Events

A wait event occurs when a process or session is forced to wait for another to complete using something that both processes require.

Chapter 20. Latches

Latches are used to protect Oracle Database memory cache buffers from simultaneous access.

Chapter 21. Tools and Utilities

There are many tools usable for physical and configuration tuning including Oracle Enterprise Manager and STATSPACK. Additionally there are numerous non-Oracle vendor tools such as Spotlight. Various tuning aspects with utilities are briefly mentioned.

Chapter 22. Tuning with the Wait Event Interface and STATSPACK

This "interface" is a group of inter-related statistical performance views, which can be used to isolate bottlenecks. Oracle Enterprise Manager may be the best tool to use when drilling down into the Oracle Database Wait Event Interface, when trying to isolate the source of performance problems. STATSPACK is currently the official Oracle Corporation recommended tuning and performance monitoring tool. However, isolating performance problems by drilling down into the Oracle Database Wait Event Interface is most easily done using the Oracle Enterprise Manager.

Appendices

The appendices contain sample database entity relationship diagrams, scripting examples, and some reference material. Please note that one particular database, the Accounts schema, had active processing executing on it throughout the writing of this book. As a result the quantities of data in the tables are not constant. If row numbers in tables vary between different chapters and sometimes within chapters then it is nothing to be concerned about.



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net