❞ كتاب Oracle® Database Performance Tuning Guide ❝

كتاب يتحدث عن اللغة PL/SQL بشرح مبسط وبالتفصيل .
iii
Contents
Preface
...............................................................................................................................
................................
xv
Audience.......................................................................................................................
..............................
xv
Documentation Accessibility ....................................................................................................
...............
xv
Related Documents ..............................................................................................................
.....................
xv
Conventions ....................................................................................................................
...........................
xvi
What's New in Oracle Data
base Performance Tuning Guide?
......................................
xvii
Oracle Database 11
g
Release 2 (11.2.0.4) New Features in Or
acle Database Performance............
xvii
Oracle Database 11
g
Release 2 (11.2.0.2) New Features in Or
acle Database Performance............
xvii
Oracle Database 11
g
Release 2 (11.2.0.1) New Features in Or
acle 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 Concep
tual 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 Data
base 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 Perfor
mance 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 Automati
c 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 Comp
are 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 Hardwa
re 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 ........

-
من كتب أوراكل كتب قواعد البيانات - مكتبة كتب تقنية المعلومات.