Oracle SQL Tuning Pocket Reference

Overview

One of the most important challenges faced by Oracle database administrators and Oracle developers is the need to tune SQL statements so that they execute efficiently. Poorly tuned SQL statements are one of the leading causes of substandard database performance and poor response time. SQL statements that perform poorly result in frustration for users, and can even prevent a company from serving its customers in a timely manner.

In this book, Mark Gurry shares his in-depth ...

See more details below
Other sellers (Paperback)
  • All (22) from $1.99   
  • New (8) from $4.24   
  • Used (14) from $1.99   
Oracle SQL Tuning Pocket Reference

Available on NOOK devices and apps  
  • NOOK Devices
  • NOOK HD/HD+ Tablet
  • NOOK
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac
  • NOOK Study
  • NOOK for Web

Want a NOOK? Explore Now

NOOK Book (eBook)
$6.99
BN.com price
(Save 12%)$7.99 List Price

Overview

One of the most important challenges faced by Oracle database administrators and Oracle developers is the need to tune SQL statements so that they execute efficiently. Poorly tuned SQL statements are one of the leading causes of substandard database performance and poor response time. SQL statements that perform poorly result in frustration for users, and can even prevent a company from serving its customers in a timely manner.

In this book, Mark Gurry shares his in-depth knowledge of Oracle's SQL statement optimizers. Mark's knowledge is the result of many hard-fought tuning battles during his many years of providing Oracle tuning services to clients. Mark provides insights into the workings of the rule-based optimizer that go well beyond what the rules tell you. Mark also provides solutions to many common problems that occur with both the rule-based and cost-based optimizers.

In addition to the specific problem/solution scenarios for the optimizers, Mark provides a number of handy SQL tuning tips. He discusses the various optimizer hints, telling you when they can be used to good effect. Finally, Mark discusses the use of the DBMS_STATS package to manage database statistics, and the use of outlines to specify execution plans for SQL statements in third-party applications that you can't otherwise modify.

In this book, Mark Gurry shares his in-depth knowledge of Oracle's SQL statement optimizers. Gurry's knowledge is the result of many hard-fought tuning battles during his many years of providing Oracle tuning services to clients. He provides insights into the workings of the rule-based optimizer that go well beyond what the rules say.

Read More Show Less

Product Details

  • ISBN-13: 9780596002688
  • Publisher: O'Reilly Media, Incorporated
  • Publication date: 11/1/2001
  • Edition description: POCKET
  • Edition number: 1
  • Pages: 110
  • Sales rank: 971,108
  • Product dimensions: 4.34 (w) x 7.00 (h) x 0.40 (d)

Meet the Author

As a database administrator on a financial IMS project about eight years ago, Mark Gurry was asked to investigate his company's database direction for the next five years. The number of users was up to 950, the cost of maintaining the mainframes was huge, and he'd heard about relational databases and downsizing. After much investigation, he chose Oracle, and has stuck with the system ever since. He has worked as Manager of Computing and Network Services, senior database administrator, senior Oracle technical support, and other jobs. He now has a small consulting company called New Age Consultants. Mark has worked for many large organizations and is currently working for Telecom Australia, the largest computer site in Australia and one of the largest in the world. He has also spoken on tuning at Oracle user group meetings and has given internal tuning courses at several of his larger client sites. He has been a senior team member on award-winning systems that have been developed using Oracle.

Read More Show Less

Read an Excerpt

The SQL Optimizers

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.

Understanding the Rule-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, and no statistics exist for any table involved in the statement
  • An ALTER SESSION SET OPTIMIZER_MODE = RULE command has been issued
  • An ALTER SESSION SET OPTIMIZER_MODE = CHOOSE command has been issued, and no statistics exist for any table involved in the statement
  • The rule hint (e.g., SELECT /*+ RULE */. . .) has been used in the statement

The rule-based optimizer is driven primarily by 20 condition rankings, or "golden rules." These rules instruct the optimizer how to determine the execution path for a statement, when to choose one index over another, and when to perform a full table scan. These rules, shown in Table 1, are fixed, predetermined, and, in contrast with the cost-based optimizer, not influenced by outside sources (table volumes, index distributions, etc.).

Table 1: Rule-based optimizer condition rankings

Rank

Condition

1

ROWID = constant

2

Cluster join with unique or primary key = constant

3

Hash cluster key with unique or primary key = constant

4

Entire Unique concatenated index = constant

5

Unique indexed column = constant

6

Entire cluster key = corresponding cluster key of another table in the same cluster

7

Hash cluster key = constant

8

Entire cluster key = constant

9

Entire non-UNIQUE CONCATENATED index = constant

10

Non-UNIQUE index merge

11

Entire concatenated index = lower bound

12

Most leading column(s) of concatenated index = constant

13

Indexed column between low value and high value or indexed column LIKE "ABC%" (bounded range)

14

Non-UNIQUE indexed column between low value and high value or indexed column like `ABC%' (bounded range)

15

UNIQUE indexed column or constant (unbounded range)

16

Non-UNIQUE indexed column or constant (unbounded range)

17

Equality on non-indexed = column or constant (sort/merge join)

18

MAX or MIN of single indexed columns

19

ORDER BY entire index

20

Full table scans

While knowing the rules is helpful, they alone do not tell you enough about how to tune for the rule-based optimizer. To overcome this deficiency, the following sections provide some information that the rules don't tell you.

What the RBO rules don't tell you #1

Only single column indexes are ever merged. Consider the following SQL and indexes:


SELECT col1, ...
 
 FROM emp
 
WHERE emp_name = 'GURRY'
 
  AND emp_no   = 127
 
  AND dept_no  = 12
 

Index1 (dept_no)

Index2 (emp_no, emp_name) 

The SELECT statement looks at all three indexed columns. Many people believe that Oracle will merge the two indexes, which involve those three columns, to return the requested data. In fact, only the two-column index is used; the single-column index is not used. While Oracle will merge two single-column indexes, it will not merge a multi-column index with another index.

There is one thing to be aware of with respect to this scenario. If the single-column index is a unique or primary key index, that would cause the single-column index to take precedence over the multi-column index. Compare rank 4 with rank 9 in Table 1.

NOTE: Oracle8i introduced a new hint, INDEX_JOIN, that allows you to join multi-column indexes.

What the RBO rules don't tell you #2

If all columns in an index are specified in the WHERE clause, that index will be used in preference to other indexes for which some columns are referenced. For example:


SELECT col1, ...
 
 FROM emp
 
WHERE emp_name = 'GURRY'
 
  AND emp_no   = 127
 
  AND dept_no  = 12
 

Index1 (emp_name)

Index2 (emp_no, dept_no, cost_center) 

In this example, only Index1 is used, because the WHERE clause includes all columns for that index, but does not include all columns for Index2.

What the RBO rules don't tell you #3

If multiple indexes can be applied to a WHERE clause, and they all have an equal number of columns specified, only the index created last will be used. For example:


SELECT col1, ...
 
 FROM emp
 
WHERE emp_name = 'GURRY'
 
  AND emp_no   = 127
 
  AND dept_no  = 12
 
  AND emp_category = 'CLERK'
 

Index1 (emp_name, emp_category)  Created 4pm Feb 11
th 2002

Index2 (emp_no, dept_no) Created 5pm Feb 11
th 2002

In this example, only Index2 is used, because it was created at 5 p.m. and the other index was created at 4 p.m. This behavior can pose a problem, because if you rebuild indexes in a different order than they were first created, a different index may suddenly be used for your queries. To deal with this problem, many sites have a naming standard requiring that indexes are named in alphabetical order as they are created. Then, if a table is rebuilt, the indexes can be rebuilt in alphabetical order, preserving the correct creation order. You could, for example, number your indexes. Each new index added to a table would then be given the next number.

What the RBO rules don't tell you #4

If multiple columns of an index are being accessed with an = operator, that will override other operators such as LIKE or BETWEEN. Two ='s will override two ='s and a LIKE. For example:


SELECT col1, ...
 
 FROM emp
 
WHERE emp_name  LIKE 'GUR%'
 
  AND emp_no       = 127
 
  AND dept_no      = 12
 
  AND emp_category = 'CLERK'
 
  AND emp_class    = 'C1'
 

Index1 (emp_category, emp_class, emp_name)  

Index2 (emp_no, dept_no) 

In this example, only Index2 is utilized despite Index1 having three columns accessed and Index2 having only two column accessed....

Read More Show Less

Table of Contents

Chapter 1: Oracle SQL TuningPocket Reference;
1.1 Introduction;
1.2 The SQL Optimizers;
1.3 Rule-Based Optimizer Problems and Solutions;
1.4 Cost-Based Optimizer Problems and Solutions;
1.5 Problems Common to Rule and Cost with Solutions;
1.6 Handy SQL Tuning Tips;
1.7 Using SQL Hints;
1.8 Using DBMS_STATS to Manage Statistics;
1.9 Using Outlines for Consistent Execution Plans;

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)