Oracle 9i. Database Performance Guide and Reference

760 Pages·2001·4.061 MB·English

by  Суran M.

Oracle9i
Database Performance Guide and Reference
Release 1 (9.0.1)
June 2001
Part No. A87503-02
Oracle9i Database Performance Guide and Reference, Release 1 (9.0.1)
Part No. A87503-02
Copyright © 2001, Oracle Corporation. All rights reserved.
Primary Author: Michele Cyran
Contributing Author: Connie Dialeris Green
Contributors: RafiAhmed, Ahmed Alomari, Hermann Baer, Ruth Baylis, Leo Cloutier, Maria Colgan,
Dinish Das, Lex de Haan, Harv Eneman, Bjorn Engsig, Cecilia Gervasio, Leslie Gloyd, Karl Haas,
Andrew Holdsworth, Mamdouh Ibrahim, Namit Jain, Hakan Jakobsson, Sanjay Kaluskar, Srinivas
Kareenhalli, Peter Kilpatrick, Sushil Kumar, Tirthankar Lahiri, Yunrui Li, Diana Lorentz, Roderick
Manalac, Alan Maxwell, Joe McDonald, Ari Mozes, Gary Ngai, Arvind Nithrakashyap, Peter Povinec,
Richard Powell, Shankar Raman, Virag Saksena, Slartibartfast, Vinay Srihari, Frank Stephens, Mike
Stewart,MineharuTakahara,PatrickTearle,NitinVengurlekar,JeanFrancoisVerrier,SteveVivian,Simon
Watt, Sabrina Whitehouse, Graham Wood, Mohamed Ziauddin
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Oracle 8i, Oracle 9i, Oracle Enterprise Manager, Oracle9i Real
ApplicationClusters,SQL,SQL*Loader,SQL*Plus,andPL/SQLaretrademarksorregisteredtrademarks
of Oracle Corporation. Other names may be trademarks of their respective owners.
Contents
Send Us Your Comments................................................................................................................. xix
Preface.......................................................................................................................................................... xxi
Audience.............................................................................................................................................. xxii
Organization........................................................................................................................................ xxii
Related Documentation.................................................................................................................... xxvi
Conventions....................................................................................................................................... xxvii
Documentation Accessibility........................................................................................................... xxix
What’s New in Oracle Performance?...................................................................................... xxxi
Part I Writing and Tuning SQL
1 Introduction to the Optimizer
Overview of SQL Processing Architecture.................................................................................... 1-2
Overview of the Optimizer............................................................................................................... 1-3
Steps in Optimizer Operations................................................................................................... 1-4
Understanding Execution Plans................................................................................................. 1-5
Choosing an Optimizer Approach and Goal............................................................................... 1-10
How the CBO Optimizes SQL Statements for Fast Response.............................................. 1-13
Features that Require the CBO................................................................................................. 1-14
Understanding the Cost-Based Optimizer (CBO)...................................................................... 1-15
Architecture of the CBO............................................................................................................ 1-16
Understanding Access Paths for the CBO.................................................................................... 1-23
iii
Full Table Scans........................................................................................................................... 1-24
Sample Table Scans..................................................................................................................... 1-27
ROWID Scans.............................................................................................................................. 1-28
Index Scans.................................................................................................................................. 1-29
Cluster Scans............................................................................................................................... 1-39
Hash Scans................................................................................................................................... 1-39
How the CBO Chooses an Access Path................................................................................... 1-39
Understanding Joins......................................................................................................................... 1-42
How the CBO Executes Join Statements.................................................................................. 1-42
How the CBO Chooses the Join Method................................................................................. 1-43
Nested Loop Joins....................................................................................................................... 1-44
Nested Loop Outer Joins........................................................................................................... 1-48
Hash Joins.................................................................................................................................... 1-49
Hash Join Outer Joins................................................................................................................. 1-51
Sort Merge Joins.......................................................................................................................... 1-53
Sort Merge Outer Joins............................................................................................................... 1-57
Cartesian Joins............................................................................................................................. 1-57
Full Outer Joins........................................................................................................................... 1-62
How the CBO Chooses Execution Plans for Joins.................................................................. 1-63
How the CBO Executes Anti-Joins........................................................................................... 1-64
How the CBO Executes Semi-Joins.......................................................................................... 1-64
How the CBO Executes Star Queries....................................................................................... 1-65
Cost-Based Optimizer Parameters................................................................................................. 1-66
OPTIMIZER_FEATURES_ENABLE Parameter..................................................................... 1-66
Other CBO Parameters............................................................................................................... 1-68
Overview of the Extensible Optimizer......................................................................................... 1-72
Understanding User-Defined Statistics................................................................................... 1-72
Understanding User-Defined Selectivity................................................................................ 1-73
Understanding User-Defined Costs......................................................................................... 1-73
2 Optimizer Operations
How the Optimizer Performs Operations...................................................................................... 2-2
How the CBO Evaluates IN-List Iterators................................................................................. 2-2
How the CBO Evaluates Concatenation.................................................................................... 2-6
How the CBO Evaluates Remote Operations........................................................................... 2-9
iv
How the CBO Executes Distributed Statements.................................................................... 2-12
How the CBO Executes Sort Operations................................................................................. 2-13
How the CBO Executes Views.................................................................................................. 2-16
How the CBO Evaluates Constants......................................................................................... 2-18
How the CBO Evaluates the UNION/UNION ALL Operators.......................................... 2-19
How the CBO Evaluates the LIKE Operator.......................................................................... 2-20
How the CBO Evaluates the IN Operator............................................................................... 2-21
How the CBO Evaluates the ANY or SOME Operator......................................................... 2-21
How the CBO Evaluates the ALL Operator........................................................................... 2-22
How the CBO Evaluates the BETWEEN Operator................................................................ 2-22
How the CBO Evaluates the NOT Operator........................................................................... 2-23
How the CBO Evaluates Transitivity...................................................................................... 2-23
How the CBO Optimizes Common Subexpressions............................................................. 2-24
How the CBO Evaluates DETERMINISTIC Functions......................................................... 2-26
How the Optimizer Transforms SQL Statements....................................................................... 2-27
How the CBO Transforms ORs into Compound Queries.................................................... 2-28
How the CBO Unnests Subqueries.......................................................................................... 2-31
How the CBO Merges Views.................................................................................................... 2-33
How the CBO Pushes Predicates.............................................................................................. 2-36
How the CBO Executes Compound Queries.......................................................................... 2-46
3 Gathering Optimizer Statistics
Understanding Statistics................................................................................................................... 3-2
Generating Statistics.......................................................................................................................... 3-3
Using the DBMS_STATS Package.............................................................................................. 3-5
Using the ANALYZE Statement............................................................................................... 3-12
Finding Data Distribution......................................................................................................... 3-13
Missing Statistics......................................................................................................................... 3-13
Using Statistics.................................................................................................................................. 3-14
Managing Statistics..................................................................................................................... 3-14
Verifying Table Statistics........................................................................................................... 3-16
Verifying Index Statistics........................................................................................................... 3-17
Verifying Column Statistics...................................................................................................... 3-18
Using Histograms............................................................................................................................. 3-20
When to Use Histograms........................................................................................................... 3-21
v
Creating Histograms.................................................................................................................. 3-21
Types of Histograms.................................................................................................................. 3-22
Viewing Histograms................................................................................................................... 3-24
Verifying Histogram Statistics.................................................................................................. 3-24
4 Understanding Indexes and Clusters
Understanding Indexes...................................................................................................................... 4-2
Tuning the Logical Structure....................................................................................................... 4-2
Choosing Columns and Expressions to Index.......................................................................... 4-3
Choosing Composite Indexes..................................................................................................... 4-4
Writing Statements that Use Indexes......................................................................................... 4-6
Writing Statements that Avoid Using Indexes......................................................................... 4-6
Re-creating Indexes...................................................................................................................... 4-6
Compacting Indexes..................................................................................................................... 4-7
Using Nonunique Indexes to Enforce Uniqueness.................................................................. 4-8
Using Enabled Novalidated Constraints................................................................................... 4-8
Using Function-based Indexes......................................................................................................... 4-9
Using Index-Organized Tables....................................................................................................... 4-11
Using Bitmap Indexes...................................................................................................................... 4-11
When to Use Bitmap Indexes.................................................................................................... 4-12
Using Bitmap Indexes with Good Performance..................................................................... 4-14
Initialization Parameters for Bitmap Indexing....................................................................... 4-16
Using Bitmap Access Plans on Regular B-tree Indexes......................................................... 4-17
Bitmap Index Restrictions.......................................................................................................... 4-18
Using Bitmap Join Indexes.............................................................................................................. 4-18
Using Domain Indexes..................................................................................................................... 4-18
Using Clusters.................................................................................................................................... 4-19
Using Hash Clusters......................................................................................................................... 4-20
5 Optimizer Hints
Understanding Optimizer Hints...................................................................................................... 5-2
Specifying Hints............................................................................................................................ 5-2
Using Optimizer Hints....................................................................................................................... 5-6
Hints for Optimization Approaches and Goals....................................................................... 5-6
Hints for Access Paths.................................................................................................................. 5-9
vi
Hints for Query Transformations............................................................................................. 5-17
Hints for Join Orders.................................................................................................................. 5-21
Hints for Join Operations.......................................................................................................... 5-23
Hints for Parallel Execution...................................................................................................... 5-28
Additional Hints......................................................................................................................... 5-33
Using Hints with Views............................................................................................................. 5-38
6 Optimizing SQL Statements
Goals for Tuning................................................................................................................................. 6-2
Reduce the Workload................................................................................................................... 6-2
Balance the Workload.................................................................................................................. 6-2
Parallelize the Workload............................................................................................................. 6-2
Identifying and Gathering Data on Resource-Intensive SQL................................................... 6-3
Identifying Resource-Intensive SQL.......................................................................................... 6-3
Gathering Data on the SQL Identified....................................................................................... 6-4
Overview of SQL Statement Tuning............................................................................................... 6-5
Verifying Optimizer Statistics..................................................................................................... 6-6
Reviewing the Execution Plan.................................................................................................... 6-7
Restructuring the SQL Statements............................................................................................. 6-7
Controlling the Access Path and Join Order with Hints....................................................... 6-16
Restructuring the Indexes......................................................................................................... 6-20
Modifying or Disabling Triggers and Constraints................................................................ 6-20
Restructuring the Data............................................................................................................... 6-20
Maintaining Execution Plans Over Time................................................................................ 6-21
Visiting Data as Few Times as Possible................................................................................... 6-21
7 Using Plan Stability
Using Plan Stability to Preserve Execution Plans........................................................................ 7-2
Using Hints with Plan Stability.................................................................................................. 7-2
Storing Outlines............................................................................................................................ 7-4
Enabling Plan Stability................................................................................................................. 7-4
Using Supplied Packages to Manage Stored Outlines............................................................ 7-4
Creating Outlines.......................................................................................................................... 7-4
Using and Editing Stored Outlines............................................................................................ 7-6
Viewing Outline Data.................................................................................................................. 7-9
vii
Moving Outline Tables................................................................................................................ 7-9
Using Plan Stability with the Cost-Based Optimizer................................................................ 7-11
Using Outlines to Move to the Cost-Based Optimizer.......................................................... 7-11
Upgrading and the Cost-Based Optimizer............................................................................. 7-12
8 Using the Rule-Based Optimizer
Overview of the Rule-Based Optimizer (RBO)............................................................................. 8-2
Understanding Access Paths for the RBO...................................................................................... 8-2
Choosing Execution Plans for Joins with the RBO................................................................ 8-15
Transforming and Optimizing Statements with the RBO........................................................ 8-17
Transforming ORs into Compound Queries with the RBO................................................. 8-17
Using Alternative SQL Syntax.................................................................................................. 8-20
Part II SQL-Related Performance Tools
9 Using EXPLAIN PLAN
Understanding EXPLAIN PLAN...................................................................................................... 9-2
How Execution Plans Can Change............................................................................................ 9-2
Looking Beyond Execution Plans............................................................................................... 9-3
Creating the PLAN_TABLE Output Table..................................................................................... 9-4
Running EXPLAIN PLAN................................................................................................................. 9-4
Identifying Statements for EXPLAIN PLAN............................................................................ 9-4
Specifying Different Tables for EXPLAIN PLAN.................................................................... 9-5
Displaying PLAN_TABLE Output.................................................................................................. 9-5
Reading EXPLAIN PLAN Output.................................................................................................... 9-6
Viewing Bitmap Indexes with EXPLAIN PLAN......................................................................... 9-10
Viewing Partitioned Objects with EXPLAIN PLAN.................................................................. 9-11
Examples of Displaying Range and Hash Partitioning with EXPLAIN PLAN................ 9-11
Examples of Pruning Information with Composite Partitioned Objects............................ 9-13
Examples of Partial Partition-wise Joins................................................................................. 9-16
Examples of Full Partition-wise Joins...................................................................................... 9-17
Examples of INLIST ITERATOR and EXPLAIN PLAN....................................................... 9-18
Example of Domain Indexes and EXPLAIN PLAN.............................................................. 9-19
EXPLAIN PLAN Restrictions......................................................................................................... 9-20
viii
PLAN_TABLE Columns.................................................................................................................. 9-20
10 Using SQL Trace and TKPROF
Understanding SQL Trace and TKPROF..................................................................................... 10-2
Understanding the SQL Trace Facility.................................................................................... 10-2
Understanding TKPROF........................................................................................................... 10-3
Using the SQL Trace Facility and TKPROF................................................................................. 10-3
Step 1: Setting Initialization Parameters for Trace File Management................................. 10-4
Step 2: Enabling the SQL Trace Facility................................................................................... 10-5
Step 3: Formatting Trace Files with TKPROF......................................................................... 10-6
Step 4: Interpreting TKPROF Output.................................................................................... 10-11
Step 5: Storing SQL Trace Facility Statistics.......................................................................... 10-16
Avoiding Pitfalls in TKPROF Interpretation............................................................................ 10-19
Avoiding the Argument Trap................................................................................................. 10-19
Avoiding the Read Consistency Trap.................................................................................... 10-19
Avoiding the Schema Trap...................................................................................................... 10-20
Avoiding the Time Trap.......................................................................................................... 10-21
Avoiding the Trigger Trap...................................................................................................... 10-22
Sample TKPROF Output............................................................................................................... 10-22
Sample TKPROF Header......................................................................................................... 10-23
Sample TKPROF Body............................................................................................................. 10-23
Sample TKPROF Summary..................................................................................................... 10-29
11 Using Autotrace in SQL*Plus
Controlling the Autotrace Report.................................................................................................. 11-2
Execution Plan............................................................................................................................. 11-2
Statistics........................................................................................................................................ 11-3
Tracing Parallel and Distributed Queries.................................................................................... 11-6
Monitoring Disk Reads and Buffer Gets..................................................................................... 11-8
12 Using Oracle Trace
Overview of Oracle Trace................................................................................................................ 12-2
Event Data................................................................................................................................... 12-2
Event Sets..................................................................................................................................... 12-2
ix
Accessing Collected Data........................................................................................................... 12-3
Collecting Oracle Trace Data.......................................................................................................... 12-3
Using the Oracle Trace Command-Line Interface................................................................. 12-3
Using Initialization Parameters to Control Oracle Trace...................................................... 12-7
Controlling Oracle Trace Collections from PL/SQL........................................................... 12-10
Accessing Oracle Trace Collection Results................................................................................ 12-12
Formatting Oracle Trace Data to Oracle Tables................................................................... 12-13
Running the Oracle Trace Reporting Utility......................................................................... 12-14
Oracle Server Events....................................................................................................................... 12-15
Data Items Collected for Events............................................................................................. 12-16
Items Associated with Each Event......................................................................................... 12-22
Troubleshooting Oracle Trace....................................................................................................... 12-32
Oracle Trace Configuration..................................................................................................... 12-32
Formatter Tables....................................................................................................................... 12-36
Part III Creating a Database for Good Performance
13 Building a Database for Performance
Initial Database Creation................................................................................................................. 13-2
Database Creation using the Installer...................................................................................... 13-2
Manual Database Creation........................................................................................................ 13-2
Parameters Necessary for Initial Database Creation............................................................. 13-2
The CREATE DATABASE Statement...................................................................................... 13-3
Running Data Dictionary Scripts.............................................................................................. 13-4
Sizing Redo Log Files................................................................................................................. 13-5
Creating Subsequent Tablespaces............................................................................................ 13-6
Creating Tables for Good Performance........................................................................................ 13-6
Loading and Indexing Data............................................................................................................ 13-8
Using SQL*Loader for Good Performance............................................................................. 13-8
Efficient Index Creation............................................................................................................. 13-8
Initial Instance Configuration........................................................................................................ 13-9
Configuring Rollback Segments............................................................................................. 13-11
Setting up OS, Database, and Network Monitoring................................................................ 13-12
x