- Shopping Bag ( 0 items )
Ships from: Murphy, TX
Usually ships in 1-2 business days
Ships from: Miami, FL
Usually ships in 1-2 business days
This Study Guide provides:
Authoritative coverage of all exam objectives, including:
Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.
ORACLE9i PERFORMANCE TUNING EXAM OBJECTIVES COVERED IN THIS CHAPTER:
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.
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.
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.
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.
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.
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.
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.
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.