BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Overview of Oracle Tuning

Oracle Tips by Burleson Consulting

Oracle tuning has always been the most difficult area of Oracle database management. Because of the flexibility of Oracle, the internal software is phenomenally complex and there are many hundreds of causes of Oracle performance problems. The inherent complexity of the Oracle database leaves many shops unable to certify that their database is properly tuned.

In addition to the complex nature of the Oracle software, we also have the issue of the dynamic nature of Oracle applications. An Oracle database is constantly changing, and it is never exactly the same at any two times. Because of the dynamic nature of tuning an Oracle environment, it is very difficult for the Oracle database administrator to get a handle on what's going on inside their database. Many people who've attempted to tune an Oracle database say that it's analogous to attempting to work on a car while it is flying down the highway at 60 miles per hour!

While it may be true that the Oracle databases are in a constant state of flux, there is a general approach that most Oracle experts use when tuning the Oracle database. It's very important to take a top-down approach to tuning Oracle databases, such that you start at a very high level, taking a look at the overall Oracle environment and then successively drill down into more detail as you begin to tune the individual components within the database engine. For Oracle, the top-down approach means starting with the server, drilling down to the instance, drilling down to the objects, and finally examining the Oracle SQL.

The goal of this text is to give you the STATSPACK tools and diagnostic techniques that are required to ensure that your Oracle database is performing at an optimal level. While you may not become an Oracle tuning expert from reading this book, you will have a high-level understanding of the important Oracle tuning issues and know how to run STATSPACK queries to get performance metrics.

The Overall Tuning Approach

While there is no silver bullet for tuning Oracle databases, a comprehensive approach to Oracle tuning can help ensure that all of the bases are covered, and that no important tuning facts have been overlooked. In tuning an Oracle database, you have to start by taking a broad look at the external environment and successively drill down for more details (see Figure 1-1).

Figure 1: The Oracle database tuning hierarchy

The concept of using a drill-down technique is very important to Oracle tuning. We must start at a very broad level, examining the overall environment and looking carefully at the database server for any problems that might exist within CPU, RAM, or disk configurations, as shown in Figure 1-2. No amount of tuning is going to help in an Oracle database when the Oracle database server is short on resources.

Figure 2: Tuning the Oracle environment

Once we've completed the tuning of the Oracle server environment, we can then begin to take a look at the global parameters that affect the Oracle database (the Oracle instance). When looking at the Oracle database, we take a look at the database as a whole, and we pay careful attention to the Oracle initialization parameters that govern the configuration of the SGA and the overall behavior of the database, as shown in Figure 1-3.

Figure 3: Tuning the Oracle instance

Once the database server in the Oracle instance has been tuned, we can then begin the work of taking a look at individual Oracle tables and indexes within the database. At this phase, we take a look at the storage settings that can govern the behavior of a table and take a look at how well the settings accommodate the processing needs of the individual objects (see Figure 1-4).

Figure 4: Tuning Oracle objects

Once the Oracle objects are tuned, we then move into tuning the individual SQL queries that are issued against the Oracle database. This is one of the most challenging of all of the areas of Oracle tuning because there can be many thousands of SQL statements issued against a highly active Oracle database. The task for the person tuning the Oracle SQL is to identify those SQL statements that are used most frequently and apply the tools necessary in order to tune each statement for the optimal execution plan (see Figure 1-5). We will also explore static binding and show how to improve execution by keeping bind plans for the SQL.

Figure 5: Tuning Oracle SQL

In summary, Oracle tuning involves the following steps, with each step being more general and broad than the step beneath it:

1.      Server, network, and disk tuningIf there is a problem with the Oracle server, such as an overloaded CPU, excessive memory swapping, or disk I/O bottleneck, then no amount of tuning within the Oracle databases is going to improve your performance. Hence, the first thing the Oracle professional examines is the server, disk, and network environment.

2.      Instance tuningThe Oracle SGA is tuned, and all of the Oracle initialization parameters are reviewed to ensure that the database has been properly configured. This phase of Oracle tuning is directed at looking for resource shortages in the db_cache_size, shared_pool_size, and pga_aggregate_target. We also investigate important default parameters for Oracle such as optimizer_mode.

3.      Object tuningThis phase of tuning looks at the setting for Oracle tables and indexes. Settings such as pctfree, pctused, and freelists can have a dramatic impact on Oracle performance, and each object can benefit from proper storage settings.

4.      SQL tuningThis is the most time-consuming tuning operation because there can be many thousands of individual SQL statements that access the Oracle database. At a high level, we identify the most common SQL statements and tune each statement by carefully reviewing the execution plan for the SQL and adjusting the execution plan using Oracle hints. We will also be investigating the new optimizer plan stability feature. Optimizer plan stability allows improved performance by storing a ready-to-go execution plan for SQL statements. We will also see how to implement optimizer plan stability with the OUTLINE package, so that we can modify execution plans for specific SQL statements. This is especially useful in cases where you are using vendor-supplied SQL and you cannot change the SQL source code. For example, in Oracle Applications and SAP, you are not allowed to change the SQL, but with optimizer plan stability you can tune the SQL by changing the stored execution plan for the SQL.

5.      Design tuningThe design of the application is the single most important factor in Oracle performance. Unfortunately, most Oracle administrators are unable to change a poor design, either because they are using proprietary software or because the design is already implemented in production.

It is critical to the success of your Oracle tuning effort to follow the tuning steps in their proper order. Many neophyte Oracle Remote DBAs will immediately begin to tune SQL statements without considering the environment in which the SQL is running. Mistakes like these can often cause problems with the overall tuning effort because the broader tuning issues have not yet been identified and corrected.

While later chapters will explore each of these areas in great detail, let's begin by covering the major areas so that we can understand their impact on Oracle performance.
 

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.
 


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter