Lessons Learned: Oracle performance tuning 101

By SearchOracle.com experts 06 Apr 2007 | SearchOracle.com

Oracle tips, scripts, and expert advice Digg This!     StumbleUpon     Del.icio.us

Introducing the first installment of SearchOracle.com's Lessons Learned series. Each week you will get a mini-lesson pertaining to a highly specific database administration, application development, data warehousing and business intelligence, or E-Business Suite topic. The lessons are comprised of information already posted on the site in the form of expert responses, tips, articles or white papers written by our panel of gurus. At the end of each month, you will be tested on what you've learned. E-mail us your specific lesson requests today.

Go to the Lessons Learned library for additional lessons and quizzes.

TABLE OF CONTENTS
   How do I measure performance?
   What causes poor database performance?
   What are the technical components of performance tuning?
   What is the current state of my performance?
   How can I solve specific tuning dilemmas?

This week's teachers:

Carol Francum

Paul Baumgartel

Karen Morton (former expert)

How do I measure performance?
[ Return to Table of Contents ]

CPU is not the most important measure in dealing with performance issues. The most important measure is response time. Time is the metric the user understands, time is the measure the manager uses and time is the most effective measure of performance improvement. While there are many important ratios that measure whether a database is working effectively, the goal is for the database to effectively support user transactions. Tuning hit ratios and cache statistics is like patching potholes. Sometimes patching fixes the problem; sometimes repaving is better. That's why a more effective measure of performance tuning results is response time. Response time concentrates on the specific business process that users have identified as problematic.

Excerpted from Carol Francum's "Performance tuning, step 1: Planning a tuning engagement."

What causes poor database performance?
[ Return to Table of Contents ]

The most common causes of Oracle performance problems are applications that cause the database engine to do much more work than is necessary to achieve the desired result; poorly-designed database schemas; and poorly-designed databases. These can manifest themselves in many ways: excessive CPU consumption due to too many logical I/Os (visits to database blocks in memory), excessive disk reads due to missing indexes, or excessive contention for shared resources.

Excerpted from Paul Baumgartel's "Most common cause of performance problems"

What are the technical components of performance tuning?
[ Return to Table of Contents ]

Technical architecture: Server and network tuning
If there is a problem with the Oracle server, such as an overloaded CPU, excessive memory swapping or a disk I/O bottleneck, no amount of tuning within the Oracle database is going to improve your performance.

Application architecture: Integration of middle tier. Apache Server, Forms/Reports Server.
When evaluating the performance of forms and reports, check both requirements and standards under which they were developed. Forms and reports created using standard defaults may benefit from reducing or relaxing the standards for performance reasons. In one example, approximately 20 Web forms were completely redesigned when it was determined extensive use of check constraints impacted performance. For many of the forms, data input was prevalidated; when the constraints were removed, overall performance improved over 100%.

Database architecture:
(a) Instance tuning: Tuning the Oracle SGA is the next step, and all of the Oracle initialization parameters must be reviewed to ensure that the database has been properly configured. This phase of Oracle tuning is directed at looking for resources shortages in the db_block_buffers, shared_pool_size and sort_area_size. Investigate important default parameters for Oracle, such as optimizer_mode.
(b) Object tuning: This 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.

SQL tuning: This 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, tune each one by carefully reviewing the execution plan for the SQL and adjust the execution plan using Oracle hints.

Networks are increasingly more important as we move to global businesses. Work with your telecom provider to evaluate link capacity and utilization. Request detailed reports. Consider resizing to reduce costs on underutilized links or upgrading those close to capacity. Evaluate network drivers. Is there a more recent, downloadable version? Vendors often update their drivers. This can make a significant improvement.

Excerpted from Carol Francum's "Performance tuning, step 3: Working the plan."

What is the current state of my performance?
[ Return to Table of Contents ]

Determining the current state involves both users and the technical architecture of the database. This includes working with knowledgeable users, often called power users, to find out what they see as problems. Ask your power users if they have observed any specific trends, jot down what time problems occur and what they were doing. Take the time to observe the user performing their problematic tasks and record the steps. Using the same steps every time establishes a baseline for comparison. When working with the user, take care to ask questions, but not to jump to conclusions or make promises about how much you will be able to change. You are in the evaluation phase and changes may be technical, training related or both.

Excerpted from Carol Francum's "Performance tuning, step 2: Determining the current state."

How can I solve specific tuning dilemmas?
[ Return to Table of Contents ]

To help answer specific tuning dilemmas, here's a list of 26 questions and solutions pertaining to Oracle performance tuning, broken down by level of difficulty (low, intermediate, high). These questions cover everything from how to get initialization parameters in an instance to multiple extents in tablespaces to row chaining.

Click for Karen Morton's "26 must-know performance tuning solutions."

Go to the Lessons Learned library for additional lessons and quizzes.

Tags: Oracle database performance problems and tuning,  VIEW ALL TAGS

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database performance problems and tuning Oracle tutorial library: SearchOracle.com's learning guides What managers should consider when starting a database scaling project Oracle releases new database, says 11g upgrade will cut costs Oracle raises prices on database management packs Oracle New Year's resolutions, part 1: Advice for navigating 2009 Solving common Oracle errors guide Oracle 11g data compression Varchar or number for better performance? Do statistics on SYS-owned objects hurt performance in 10g? Inside the Oracle 11g SQL Performance Advisor, part 1

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary