Oracle Tuning Second Edition
Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

  

 

ISBN
0-9797951-9-2
ISBN 13:
978-0-9797951-9-0
Library of Congress Number: 2010900994
1,150 pages: Hardcover -  8x10 PD 511
Shelving: Database/Oracle Oracle in-Focus Series:  # 32

  Oracle Tuning
The Definitive Reference
Second Edition


Donald K. Burleson

Retail Price $69.95 USD/  £45.95 / $75.95 Canadian

Order now for 40% off ! Only $41.95
(40% off)
     
Key Features About the Authors Table of Contents
Index Reader Comments Errata
     
Get the Complete Oracle Tuning Set
Both for only $79.95 - a $119.00 value
Oracle Tuning: The Definitive Reference 2nd Edition $69.95
Advanced Oracle SQL Tuning $59.95


Oracle has become the world's most flexible and robust database and along with great power comes great complexity.  This complexity requires that the DBA have expert knowledge of Oracle internals.  This book provides a thorough step-by-step approach for holistic Oracle tuning in this challenging information technology era.  It represents the knowledge accumulated from tuning thousands of Oracle databases.

Oracle tuning has always been a complex task; however, it has become even more complex as Oracle evolves and yields new techniques for achieving optimal performance in the stressed production environment of today’s high-tech world.

Oracle STATSPACK and AWR has introduced a revolution in database tuning. By understanding these time-series tables, we can develop time-series tuning models to predict upcoming outages and dynamically change the instance to accommodate the impending resource changes.  Database tuning efforts must become as sophisticated as the databases themselves.

This book strives to show you how to leverage upon the wealth of Oracle performance information so that you can create a robust Oracle database engine, one that maximizes computing resources while minimizing overhead.

If you are seeking theory, this is not the book for you.  This book encapsulates the combined knowledge of over a century of hands-on DBA tuning experience, a pragmatic, practical approach for the professional Oracle DBA.

This is not a book for beginners. Targeted at the senior Oracle DBA, this comprehensive book gives you all of the knowledge you need to be successful in tuning even the most complex Oracle database.  The code download for this book is packed with ready-to-run scripts to monitor and identify even the most challenging performance issues.

 
Key Features

* See how to access Oracle's time-series performance data.

* Learn secrets on how to identify performance signatures and shifting workloads over time.

* Download the code depot for a gold mine of ready-to-run tuning scripts.

* See how to analyze historical trends to predict impending performance problems.

* Get tips for understanding Oracle elapsed-time tuning reports.

* Create customized performance alerts to fix a problem before it impedes end-user response time.

* See how to deploy intelligent Oracle performance optimization.

*
Understand proactive time-series Oracle tuning techniques.

* Learn secrets for optimizing entire SQL workloads.

About the Author:


Donald Burleson

Donald K. Burleson is one of the world’s top Oracle Database experts with more than 25 years of full-time DBA experience.  He specializes in creating database architectures for very large online databases and he has worked with some of the world’s most powerful and complex systems.  

A former Adjunct Professor, Don Burleson has written more than 30 books, published more than 100 articles in National Magazines, and serves as Editor-in-Chief of Rampant TechPress. Don is a popular lecturer and teacher and is a frequent speaker at Oracle OpenWorld and other international database conferences.

 

Free Oracle Tips

HTML Text

Table of Contents:

Using the Online Code Depot
Oracle Script Collection
Conventions Used in this Book
Are you ready to tune?
Acknowledgements

Preface
 

Chapter 1:  Introduction to Oracle Tuning      

An Introduction to Oracle Tuning
Throughput vs. Response Time
Top-down Tuning vs. Bottom-up Tuning
Proactive Tuning vs. Reactive Tuning

Reactive Oracle Tuning
Proactive Oracle Tuning

Know the Limits:
  Things that We Can Not Tune
Oracle Application-level Tuning

A Historical Review of DBA Job Duties
Oracle Tuning and Server Consolidation
Hardware Technology Drives Database Technology
The Changing Role of an Oracle Tuning Professional

The Causes of Poor Oracle Performance

The Oracle Tuning Hierarchy

External Hardware Performance Review
Finding Database Bottlenecks
Oracle Instance Tuning
AWR Licensing Options
Tracking your Oracle Option Usage
Oracle SQL Tuning

Conclusion

 
Chapter 2:  Time-Series Oracle Tuning         

Managing the Complexity of Oracle
The Pros and Cons of Time Series Tuning
Data Quality
Oracle’s Approach to Workload Thresholds
Signatures, Workloads and Exceptions

Workloads and Predictive Analysis
What is an Oracle Workload?

Using Adaptive Thresholds for Predictive Modeling
Using OEM for Predictive Modeling

Time Series Tuning Guidelines

Scheduling an SGA Reconfiguration
Trend-based Oracle Reconfiguration
When to Trigger a Dynamic Reconfiguration

Approaches to Self-tuning Oracle Databases

Tuning a Constantly Changing Database
Can Oracle Possess Psychic Abilities?

Capturing Time Series Metrics

Customized AWR Tuning Reports

Exception Reporting

Exception Reporting with the AWR

Exception Reporting with dba_hist_filestatxs

Trend Identification with the AWR
Conclusion
References
   

Chapter 3:  The Time Model Tuning Approach        

Inside the Oracle Time Model Views
Displaying Session Details with Time Model Data
Real-time Reporting
Time Model Tables in AWR
Time Model Statistics
Conclusion
    
 
Chapter 4:  Predictive Modeling 

Predicting the Future with Oracle
Oracle Data Mining and Predictive Analytics

The Evolution toward Data Mining
Oracle Data Mining and Predictive Analytics
Components of the Oracle Data Miner

Predictive Models Made Easy
Exception Reporting with the AWR

General Trend Identification with the AWR

Correlation Analysis with AWR and ASH

Predictive Modeling with dba_hist_filestatxs

Conclusion
    
 

Chapter 5:  Oracle Troubleshooting  

Introduction to Troubleshooting
Emergency Troubleshooting Methods

Why Oracle Shops Lose Their Databases
The Limitations of the DBA

Case Studies in Oracle Troubleshooting

Accurately Measuring Oracle Performance

Using the BCHR for Performance Tuning

The Danger of Relying on Blanket Ratios

Oracle Bottleneck Analysis

Prerequisites for Bottleneck Analysis
Combining Bottleneck and Ratio Analysis
The Number One Oracle Performance Myth

Troubleshooting Oracle Disk Performance

Automatic Growth

Troubleshooting Critical Storage Problems

Detecting Tablespace Fragmentation
Detecting Object Fragmentation
Correcting Object Fragmentation

Troubleshooting Table Problems

Index Diagnostics
Correcting Space-related Object Performance Problems

Troubleshooting Memory Problems

Getting a Handle on Memory Usage

Understanding the SGA

Gaining Insight Into Memory Use
The Buffer Cache Hit Ratio – Still Worthwhile?

Troubleshooting the Shared Pool

When Does Less Become More?

Troubleshooting the Log Buffer

Log Buffer Related Parameter Issues
Investigating Sorts

Troubleshooting Oracle Sorting

buffer busy waits

Troubleshooting Latches

Troubleshooting I/O Hotspots
Global Basic Queries
Locating Hot I/O Objects
Examining Background Processes
Troubleshooting Rollback Activity

Troubleshooting Problem Sessions

Finding Storage Hogs
Locating Top Resource Sessions
Pinpointing Sessions with Problem SQL

Troubleshooting Problem SQL

What is Bad SQL?

Proactive SQL Troubleshooting
Tricks for SQL Troubleshooting

Troubleshooting Triage
Using Active Session History (ASH) for Troubleshooting

Collecting ASH Wait Information
Using ASH for Troubleshooting
Inside v$active_session_history
Display SQL Wait Details in ASH
Event Wait Analysis with ASH
The Performance Overhead of Collecting ASH Data
Inside the v$session_wait_history
Signature Analysis of ASH Wait Events
Using ASH in OEM

Conclusion
 

 
Chapter 6:  Inside STATSPACK and AWR         

Introduction to Tuning with STATSPACK
Oracle vs. STATSPACK

Inside the AWR tables

Using STATSPACK for Oracle Tuning
The structure of the STATSPACK tables
How STATSPACK Works
Trend Reports with STATSPACK

Inside the AWR Tables

The AWR Data Collection Mechanism
Customizing AWR snapshots
The Mysterious AWR Performance Tables

Inside the AWR Tables

Inside the dba_hist Tables
Database Wait Events in AWR
The AWR Metric Tables

AWR System Statistics

Operating System Statistics in AWR
SQL Statistics in AWR
Segment Statistics in AWR
Datafile I/O Statistics in AWR

Conclusion

Chapter 7:  AWR and STATSPACK Configuration    

The Differences between STATSPACK and AWR
Installing STATSPACK

Install Prerequisites

STATSPACK vs. AWR
Statistic Management in AWR and STATSPACK

The snap_level Parameter
The session_id
The num_sql
What SQL is Captured?

STATSPACK SQL Collection Thresholds
AWR SQL Collection Thresholds
The stats$sql_summary Table

Installing AWR

The dbms_workload_repository Package

Creating an AWR Report

Report Naming

Conclusion

Chapter 8:  Reading an AWR or STATSPACK Report        

The Evolution of the Elapsed Time Report
What’s  New in STATSPACK and AWR Reports

Generating a STATSPACK Report
Generating an AWR Report

Reading the STATSPACK / AWR Report

STATSPACK / AWR Report Summary

Cache Sizes Section

Load Profile

Instance Efficiency Percentage

All about Oracle Ratios

Top 5 Timed Events Section of a STATSPACK / AWR Report

The Wait Events Section of the AWR / STATSPACK Report

Wait Event Histogram Section
Instance Activity Section in the STATSPACK / AWR Report
Instance Recovery Statistics Section of a STATS/AWR Report
PGA Section
Process Memory Summary Section
Enqueue Statistics
Rollback Segments
Undo Segments
Latch Activity Section of a STATSPACK / AWR report

Dictionary and Library Cache Stats

The Shared Pool Advisory Section

The Time Model Statistics section

The Operating System Statistics Section

The Top SQL Section
The Instance Activity Section
The I/O Reports Section

The Advisory Sections

Buffer Pool Advisory

The Buffer Wait Statistics Section

Conclusion
 

Chapter 9:  Oracle Metrics and v$ Tuning Views           

Classifying v$ Views and Metrics
Inside the Oracle Metrics
Inside the v$metric Tables
Database Wait Metrics
Oracle File Metrics
Oracle Service Metrics

The Secret World of the v$ Views
The Active Session History v$ View
Conclusion
 

Chapter 10:  Oracle Wait Event Tuning       

The Oracle Wait Event Model
The AWR Wait Event Tables
Why Wait Event Tuning for Oracle?

Addressing Wait Bottlenecks
Systemwide Wait Event Tuning
Not All Events Are Created Equal

Inside the Real-time V$ Wait Events

Inside v$session_wait

Inside v$session_event

Conclusion
 

Chapter 11:  Oracle Tuning Tools   

The Evolution of Oracle Tuning Tools
The Spirit of Independence
A Best Practices Approach to Oracle Tuning
The History of Oracle Tuning Techniques
An Automated Approach to SQL Tuning
How Fully Automated SQL Tuning Works
Fully Automated SQL Tuning is not a Panacea

Oracle Trace Analyzer (sqltxplain)
Oracle Lightweight Onboard Monitor (LTOM)

LTOM Features

Oracle Trace Utility

Inside Oracle Event Tracing
Setting an Oracle Trace Event
Accessing Oracle Trace Records with External Tables
Exception Reporting within a Trace File
Loading and Analyzing the 10046 Event
Inside the Oracle 10046 Trace File Output
Conclusions on Tracing

Generating Oracle Test Data

Gathering a Sample Workload in Oracle 11g
Creating Data for Performance Testing

Conclusion
 

Chapter 12:  Server & Network Tuning    

Oracle Server Tuning
Outside the Oracle Instance

Oracle Server Bottlenecks

Oracle Server Monitoring

Capturing Server-side Metrics
OS Statistics for the Cost-based Optimizer
OS data inside Oracle views

The Oracle OS Watcher utility

Starting Oracle OS Watcher

Oracle CPU Tuning

Viewing CPU Utilization for Oracle
Identifying High CPU Usage with vmstat
Storing Information from vmstat

Disk I/O and Oracle

Moore’s Law and Disk Speed
Server RAM and Oracle
Oracle and the 64-bit Server Technology
The New Age of Oracle Server Consolidation
Oracle Enterprise Manager and Server Metrics
Server Metrics and SQL Execution

Oracle Network Tuning
Conclusion

Chapter 13:  Tuning the I/O Subsystem

Inside Oracle Disk Architecture
The Plague of Large Oracle Disks

Disk Architectures of the 21st Century

RAID Technology and Oracle

RAID 5 is Not for Every Database

Oracle and Direct I/O

Enabling Oracle Direct I/O

Calibrating Disk I/O
Monitoring External Disk I/O

Capturing External iostat Information
Generating iostat Reports
Solutions to Physical Read Waits

Choosing a default blocksize

Using Oracle Multiple Blocksizes

Reducing Data Buffer Waste with multiple blocksizes
Reducing Logical I/O with Multiple Blocksizes
Improving Buffer Efficiency with Multiple Blocksizes
Improving SQL Execution with Multiple Blocksizes
Real World Applications of Multiple Blocksizes

The db_file_multiblock_read_count Parameter

Oracle Blocksize & Index I/O

The Latest Consensus on Using Multiple Blocksizes
Vendor Notes on Oracle Multiple Blocksizes

Reducing Disk I/O with SSD

2010 Market Survey of SSD Vendors for Oracle

Oracle Disk Monitoring
Examining Real-time Disk Statistics

Examining Global I/O
Tracking I/O for Specific Tables

Analyzing Real Time I/O Waits

Collecting Real-Time Disk Wait Events

Find the Current Disk I/O Session Bandits
Measuring Disk I/O Speed
Time Series I/O Wait Analysis
Time Series Monitoring of the Data Buffers
Monitoring Disk I/O with AWR
Conclusion

 
 
 
Chapter 14:  Oracle Instance Tuning  

Inside Instance Tuning
Instance Tuning Comes First
Instance Configuration for High Performance

Automatic Memory Management

Manual RAM allocation vs. AMM

Sizing the Oracle SGA and PGA Regions

Viewing Server RAM Resources
Sizing your SGA
SGA Sizing on a Dedicated Server
RAM and Virtual Memory for Oracle
Finding the High Water Mark of Oracle User Connections
.
Determining the Optimal PGA Size

Display PGA Area Sizes
A Script for Estimating Total PGA RAM
Optimizing pga_aggregate_target

Rules for adjusting

Important caveats in PGA management
Sizing your PGA for hash joins
The 11g full hash join
Sizing the PGA for Batch Processing
A case study RAM hash joins
Hidden Parameters for Oracle PGA Regions
Supersizing the PGA
Monitoring Server Resources in MS Windows

OS Kernel Parameters

Server Settings for Windows Servers
Kernel Setting for UNIX and Linux Servers

Oracle Parameter Tuning

Oracle Hidden Parameters

Oracle Parallel Query Parameters

Hidden Parallel Parameters
SQL Optimizer Parameters
Data Buffer Cache Hidden Parameters

Instance Wait Event Tuning
Tuning the Oracle Data Buffer Pools

The Problem of Duplicate RAM Caches
Why is Oracle Logical I/O So Slow?

Data Block Caching in the SGA
Full Table Caching in Oracle
Oracle Data Buffer Metrics

Using AWR for Buffer Pool Statistics
Oracle’s Seven Data Buffer Hit Ratios
Viewing Information about SGA Performance
AMM and Oracle Instance Tuning

Internals of the Oracle Data Buffers

Finding Hot Blocks inside the Oracle Data Buffers
Viewing the Data Buffer Contents

The Downside of Mega Data Buffers

Allocating Oracle Objects into Multiple RAM data Buffers
Sizing the KEEP Pool

Automating KEEP Pool Assignment
Tuning the RECYCLE Pool

Large Blocks and Oracle Instance Caching
Finding Baselines
Learning Instance Tuning from Performance Benchmarks

Rules for adjusting shared_pool_size
Sizing the Shared Pool with the Oracle Advisory Utility
Rules for Adjusting the Data Buffer Sizes

Monitoring RAM usage

Tracking hash joins
Viewing RAM usage for hash joins in SQL

Conclusion

Chapter 15:  Tablespace & Object Tuning       

Oracle Tablespace Tuning
Inside Oracle Tablespace Tuning

The Issue of pctfree
The Freelist Unlink Process
The Issue of pctused

Setting Pctfree and Pctused

Freelists and Performance

ASSM and Tablespace Performance

Internal Freelist Management
Characteristics of Bitmap Segment Management
New High Watermark Pointers
Extent Control Header Block

Using ASSM with RAC

Potential Performance Issues with ASSM
Faster SQL with Database Reorganizations
Managing Row Chaining in Oracle
A Summary of Object Tuning Rules

Reorganizing Tables for High Performance

Online Reorganization
Segment Space Growth Prediction

Tuning SQL Access with clustering_factor

Not all Indexes are Used in Range Scans

Rebuilding Indexes

When to Rebuild Indexes

Oracle Parallel DDL

Invoking Parallelism
Parallel DBA Operations

Conclusion

Chapter 16:  Inside Oracle SQL Tuning      

Introduction to Oracle SQL
The Origin of SQL
Understanding SQL Tuning
Holistic Oracle SQL Tuning

Dealing with Time Constraints

Best Practices for SQL Optimization

Proper Development Environment
Maintaining A SQL Infrastructure
A Release-centric Approach to Holistic Optimization
Oracle 6 – Oracle 7 enhancements
Oracle 8 – Oracle 8i enhancements
Oracle 9i enhancements
Oracle 10g enhancements
Oracle 10g Release 2 enhancements
Oracle 11g enhancements

What is the Best Optimizer Philosophy?

The Persistent SQL Philosophy
The Dynamic SQL Philosophy

Goals of SQL Tuning

Determine Optimal Table Join Order
Remove Unnecessary Large-table Full-table Scans

Locating full-scan operations

Tuning large-table full-table scans
Tuning small-table full-table scans
Optimizing SQL RAM Resources
Cache Small-table Full-table Scans
Verify Optimal Index Usage
Verify Optimal Join Techniques
Tuning by Simplifying SQL Syntax

Roadblocks to SQL Tuning

SQL Profiles

Tracing SQL Execution History
Oracle SQL as a Database Access Method

The rule Hint is still Popular in Oracle 11g
The Library Cache and Oracle SQL Performance
Using cursor_sharing=force

Oracle Cursor Sharing Enhancements
Oracle ISO 99 Table Syntax

Outer Join
Syntax
External Tables and SQL

Defining an External Table
Internals of External Tables

Tuning Distributed SQL Queries

Subqueries and SQL
Basic SQL Subqueries
Scalar Subqueries
In-line Views (Subqueries in the from Clause)

Inside Oracle Views

Benefits of Oracle Views
The Downside to Using Views
Combining Hints and Views
Parsing SQL Syntax
Create Executable
Interrogating SQL Execution Plans

Oracle SQL Optimizer Statistics

The Oracle dbms_stats Package
Managing Schema Statistics with dbms_stats
Column Skew and histograms
Automating Histogram Sampling with dbms_stats
Oracle Workload Statistics and SQL Performance
External Costing with the Optimizer
Tuning SQL with Histograms
Determining the Optimal Table Join Order
How is Join Cardinality Estimated?

Oracle Join Elimination

Using Dynamic Sampling
Sampling Table Scans

Oracle Tuning with Hints

When hints appear to be ignored
Oracle Indexes – Is Maintenance Required?

Identifying Problem SQL

Find the Problem Sessions

AWR and SQL Tuning

Viewing Table and Index Access with AWR
Towards Automated SQL Tuning
The Goals of Holistic SQL Tuning
The SQL Tuning Advisor
Using SQL Tuning Advisor Session
Inside the 11g SQL Performance Analyzer
Inside the Oracle 11g SQL Performance Analyzer
Gathering the SQL Tuning Set
Setting the SQL Optimizer Cost Model
Turning on CPU Costing

Tuning SQL with "rownum" Filters

Using rownum for top-n queries
Using rownum with range bound queries
Alternatives to rownum
Using OPQ in SQL
Optimizing Oracle SQL Insert Performance
Blocksize and Insert Performance

Oracle Delete Tuning

Using Bulking for Delete Performance

Oracle Update Tuning

CTAS vs. SQL Update statements
Bulking SQL Updates
Bulking SQL Inserts

Oracle tuning with indexes
SQL Tuning with Indexes
The types of Oracle indexes

The Oracle b-tree index
Creating a b-tree index
Does block size matter?

Tuning SQL with bitmapped indexes

Distinct key values and bitmap indexes!

SQL Tuning with bitmap join indexes
How bitmap join indexes work
Bitmap join index example

When Oracle SQL chooses the wrong index

Beware of the fast fix

Forcing index usage

Why doesn’t Oracle use my index?

Using nls_date_format with date indexes

Managing complex date comparisons in SQL

Using the months_between date function
Using the add_months date function
Using the last_day date function
Using the next_day date function
Using the round date function
Using the trunc date function
Index usage and built-in functions
Finding BIF’s
Tuning SQL with Function-based Indexes (FBI)

Using case statements with a function-based index
Indexing on complex functions
Statistics and function-based indexes
Conclusions on function-based indexes

SQL tuning with regular expression indexes

Indexing on regular expressions
Doing case sensitive searches with indexes
SQL Tuning with Oracle*Text Indexes
Oracle Text Index re-synchronization

Tuning SQL with Index Organized Tables
Testing new Oracle indexes

Testing SQL workloads with invisible indexes

Monitoring index usage

Monitoring for Index Range Scans

Monitoring SQL workload activity

Verifying optimal index usage
Finding indexing opportunities
Find SQL that uses sub-optimal indexes
Finding SQL with excessive I/O
Finding sub-optimal SQL in the library cache
Finding index opportunities in AWR
Locating un-used indexes
Finding un-used indexes in Oracle 8i and earlier
Finding un-used indexes in Oracle 9i
Finding un-used indexes in Oracle 10g and beyond

Dropping un-used indexes
Locating infrequently used indexes

The problem of too many indexes
Determining which index to delete
Large Multi-column Indexes

Row clustering and SQL Performance
Index reorganization and SQL Performance

When rebuilding indexes may help SQL performance
When rebuilding indexes will hurt performance
Choosing candidates for index maintenance

Conclusion


Chapter 17:  Oracle Data Warehouse Tuning     

Oracle Data Warehouse Tuning
What Does a Data Warehouse Need?
Oracle star transformations and SQL

Bad star transformation Plan

Why Oracle for the Data Warehouse?
Scaling the Oracle Data Warehouse

Parallel Query for Data Warehouses

Oracle Data Warehouse Tuning TPC-H Benchmarks

Tuning Tricks for Oracle Data Warehouse Configuration

Data Warehouse Design for High Performance

Oracle Data Warehouse Evolution
End-user Query Approach

Data Warehouse Tuning Skills

Data Warehouse Project Manager
The Data Warehouse Informaticist
The Warehouse Statistician
The Data Warehouse Oracle Tuning Professional

Conclusion

Chapter 18:  OEM Tuning 

Introduction to OEM
The New OEM

Tuning with Metrics and Exceptions

Active Session History in Enterprise Manager

Easy Customization of OEM Alerts
Instance Efficiency Metrics

Alerts Notification and Setup

Overview of dbms_scheduler Functions

Throughput Metrics in OEM

OEM Outside the Instance

Exception Tuning Inside Enterprise Manager
Advisor Central in OEM
ADDM Main Screen

ADDM Recommendations

Understanding SQL Advisor Recommendations
The SQL Tuning Advisor Links

The Top SQL Screen
Viewing SQL Details in OEM
The Execution Plan Tab
Current Statistics Tab
Execution History Tab
Tuning History Tab

Oracle SQL Tuning Sets

Creating a SQL Tuning Set
Viewing SQL Tuning Set Details

Using the SQL Access Advisor
New Features of the SQL Advisors
Inside the SQL Access Advisor

The SQL Access Advisor Workload Definition
The SQL Access Advisor Recommendation Options
The SQL Access Advisor Schedule Advisor
The SQL Access Advisor Review
SQL Access Advisor Recommendations

Using the Memory Advisor through OEM

Persistence of Automatically Tuned Values
Automated Maintenance Tasks
Resource Management

Introduction to Online Oracle Tuning Tools
Using Custom Scripts for Oracle Tuning
Shortcomings of OEM
Conclusion
 

Chapter 19:  Oracle RAC and Grid Tuning   

Introduction to Tuning with RAC
Oracle RAC in a Nutshell
Oracle Scalability and Grid Technology

First Scale Up with SMP Servers
Next Scale Out with Multiple SMP Servers

Oracle Grid in a Nutshell
Blade Servers and Oracle RAC Tuning

Blade Servers and Oracle App Servers

The Revolution of Cache Fusion
Overview of RAC and Grid Tuning

RAC Load Balancing
Managing Inter-instance Data Block Transfers

Block Spreading

Blocksize Adjustment
Read-only Tablespaces
Parallel Processing and RAC Performance

Conclusion
Index

Index Topics:

$oracle_sid

_always_star_transformation
_db_aging_cool_count
_db_aging_freeze_cr
_db_aging_hot_criteria
_db_aging_stay_count
_db_aging_touch_time
_db_block_cache_clone
_db_block_cache_map
_db_block_cache_protect
_db_block_hash_buckets
_db_block_hash_latches
_db_block_hi_priority_batch_size
_db_block_max_cr_dba
_db_block_max_scan_cnt
_db_block_med_priority_batch_size
_db_block_write_batch
_db_percent_hot_default
_db_percent_hot_keep
_db_percent_hot_recycle
_fast_full_scan_enabled
_kgl_latch_count
_latch_spin_count
_optimizer_cost_model
_parallelism_cost_fudge_factor
_pga_max_size
_small_table_threshold
_smm_px_max_size

10046
10046 trace event
10046 trace file
10046 wait event
32-bit shops

A
Active Session History
Active Session History (ASH)
adaptive alert thresholds
Adaptive cursor sharing
adaptive threshold
add_window_group_member
ADDM
addm_rpt.sql
ad-hoc display
ad-hoc query
Advisor Central
advisor_name
advisories
alert mechanism
alert.log
all_rows
all_rows optimizer_mode
alter index rebuild
alter session
alter system
alter system command
alter system flush shared_pool
alter table
alter table shrink space
AMM
analysis job
analyze table
append option
Application Server 10g
Apps 11i
ARCH
Artificial Intelligence (AI)
ASH
ASH tables
ASH views
ASSM
asynch
auto option
auto_degree
auto_task_consumer_group
auto_task_job_class
auto_tasks_job_class
autoextend
Automated Database Diagnostic Monitor (ADDM)
Automated Memory Management
Automated Session History
Automated Session History (ASH)
automated SQL tuning
Automated Storage Management
Automated Workload Repository
Automated Workload Repository (AWR)
Automatic Database Diagnostic Monitor
Automatic Diagnostic Database Advisor
automatic load balancing
Automatic Maintenance Tasks
Automatic Memory Management
Automatic Memory Management (AMM
Automatic Memory Manager
Automatic Memory Manager (AMM)
Automatic Segment Advisor
Automatic segment management
Automatic Segment Management
Automatic Segment Space
Automatic Segment Space Management
Automatic Shared Memory Management
Automatic Shared Memory Management (ASMM)
Automatic Storage Management
Automatic Storage Management (ASM)
Automatic Workload Repository
automatic_ipc
average_waiter_count
avg_row_len
AWR
AWR baselines
AWR metrics
AWR Report
AWR/STATSPACK report
awr_high_resource_sql.sql
awr_report_html
awr_report_text
awr_sql_scan_sums.sql
awrrpt.sql

B
b*tree
baseline_name
begin_interval_time
begin_time
BgnSnap
Bi-modal systems
bitmap
bitmap block
bitmap freelists
bitmap indexes
bitmap join
bitmap space management
bitmap_merge_area_size
blade servers
block spreading
block_count.sql
Blocking lock ratio
blocksize
Bottleneck analysis
break_poll_skip
bstat-estat
bstat-estat reports
Bubble fragmentation
buf_blocks.sql
buf_keep_pool.sql
buffer busy wait
buffer busy waits
Buffer busy waits
buffer cache
Buffer cache hit (%)
Buffer cache hit ratio
Buffer Cache Hit Ratio (BCHR)
buffer cache size
buffer hit ratio
buffer wait statistics
buffer_busy_wait
buffer_gets_th
buffer_pool_keep
buffer_pool_recycle
bulk binds

C
C++
cache buffers chain
cache buffers LRU chain
cache fusion
cache hit
cache size
cardinality
Cardinality
Cartesian joins
cascade
catawr.sql
catdbsyn.sql
CBO
CBO statistics
chained row fetches
chained/migrated rows
change data capture
close_window
cluster interconnect9
clustering_factor
clusters
CODASYL DBTG
Confio Ignite
consistent gets
consistent_gets
convosync
correlation matrix
Cost Based Optimizer
Cost Based Optimizer (CBO)
cost-based optimizer
covariate analysis
CPU bottleneck
CPU costs
CPU dispatcher run queue
CPU dispatching
CPU overhead
CPU run queue
CPU run queue waits
cpu_count
CPU-bound
CPU-bound database
Create Job
create part (DDL)
Create Table As Select (CTAS)
create_bitmap_area_size
create_job
create_snapshot
create_tuning_task
crontab
cross join
CTAS
current statistics
cursor_sharing
cursor_space_for_time

D
data blocks
data buffer
data buffer hit ratio
data buffer hit ratio (DBHR)
data buffer pools
data buffers
data dictionary views
data localization
Data Manipulation Language
Data Manipulation Lock (DML)
data quality
data transmission delay
data warehouse
data_object_id
Database block gets
database blocksize
Database Control
Database Diagnostic Pack
Database Tuning Pack
day\ mode
DB block gets
DB CPU
db file parallel write
db file scatter read
db file scatter reads
db file scattered read
db file scattered read waits
db file scattered reads
db file sequential read
db file sequential read waits
db file sequential reads
db time
DB time
db_16k_cache_size
db_2k_cache_size
db_32k cache_size
db_32k_cache_size
db_aging_hot_criteria
db_aging_touch_time
db_block_buffers
db_block_gets
db_block_lru_latches
db_block_lru_statistics
db_block_size
db_cache_advice
db_cache_size
db_file_multiblock_read_count
db_file_simultaneous_writes
db_files
db_keep_cache_size
db_nk_cache_size
db_recycle_cache_size
db_time_in_wait
DB2
DBA
dba_advisor
dba_advisor_actions
dba_advisor_commands
dba_advisor_def_parameters
dba_advisor_definitions
dba_advisor_directives
dba_advisor_findings
dba_advisor_journal
dba_advisor_log
dba_advisor_object_types
dba_advisor_objects
dba_advisor_parameters
dba_advisor_rationale
dba_advisor_recommendations
dba_advisor_sqla_rec_sum
dba_advisor_sqla_wk_map
dba_advisor_sqla_wk_stmts
dba_advisor_sqlw_colvol
dba_advisor_sqlw_journal
dba_advisor_sqlw_parameters
dba_advisor_sqlw_stmts
dba_advisor_sqlw_sum
dba_advisor_sqlw_tables
dba_advisor_sqlw_tabvol
dba_advisor_sqlw_templates
dba_advisor_tasks
dba_advisor_templates
dba_advisor_usage
dba_cache_advice
dba_data_files
dba_extents
dba_feature_usage_statistics
dba_hist
dba_hist tables
dba_hist$buffer_pool_statistics table
dba_hist_active_sess_history
dba_hist_active_session_history
dba_hist_baselines
dba_hist_bg_event_summary
dba_hist_buffer_pool_stat
dba_hist_buffer_pool_stat table
dba_hist_buffer_pool_statistics
dba_hist_db_cache_advice
dba_hist_enqueue_stat
dba_hist_event_name
dba_hist_event_summary
dba_hist_filemetric_history
dba_hist_filestatxs
dba_hist_latch
dba_hist_latch_children
dba_hist_latch_misses_summary
dba_hist_librarycache
dba_hist_metric_name
dba_hist_osstat
dba_hist_pga_target_advice
dba_hist_pgastat
dba_hist_rowcache_summary
dba_hist_seg_stat
dba_hist_service_name
dba_hist_service_stat
dba_hist_service_wait_class
dba_hist_sessmetric_history
dba_hist_sgastat
dba_hist_shared_pool_advice
dba_hist_snapshot
dba_hist_sql_plan
dba_hist_sql_summary
dba_hist_sqlplan
dba_hist_sqlstat
dba_hist_sqltext
dba_hist_stat_name
dba_hist_statname
dba_hist_sys_time_model
dba_hist_sysmetric_history
dba_hist_sysmetric_summary
dba_hist_sysstat
dba_hist_systat
dba_hist_system_event
dba_hist_tempstatxs
dba_hist_waitclassmet_history
dba_hist_waitstat
dba_hist_wr_control
dba_histograms
dba_indexes
dba_jobs
dba_objects
dba_scheduler_job_classes
dba_scheduler_jobs
dba_scheduler_schedules
dba_scheduler_window_groups
dba_scheduler_windows
dba_scheduler_wingroup_members
dba_segments
dba_tab_modifications
dba_tables
dba_tables.avg_row_len
dba_users
DBHR
dbid
DbId
dbms_advisor
dbms_job
dbms_mview
dbms_redefinition
dbms_scheduler
dbms_session.set_ev()
dbms_shared_pool
dbms_space.space_usage
dbms_sqlpa
dbms_sqltune
dbms_sqltune.create_sqlset
dbms_stat.gather_system_stats
dbms_stats
dbms_stats.gather_workload_stats
dbms_utility
dbms_workload_capture
dbms_workload_repository
dbms_xplan.display_awr
dbmsawr.sql
dbmspool.sql
DBWR
Decision Support System
Decision Support System (DSS)
Decision Support Systems
decision support systems (DSS)
Decision Support Systems (DSS)
Default buffer cache
DEFAULT pool
default_degree
deleted leaf node
deletes
dictionary cache
dictionary cache
dictionary cache
dictionary-managed tablespace
dictionary-managed tablespaces
direct I/O
directio
Disk bottleneck
Disk enqueues
disk I/O
disk I/O waits
disk_read_th
dispatcher timer
dispatching priority
DISTINCT
DML
driving \table
drop indexes
drop_window_group
DSS
duration
dynamic sampling
dynamic_sampling

E
efficiency metrics
elapsed time
end_interval_time
end_time
EndSnap
enqueue
enqueue statistics
enqueue waits
enqueues
Enterprise Manager
enterprise resource planning
equijoin predicate
estimate_percent
ETL process

event tables
exception reports
excessive logical I/O
execute_tuning_task
execution history
execution plan
executions_delta
executions_th
EXPLAIN
explain_rewrite
extent management local
extent size
external tables
Extract, Transform, and Load (ETL)
Extract, Transformation and Loading (ETL)

F
Fast Application Notification (FAN)

fast_start_parallel_rollback
fetch phase
fileio.sql script
filesystem
filesystemio_options
first_rows
first_rows optimizer_mode
flush_level
force
forcedirectio
forecasting
fragmentation
free_buffer_wait
freelist
freelist groups
freelist_groups
freelists
from clause
full outer join
full table scans
full-table scan
full-table scans
function-based indexes

G
Gallium Arsenide
gather auto
gather stale
gather_schema_stats
gather_stats_job
gather_table_stats
get_io.sql script
get_iostat.ksh
get_vmstat.ksh
glance
Graphical User Interface (GUI)
grep
grid computing
Grid computing
GROUP BY
group_name
GUI

H
hard parse
hard parse elapsed time
hard parses
hash cluster
hash cluster tables
HASH JOIN FULL OUTER
hash joins
hash_area_size
hash_multiblock_io_count
HASH_VALUE
having clause
hidden parameters
high high watermark (HHWM)
high water mark (HWM)
high watermark
High WaterMark (HWM)
histogram
holistic tuning
hypothesis testing
Hypothesis testing
hypothetical workload

I
I/O activity
I/O bound database
I/O overload
I/O session
I/O statistics
I/O-bound
IN
index
Index Organized Tables
index range scan
index range scans
index rebuilding
index rebuilds
index usage
index_ffs
indexes
Informaticists (data content experts)
init.ora
init.ora parameter
init.ora parameters
initialization parameters
initrans
inline hint
insert … select statement
insert statement
instance activity
instance configuration parameters
instance recovery
instance tuning
instance_number
InstNum
inter-instance block transfers
inter-instance parallelism
internal transaction list (ITL) waits
inter-query parallelism
INTERSECT
interval
intra-instance transportable locks
intra-query parallelism
io_cost
Ion
Ion Enterprise Edition
Ion tool
iostat
iostat utility
Itanium 2
Itanium 2 architecture

J
Java
Java API
java pool
Java pool
java_pool_size
job_name
Journal File System (JFS)
junk data

K
Keep buffer cache
keep cache
keep pool
KEEP pool

L
Laboratory Information Management Systems
Large pool
large_pool_size
large-scale RAC databases
large-table full-table scans
latch contention
latch free
latch hit percentage
latch sleeps
latch statistics
latch waits
latches
least-frequently-used (LRU)
left outer join
LGWR
library cache
library cache hit
library cache latch
library cache miss ratio
Lightweight Onboard Monitor (LTOM)
listener.ora
load profile
LOB
LOBs
Locally Managed Tablespace
Locally Managed Tablespaces
locally-managed tablespaces
lock element cleanup
log buffer space
Log Writer
log_buffer
logical I/O (LIO)
logical reads
long duration metrics
low high watermark (LHWM)
lsattr
lsattr -El
lsdev
LTOM

M
Manage Scheduler
Manageability Monitor
materialize hint
materialized views
max_interval
max_retention
maximum transmission unit
Memory Advisor
memory_max_target
merges
method_opt
metric baselines
metric group
metric_unit
min_interval
min_retention
minextents
MMON
modify_snapshot_settings
monitoring clause
Monolithic servers
Moore’s law
Moore’s Law
MS Windows
mts_dispatchers
MTU
multiblock reads
multiblock_read_count
multi-instance RAC database
multiple blocksizes
multiple freelists
multivariate statistics
mutable joins
MySQL

N
name
natural join
nested loop join
nested loops join
nested loops joins
Network bottleneck
network bottlenecks
Network bottlenecks
Network latency
network overload
network-bound
new explain plan
next
nice values
night\ mode
no_cpu_costing 1189
Non Uniform Memory Access (NUMA)
Non-Uniform Memory Access (NUMA)
NOT IN
noworkload statistics
Null event
null values
num_rows
num_sql

O
Object fragmentation
object partition joins
object_growth_trend
ODM
OEM
OEM 10g DB Control
OEM workload
OEM2GO
OLTP
on clause
on-demand CPU
online reorg
Online Transaction Processing (OLTP)
Online Transaction Processing Systems (OLTP)
open_window
optimal join techniques
optimal parameter
optimizer
optimizer directives
optimizer plan stability
Optimizer Plan Stability
optimizer statistics
optimizer_dynamic_sampling
optimizer_features_enable
optimizer_index_caching
optimizer_index_cost_adj
optimizer_max_permutations
optimizer_mode
optimizer_percent_parallel
options
Oracle 10g Discoverer
Oracle Data Guard
Oracle Data Miner
Oracle Discoverer
Oracle Enterprise Manager
Oracle Enterprise Manager (OEM)
Oracle hints
Oracle HTTP server (OHS)
Oracle instance tuning
Oracle Parallel Query
Oracle Parallel Query (OPQ)
Oracle Parallel Server
Oracle Parallel Server (OPS)
Oracle Streams
Oracle TPC-H
Oracle Trace Analyzer
Oracle Wait Interface
Oracle Warehouse Builder (OWB)
Oracle Web Server
Oracle workload
oradebug
ORDER BY
order by clause
ordered hint
ordered_predicates
OS buffer
OS parameters
outer join

P
page-in
page-out
parallel backup/recovery
parallel clause
parallel DDL statements
parallel indexing
parallel loading
parallel query
parallel query dequeue wait
parallel query idle wait - Slaves
parallel server tables
parallel_adaptive_multi_user
parallel_automatic_tuning
parallel_automatic_tuning (PAT)
parallel_index
parallel_max_servers
parallel_threads_per_cpu
parallelism
Parallelize deletes
parse count (hard)
parse_calls_th
partitioning
pctfree
PCTFREE
pctfree / pctused
pctused
PCTUSED
Percentage of Maximum
perfstat
persistent SQL philosophy
PGA
PGA Advisor
PGA cache hit (%)
PGA management
workarea_size_policy
PGA memory management
PGA multi-pass executions
pga_aggregate_target
pga_size.sql
phys_reads.sql
physical disk I/O
physical disk I/O (PIO)
physical I/O waits
physical read waits
physical reads
physical_reads
physical_writes
physpctio.sql query
phywrts
pipe get
PL/SQL lock timer
PL/SQL packages
plan regression
plan9i.sql
pmon timer
predicate pushing
predictive modeling
Predictive modeling
Proactive models
proactive tuning
process events
processor affinity
Processor Queue Length
ProfitLogic
Program Global Area
Program Global Area (PGA)
project
project manager
protocol.ora
prtconf

Q
QIO
QIOSTAT
query_rewrite_enabled
Quest Central

R
RAC
RAC load balancing
RAID 0+1
RAID 1+0
RAID 10
RAID 5
RAID striping
RAID5
RAID-5
RAM
RAM bottleneck
RAM overload
RAM page-ins
RBO
RDBMS
rdbms ipc message
reactive tuning
read waits
read-only tablespaces
read-write head delay
Real Application Cluster (RAC)
Real Application Clusters
Real Application Clusters (RAC)
Real Applications Clusters
rebuild command
recodify_snapshot_settings
Recommendations Options
Recycle buffer cache
recycle pool
RECYCLE pool
redo allocation latch
Redo log buffer
remove_window_group_member
repeat
report_plans
report_tuning_task
retention
right outer join
rollback segments
Rotational delay
row lock waits
ROWID values
rpt_bhr_all.sql
rpt_bhr_awr_hr.sql
RRDtool
RRDTool
run_job
run_vmstat.ksh
runqueue value
runqueue values

S
SAME
SAME (Stripe and Mirror Everywhere)
sample_size
sample_time
sar
SAR
SAS
scalar subqueries
scale out
scale out approach
scale up
scale up approach
scattered disk reads
Schedule Advisor
schema
SDU
Segment Advisor
segment space management auto
segment statistics
select
select clause
select part (query)
select statement
sequential disk reads
server alerts
server consolidation
server metrics
Server RAM paging
server run queue waits
service statistics
sess_waits_ash.sql
session data unit
session events
Session ID (SID)
session_cached_cursors
session_id
session_state
set events
set_attribute
setall
SGA
SGA sizing
SGA summary tables
sga_max_size
sga_target
sharable_mem_th
shared pool
shared pool advice
Shared Pool Free (%)
shared pool size
shared_pool
shared_pool_size
short duration metrics
show parameters buffer
show sga
shrink space
signature
signature analysis
Signature analysis
signatures
Significance Level
skewonly
slave wait
smon timer
SMP servers
snap_level
snap_time
snapdeltafileio_awr.sql
snapfileio_10g.sql
snapshot set
snapshots
soft parse
soft parses
Solid-state Disk (SSD)
Solid-State Disk (SSD)
sort_area_retained_size
sort_area_size
sp*.sql
SPA
space management blocks
space_alloc
space_usage
spauto.sql
spcpkg.sql
spcreate.sql
spctab.sql
spcusr.sql
spdoc.txt
spdrop.sql
spdtab.sql
spdusr.sql
spfile
sppurge.sql
sprepcon.sql
sprepins.sql
spreport.sql
sprepsql.sql
sprsqins.sql
SPSS
sptrunc.sql
spuexp.par
SQL
SQL Access Advisor
SQL Advisor
SQL details
SQL Performance Advisor
SQL Performance Analyzer
SQL Performance Analyzer (SPA)
SQL Plan Manager (SPM)
SQL profile
SQL profiles
SQL Profiles
SQL query
SQL Response Time (%)
SQL Scripts

9icartcount.sql
9icartsql.sql
9ilarge_scanusers.sql
9iltabscan.sql
9iplanstats.sql
9itabscan.sql
9iunused_indx.sql
archhist.sql
bgact.sql
buffutl.sql
bufobjwaits.sql
cacheobjcnt.sql
cartsession.sql
cartsql.sql
csesswaits.sql
curriosql.sql
currlwaits.sql
datafileae.sql
dictdet.sql
fullsql.sql
globaccpatt.sql
globiostats.sql
largescan9i.sql
latchdet.sql
libdet.sql
libobj.sql
libwait.sql
lockcnt.sql
maxext7.sql
memhog.sql
memsnap.sql
objdef.sql
objwait.sql
physpctio.sql
poolhit.sql
scatwait.sql
sesshitrate.sql
sesswaits.sql
sgasize.sql
sortdet.sql
sortusage.sql
sqlhitrate.sql
syswaits.sql
top9isql.sql
topiousers.sql
topsess.sql
topsessdet.sql
toptables.sql
totpctio.sql
totuserspace.sql
tsfrag.sql
userscans.sql
SQL Server
SQL Snapshots
SQL statement
SQL tuning
SQL Tuning
SQL Tuning Advisor
SQL Tuning Set
SQL Tuning Set (STS)
SQL Tuning Sets
SQL Tuning Sets (STS)
SQL*DBA
SQL*Loader
SQL*Net
SQL*Net break/reset to client
SQL*Net message from client
SQL*Net message to client
SQL*Net more data to client
SQL*Net wait events
SQL*Net waits
SQL*Plus
SQL*Trace
sql_id
sql_trace
SQLAccess
sqlnet.ora
SQLTuning
sqltxplain
SSD
stacked views
standalone database
STAR query
STAR schema
star_query
star_transformation
star_transformation_enabled
start_capture
statistcis_level
statistician
statistics_level
stats$bg_event_summary
stats$buffer_pool_statistics
stats$buffer_pool_statistics table
stats$buffered_queues
stats$buffered_subscribers
stats$event_histogram
stats$filestatxs
stats$latch
stats$latch_children
stats$librarycache
stats$propagation_receiver
stats$propagation_sender
stats$rowcache_summary
stats$rule_set
stats$sesstat table
stats$sgastat
stats$snapshot
stats$sql_summary
stats$statspack_parameter
stats$streams_apply_sum
stats$streams_capture
stats$sysstat
stats$system_event
stats$waitstat
statscre.sql
statsctab.sql
STATSPACK
STATSPACK report
STATSPACK reports
STATSPACK utility
statspack.snap
STATSPACK/AWR report
stop_job
Storage Area Networks (SAN)
Stripe and Mirror Everywhere
STS
suboptimal SQL
subquery
Support Vector Machines
swap disk
symmetric multiprocessing (SMP)
sys.aux_stat$
sys.v_$sysstat
sys.v_$system_event
System Global Area
System Global Area (SGA)
system latches
System Response Time
system tables
syswaits.sql script

T
table fetch continued row
table join
table join order
table scan
table scans (long tables)
Tablespace fragmentation
tablespace partitions
TAF
tch column 865, 890
tcp.nodelay 665, 666, 667
TDU
throughput metrics
tim column
time model approach
time model statistics
Time Model Statistics report
time series trend charts
time series tuning
time_waited
time-based wait tuning
timed_statistics
time-series tuning
TKPROF
tnsnames.ora
top
top 5 timed events
top five timed event
top five wait events
top SQL
Top SQL screen
top-down approach
topiousers.sql query
toptables.sql query
Total Cost of Ownership (TCO)
totpctio.sql query
touch count
trace events
trace file
trace report
transaction tables
Transparent Application Failover (TAF)
Transparent Network Substrate
trend-based reconfiguration
trend-based tools
Tuning Advisors
tuning history

U
undo segment statistics
undo segments
UNDO tablespace
UNION
UNIX commands
update statement
updates
use_current_session
user_dump_dest
using clause
utlbstat
utlestat

V
v$ views
v$active_sess_hist
v$active_session_history
v$bh
v$buffer_pool_statistics
v$db_cache_advice
v$enqueue_statistics
v$event_histogram
v$event_name
v$eventmetric
v$file_histogram
v$filemetric
v$filemetric_history
v$latch
v$librarycache
v$metric_name
v$metricgroup
v$metricname
v$osstat
v$pga_target_advice
v$rowcache
v$segment_statistics
v$segstat
v$segstat_name
v$servicemetric
v$servicemetric_history
v$services
v$sess_time_model
v$session
v$session_event
v$session_wait
v$session_wait_class
v$session_wait_history
v$sessmetric
v$shared_pool_advice
v$sql
v$sql_plan
v$sql_shared_cursor
v$sqlarea
v$statname
v$sys_time_model
v$sysmetric
v$sysmetric_history
v$sysmetric_summary
v$sysstat
v$sysstat value
v$system_event
v$system_wait_class
v$temp_histogram
v$waitclassmetric
v$waitclassmetric_history
v$waitstat
v_$sql_plan
varchar
varyio
version_count_th
very large database (VLDB)
very large databases (VLDB)
view
Views
v$sessstat view
v$sql_plan
v$sql_plan_statistics
v$sqlarea
v$sysstat
v_$sql_plan
virtual circuit status
Virtual Memory
Virtual Memory (VM)
vmstat
vmstat capture
vmstat utility

W
wait event
wait event histogram
Wait Event Interface
wait event tuning
wait events
wait_class
wait_count
wait_time
wait_time_detail
wait_time_detail.sql
where clause
WHERE clause
window_list
window_name
Windows Performance Manager
WISE tool
with clause
workarea_size_policy
Workload analysis
workload statistics
wrh$ tables
wrh$ views
wrh$_active_session_history
wrh$_active_session_history_bl
wrh$_bg_event_summary
wrh$_buffer_pool_statistics
wrh$_event_name
wrh$_filemetric_history
wrh$_filestatxs
wrh$_latch
wrh$_latch_children
wrh$_librarycache
wrh$_metric_name
wrh$_rowcache_summary
wrh$_sessmetric_history
wrh$_sgastat
wrh$_sql_summary
wrh$_sys_time_model
wrh$_sys_time_model_bl
wrh$_sysmetric_history
wrh$_sysmetric_summary
wrh$_sysstat
wrh$_sysstat_bl
wrh$_system_event
wrh$_system_event_bl
wrh$_waitclassmetric_history
wrh$_waitstat
wrh$_waitstat_bl
wri$ tables
wri$_alert_history
wri$_dbu_feature_metadata
wri$_dbu_feature_usage
wri$_dbu_usage_sample
wrm$ tables
wrm$_snapshot

X
x$bh
x$bh.tch
x$kcbcbh
x$kcbrbh


 


Reviews:

 Targeted at Oracle professionals who need fast and accurate working examples of complex issues, Oracle In-focus books target specific areas of Oracle technology in a concise manner. Plenty of working code is provided without a lot of theory, allowing database managers to solve their problems quickly without reviewing data that they already know. All code scripts are available for instant download from a companion web site. - Hai Lua




Errata for fourth printing:

The cursor_sharing=similar option has been deprecated in Oracle 11g and will be removed in version 12 per MOSC Note 1169017.


 Errata for second printing:


Page 727:

It might be appropriate to rebuild an index if the block gets per access is greater than five, since excessive block get can indicate a b-tree structure.   Another rebuild condition would be cases where deleted leaf blocks comprise more than 20% of the index structure.

Change to:

While there is no formula that can predict when an index will benefit from rebuilding, some experts will rebuild indexes when deleted leaf blocks comprise more than 20% of the index structure.



Page 727:

The DBA can run the following script during SAP system downtime

Change to:

The DBA can run the following script during Oracle system downtime

Errata for first printing:

Page 346-348

Format error

 

                                                              NoWait              Waiter

Latch Name               Where                                Misses     Sleeps   Sleeps

------------------------ -------------------------------- ---------- ---------- --------

archive process latch    kcrrpa                                    0          1        0

cache buffers chains     kcbgtcr: kslbegin excl                    0        187      157

cache buffers chains     kcbrls: kslbegin                          0         67      104

cache buffers chains     kcbgcur: kslbegin                         0         13       10

cache buffers chains     kcbgtcr: fast path                        0         12       28

cache buffers chains     kcbget: pin buffer                        0          9        6

cache buffers chains     kcbzgb: scan from tail. nowait            0          7        0

cache buffers chains     kcbget: exchange                          0          6        2

cache buffers chains     kcbzib: multi-block read: nowait          0          6        0

cache buffers chains     kcbzwb                                    0          3        2

cache buffers chains     kcbchg: kslbegin: bufs not pinne          0          2        3

cache buffers chains     kcbchg: kslbegin: call CR func            0          2        1

cache buffers chains     kcbzib: finish free bufs                  0          2        4

cache buffers chains     kcbnew                                    0          2        0

cache buffers chains     kcbget: exchange rls                      0          1        2

enqueue hash chains      ksqrcl                                    0         19        2

enqueue hash chains      ksqgtl3                                   0          1       18

enqueues                 ksqdel                                    0         21        6

enqueues                 ksqgel: create enqueue                    0         12       26

library cache            kglpnc: child                             0        522      593

library cache            kglpin: child: heap processing            0        141        1

library cache            kglupc: child                             0        122      211

library cache            kglpndl: child: before processin          0        110      114

library cache            kglhdgc: child:                           0        106        3

library cache            kgllkdl: child: cleanup                   0         45        2

library cache            kglpnp: child                             0         43      155

library cache            kglpndl: child: after processing          0         32        0

library cache            kglobpn: child:                           0         22        0

library cache            kglhdgn: child:                           0         21       65

library cache            kglic                                     0          9       16

library cache            kgldte: child 0                           0          4       17

library cache            kgldti: 2child                            0          1        0

library cache            kglpin                                    0          1        3

library cache pin        kglupc                                    0          1        0

redo allocation          kcrfwr                                    0         21       22

redo allocation          kcrfwi: more space                        0          1        1

redo allocation          kcrfwr: redo allocation 0                 0          1        0

resmgr group change latc kskincrstat1                              0         24       24

session allocation       ksufap: active sessions                   0         12        0

session idle bit         ksupuc: clear busy                        0         10        6

session idle bit         ksupuc: set busy                          0          3        7

shared pool              kghalo                                    0         16        2

shared pool              kghupr1                                   0          7       25

shared pool              kghfrunp: alloc: wait                     0          6        0

shared pool              kghfrunp: clatch: nowait                  0          4        0

shared pool              kghfrunp: clatch: wait                    0          2        3


Page 476

Format error

Dialect

Run Queue Column

Page-In Column

Page-Out Column

User Column

HP/UX

1

8

9

16

AIX

1

6

7

14

Solaris

1

8

9

20

Linux

1

8

9

14

 

System Column

Idle Column

Wait Column

 

HP/UX

17

18

NA

 

AIX

15

16

17

 

Solaris

21

22

NA

 

Linux

15

16

NA

 

 

 

 

 

 

 

 

Table 12.1:  Differences in vmstat Data by Operating System


Page 477

Format error

HP/UX vmstat columns:

cat /tmp/msg$$|sed 1,3d |\

awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9, $16, $17, $18) }' |\

while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU IDLE_CPU

IBM AIX vmstat columns:

cat /tmp/msg$$|sed 1,3d |\

awk '{ printf("%s %s %s %s %s %s\n", $1, $6, $7, $14, $15, $16, $17) }' |\

while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU IDLE_CPU WAIT_CPU

Sun Solaris vmstat columns:

cat /tmp/msg$$|sed 1,3d |\

awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9, $20, $21, $22) }' |\

while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU IDLE_CPU

Linux vmstat columns:

cat /tmp/msg$$|sed 1,3d |\

awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9, $14, $15, $16) }' |\

while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU IDLE_CPU


Page 541:  "network RAM and CPU speeds"  should read "Network, RAM and CPU speeds".


Page 605

Format error

 

In the example below, because we have lots of RAM defined in PGA (more than 5% of pga_aggregate_target), the standard full join is replaced by a hash full join outer operation:

 

 


with

   emp

as

(

   select

      'joel' ename,

      40 deptno

   from

      dual

   union all

   select

      'mary' ename,

      50 deptno

   from

      dual

)

select

   e.ename,

   d.dname

from

   emp e

full join

   dept d

using

(

   deptno

);

 

ENAM DNAME

---- --------------

JOEL OPERATIONS

MARY

     SALES

     RESEARCH

     ACCOUNTING

 

----------------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          |     8 |   120 |     8  (13)| 00:00:01 |

|   1 |  VIEW                 | VW_FOJ_0 |     8 |   120 |     8  (13)| 00:00:01 |

|*  2 |   HASH JOIN FULL OUTER|          |     8 |   176 |     8  (13)| 00:00:01 |

|   3 |    VIEW               |          |     2 |    18 |     4   (0)| 00:00:01 |

|   4 |     UNION-ALL         |          |       |       |            |          |

|   5 |      FAST DUAL        |          |     1 |       |     2   (0)| 00:00:01 |

|   6 |      FAST DUAL        |          |     1 |       |     2   (0)| 00:00:01 |

|   7 |    TABLE ACCESS FULL  | DEPT     |     4 |    52 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------


Page 885:  The table name should be sys.aux_stats$ and the disk read timings should be expressed in milliseconds

The gathered statistics are captured via the dbms_stats package (in 9.2 and above) and CPU statistics are captured automatically in 10g and stored in the sys.aux_stats$ view.

·         sreadtim:  Single block disk read time (in milliseconds)

·         mreadtim:  Multiblock disk read-time (in milliseconds)

·         cpuspeed:  CPU speed in MHz

·         mbrc:  average db_file_multiblock_read_count in number of blocks

·         maxthr:  Maximum I/O throughput (only used with Oracle parallel query)

·         slavethr:  Oracle parallel query slave (factotum) throughput


Page 923

Format error

 

n  The numbers of blocks in the table: Small tables are accessed faster with a full scan and forcing index usage may hurt performance.
 

n  System statistics: the dbms_stats.gather_system_stats procedure measures external timing for index access (sequential reads) and full-scan access (scattered reads).  If Oracle sees expensive index disk reads, it may ignore an index.

n  System parameters: There are several initialization parameters that can impact the propensity of the optimizer to choose an index:

o    db_file_multiblock_read_count:  Prior to Oracle 10.2 (when this setting became automatic), this parameter helped govern the relative costs of full-scan vs. index costs.

o    sort_area_size (if not using pga_aggregate_target):  The sort_area_size influences the CBO when deciding whether to perform an index access or a sort of the result set. The higher the value for sort_area_size, the more likely that a sort will be performed in RAM, and the more likely that the CBO will favor a sort over presorted index retrieval.
 

n  Optimizer parameter values: You can adjust several optimizer parameters to force Oracle to use an index:

 

o    optimizer_mode: The all_rows access method often favors a parallel full-table scan over an index scan. The first_rows_n optimizer_mode will often stop Oracle from ignoring an index because it favors index access over computing resources.

o    optimizer_index_cost_adj: This parameter alters the costing algorithm for access paths involving indexes. The smaller the value, the lower the cost of index access.

o    Optimizer_index_caching The optimizer_index_caching parameter is set by the DBA to help the optimizer know, on average, how much of an index resides inside the data buffer.  The setting for optimizer_index_caching affects the CBO’s decision to use an index for a table join (nested loops) or to favor a full-table scan.

For an example of how parameters can effect index usage, a developer may be tempted to switch to the all_rows optimizer_mode (or add an all_rows hint to their SQL), believing that it will improve throughput, while not realizing that all_rows favors full-scans over index usage.

 

In a similar example, some developers will mistakenly add a parallel hint to a SQL statement to improve performance, not realizing that Oracle parallel query only works with full-scans.


Page 656

Typo in code:

select
   p1 "File #".
   p2 "Block #",
   p3 "Reason Code"
from
   v$session_wait
where
   event = 'buffer busy waits';

Should be:

select
   p1 "File #",
   p2 "Block #",
   p3 "Reason Code"
from
   v$session_wait
where
   event = 'buffer busy waits';


 

 
 
 
 

Oracle tuning            oracle tuning book

 

Hit Counter

 

   

 Copyright © 1996 -2011 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks