Skip to content

Archive for

Book Review : Oracle database 11gr2 performance tuning cookbook

I have recently received a cookbook about Oracle 11gr2  performance tuing cookbook for book review.

The title of this book have a big sound for who ready to prepare about  oracle performance tuing .Yes this is the cookbook focus only for Beginneer level and also get a  good idea for experience guys when you have a performance issues and diagonse the issues immediately.

Someone thinking ,i don’t know where I can start to read about Oracle performace tuning. At this situation,  i hope this is the one of the  guide helps you to start the beginning level and get a good idea about troubleshooting the performance issues and also you can able to know more stuff about performance. Here the author explained through this book when  the performance issues will happen  and how do we fix this issues and also How we can  proactively written the sql code and developing the applications .This guide helps with the lot of examples, step by step and it has include many screenshot also.

I hope you may get a good stuff about performance tuning after you read this book. you will quickly learn to apply the right methodology to tune the performance of an Oracle Database, and to optimize application design and SQL and PL/SQL code. By following the real-world examples you will see how to store your data in correct structures and access and manipulate them at a lightning speed. You will learn to speed up sort operations, hack the optimizer and the data loading process, and diagnose and tune memory, I/O, and contention issues.

I mentioned this book written by  Ciro Fiorillo.

What you will learn from this book

  • Design applications that run at lightning speed
  • Implement fast and scalable SQL and PL/SQL code
  • Choose the correct structures to store the data and access them
  • Optimize sort operations, such as order-by, Top-N queries, ranking, and set operators
  • Help the optimizer to choose the right access plan to retrieve data at the best available speed
  • Load data in the database at a faster speed by using the correct tools and options
  • Tune the database memory to obtain maximum performance using available resources
  • Tune the I/O operations, by designing a database over the I/O system
  • Tune and reduce contention issues on data and structures by using an optimal design

Table of contents

You can download sampe chapter from here

This book has including 11 chapters.

What this book covers

Chapter 1, Starting with Performance Tuning will show how to set up the example database, how to adopt a performance tuning process that can help in solving performance problems, and how to collect and analyze data from Oracle Database using various methods.

Chapter 2, Optimizing Application Design presents the most common application design issues that prevent an application from running without performance issues. You will see how to improve database performance by sharing reusable code and by reducing the number of requests to the database by using various database objects.

Chapter 3, Optimizing Storage Structures will show how to optimize the use of different database storage structures, presenting the optimal use for tables, clusters, indexes, and partitioning. You will see how to choose the appropriate structure to improve access time to your data, also analyzing the possible drawbacks in the operations that modify the data.

Chapter 4, Optimizing SQL Code is focused on SQL code optimization. Throughout the chapter you will find many methods to diagnose and solve typical performance problems caused by poorly written SQL code. You will find answers on how (and when) to avoid full table scans, how to use indexes, bulk operations and arrays, join and subquery optimization. You will also see how to trace SQL activity to diagnose problems.

Chapter 5, Optimizing Sort Operations will show the importance of optimizing sort operations to achieve better performance even when you don’t see any explicit sort operations in your SQL code. In this chapter, we will see the difference between in-memory and on-disk sort, how an index can improve the performance by reducing or avoiding sort operations, how to perform top-n queries, and how to use aggregate functions, and the use of set operations

Chapter 6, Optimizing PL/SQL Code will show how to optimize PL/SQL code in stored procedures, triggers, and user-defined functions. You will see the advantages of using bulkcollect and array processing, native compilation and function result cache.

Chapter 7, Improving the Oracle Optimizer is focused on how to help the Oracle Optimizer in choosing the best execution plan using various tools, tricks, and tips, to obtain better performance. You will see the use of hints, statistics, histograms, stored outlines, adaptive cursor sharing, SQL tuning sets, and SQL baselines.

Chapter 8, Other Optimizations will show how to use Client Side Result Cache, parallel SQL, CREATE TABLE AS SELECT, and direct path inserting to optimize performance in both queries and DML operations. You will also see how to use SQL*Loader and Data Pump to load data into your Oracle Database.

Chapter 9, Tuning Memory will show how to avoid different memory-related issues, starting with Operating System paging. You will learn how to properly configure the library cache, the shared pool, the Program Global Area (PGA), the User Global Area (UGA), and the database buffer cache.

Chapter 10, Tuning I/O will focus on how to optimize the I/O, learning how to distribute Oracle files and stripe objects on different disks, what RAID level is better for each type of database files. The use of asynchronous I/O, checkpoint and redo logs tuning are also discussed in this chapter.

Chapter 11, Tuning Contention will show how to prevent, detect, and tune contention-related issues. You will see both lock and latch contention, why they occur, and how to prevent and solve any issue related to concurrency and contention in your database.

In Appendix A, Dynamic Performance Views you will find a list of the most used dynamic performance views; for each view you will find a brief description and a list of the most useful fields of the view, to be used as a reference in your daily work.

In Appendix B, A Summary of Oracle Packages Used for Performance Tuning you will find a brief summary of Oracle supplied packages useful in order to solve performance-related problems.

who this book for

This book is aimed at software developers, software and data architects, and DBAs who are beginning to use the Oracle Database, and want to solve performance problems faster and in a rigorous way.

If you are an architect who wants to design fast performing applications, a DBA who is keen to dig into the causes of performance issues, or a developer who wants to learn why and where the application is running slowly this book will provide a good start for your career in performance tuning.