Oracle Performance Tuning
Authors: Edward Whalen and Mitchell Schroeter
Since the launch of the world’s first relational database in 1977, Oracle has been an essential part of a technology revolution that has irrevocably changed modern business. Oracle innovation has always lead by example; whether by adopting open source technologies, integrating its software across all product lines or embracing Internet-based business practices, Oracle has set a high standard for software ingenuity. In fact, in 1999, Oracle was the first company to deliver a relational database on the Linux platform, and now delivering the first unbreakable Linux database server platform (strongly believing that Linux will be the most popular server operating system on the market, by the year 2006.)
About the authors
Edward Whalen is vice president and founder of Performance Tuning Corporation, a consulting company that specializes in database performance, administration, and backup/recovery solutions. Edward has published two other books on the Oracle RDBMS: “Oracle Performance Tuning and Optimization” and “Teach Yourself Oracle 8 in 21 Days”.
Mitchell Schroeter is a senior consultant with Performance Tuning Corporation and has worked in the field of database performance tuning for five years. Prior to Performance Tuning Corporation, Mitchell worked at Dell Computer Corporation as a system analyst in the system performance analysis group. Mitchell specializes in tuning database systems and application code, storage area networks, and Oracle9i Real Application Clusters.
Inside the book
Oracle software powers the Internet, the world’s biggest data centers and mission critical 24/7/365 applications, serving terabytes of data to large user communities. It’s all about making our data secure, available to our users anytime and from everywhere when they need it. Computer systems processing power is rapidly increasing every year, while memory and disk storage are becoming plentiful and less expensive than ever. This has allowed users to increase the amount of stored data as well as increase the amount of processing that is done with the data. The user communities are also becoming larger, especially with the advent of the Internet age. Because of this, society cannot simply solve performance problems by throwing more hardware at the problem. It’s necessary to analyze, size and properly configure systems to perform optimally.
The topics covered in this book aim to offer a basic understanding of how the components of the system work together to form the whole. Having this foundation should certainly help the reader to tackle a performance problem, know what to look for and finally know how to fix it.
Conceptually, this book is divided into four main parts.
Part one, instance tuning starts with an introduction to tuning fundamental concepts and methodologies. The rest of the chapters in this part of the book covers in detail the architecture of the Oracle Server. While explaining the purpose of the various components of the Oracle instance (the shared memory structures), as well as the physical components of the Oracle server the authors cover also the performance aspects, shedding light on some more or less known intricacies, configuration parameters and rules of thumb on how various components should be configured, interoperate with the rest of the system, etc. Hints on calculating common SGA (shared pool, library cache, dictionary cache) ratios, configuration and management of rollback segments, redo log buffer, sort and hash area sizes and temporary storage are also discussed here. Chapter 4 of the book covers some of the more important performance enhancing features in the Oracle database, namely index concepts, and index types, partitioning option, index and hash clusters and parallel query processing are explained here. Chapter 1 is rounded with an overview of the Oracle database resource manager, brief reference of the most commonly used dynamic performance views and finally a showcase on how to setup and use BSTAT/ESTAT and Statspack tools.
Part two, Oracle and system hardware, offers a thorough explanation of the IO concepts, disk drive performance, disk capacity sizing, RAID subsystems and SAN systems. Various RAID Disk Subsystems are covered in detail in a clear and concise way with nice graphical explanations, and for each RAID level a performance, fault tolerance and economics comparison are given. It’s very useful to read and know which RAID level is suitable for Oracle, and for what physical components of the Oracle server. Finally, the most important Oracle object’s storage parameters are explained (such as PCTFREE, PCTUSED, DB block size, fragmentation issues, etc.)
Part three, Application and SQL tuning is dedicated to the most crucial part of an overall Oracle server tuning mission. Most likely, your endeavor will start by optimizing your SQL statements performance. Sometimes, only one “monster” query is enough to bring your server to it’s knees and render it slow and unresponsive. The most powerful tools in your Oracle performance toolkit are explained here, starting with the SQL trace facility and how to interpret the “raw” trace file using TKPROF utility. The explain plan is also covered here. Chapter 12 is solely dedicated to index tuning. Chapter 13 covers the Oracle optimizer. The Oracle optimizer is responsible for outputting the SQL statements execution plan. An overview of how the optimizer works, the optimizer initialization parameters and optimization methods (RBO vs. CBO) are given here. In order to create a good and performance effective execution plans we must provide the most up to date information about the Oracle dictionary objects. Gathering information using DBMS_STATS and the ANALYZE command are well explained. Chapter 14 covers some sql tuning fundamentals. Things such as how to identify poorly tunded SQL statements, using various join types or what to index are covered here. Chapter 15 is a brief reference of hints that could be used to influence the optimizer’s decision about the SQL statements execution plan.
Part four, advanced topics, is an overview from an technology and performance perspective of the Oracle9i Real Application Clusters, Tuning Backup and Recovery, tuning Oracle9i Data Guard, and how to solve some Oracle networking performance issues. While Oracle9i RAC although being a state of the art technology is not so largley adopted mainly because of implementation costs and is not likely to became a usual configuration in small Oracle shops, tuning backup and recovery is something every DBA should be concerned with. A good and well performing backup is a single most important DBA task. Tuning Oracle9i Data Guard and Oracle networking performance is not so deeply covered in the book.
What I think of it
This book is written with conciseness. People who are entering the world of Oracle performance tuning are the ones that will benefit the most from reading this book. Seasoned Oracle veterans should consider reading this book to found new insights, as every book offers some new and exciting stuff and a different point of view to some more or less known topics.
As an Oracle fan, my Oracle performance tuning book is still a dream since the Oracle Server is a large and complex system. Oracle performance tuning is a huge topic, which requires a lot of knowledge and hands on experience. Although performance tuning has nothing to do with black magic, rather with an exact, scientific approach to problem solving, it’s really a magic when we succeed to push our system closest of it’s full potential.
Writing a good Oracle performance tuning book involves a lot of time and effort. To us, as readers, this book will offer a good fundamental knowledge and serve as a good lead to further expand our Oracle performance tuning knowledge.