Cover | Table of Contents

Table of Contents

Chapter 1: Oracle SQL TuningPocket Reference

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This book is a quick-reference guide for tuning Oracle SQL. This is not a comprehensive Oracle tuning book.

The purpose of this book is to give you some light reading material on my "real world" tuning experiences and those of my company, Mark Gurry & Associates. We tune many large Oracle sites. Many of those sites, such as banks, large financial institutions, stock exchanges, and electricity markets, are incredibly sensitive to poor performance.

With more and more emphasis being placed on 24/7 operation, the pressure to make SQL perform in production becomes even more critical. When a new SQL statement is introduced, we have to be absolutely sure that it is going to perform. When a new index is added, we have to be certain that it will not be used inappropriately by existing SQL statements. This book addresses these issues.

Many sites are now utilizing third-party packages such as Peoplesoft, SAP, Oracle Applications, Siebel, Keystone, and others. Tuning SQL for these applications must be done without placing hints on SQL statements, because you are unauthorized to touch the application code. Obviously, for similar reasons, you can't rewrite the SQL. But don't lose heart; there are many tips and tricks in this reference that will assist you when tuning packaged software.

This book portrays the message, and my firm belief, that there is always a way of improving your performance to make it acceptable to your users.

Many thanks to my editor, Jonathan Gennick. His feedback and suggestions have added significant improvements and clarity to this book. A hearty thanks to my team of technical reviewers: Sanjay Mishra, Stephen Andert, and Tim Gorman.Thanks also to my Mark Gurry & Associates consultants for their technical feedback. Special thanks to my wife Juliana for tolerating me during yet another book writing exercise.

This book does not cover every type of environment, nor does it cover all performance tuning scenarios that you will encounter as an Oracle DBA or developer.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Introduction

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This book is a quick-reference guide for tuning Oracle SQL. This is not a comprehensive Oracle tuning book.

The purpose of this book is to give you some light reading material on my "real world" tuning experiences and those of my company, Mark Gurry & Associates. We tune many large Oracle sites. Many of those sites, such as banks, large financial institutions, stock exchanges, and electricity markets, are incredibly sensitive to poor performance.

With more and more emphasis being placed on 24/7 operation, the pressure to make SQL perform in production becomes even more critical. When a new SQL statement is introduced, we have to be absolutely sure that it is going to perform. When a new index is added, we have to be certain that it will not be used inappropriately by existing SQL statements. This book addresses these issues.

Many sites are now utilizing third-party packages such as Peoplesoft, SAP, Oracle Applications, Siebel, Keystone, and others. Tuning SQL for these applications must be done without placing hints on SQL statements, because you are unauthorized to touch the application code. Obviously, for similar reasons, you can't rewrite the SQL. But don't lose heart; there are many tips and tricks in this reference that will assist you when tuning packaged software.

This book portrays the message, and my firm belief, that there is always a way of improving your performance to make it acceptable to your users.

Many thanks to my editor, Jonathan Gennick. His feedback and suggestions have added significant improvements and clarity to this book. A hearty thanks to my team of technical reviewers: Sanjay Mishra, Stephen Andert, and Tim Gorman.Thanks also to my Mark Gurry & Associates consultants for their technical feedback. Special thanks to my wife Juliana for tolerating me during yet another book writing exercise.

This book does not cover every type of environment, nor does it cover all performance tuning scenarios that you will encounter as an Oracle DBA or developer.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

The SQL Optimizers

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Whenever you execute a SQL statement, a component of the database known as the optimizer must decide how best to access the data operated on by that statement. Oracle supports two optimizers: the rule-base optimizer (which was the original), and the cost-based optimizer.

To figure out the optimal execution path for a statement, the optimizers consider the following:

• The syntax you've specified for the statement
• Any conditions that the data must satisfy (the WHERE clauses)
• The database tables your statement will need to access
• All possible indexes that can be used in retrieving data from the table
• The Oracle RDBMS version
• The current optimizer mode
• SQL statement hints
• All available object statistics (generated via the ANALYZE command)
• The physical table location (distributed SQL)
• INIT.ORA settings (parallel query, async I/O, etc.)

Oracle gives you a choice of two optimizing alternatives: the predictable rule-based optimizer and the more intelligent cost-based optimizer.

The rule-based optimizer (RBO) uses a predefined set of precedence rules to figure out which path it will use to access the database. The RDBMS kernel defaults to the rule-based optimizer under a number of conditions, including:

• OPTIMIZER_MODE = RULE is specified in your INIT.ORA file
• OPTIMIZER_MODE = CHOOSE is specified in your INIT.ORA file, andno statistics exist for any table involved in the statement
• An ALTER SESSION SET OPTIMIZER_MODE = RULE command has been issued

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Rule-Based Optimizer Problems and Solutions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The rule-based optimizer provides a good deal of scope for tuning. Because its behavior is predictable, and governed by the 20 condition rankings presented earlier in Table 1-1, we are easily able to manipulate its choices.

I have been tracking the types of problems that occur with both optimizers as well as the best way of fixing the problems. The major causes of poor rule-based optimizer performance are shown in Table 1-2.

Table 1-2: Common rule-based optimizer problems

Problem	% Cases
1. Incorrect driving table	40%
2. Incorrect index	40%
3. Incorrect driving index	10%
4. Using the ORDER BY index and not the WHERE index	10%

Each problem, along with its solution, is explained in detail in the following sections.

If the table driving a join is not optimal, there can be a significant increase in the amount of time required to execute a query. Earlier, in Section 1.2.1.6, I discussed what decides the driving table. Consider the following example, which illustrates the potential difference in runtimes:

SELECT COUNT(*)
  FROM acct a, trans b
WHERE b.cost_center = 'MASS'
  AND a.acct_name = 'MGA'
  AND a.acct_name = b.acct_name;

In this example, if ACCT_NAME represents a unique key index and COST_CENTER represents a single column non-unique index, the unique key index would make the ACCT table the driving table.

If both COST_CENTER and ACCT_NAME were single column, non-unique indexes, the rule-based optimizer would select the TRANS table as the driving table, because it is listed last in the FROM clause. Having the TRANS table as the driving table would likely mean a longer response time for a query, because there is usually only one ACCT row for a selected account name but there are likely to be many transactions for a given cost center.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Cost-Based Optimizer Problems and Solutions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The cost-based optimizer has been significantly improved from its initial inception. My recommendation is that every site that is new to Oracle should be using the cost-based optimizer. I also recommend that sites currently using the rule-based optimizer have a plan in place for migrating to the cost-based optimizer. There are, however, some issues with the cost-based optimizer that you should be aware of. Table 1-3 lists the most common problems I have observed, along with their frequency of occurrence.

Table 1-3: Common cost-based optimizer problems

Problem	% Cases
1. The skewness problem	30%
2. Analyzing with wrong data	25%
3. Mixing the optimizers in joins	20%
4. Choosing an inferior index	20%
5. Joining too many tables	< 5%
6. Incorrect INIT.ORA parameter settings	< 5%

Imagine that we are consulting at a site with a table TRANS that has a column called STATUS. The column has two possible values: `O' for Open Transactions that have not been posted, and `C' for closed transactions that have already been posted and that require no further action. There are over one million rows that have a status of `C', but only 100 rows that have a status of `O' at any point in time.

The site has the following SQL statement that runs many hundreds of times daily. The response time is dismal, and we have been called in to "make it go faster."

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Problems Common to Rule and Cost with Solutions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section lists problems that are common to both the rule-based and cost-based optimizers. It is important that you are aware of these problems and avoid them wherever possible. Table 1-4 lists the problems and their occurrence rates.

Table 1-4: Common problems with both optimizers

Problems for both Rule and Cost	Occurrence %
1. Statement not written for indexes	25%
2. Indexes are missing or inappropriate	16%
3. Use of single-column index merge	15%
4. Misuse of nested loop, sort merge, or hash join	12%
5. Misuse of IN, EXISTS, NOT IN, NOT EXISTS, or table joins	8%
6. Unnecessary Sorts	4%
7. Too many indexes on a table	4%
8. Use of OR instead of UNION	3%
9. Tables and indexes with many deletes	3%
10. Other	10%

Some SELECT statement WHERE clauses do not use indexes at all. Most such problems are caused by having a function on an indexed column. Oracle8

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Handy SQL Tuning Tips

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The following sections list some SQL tuning tips that you may find useful both when writing SQL statements and when troubleshooting performance problems.

The SQL statements in this section demonstrate how to identify SQL statements that have an expected response time of more than 10 seconds. The assumption has been made that 300 disk I/Os can be performed per second, and that 4,000 buffer gets can be performed per second. These times are typical of a medium- to high-end machine.

Use the following SQL*Plus commands to identify statements using, on average, more than 3,000 disk reads (10 seconds' worth) per execution:

column "Response" format 999,999,999.99;
column nl newline;

ttitle 'SQL With Disk Reads > 10 Seconds'

SELECT sql_text nl, 'Executions='|| 
            executions  nl,
  'Expected Response Time in Seconds= ', 
   disk_reads / decode(executions, 0, 1, 
                    executions) / 300   
              "Response"  
  FROM v$sql
WHERE  disk_reads / decode(executions,0,1, executions) 
                   / 300 > 10
  AND executions > 0
ORDER BY hash_value, child_number;

Similarly, the following SQL*Plus commands identify statements that result in more than 40,000 buffer gets:

column "Response" format 999,999,999.99
ttitle 'SQL Buffer Scan > 10 Seconds'   

SELECT sql_text nl, 'Executions='|| 
    executions  nl,
   'Expected Response Time in Seconds= ', 
   buffer_gets / 
     decode(executions, 0, 1, executions) 
     / 4000 "Response"
  FROM v$sql
 WHERE  buffer_gets / 
      decode(executions, 0,1, executions) 
                   /   4000 > 10
 AND executions > 0
ORDER BY hash_value, child_number;

Once you've identified poorly performing SQL statements, you can work to tune them.

Oracle8i and later has a great feature that stores information on long-running queries currently active in the V$SESSION_LONGOPS view.

The following example shows the results of a query against V$SESSION_LONGOPS:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using SQL Hints

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Hints can be placed into your SQL statements to force the optimizers to utilize a particular execution path for absolute best performance. The following sections describe available hints up through Oracle9i.

By including your own optimization hints as "comments" within the SQL statement, you force the statement to follow your desired retrieval path, rather than the one calculated by the optimizer. In the following example, including /*+ RULE */ inside the SELECT statement instructs the optimizer to use the rule-based optimizer rather than the cost-based optimizer:

SELECT    /*+ RULE */  . . . .
  FROM    emp, dept
  WHERE   . . .

The optimizer hint(s) can be included only immediately after the initial SQL "action" verb and most are ignored when included in INSERT statements:

SELECT  /*+ hint text */  . . . .
DELETE  /*+ hint text */  . . . .
UPDATE  /*+ hint text */  . . . .

Each hint is operational only within the statement block for which it appears. A statement block is one of the following:

• A simple SELECT, DELETE, or UPDATE statement
• The parent portion of a complex statement
• The subquery portion of a complex statement
• Part of a compound query

Consider these examples:

SELECT  /*+ RULE */  . . . .
FROM    emp
WHERE   emp_status = 'PART-TIME'
AND     EXISTS     
    ( SELECT  /*+ FIRST_ROWS */  'x'
       FROM  emp_history
      WHERE   emp_no = E.emp_no
        AND  emp_status 
            != 'PART-TIME' )

SELECT  /*+ RULE */  . . . .
FROM    emp
WHERE   emp_status = 'PART-TIME'
UNION
SELECT  /*+ ALL_ROWS */  . . . .
FROM    emp_history
WHERE   emp_status != 'PART-TIME'

The optimizer cannot distinguish a bad hint from a programmer comment. An incorrectly structured or misspelled hint will be ignored rather than reported as an error.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using DBMS_STATS to Manage Statistics

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

DBMS_STATS was introduced in Oracle8i; it provides critical functionality for the cost-based optimizer, including speeding the analyze process, allowing statistics to be modified, reverting back to previous statistics, and copying statistics from one schema (or database) to another.

DBMS_STATS offers two powerful ways of speeding up the analyze process. First, you can analyze tables (not indexes) in parallel. Second, you can analyze only tables and their associated indexes that have had more than 10% of their rows modified through INSERT, UPDATE, or DELETE operations.

To analyze a schema's tables in parallel, use a command such as the following:

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>
'HROA', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE);

This command estimates statistics for the schema HROA. The DEGREE value specifies the degree of parallelism to use. CASCADE=>TRUE causes the indexes for each table to be analyzed as well. DBMS_STATS has a GATHER STALE option that will only analyze tables that have had more than 10% of their rows changed. To use it, you first need to turn on monitoring for your selected tables. For example:

ALTER TABLE WINNERS MONITORING; 

You can observe information about the number of table changes for a given table by selecting from the USER_TAB_MODIFICATIONS view. You can see if monitoring is turned on for a particular table by selecting the MONITORING column from USER_TABLES.

With monitoring enabled, you can run the GATHER_SCHEMA_STATS package using the GATHER STALE option:

EXECUTE SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>
'HROA', ESTIMATE_PERCENT=>10, DEGREE=>4, CASCADE=>TRUE, 
OPTIONS=>'GATHER STALE');
            

Because GATHER_STALE is specified, tables will only be analyzed if they have had 10% or more of their rows changed since the previous analyze.

DBMS_STATS gives you the ability to copy statistics from one schema to another, or from one database to another, using the following procedure:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using Outlines for Consistent Execution Plans

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle introduced outlines in Oracle8i to allow you to have a pre-defined execution plan for a SQL statement. Consistency can then be provided without changing the actual SQL. Outlines can be used for packaged software to provide execution plan stability without the need to change the application's SQL.

An outline is nothing more than a stored execution plan that Oracle uses rather than computing a new plan based on current table statistics. Before you can use outlines, you must record some. You can record outlines for a single statement, for all statements issued by a single session, or for all statements issued to an instance.

Section 1.9.1.1: Recording an outline for a SQL statement

You can record the outlines for a particular statement using the CREATE OR REPLACE OUTLINE command as follows:

CREATE OR REPLACE OUTLINE aug0901
FOR CATEGORY harness_racing
ON select * 
     from winners
    where owner > 'G%';

In this example, aug0901 is the name of the specific outline being created, and harness_racing is the name of a category, or group, or related outlines.

Section 1.9.1.2: Recording outlines for all of a session's SQL

You can turn on the recording of outlines for a session by using the ALTER SESSION CREATE_STORED_OUTLINES command.

Specify a category name to save outlines into a specific category:

   ALTER SESSION 
   SET CREATE_STORED_OUTLINES
       =GENERAL_LEDGER;

Specify TRUE to record outlines in the default category, which is named DEFAULT:

ALTER SESSION 
   SET CREATE_STORED_OUTLINES=TRUE;

Specify FALSE to disable the recording of stored outlines:

ALTER SESSION 
   SET CREATE_STORED_OUTLINES=FALSE;

Section 1.9.1.3: Recording outlines for the whole system

The syntax to turn on recording of outlines system-wide is very similar to the ALTER SESSION syntax. Simply use ALTER SYSTEM instead of ALTER SESSION. For example:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Return to Oracle SQL Tuning Pocket Reference