Oracle Database 11g Release 2 Performance Tuning Tips & Techniques | Oracle Press

Home >
Browse >
Oracle Press >
Oracle Database 11g Release 2 Performance Tuning Tips & Techniques

Oracle Database 11g Release 2 Performance Tuning Tips & Techniques

Authors: Richard Niemiec

Published: February 2012

eISBN: 9780071780278 0071780270 | ISBN: 9780071780261

Oracle Database 11g Release 2 Performance Tuning Tips & Techniques

About the Author

About the Technical Reviewers

Contents

Acknowledgments

Introduction

TUSC

How This Book Is Organized and Changes in This Version

References

Chapter 1: Introduction to 11g R1 & R2 New Features (DBA and Developer)

Exadata Is What’s Next!

Advanced Compression

Automatic Diagnostic Repository

Automatic Shared Memory Management (ASMM) Improvements

Automatic Storage Management (ASM) Improvements

ASM Preferred Mirror Read

Rolling Upgrades/Patching

Faster Rebalance

ASM Diskgroup Compatibility

ASMCMD Extensions

Automatic SQL Tuning

Data Guard Enhancements

Snapshot Standby

Active Data Guard

Mixed Platform Support

Advanced Compression for Logical Standby (11gR2)

Transparent Data Encryption Support for Logical Standby

Data Pump Compression Enhancements

Data Pump Encrypted Dump File Sets

Data Pump Legacy Mode

Enhanced Statistics

Enhanced I/O Statistics

Reducing the Gathering of Partitioned Objects Statistics

Pending Statistics

Multicolumn Statistics

Expression Statistics

Flashback Data Archive

Health Monitor

Incident Packaging Service (IPS)

Invisible Indexes

New Partitioning Features

Interval Partitioning

REF Partitioning

System Partitioning

Virtual Column-based Partitioning

Partition Advisor

Read-Only Tables

Real Application Clusters (RAC) One Node and RAC Patching

Real Application Testing

Database Replay

SQL Performance Analyzer (SPA)

Result Cache

RMAN New Features

Optimized Backups

Improved Handling of Long-term Backups

Parallel Backup of Very Large Datafiles

Faster Backup Compression (Improved Compression)

Active Database Duplication

Better Recovery Catalog Management

Archived Log Deletion Policy Enhancements

Data Recovery Advisor

Virtual Private Catalog

Proactive Health Check

Block Recovery (Flashback Logs)

Block Recovery (Physical Standby)

SecureFiles

Compression

Encryption

Deduplication

Enhancements to Streams (Golden Gate Is the Future of Streams)

XStream In

XStream Out

Advanced Compression Support for Streams (11gR2)

Shrink Temporary Tablespace

Transparent Data Encryption (TDE) Improvements

New Background Processes in 11g

Version Comparison Chart

New Features Review

References

Chapter 2: Basic Index Principles (Beginner Developer and Beginner DBA)

Basic Index Concepts

Invisible Indexes

Concatenated Indexes

Suppressing Indexes

Using the NOT EQUAL Operators: <>, !=

Using IS NULL or IS NOT NULL

Using LIKE

Using Functions

Comparing Mismatched Data Types

Selectivity

The Clustering Factor

The Binary Height

Additional Details Concerning BLEVEL and Index Height

Effects on Index from Update Operations

Effects on Index from DELETE Operations

Effects on Index of UPDATE and DELETE Operations

Effects on Blocksize

Using Histograms

Fast Full Scans

Skip-Scans

Types of Indexes

B-Tree Indexes

Bitmap Indexes

Bitmap Index Example

Hash Indexes

Index-Organized Tables

Reverse Key Indexes

Function-Based Indexes

Partitioned Indexes

Local (Commonly Used Indexes)

Global

Bitmap Join Indexes

Fast Index Rebuilding

Rebuilding Indexes Online

Tips Review

References

Chapter 3: Disk Implementation Methodology and ASM (DBA)

Disk Arrays: Not a Choice Anymore

Use Disk Arrays to Improve Performance and Availability

How Many Disks Do You Need?

What Are Some of the RAID Levels Available?

The Newer RAID 5

Setup and Maintenance of the Traditional Filesystem

What Is the Cost?

Distributing “Key” Datafiles Across Hardware Disks

Storing Data and Index Files in Separate Locations

Avoiding I/O Disk Contention

Moving Datafiles to Balance File I/O

Locally Managed Tablespaces

Creating Tablespaces as Locally Managed

Migrating Dictionary-Managed Tablespaces to Locally Managed

Oracle Bigfile Tablespaces

Oracle Managed Files

ASM Introduction

Communication Across IT Roles

ASM Instances

ASM Initialization Parameters

ASM Installation

ASM Parameters and SGA Sizing

ASM and Privileges

ASM Disks

ASM and Multipathing

ASM Diskgroups

Important Performance Notes

ASM Diskgroups and Databases

ASM Redundancy and Failure Groups

New Space-Related Columns

Cluster Synchronization Services

Database Instances and ASM

Database Consolidation and Clustering with ASM

Database Processes to Support ASM

Bigfile and ASM

Database Initialization Parameters to Support ASM

ASM and Database Deployment Best Practices

ASM Storage Management and Allocation

ASM Rebalance and Redistribution

Avoiding Disk Contention by Using Partitions

Getting More Information About Partitions

Other Types of Partitioning

Multicolumn Range Partitioning

Hash Partitioning

Composite Partitioning

List Partitioning

New Partitioning Options in Oracle 11gR2

Interval Partitioning

Reference Partitioning

Partitionwise Joins

System Partitions

Other Partitioning Options

Index Partitioning

Exporting Partitions

Eliminating Fragmentation

Using the Correct Extent Size

Create a New Tablespace and Move the Segments to It

Exporting and Then Reimporting the Table

To Avoid Chaining, Set PCTFREE Correctly

Automatic Segment Space Management

Rebuilding the Database

Increasing the Log File Size and LOG_CHECKPOINT_INTERVAL for Speed

Determining If Redo Log File Size Is a Problem

Determining the Size of Your Log Files and Checkpoint Interval

Other Helpful Redo Log Commands

Additional Instance Parameters

Fast Recovery Area

Increasing Chances of Recovery: Committing After Each Batch

Isolating Large Transactions to Their Own Rollback Segments

Using the UNDO Tablespace

Monitoring UNDO Space

Killing Problem Sessions

Have Multiple Control Files on Different Disks and Controllers

Other Disk I/O Precautions and Tips

Issues to Consider in the Planning Stages

Tips Review

References

Chapter 4: Tuning the Database with Initialization Parameters (DBA)

When Upgrading to Oracle 11gR2

Using SEC_CASE_SENSITIVE_LOGON (new in 11g)

Identifying Crucial Initialization Parameters

Changing the Initialization Parameters Without a Restart

Insight into the Initialization Parameters from Oracle Utilities

Viewing the Initialization Parameters with Enterprise Manager

Increasing Performance by Tuning the DB_CACHE_SIZE

Using V$DB_CACHE_ADVICE in Tuning DB_CACHE_SIZE

Keeping the Hit Ratio for the Data Cache Above 95 Percent

Monitoring the V$SQLAREA View to Find Bad Queries

Bad Hit Ratios Can Occur When an Index Is Suppressed

Getting Good Hit Ratios with Well-Indexed Queries

Bad Queries Executing a Second Time Can Result in Good Hit Ratios

Other Hit Ratio Distortions

Setting DB_BLOCK_SIZE to Reflect the Size of Your Data Reads

Setting SGA_MAX_SIZE to 25 to 50 Percent of the Size Allocated to Main Memory

Tuning the SHARED_POOL_SIZE for Optimal Performance

Using Stored Procedures for Optimal Use of the Shared SQL Area

Setting the SHARED_POOL_SIZE High Enough to Fully Use the DB_CACHE_SIZE

Keeping the Data Dictionary Cache Hit Ratio at or above 95 Percent

Using Individual Row Cache Parameters to Diagnose Shared Pool Use

Keeping the Library Cache Reload Ratio at 0 and the Hit Ratio Above 95 Percent

Using Individual Library Cache Parameters to Diagnose Shared Pool Use

Keeping the Pin Hit Ratio for Library Cache Items Close to 100 Percent

Keeping the Miss Ratio Less Than 15 Percent

Using Available Memory to Determine If the SHARED_POOL_SIZE Is Set Correctly

Using the X$KSMSP Table to Get a Detailed Look at the Shared Pool

Points to Remember About Cache Size

Waits Related to Initialization Parameters

Using Oracle Multiple Buffer Pools

Pools Related to DB_CACHE_SIZE and Allocating Memory for Data

Modifying the LRU Algorithm

Pools Related to SHARED_POOL_SIZE and Allocating Memory for Statements

Tuning the PGA_AGGREGATE_TARGET for Optimal Use of Memory

Modifying the Size of Your SGA to Avoid Paging and Swapping

Understanding the Oracle Optimizer

How Optimization Looks at the Data

Creating Enough Dispatchers

Have Enough Open Cursors (OPEN_CURSORS)

Don’t Let Your DDL Statements Fail (DDL Lock Timeout)

Two Important Exadata Initialization Parameters (EXADATA ONLY)

25 Important Initialization Parameters to Consider

Top 25 Initialization Parameters

Initialization Parameters over the Years

Finding Undocumented Initialization Parameters

Understanding the Typical Server

Modeling a Typical Server

Sizing the Oracle Applications Database

Tips Review

References

Chapter 5: Enterprise Manager and Grid Control (DBA and Developer)

The Enterprise Manager (EM) Basics

Starting with All Targets and Other Groupings

SQL Performance Analyzer (SPA)

Automatic Database Diagnostic Monitor (ADDM)

Database Instance Server Tab and Database Administration Tab

Database Instance Server Tab: Tablespaces

Database Instance Server Tab: Instance Level Focus

Database Instance Server Tab: All Initialization Parameters

Database Instance Server Tab: Manage Optimizer Statistics

Database Instance Server Tab: Resource Manager (Consumer Groups)

Database Maintenance Tab

Database Topology Tab

Database Performance Tab

Monitoring the Hosts

Monitoring the Application Servers

Monitoring the Web Applications

SQL Advisors

Deployments Tab (Patching Options)

Scheduler Central and the Jobs Tab

Reports Tab

Automatic Storage Management Performance

Real Application Testing (Database Replay)

EM for Exadata

Summary

Tips Review

References

Chapter 6: Using EXPLAIN and SQL PLAN MANAGMENT (Developer and DBA)

The Oracle SQL TRACE Utility

Simple Steps for SQL TRACE with a Simple Query

The Sections of a TRACE Output

The SQL Statement

The Statistics Section

Information Section

The Row Source Operation Section

The EXPLAIN PLAN (Execution Plan)

Digging into the TKPROF Output

Using DBMS_MONITOR

TRCSESS Multiple Trace Files into One File

Using EXPLAIN PLAN Alone

EXPLAIN PLAN—Read It Top to Bottom or Bottom to Top?

Reading the EXPLAIN PLAN

Setting AUTOTRACE On

EXPLAIN PLAN When Using Partitions

Finding High Disk and/or Memory Reads Without Using TRACE

Yet Another EXPLAIN PLAN Output Method: Building the Tree Structure

Another Example Using the Tree Approach

Tracing/Explaining Problem Queries in Developer Products

Important Columns in the PLAN_TABLE Table

Initialization Parameters for Undocumented TRACE

Tracing Errors Within Oracle for More Information

Tracing by Enabling Events

Using Stored Outlines

Using SQL Plan Management (SPM) (11g New Feature)

Converting from Stored Outlines to SQL Plan Management

Tips Review

References

Chapter 7: Basic Hint Syntax (Developer and DBA)

Top Hints Used

Use Hints Sparingly

Fix the Design First

Available Hints and Groupings

Execution Path

Access Methods

Query Transformation Hints

Join Operations

Parallel Execution

Other Hints

Specifying a Hint

Specifying Multiple Hints

When Using an Alias, Hint the Alias, Not the Table

The Hints

The Oracle Demo Sample HR Schema

The FIRST_ROWS Hint

The ALL_ROWS Hint

The FULL Hint

The INDEX Hint

The NO_INDEX Hint

The INDEX_JOIN Hint

The INDEX_COMBINE Hint

The INDEX_ASC Hint

The INDEX_DESC Hint

The INDEX_FFS Hint

The ORDERED Hint

The LEADING Hint

The NO_EXPAND Hint

The DRIVING_SITE Hint

The USE_MERGE Hint

The USE_NL Hint

The USE_HASH Hint

The QB_NAME Hint

The PUSH_SUBQ Hint

The PARALLEL Hint

The NO_PARALLEL Hint

The PARALLEL_INDEX Hint

The PQ_DISTRIBUTE Hint

The APPEND Hint

The NOAPPEND Hint

The CACHE Hint

The NOCACHE Hint

The RESULT_CACHE Hint

The CURSOR_SHARING_EXACT Hint

Some Miscellaneous Hints and Notes

Undocumented Hints

Using Hints with Views

Notes on Hints and Stored Outlines (or SQL Plan Baselines in 11g)

Why Isn’t My Hint Working?

Hints at a Glance

Tips Review

References

Chapter 8: Query Tuning: Developer and Beginner DBA

What Queries Do I Tune? Querying V$SQLAREA and V$SQL Views

Selecting from the V$SQLAREA View to Find the Worst Queries

Selecting from the V$SQL View to Find the Worst Queries

Oracle 11g Views for Locating Resource-Intensive Sessions and Queries

Selecting from V$SESSMETRIC to Find Current Resource-Intensive Sessions

Viewing Available AWR Snapshots

Selecting from the DBA_HIST_SQLSTAT View to Find the Worst Queries

Selecting Query Text from the DBA_HIST_SQLTEXT View

Selecting Query EXPLAIN PLAN from the DBA_HIST_SQL_PLAN View

When Should I Use an Index?

What If I Forget the Index?

Creating an Index

Invisible Index

Checking the Index on a Table

Is the Column Properly Indexed?

What If I Create a Bad Index?

Exercising Caution When Dropping an Index

Indexing the Columns Used in the SELECT and WHERE

Using the Fast Full Scan

Making the Query “Magically” Faster

Caching a Table in Memory

Using the New 11g Result Cache

Choosing Among Multiple Indexes (Use the Most Selective)

The Index Merge

Indexes That Can Get Suppressed

Function-Based Indexes

Virtual Columns

The “Curious” OR

Using the EXISTS Function and the Nested Subquery

That Table Is Actually a View!

SQL and Grand Unified Theory

Tuning Changes in Oracle Database 11g

Oracle 11g Automatic SQL Tuning

Ensuring the Tuning User Has Access to the API

Creating the Tuning Task

Making Sure the Task Can Be Seen in the Advisor Log

Executing the SQL Tuning Task

Checking Status of the Tuning Task

Displaying the SQL Tuning Advisor Report

Reviewing the Report Output

Tuning SQL Statements Automatically Using SQL Tuning Advisor

Enabling Automatic SQL Tuning Advisor

Configuring Automatic SQL Tuning Advisor

Viewing Automatic SQL Tuning Results

Using SQL Performance Analyzer (SPA)

Tips Review

References

Chapter 9: Table Joins and Other Advanced Tuning (Advanced DBA and Developer)

Real Application Testing

Database Replay

Set Up Source Database for Database Replay Capture

Prepare to Capture Workload

Capture the Workload

Prepare the Workload for Replay

Process the Workload for Replay

Prepare to Replay the Workload

Execute the Workload Replay

SQL Performance Analyzer

Create a SQL Tuning Set

Create an Analysis Task

Execute Analysis Task

Query Performance Analyzer Advisor Tasks

Cancel an Executing SQL Performance Analyzer Analysis Task

Remove SQL Performance Analyzer Analysis Task

Determine Active SQL Tuning Sets

Remove SQL Tuning Set

Remove SQL Tuning Set Reference

Drop SQL Tuning Set

Join Methods

Nested Loops Joins

SORT-MERGE Joins

CLUSTER Joins

HASH Joins

INDEX-MERGE Joins

Table Join Initialization Parameters

SORT-MERGE and HASH Join Parameters

A Two-Table Join: Equal-Sized Tables (Cost-Based)

A Two-Table INDEXED Join: Equal-Sized Tables (Cost-Based)

Forcing a Specific Join Method

Eliminating Join Records (Candidate Rows) in Multitable Joins

A Two-Table Join Between a Large and Small Table

Three-Table Joins: Not as Much Fun

Bitmap Join Indexes

Bitmap Indexes

Bitmap Index Caveats

Bitmap Join Index

Best Uses for the Bitmap Join Index

Bitmap Join Indexes on Columns Other Than the Join

Bitmap Join Indexes on Multiple Columns

Bitmap Join Indexes on Multiple Tables

Bitmap Join Index Caveats

Another Nice Use for the Bitmap Join Index

Third-Party Product Tuning

Example 1

Example 2

Example 3

Tuning Distributed Queries

When You Have Everything Tuned

Miscellaneous Tuning Snippets

External Tables

Snapshot Too Old: Developer Coding Issue

Set Event to Dump Every Wait

14 Hours to 30 Seconds with the EXISTS Operator

Tuning at the Block Level (Advanced)

Key Sections of a Block Dump

The Block ITL Section in a Block Dump

The Flag Section

The Block Dump Data Section

A Brief Look at an Index Block Dump

Tuning Using Simple Mathematical Techniques

Traditional Mathematical Analysis

Seven-Step Methodology

Deriving Performance Equations

Simple Linear Equation Determination

Simple Quadratic Equation Determination

Pattern Interpretation

General Linear and Quadratic Interpretations

Indexing

Optimizer Execution Plan

Multiple Table Joins

Jackknifing

Riding the Quadratic Curve

Volatility Effects

Mathematical Techniques Conclusions

Join Tuning: Relational vs. Object-Relational Performance

Models Used

Results

Three-Table Join (CUSTOMER, INVOICE, and DETAIL)

Conclusion

Tips Review

References

Chapter 10: Using PL/SQL to Enhance Performance (Developer and DBA)

Leverage the PL/SQL Function Result Cache to Improve Performance (New in 11g)

Reference Sequences Directly in PL/SQL Expressions (New in 11g)

Use Named Parameters in SQL Function Calls (New in 11g)

Simplify loops with the CONTINUE Statement (New in 11g)

Leverage Compile-Time Warnings to Catch Programming Mistakes (Improved in 11g)

Using Table Triggers (Improved in 11g)

Increase Performance with Native Compilation (Improved in 11g)

Maximize Performance with the Optimizing Compiler (Improved in 11g)

Use DBMS_APPLICATION_INFO for Real-Time Monitoring

Log Timing Information in a Database Table

Reduce PL/SQL Program Unit Iterations and Iteration Time

Use ROWID for Iterative Processing

Standardize on Data Types, IF Statement Order, and PLS_INTEGER

Ensure the Same Data Types in Comparison Operations

Order IF Conditions Based on the Frequency of the Condition

Use the PLS_INTEGER PL/SQL Data Type for Integer Operations

Reduce the Calls to SYSDATE

Reduce the Use of the MOD Function

Improve Shared Pool Use by Pinning PL/SQL Objects

Pinning (Caching) PL/SQL Object Statements into Memory

Pinning All Packages

Identify PL/SQL Objects That Need to Be Pinned

Use and Modify DBMS_SHARED_POOL.SIZES

Find Large Objects

Get Detailed Object Information from DBA_OBJECT_SIZE

Get Contiguous Space Currently in the Shared Pool

Find Invalid Objects

Find Disabled Triggers

Use PL/SQL Associative Arrays for Fast Reference Table Lookups

Find and Tune the SQL When Objects Are Used

Consider the Time Component When Working with DATE Data Types

Use PL/SQL to Tune PL/SQL

Understand the Implications of PL/SQL Object Location

Use Rollback Segments to Open Large Cursors

Use Active Transaction Management to Process Large Quantities of Data

Use Temporary Database Tables for Increased Performance

Limit the Use of Dynamic SQL

Use Pipelined Table Functions to Build Complex Result Sets

Leave Those Debugging Commands Alone!

The “Look and Feel”: Just for the Beginners

PL/SQL Example

Create a Procedure Example

Execute the Procedure from PL/SQL Example

Create a Function Example

Execute the GET_CUST_NAME Function from SQL Example

Create a Package Example

Database Trigger Example Using PL/SQL

Tips Review

References

Chapter 11: Exadata, Tuning RAC, and Using Parallel Features

Exadata Terminology and the Basics

Exadata Stats

Exadata Storage Expansion Rack Briefly

Exalogic Briefly

Smart Scans

Flash Cache

Storage Indexes

Hybrid Columnar Compression (11.2)

I/O Resource Management (IORM)

Use All Oracle Security Advantages with Exadata

Best Practices

Summary: Exadata = Paradigm Shift!

Oracle Database Appliance

SPARC SuperCluster

Oracle Exalytics Business Intelligence Machine

Other Oracle Hardware to Consider

The Oracle Big Data Appliance

ZFS Storage Servers

Pillar Storage System

StorageTek Modular Library System

The Oracle Public Cloud and the Oracle Social Network

Parallel Databases

Real Application Clusters (RAC)

Oracle RAC Architecture

Cluster Interconnect

Internal Workings of the Oracle RAC System

Global Cache Service (GCS) and Global Enqueue Service (GES)

Cache Fusion and Resource Coordination

Past Image

SCN Processing

Is RAC Unbreakable?

Summary

RAC Performance Tuning Overview

RAC Cluster Interconnect Performance

Finding RAC Wait Events—Sessions Waiting

RAC Wait Events and Interconnect Statistics

GES Lock Blockers and Waiters

Cache Fusion Reads and Writes

Cluster Interconnect Tuning—Hardware Tier

Resource Availability

Tuning RAC Using Enterprise Manager Grid Control

Database Performance Tab

Basic Concepts of Parallel Operations

Parallel DML and DDL Statements and Operations

Parallel DML Statements and Operations

Managing Parallel Server Resources and Parallel Statement Queuing

Parallelism and Partitions

Inter- and Intra-operation Parallelization

Examples of Using Inter- and Intra-operations (PARALLEL and NOPARALLEL Hints)

Creating Table and Index Examples Using Parallel Operations

Real-World Example of Distributing Data for Effective Parallel Operations

Parallel DML Statements and Examples

Parallel DML Restrictions

Parallel DML Statement Examples

Monitoring Parallel Operations via the V$ Views

V$PQ_TQSTAT

V$PQ_SYSSTAT

V$PQ_SESSTAT

Using EXPLAIN PLAN and AUTOTRACE on Parallel Operations

Tuning Parallel Execution and the Initialization Parameters

Parallel Loading

Performance Comparisons and Monitoring Parallel Operations

Optimizing Parallel Operations in RAC

Objectives of Parallel Operations

RAC Parallel Usage Models

INIT.ORA Parameters

V$ Views for Viewing Parallel Statistics

Parallel Configuration and Associated Baseline Test

Parallel Query Test Examples

Create Table As

Index Builds

Performance Considerations and Summary

Other Parallel Notes

Oracle Documentation Is Online

Tips Review

References

Chapter 12: The V$ Views (Developer and DBA)

Creating and Granting Access to V$ Views

Obtaining a Count and Listing of All V$ Views

Getting a Listing for the X$ Scripts That Make Up the V$ Views

Examining the Underlying Objects That Make Up the DBA_ Views

Using Helpful V$ Scripts

Basic Database Information

Basic Automatic Workload Repository (AWR) Information

Basic Licensing Information

Database Options Installed in Your Database

Summary of Memory Allocated (V$SGA)

Automatic Memory Management (AMM) and MEMORY_TARGET

Detailed Memory Allocated (V$SGASTAT)

Finding spfile.ora/init.ora Settings in V$PARAMETER

Determining Hit Ratio for Data (V$SYSSTAT & V$SYSMETRIC)

Determining Hit Ratio for the Data Dictionary (V$ROWCACHE)

Determining Hit Ratio for the Shared SQL and PL/SQL (V$LIBRARYCACHE)

Using the Result Cache

Identifying PL/SQL Objects That Need to Be Kept (Pinned)

Finding Problem Queries by Monitoring V$SESSION_LONGOPS

Finding Problem Queries by Querying V$SQLAREA

Finding Out What Users Are Doing and Which Resources They Are Using

Finding Out Which Objects a User Is Accessing

Getting Detailed User Information

Using Indexes

Identifying Locking Issues

Killing the Problem Session

Finding Users with Multiple Sessions

Querying for Current Profiles

Finding Disk I/O Issues

Finding Rollback Segment Contention

Determining Whether Freelists Are Sufficient

Checking for Privileges and Roles

Wait Events V$ Views

Some of the Major V$ View Categories

Tips Review

References

Chapter 13: The X$ Tables (Advanced DBA)

Introducing the X$ Tables

Misconceptions about the X$ Tables

Granting Access to View the X$ Tables

Creating V$ Views and X$ Tables

Obtaining a List of the X$ Tables That Make Up the V$ Views

Obtaining a List of All the X$ Tables

Obtaining a List of All the X$ Indexes

Using Hints with X$ Tables and Indexes

Monitoring Space Allocations in the Shared Pool

Creating Queries to Monitor the Shared Pool

ORA-04031 Errors

Large Allocations Causing Contention

Shared Pool Fragmentation

Low Free Memory in Shared and Java Pools

Library Cache Hit Ratio

High Number of Hard Parses

Mutex/Latch Waits and/or Sleeps

Miscellaneous

Obtaining Information about Redo Log Files

Setting Initialization Parameters

Case 1

Case 2

Case 3

Case 4

Case 5

Buffer Cache/Data Block Details

Buffer Statuses

Segments Occupying Block Buffers

Hot Data Blocks and the Causes of Latch Contention and Wait Events

Obtaining Database- and Instance-Specific Information

Effective X$ Table Use and Strategy

Related Oracle Internals Topics

Traces

DBMS_TRACE Package

Events

Dumps

ORADEBUG

trcsess Utility

Reading the Trace File

Wait Information and Response Time

Recursive Calls

Module Info

Commit

Unmap

Bind Variables

Errors

Some Common X$ Table Groups

Some Common X$ Table and Non-V$ Fixed View Associations

Common X$ Table Joins

X$ Table Naming Conventions

Future Version Impact

Tips Review

References

Chapter 14: Using Statspack and the AWR Report to Tune Waits, Latches, and Mutexes

What’s New in 11gR2 (11.2) Statspack and the AWR Report

Installing Statspack

Security of the PERFSTAT Account

Post-Installation

Gathering Statistics

Running the Statistics Report

The Automatic Workload Repository (AWR) and the AWR Report

Manually Managing the AWR

AWR Automated Snapshots

AWR Snapshot Reports

Run the AWR Report in Oracle Enterprise Manager Grid Control

Interpreting the Statspack and AWR Report Output

The Header Information

The Load Profile

Instance Efficiency

Top Wait Events

DB File Scattered Read

DB File Sequential Read

Buffer Busy Waits IDs and Meanings

Buffer Busy/Segment Header

Buffer Busy/UNDO Header

Buffer Busy/Undo Block

Buffer Busy/Data Block

Buffer Busy/Index Block

Latch Free

Cursor: pin S

Cursor: pin S wait on X

Library cache: mutex X

Enqueue

Log File Switch

Log Buffer Space

Log File Sync

Global Cache CR Request

Log File Parallel Write

DB File Parallel Write

Direct Path Read

Direct Path Write

Async Disk I/O

Idle Events

Oracle Bugs

The Life of an Oracle Shadow Process

RAC Wait Events and Interconnect Statistics

Top SQL Statements

Instance Activity Statistics

Things to Look for in the Instance Statistics Section

Key Areas to Consider

Tablespace and File I/O Statistics

Segment Statistics

Additional Memory Statistics

UNDO Statistics

Latch and Mutex Statistics

Tuning and Viewing at the Block Level (Advanced)

Dictionary and Library Cache Statistics

SGA Memory Statistics

Nondefault Initialization Parameters

Top 10 Things to Look for in AWR Report and Statspack Output

Managing the Statspack Data

Upgrading Statspack

Deinstalling Statspack

Quick Notes on the New ADDM Report

Scripts in 11gR2

Tips Review

References

Chapter 15: Performing a Quick System Review (DBA)

Total Performance Index (TPI)

Education Performance Index (EPI)

System Performance Index (SPI)

Memory Performance Index (MPI)

Top 25 “Memory Abusers” Statements Tuned

Top 10 “Memory Abusers” as a Percent of All Statements

Buffer Cache Hit Ratio

Dictionary Cache Hit Ratio

Library Cache Hit Ratio

PGA Memory Sort Ratio

Query for PGA Memory Sort Ratio

Percentage of Data Buffers Still Free

Using the Result Cache Effectively

Pinning/Caching Objects

Disk Performance Index (DPI)

Top 25 “Disk-Read Abuser” Statements Tuned

Top 10 Disk-Read Abusers as Percentage of All Statements

Tables/Indexes Separated

Mission-Critical Table Management

Key Oracle Files Separated

Automatic UNDO Management

Temporary Segment Balance

Total Performance Index (TPI)

Overall System Review Example

Rating System

Example System Review Rating Categories

Items Requiring Immediate Action

Other Items Requiring Action

System Information List

Memory-Related Values

Disk-Related Values

CPU-Related Values

Backup- and Recovery-Related Information

Naming Conventions and/or Standards and Security Information Questions

DBA Knowledge Rating

Other Items to Consider in Your TPI and System Review

Tips Review

References

Chapter 16: Monitor the System Using Unix Utilities (DBA)

Unix/Linux Utilities

Using the sar Command to Monitor CPU Usage

sar –u (Check for CPU Bogged Down)

The sar –d Command (Find I/O Problems)

Tuning Disk-Bound Systems

The sar –b Command (Check the Buffer Cache)

The sar –q Command (Check the Run Queue and Swap Queue Lengths)

Using the sar and vmstat Commands to Monitor Paging/Swapping

Using sar –p to Report Paging Activities

Using sar –w to Report Swapping and Switching Activities

Using sar –r to Report Free Memory and Free Swap

Using sar –g to Report Paging Activities

Using sar –wpgr to Report on Memory Resources

What’s a Good Idle Percentage for the CPU?

CPU Scheduler and Context Switching

Checking Oracle CPU Utilization Within Oracle

Finding the Worst User on the System Using the top Command

Monitoring Tools

Using the uptime Command to Monitor CPU Load

Using the mpstat Command to Identify CPU Bottlenecks

Combining ps with Selected V$ Views

Using the iostat Command to Identify Disk I/O Bottlenecks

Using iostat –d for Disk Drives sd15, sd16, sd17, and sd18

Using iostat –D

Using iostat –x

Combining iostat –x with Logic in a Shell Script

Using the ipcs Command to Determine Shared Memory

Using the vmstat Command to Monitor System Load

Monitoring Disk Free Space

The df Command

Monitoring Network Performance

Monitoring Using the spray Command

Monitoring Network Performance with nfsstat –c

Monitoring Network Performance with netstat

Corrective Network Actions

Displaying Current Values of Tunable Parameters

Modifying the Configuration Information File

Other Factors That Affect Performance

Tuning a CPU System

Tuning Memory-Bound Systems

Disk Tuning

Volume Manager Factors That Can Affect Performance

Other Sources to Improve Performance

Tips Review

References

Appendix A: Key Initialization Parameters (DBA)

Obsoleted/Desupported Initialization Parameters

Deprecated Initialization Parameters

Top 25 Initialization Parameters

Top 20 Initialization Parameters Not to Forget

Top 13 Undocumented Initialization Parameters (As I See It)

Bonus 11 Undocumented Initialization Parameters

Listing of Documented Initialization Parameters (V$PARAMETER)

Listing of Undocumented Initialization Parameters (X$KSPPI/X$KSPPCV)

Oracle Applications Release 12 Recommendations (Note: 396009.1)

Database Initialization Parameter Sizing

Top 10 Reasons Not to Write a Book

Tips Review

References

Appendix B: The V$ Views (DBA and Developer)

Creation of V$ and GV$ Views and X$ Tables

A List of Oracle 11g (11.2.0.1.0) GV$ Views

A List of Oracle 11g (11.2.0.1.0) V$ Views

Oracle 11g Scripts for the X$ Tables Used to Create the V$ Views

Appendix C: The X$ Tables (DBA)

Oracle 11gR2 X$ Tables Ordered by Name

Oracle 11g X$ Indexes

Oracle 11g V$ Views Cross-Referenced to the X$ Tables

Index