[PDF] Oracle Database Performance Tuning Guide

View - Download Oracle Database Performance Tuning Guide

Previous PDF

Next PDF

Oracle® Database

Performance Tuning Guide

11g Release 2 (11.2)

E41573-04

June 2014

Oracle Database Performance Tuning Guide, 11g Release 2 (11.2)

E41573-04

Copyright © 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Primary Authors: Immanuel Chan, Lance Ashdown

Contributors: Aditya Agrawal, Hermann Baer, Vladimir Barriere, Mehul Bastawala, Eric Belden, Pete

Belknap, Supiti Buranawatanachoke, Sunil Chakkappen, Maria Colgan, Benoit Dageville, Dinesh Das, Karl

Dias, Kurt Engeleiter, Marcus Fallen, Mike Feng, Leonidas Galanis, Ray Glasstone, Prabhaker Gongloor,

Kiran Goyal, Cecilia Grant, Connie Dialeris Green, Shivani Gupta, Karl Haas, Bill Hodak, Andrew

Holdsworth, Hakan Jacobsson, Shantanu Joshi, Ameet Kini, Sergey Koltakov, Vivekanada Kolla, Paul Lane,

Sue K. Lee, Herve Lejeune, Ilya Listvinsky, Bryn Llewellyn, George Lumpkin, Mughees Minhas, Gary Ngai,

Mark Ramacher, Yair Sarig, Uri Shaft, Vishwanath Sreeraman, Vinay Srihari, Randy Urbano, Amir Valiani,

Venkateshwaran Venkataramani, Yujun Wang, Graham Wood, Khaled Yagoub, Mohamed Zait, Mohamed

Ziauddin

This software and related documentation are provided under a license agreement containing restrictions on

use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your

license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,

transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse

engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is

prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If

you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it

on behalf of the U.S. Government, the following notice is applicable: U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and

adaptation of the programs, including any operating system, integrated software, any programs installed on

the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to

the programs. No other rights are granted to the U.S. Government. This software or hardware is developed for general use in a variety of information management

applications. It is not developed or intended for use in any inherently dangerous applications, including

applications that may create a risk of personal injury. If you use this software or hardware in dangerous

applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other

measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages

caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of

their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks

are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products,

and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly

disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle

Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your

access to or use of third-party content, products, or services. iii

Contents

Preface............................................................................................................................................................... xv

Audience..................................................................................................................................................... xv

Documentation Accessibility................................................................................................................... xv

Related Documents................................................................................................................................... xv

Conventions ............................................................................................................................................... xvi

What's New in Oracle Database Performance Tuning Guide?...................................... xvii

Oracle Database 11g Release 2 (11.2.0.4) New Features in Oracle Database Performance............ xvii

Oracle Database 11g Release 2 (11.2.0.2) New Features in Oracle Database Performance............ xvii

Oracle Database 11g Release 2 (11.2.0.1) New Features in Oracle Database Performance........... xviii

Part I Performance Tuning

1 Performance Tuning Overview

Introduction to Performance Tuning.................................................................................................... 1-1

Performance Planning....................................................................................................................... 1-1

Instance Tuning.................................................................................................................................. 1-1

SQL Tuning......................................................................................................................................... 1-4

Introduction to Performance Tuning Features and Tools................................................................ 1-4

Automatic Performance Tuning Features ...................................................................................... 1-5

Additional Oracle Database Tools................................................................................................... 1-6

Part II Performance Planning

2 Designing and Developing for Performance

Oracle Methodology................................................................................................................................ 2-1

Understanding Investment Options..................................................................................................... 2-1

Understanding Scalability...................................................................................................................... 2-2

What is Scalability?............................................................................................................................ 2-2

System Scalability............................................................................................................................... 2-3

Factors Preventing Scalability.......................................................................................................... 2-4

System Architecture................................................................................................................................. 2-5

Hardware and Software Components............................................................................................ 2-5

Configuring the Right System Architecture for Your Requirements......................................... 2-7

iv

Application Design Principles.............................................................................................................. 2-9

Simplicity In Application Design.................................................................................................. 2-10

Data Modeling................................................................................................................................. 2-10

Table and Index Design.................................................................................................................. 2-10

Using Views..................................................................................................................................... 2-12

SQL Execution Efficiency............................................................................................................... 2-13

Implementing the Application ..................................................................................................... 2-14

Trends in Application Development............................................................................................ 2-16

Workload Testing, Modeling, and Implementation...................................................................... 2-16

Sizing Data ....................................................................................................................................... 2-17

Estimating Workloads.................................................................................................................... 2-17

Application Modeling .................................................................................................................... 2-18

Testing, Debugging, and Validating a Design............................................................................ 2-18

Deploying New Applications............................................................................................................. 2-19

Rollout Strategies ............................................................................................................................ 2-19

Performance Checklist.................................................................................................................... 2-20

3 Performance Improvement Methods

The Oracle Performance Improvement Method................................................................................ 3-1

Steps in The Oracle Performance Improvement Method............................................................. 3-2

A Sample Decision Process for Performance Conceptual Modeling.......................................... 3-3

Top Ten Mistakes Found in Oracle Systems.................................................................................. 3-4

Emergency Performance Methods........................................................................................................ 3-6

Steps in the Emergency Performance Method............................................................................... 3-6

Part III Optimizing Instance Performance

4 Configuring a Database for Performance

Performance Considerations for Initial Instance Configuration.................................................... 4-1

Initialization Parameters................................................................................................................... 4-1

Configuring Undo Space................................................................................................................... 4-3

Sizing Redo Log Files ........................................................................................................................ 4-3

Creating Subsequent Tablespaces.................................................................................................... 4-4

Creating and Maintaining Tables for Optimal Performance.......................................................... 4-5

Table Compression ............................................................................................................................ 4-5

Reclaiming Unused Space................................................................................................................. 4-6

Indexing Data ..................................................................................................................................... 4-7

Performance Considerations for Shared Servers............................................................................... 4-7

Identifying Contention Using the Dispatcher-Specific Views .................................................... 4-8

Identifying Contention for Shared Servers..................................................................................... 4-9

5 Automatic Performance Statistics

Overview of Data Gathering.................................................................................................................. 5-1

Database Statistics.............................................................................................................................. 5-2

Operating System Statistics .............................................................................................................. 5-4

Interpreting Statistics......................................................................................................................... 5-7

v

Overview of the Automatic Workload Repository............................................................................ 5-8

Snapshots............................................................................................................................................. 5-9

Baselines .............................................................................................................................................. 5-9

Adaptive Thresholds...................................................................................................................... 5-10

Space Consumption........................................................................................................................ 5-12

Managing the Automatic Workload Repository............................................................................. 5-12

Managing Snapshots....................................................................................................................... 5-13

Managing Baselines........................................................................................................................ 5-14

Managing Baseline Templates....................................................................................................... 5-17

Transporting Automatic Workload Repository Data................................................................ 5-19

Using Automatic Workload Repository Views.......................................................................... 5-21

Generating Automatic Workload Repository Reports.............................................................. 5-22

Generating Automatic Workload Repository Compare Periods Reports .............................. 5-28

Generating Active Session History Reports................................................................................ 5-34

Using Active Session History Reports......................................................................................... 5-38

6 Automatic Performance Diagnostics

Overview of the Automatic Database Diagnostic Monitor............................................................. 6-1

ADDM Analysis................................................................................................................................. 6-2

Using ADDM with Oracle Real Application Clusters.................................................................. 6-3

ADDM Analysis Results ................................................................................................................... 6-4

Reviewing ADDM Analysis Results: Example.............................................................................. 6-5

Setting Up ADDM................................................................................................................................... 6-5

Diagnosing Database Performance Problems with ADDM............................................................ 6-6

Running ADDM in Database Mode................................................................................................ 6-7

Running ADDM in Instance Mode.................................................................................................. 6-7

Running ADDM in Partial Mode..................................................................................................... 6-8

Displaying an ADDM Report........................................................................................................... 6-8

Views with ADDM Information........................................................................................................... 6-9

7 Configuring and Using Memory

Understanding Memory Allocation Issues......................................................................................... 7-1

Oracle Memory Caches ..................................................................................................................... 7-2

Automatic Memory Management ................................................................................................... 7-2

Automatic Shared Memory Management...................................................................................... 7-2

Dynamically Changing Cache Sizes................................................................................................ 7-3

Application Considerations.............................................................................................................. 7-5

Operating System Memory Use....................................................................................................... 7-5

Iteration During Configuration........................................................................................................ 7-6

Configuring and Using the Buffer Cache............................................................................................ 7-6

Using the Buffer Cache Effectively.................................................................................................. 7-7

Sizing the Buffer Cache..................................................................................................................... 7-7

Interpreting and Using the Buffer Cache Advisory Statistics .................................................. 7-10

Considering Multiple Buffer Pools............................................................................................... 7-11

Buffer Pool Data in V$DB_CACHE_ADVICE............................................................................ 7-13

Buffer Pool Hit Ratios..................................................................................................................... 7-13

vi

Determining Which Segments Have Many Buffers in the Pool............................................... 7-13

KEEP Pool......................................................................................................................................... 7-15

RECYCLE Pool ................................................................................................................................ 7-15

Configuring and Using the Shared Pool and Large Pool.............................................................. 7-16

Shared Pool Concepts..................................................................................................................... 7-17

Using the Shared Pool Effectively ................................................................................................ 7-19

Sizing the Shared Pool.................................................................................................................... 7-22

Interpreting Shared Pool Statistics ............................................................................................... 7-27

Using the Large Pool ...................................................................................................................... 7-28

Using CURSOR_SPACE_FOR_TIME........................................................................................... 7-31

Caching Session Cursors................................................................................................................ 7-31

Configuring the Reserved Pool..................................................................................................... 7-33

Keeping Large Objects to Prevent Aging .................................................................................... 7-35

Sharing Cursors for Existing Applications.................................................................................. 7-36

Maintaining Connections............................................................................................................... 7-38

Configuring and Using the Redo Log Buffer.................................................................................. 7-38

Sizing the Log Buffer...................................................................................................................... 7-39

Log Buffer Statistics........................................................................................................................ 7-39

PGA Memory Management................................................................................................................ 7-39

Configuring Automatic PGA Memory ........................................................................................ 7-41

Configuring OLAP_PAGE_POOL_SIZE..................................................................................... 7-53

Managing the Server and Client Result Caches.............................................................................. 7-53

Managing the Server Result Cache............................................................................................... 7-54

Managing the Client Result Cache ............................................................................................... 7-57

Specifying Queries for Result Caching ........................................................................................ 7-59

Requirements for the Result Cache .............................................................................................. 7-62

Accessing Result Cache Information............................................................................................ 7-63

8 I/O Configuration and Design

About I/O................................................................................................................................................... 8-1

I/O Configuration..................................................................................................................................... 8-2

Lay Out the Files Using Operating System or Hardware Striping............................................. 8-2

Manually Distributing I/O............................................................................................................... 8-5

When to Separate Files...................................................................................................................... 8-5

Three Sample Configurations........................................................................................................... 8-7

Oracle Managed Files........................................................................................................................ 8-8

Choosing Data Block Size ................................................................................................................. 8-9

I/O Calibration Inside the Database.................................................................................................. 8-10

Prerequisites for I/O Calibration.................................................................................................. 8-10

Running I/O Calibration ............................................................................................................... 8-11

I/O Calibration with the Oracle Orion Calibration Tool.............................................................. 8-12

Introduction to the Oracle Orion Calibration Tool .................................................................... 8-12

Getting Started with Orion............................................................................................................ 8-14

Orion Input Files............................................................................................................................. 8-15

Orion Parameters ............................................................................................................................ 8-15

Orion Output Files.......................................................................................................................... 8-20

Orion Troubleshooting................................................................................................................... 8-23

vii

9 Managing Operating System Resources

Understanding Operating System Performance Issues.................................................................... 9-1

quotesdbs_dbs6.pdfusesText_12

[PDF] advanced oracle sql tuning the definitive reference pdf free download

[PDF] advanced oracle sql tutorial

[PDF] advanced php book pdf

[PDF] advanced piano chords pdf

[PDF] advanced piano chords. pdf

[PDF] advanced placement french: preparing for the language examination

[PDF] advanced powerpoint 2010 tutorial pdf

[PDF] advanced powerpoint 2010 tutorial pdf free download

[PDF] advanced powerpoint 2013 tutorial pdf

[PDF] advanced powerpoint animation

[PDF] advanced powerpoint animation free download

[PDF] advanced powerpoint charts

[PDF] advanced powerpoint exercises

[PDF] advanced powerpoint presentation examples

[PDF] advanced powerpoint presentation free download