Learning Guide: Performance tuning

Performance tuning is a never-ending task for the DBA, and this learning guide can help newbies and experts establish a smart approach to tuning, use and interpret Oracle's tuning utilities and identify specific problem areas.

[TABLE]

[TABLE]

No matter what version of Oracle you are using, the basics of performance tuning remain the same. Problems can stem from a variety of causes, and as the DBA, your job is to figure out what they are. For example, poorly designed applications and database schemas can cause issues like excessive CPU consumption due to too many logical I/Os, excessive disk reads due to missing indexes or excessive contention for shared resources.

Fighting the performance war needs to be done quickly and decisively. But you might not be able to rely on performance tools to help you. Find out if and how you can calculate database efficiency. Download the first chapter of Optimizing Oracle performance for an overview of some of the root causes of performance issues, problems with commonly used tuning techniques, requirements of a good method, advances in performance improvements, tools for analyzing response time and more. In this expert response Brian Peasland answers a handful of performance tuning questions. You might also want to familiarize yourself with the target values for performance statistics.

Although preeminent performance tuning guru Don Burleson stresses that there is no shortcut to successful Oracle tuning, and the tuner must intimately understand the complex interactions of the Oracle subsystems and be aware of some common tuning fallacies. Mike Ault further debunks common Oracle tuning myths.

And remember, it's possible to devote too much time to tuning.

[TABLE]

Tuning a database is a time-intensive, repetitive task. Over time, the database changes in many ways and must be reevaluated. Read up on some first steps involv

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database performance problems and tuning 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 Inside the Oracle 11g SQL Performance Advisor, part 2 Difference between driving table and driver table in Oracle Best design for E-Business Suite on hard drive Using the cost-based optimizer to improve Database 10g performance Oracle database design and architecture Can I download DBCA for Oracle Express Edition? How to recreate an Oracle index in a new schema with the CREATE command Using Oracle Universal Installer to install Oracle with Pro*C Defining Oracle database repository vs. information repository Can I create multiple schemas in Oracle for one user? ORA-12514 error when connecting to the Oracle database through Toad Solving the ORA-00904 error: invalid identifier in Oracle How to tune SQL UPDATE statements for an Oracle 10g upgrade Will queries run slower in a smaller Oracle buffer cache? Using a database link to connect two Oracle apps instances Oracle and SQL Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to use the Oracle Database SQL Reference Manual How to use SQL Developer to run SQL statements How to work with the Oracle database home page How to use SQL*Plus in Oracle How to use SQL Developer to work with an Oracle database How to view and edit table column definitions How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join Oracle and SQL Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary E. F. Codd  (SearchOracle.com) extent  (SearchOracle.com) flexfield  (SearchOracle.com) foreign key  (SearchOracle.com) multidimensional database  (SearchOracle.com) object-oriented database management system  (SearchOracle.com) quad tree  (SearchOracle.com) relational online analytical processing  (SearchOracle.com) row  (SearchOracle.com) splay tree  (SearchOracle.com)

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

ed in determining database performance. Know that upgrades may cause performance issues. In addition, changes made to improve performance will need to be evaluated. This series identifies a standard approach for performing or evaluating database tuning efforts. It is applicable to data warehouses, custom databases, custom database applications or Oracle Applications database.

You'll also want to understand performance forecasting. Craig Shallahamer discusses the essentials of performance forecasting in a two-part column.

Using Oracle's tuning utilities:

[TABLE]

Performance tuning means optimizing specific, individual aspects of your system's hardware and software, but here are a few general ideas to keep in mind:

[TABLE]

Optimizing disk I/O is a critical part of performance tuning. Mike Ault's book Oracle disk I/O tuning covers topics that include disk performance, RAID management, Oracle data file performance and Oracle data segment internals. Background information includes general disk architecture, disk layout, disk performance statistics and disk capacity.

Specific information on tuning includes:

[TABLE]

SQL tuning is the process of ensuring that the SQL statements that an application issues will run as fast as possible. Just like there may be ten different ways for you to drive from work to your house, there may be ten different ways to execute a query. Tuning SQL and PL/SQL begins with an understanding of how Oracle processes SQL and PL/SQL. You'll need to know how to use SQL trace files. You also have to understand a bit about Oracle's optimizer and how it chooses an execution path/plan for your SQL statement. Get started with five quick tuning tips and scripts.

This script checks all active processes, the latest SQL, and the SQL hit ratio. Once you identify slow running SQL, you can use SQL*Plus autotrace to examine the execution plan. Here is an example of two load profiles showing how reducing consistent gets reduces the overall workload.

Kimberly Floss' book Oracle SQL and index internals describes advanced Oracle SQL internals and Oracle indexing management. These chapter excerpts offer an introduction to the step-by-step process of SQL tuning:

Don Burleson provides some basic rules for writing efficient queries and lists a few undocumented SQL hints that can be extremely useful for solving complex SQL execution problems. In addition, learn about using SQL Access Advisor to tune SQL.

[TABLE]

System performance problems may not be caused by DBMS problems at all. These resources provide an overview of tuning Oracle applications and Application Server.

[TABLE]

Prepare for a job interview or just quiz yourself on your performance tuning knowledge. Here's a list of 26 questions you should be able to answer by now.