OCP: Oracle9i Performance Tuning Study Guide / Edition 1

Hardcover (Print)
Used and New from Other Sellers
Used and New from Other Sellers
from $1.99
Usually ships in 1-2 business days
(Save 96%)
Other sellers (Hardcover)
  • All (19) from $1.99   
  • New (7) from $14.50   
  • Used (12) from $1.99   
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
Seller since 2011

Feedback rating:



New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

new condition not used

Ships from: Murphy, TX

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
Seller since 2010

Feedback rating:


Condition: New
2002 Hard cover Illustrated. New. No dust jacket as issued. Includes CD-ROM. 602 p. Audience: General/trade.

Ships from: Miami, FL

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
Seller since 2008

Feedback rating:


Condition: New

Ships from: fallbrook, CA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Seller since 2015

Feedback rating:


Condition: New
Brand New Item.

Ships from: Chatham, NJ

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
Seller since 2015

Feedback rating:


Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Seller since 2015

Feedback rating:


Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Seller since 2008

Feedback rating:


Condition: New

Ships from: Chicago, IL

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
Page 1 of 1
Showing All
Sort by


Here's the book you need to prepare for Exam 1Z0-033: Oracle9i Database: Performance Tuning.

This Study Guide provides:

  • In-depth coverage of official exam objectives
  • Practical information on optimizing Oracle9i databases
  • Hundreds of challenging review questions, in the book and on the CD

Authoritative coverage of all exam objectives, including:

  • Using Diagnostic and Tuning Tools
  • Monitoring and Managing the Buffer Cache
  • Optimizing Sort Operations
  • Detecting and Resolving Lock Contention
  • Diagnosing Shared Server Performance Issues
  • Using Oracle Blocks Efficiently
  • Tuning SQL Statements
  • Configuring Resource Manager

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Read More Show Less

Product Details

  • ISBN-13: 9780782140651
  • Publisher: Wiley
  • Publication date: 5/24/2002
  • Edition description: Study Guide
  • Edition number: 1
  • Pages: 602
  • Product dimensions: 7.80 (w) x 9.20 (h) x 1.70 (d)

Meet the Author

Joseph Johnson is an OCP in database administration with more than 7 years' experience with Oracle databases. He is currently a senior DBA for Greenbriar & Russel in Milwaukee. Johnson was the coauthor of Sybex's Oracle8i DBA Performance Tuning and Network Administration Study Guide.
Read More Show Less

Read an Excerpt

OCP: Oracle9i Performance Tuning Study Guide

By Joseph C. Johnson

John Wiley & Sons

ISBN: 0-7821-4065-3

Chapter One

Introduction to Performance Tuning


  •   Describe the roles associated with the database tuning process
  •   Describe the dependency between tuning in different development phases
  •   Describe Service Level Agreements
  •   Describe appropriate tuning goals
  •   Describe the most common tuning problems
  •   Describe the tuning considerations during development and production
  •   Describe performance and safety tradeoffs


Exam objectives are subject to change at any time without prior notice and at Oracle's sole discretion. Please visit Oracle's Certification website (oracle.com/education/ certification/) for the current exam objectives listing.

"Why is the system so slow?" The ability to answer this question definitively and authoritatively is one of the most important skills for an Oracle DBA to possess. Acquiring this skill requires a thorough understanding of the Oracle Server architecture, as well as knowledge of how Oracle processes SQL statements and how Oracle interacts with host operating systems and server hardware. Consequently, tuning an Oracle database can be a daunting task. Indeed, Oracle9i is so tunable and so feature-rich that it's often difficult to know where to begin your tuning effort. Because of this, the application of proven tuning methodologies is the key to a successful tuning exercise.

This chapter will introduce the concepts related to selecting an appropriate performance tuning methodology and then discusses how to apply that methodology to produce a successful Oracle database tuning effort.

The Oracle Tuning Methodology

Oracle's recommended tuning methodology has undergone some refinement in Oracle9i. Traditionally, Oracle recommended a top-down tuning approach that focused on application design and SQL tuning before examining tuning issues related to the database memory structures and physical I/O. While this methodology is still useful in some situations, Oracle now recommends that a more general set of "Performance Tuning Principles" be followed when the traditional top-down approach is not appropriate.

However, the two methodologies are not necessarily mutually exclusive. Instead, they are intended to help you recognize that the focus of your tuning efforts will be different in each situation. No matter which you choose, performance tuning should be considered an on-going, iterative process- not one that ends when the first tuning issue is resolved. The two methodologies also recognize that the tuning effort must draw upon the expertise of not only the DBA, but also the application designers and developers, and system and network administrators.

Oracle Objective

Describe the roles associated with the database tuning process.

The methodology you choose is generally dependent upon the type of system you are tuning. In general, the traditional approach is still well suited for tuning development databases, while the new, principle-based approach is generally more appropriate for tuning production systems.

Tuning Development Systems

When tuning development systems, Oracle recommends a top-down approach that emphasizes examining the highest performance payoff areas first, and leaving areas with less impact on performance for later inspection. Table 1.1 shows the areas that this methodology focuses on and the order in which they should be examined.

Using this methodology, application-related areas are examined before any database tuning issues are ever considered. This is an excellent strategy, since nearly 90 percent of all tuning issues are application-related and changes made to these first two areas can have a large impact on the remaining areas. For example, improvements to Application Design may necessitate changes in the areas of Memory Allocation and I/O. Therefore, the first two steps of the development tuning methodology focus on issues related to tuning the application design and SQL.

Once any application issues are addressed, the remaining four areas in this methodology focus on performance tuning topics that are directly related to the Oracle Server. These topics include tuning the memory structures, disk I/O activity, and resource contention in the database. Performance problems in these areas are identified using Oracle supplied scripts, queries against the data dictionary, and GUI tools such as Oracle Enterprise Manager, Oracle Expert, and Oracle Trace.


The Oracle9i Performance Tuning OCP Exam addresses only some of the topics related to the first two areas-Tuning the Data Design and Tuning the Application Design. These topics will be addressed in Chapter 3, "SQL Application Tuning and Design," but only to the extent they are covered on the OCP exam. A full treatment of these topics is outside the scope of this book. The Oracle9i Performance Tuning Exam concentrates heavily on the last four areas-Tuning Memory, I/O and Physical Structure, Resource Contention, and Underlying Platforms. In general, the exam requires that you understand how each of these architecture mechanisms work, which measures are available to gauge the performance of these mechanisms, how to interpret the results of these measurements, and what changes are appropriate to improve the performance of these mechanisms. Additionally, topics such as the special tuning considerations for Decision Support Systems (DSS) and Online Transaction Processing Systems (OLTP) as well as the features of Oracle's Shared Server option are also incorporated into the exam. These topics are discussed in Chapters 3 and 6, respectively.

Oracle Objective

Describe the tuning considerations during development and production.

Tuning Production Systems

Unlike development tuning, the "Performance Tuning Principles" that Oracle recommends for production systems focus on more general techniques to identify and resolve tuning issues. Oracle outlines these principles in Chapter 22, "Instance Tuning," of the Database Performance Guide and Reference Release 1 (9.0.1) documentation (Part Number A87503-02). They are also summarized in Table 1.2.


The full library of Oracle documentation is available online at Oracle's free Technet website: technet.oracle.com. This site also features trial software downloads and helpful user forums.

Notice that there are several areas of overlap between the traditional top-down methodology and this newer principle-based approach. One important difference is that the principle-based tuning approach recognizes the fact that changes to an application's design are difficult, if not impossible, after a production system is in use. As packaged business applications become more prevalent, this tuning methodology becomes increasingly important since the highest priority tuning factors considered by the traditional top-down approach (data and application design) are almost entirely outside the control of the DBA when working with a packaged application.


Most of the objectives for the Oracle9i Performance Tuning OCP Exam are related to the second, fourth, and fifth principles (as listed in Table 1.2). They generally focus on examining the database for wait events-users or processes who are waiting for access to a database resource-and then trying to minimize these waits by making the appropriate changes to the system's configuration.

Goal-Oriented Tuning

Whichever tuning methodology you choose, it is important to set specific tuning goals and then measure your progress toward those goals throughout the tuning process. This is not unlike the process a physician uses when treating a patient, where the physician records standard "performance" measures (i.e., blood pressure, temperature, respiration, heart rate, etc.) and then uses these measures throughout the course of treatment to track the progress of the patient's condition.

This process includes establishing benchmarks and gathering baseline statistics or timings against which you can measure your tuning progress, and setting measurable tuning goals.

Establishing a Benchmark

You should measure exactly how the system is currently performing before beginning any tuning effort. Using this benchmark, it is then possible to formulate an idea of how you would like the system to perform after the initial tuning effort is complete. As in medicine, it is important to take a holistic view of the system when gathering this benchmark performance data. No single area should be examined in isolation. Some of the items that should be included in your initial measurements are:

* The performance of client machines used to access the system

* The performance of the network used to access the system

* The performance of the middle-tier application or web servers used to access the system

* The performance of the server hardware used to host the system

* The performance of the operating system used to manage the system hardware

* The performance of the Oracle database used as the repository for the application data

Ideally, all of these areas would be monitored simultaneously for a meaningful period of time, while the system is under a normal workload. This monitoring will likely require the expertise of many people, including system designers, application developers, system and network administrators, and power users. Outputs from monitoring consist primarily of statistics and timings.


Important benchmark statistics can be gathered in all of the design, application, and developer areas listed above. These statistics might include:

* Client PC CPU and memory utilization

* Network traffic efficiency

* Server CPU and memory utilization

* Server disk I/O and controller activity

* Oracle instance memory utilization

* Oracle database I/O activity

These statistics can be gathered using vendor-supplied utilities, third-party tuning tools, or empirical observations. Oracle Server statistics are typically gathered by running Oracle-supplied scripts, monitoring activity through the use of a GUI tool, or by examining database trace and log files. The resulting statistics are frequently expressed in terms of ratios, percentages, or wait times. Other statistics are expressed in terms of throughput. Throughput is the amount of processing that a computer or system can perform in a given amount of time. An example of throughput might be, "How many customer deposits can we post to the appropriate accounts in four hours under regular workloads?" Throughput is an important measure when considering the scalability of the system. Scalability refers to the degree to which additional users can be added to the system without having system performance decline significantly. New features like Real Application Clusters make Oracle9i one of the most scalable database platforms on the market.


Performance tuning considerations for Online Transaction Processing Systems usually revolve around throughput maximization.


Timings related to performance tuning are usually related to response times. Response time is the amount of time it takes for a single user's request to return the desired result while using an application. An example of response time might be "How long does it take for the system to return a listing of all the customers who have purchased products that require service contracts?" Another example of tuning-related timings is time that users are spending waiting for access to a particular database resource.


Performance tuning considerations for Decision Support Systems usually revolve around response time minimization.

Setting Performance Goals

Once you have established a good baseline against which you'll measure your tuning activities, you need to establish measurable tuning goals. These goals should be specific and stated in a measurable way that allows you to use your existing data for comparison.

Oracle Objectives

Describe appropriate tuning goals

Describe Service Level Agreements

Tuning goals should be specific enough to allow you to quantitatively measure the benefits that your tuning efforts provide. For example:

* "I'd like to pin the five most frequently used PL/SQL packages in memory so that the users can access them faster."

* "I'd like to reduce to 10 seconds or less the time it takes to enter a customer's checking account deposit, post the funds to the customer's account, and issue a receipt."

These are both good examples of measurable tuning goals. They are much more useful tuning goals than something generic like, "I'd like the system to run faster."

This explicit statement of goals is important for several reasons. First, it limits the tuning effort to a narrow scope. Changing too many factors at once has foiled many well-intentioned tuning campaigns. By limiting your tuning activities to one or two target areas, the effect of each change can be more easily measured and its effectiveness evaluated.

Second, explicit goals allow you to stop that part of the tuning process once the goals are achieved. While overall tuning is an ongoing and iterative process, it is possible to "tune a system to death" by endlessly tweaking obscure components that produce little or no real performance benefit. Setting tuning targets helps to prevent this problem by allowing you to move on to new tuning areas once you have achieved your specific goals. Specific areas where measurable tuning goals can be specified include the following:

* Reducing the frequency and duration of waits for database resources. These techniques are discussed in Chapter 9, "Tuning Contention."

* Making sure that frequently accessed data and SQL statements are cached in memory. These techniques are discussed in Chapters 4, "Tuning the Shared Pool," and Chapter 5, "Tuning the Database Buffer Cache."

* Minimizing response times and maximizing throughput. These concepts are discussed in Chapter 3, "SQL Application Tuning and Design."

Service Level Agreements may also drive your performance tuning goals. Service Level Agreements, or SLAs, are written agreements between Database Administrators and their user communities. Some SLAs may specify that a particular report or process must complete within a specific period of time. SLAs may also include references to the maximum allowable downtime and recovery times for a system following media failure or user error. Each of these service level conditions represents areas that can be managed by proper performance tuning.

General Tuning Concepts

You now have an understanding of Oracle's tuning methodology and can appreciate the roles that goal setting and performance measurement play in this model. This section will build on these concepts by explaining how the consideration of tuning trade-offs, common problem areas, simple tuning guidelines, and the tuning lifecycle can enhance the performance tuning process.

Understanding Trade-offs

There is an old saying that states, "Oracle is not really a software company at all; it's merely a front for a consortium of CPU, disk, and memory manufacturers." It probably seems that way to many purchasing managers after approving yet another purchase order for Oracle-related hardware.

However, this saying does demonstrate that, even with the application of a proven tuning methodology that utilizes extensive benchmarks, most tuning efforts involve some degree of compromise. This occurs because every Oracle Server is constrained by the availability of three key resources: CPU, disk (I/O), and memory.


Tuning Oracle's memory and I/O activity will provide little benefit if the server's processor is already overburdened. However, even in high-end, multi-CPU servers, consideration should be given to the impact that changing memory or device configurations will have on CPU utilization. Oracle is also a very "CPU-aware" product. This means that several Oracle Server configuration parameters change dynamically when CPUs are added or removed from the server.

Disk (I/O)

The more Oracle Server activity that occurs in memory, the lower the physical I/O will be. However, placing too many demands on the available memory by oversizing Oracle's memory structures can cause undesirable additional I/O in the form of operating system paging and swapping. Modern disk-caching hardware and software also complicate database I/O tuning, since I/O activity on these systems may result in reads from the disk controller's cache and not directly from disk.


The availability of memory for Oracle's memory structures is key to good performance. Managing that memory is important so that it is used to maximum benefit without wasting any that could be better used by other server processes. Oracle offers several memory tuning options that help you make the most efficient use of the available memory.


Excerpted from OCP: Oracle9i Performance Tuning Study Guide by Joseph C. Johnson Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Read More Show Less

Table of Contents

IntroductionxviiAssessment Test.

Chapter1 Introduction to Performance Tuning.

Chapter2 Sources of Tuning Information.

Chapter3 SQL Application Tuning and Design.

Chapter4 Tuning the Shared Pool.

Chapter5 Tuning the Database Buffer Cache.

Chapter6 Tuning Other SGA Areas.

Chapter7 Tuning Redo Mechanisms.

Chapter8 Tuning Disk I/O.

Chapter9 Tuning Contention.

Chapter10 Operating System Tuning.



Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)