没有合适的资源?快使用搜索试试~ 我知道了~
Oracle Database Performance Tuning Guide 11g Release 2 (11.2)-56
需积分: 5 0 下载量 115 浏览量
2023-06-18
15:37:23
上传
评论
收藏 6.18MB PDF 举报
温馨提示
试读
560页
Oracle Database Performance Tuning Guide 11g Release 2 (11.2)-560
资源推荐
资源详情
资源评论
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
剩余559页未读,继续阅读
资源评论
weixin_40191861_zj
- 粉丝: 62
- 资源: 1万+
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功