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-9823061-3-X
ISBN 13:
978-0-9823061-3-0
Library of Congress Number: 2014903997
828 pages
Perfect Bind -  7.5x9.25
PD 214
Shelving: Database/Oracle Oracle in-Focus Series:  # 45

  Oracle Tuning
The Definitive Reference
Third 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 Author Table of Contents
Index Reader Comments Errata
     
Get the Oracle Tuning Library
Save $100 - All four books for only $118.95 
Oracle Tuning: The Definitive Reference 3rd Edition $69.95
Advanced Oracle SQL Tuning: The Definitive Reference $59.95
Oracle Performance Troubleshooting 2nd Ed $34.95
Oracle Tuning Powerscripts $37.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

Are you ready to tune?

Oracle Script Collection

Conventions Used in this Book

Acknowledgements

Preface

Chapter 1: Introduction to Oracle Tuning

Why Hardware Technology Drives Database Technology

How economic trends change Oracle DBA duties

Oracle Tuning and Server Consolidation

Summary

An Introduction to Oracle Tuning

Maximizing Throughput vs. Minimizing Response Time

Top-down Tuning vs. Bottom-up Tuning

Proactive Tuning vs. Reactive Tuning

Reactive Oracle Tuning treats the symptoms, not the disease

Proactive Oracle Tuning anticipates pending performance problems

Knowing your Limits:  Oracle problems that cannot be tuned

Oracle Application-level Tuning

The Causes of Poor Oracle Performance

The Oracle Tuning Hierarchy

External Hardware Performance Review

Finding Database Bottlenecks

Oracle Instance Tuning

Oracle SQL Tuning

Conclusion

Chapter 2: Time Series Tuning for Oracle

Managing the Complexity of Oracle

Waste not, want not

The Pros and Cons of Time Series Tuning

The Trumpet of Doom

Oracle’s Predictive Models

Data Quality and Predictive Validity

Sampling Intervals and Data Quality

Finding Data Processing Signatures

Finding repetition in workloads

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 a 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?

AWR Licensing Options

Tracking your Oracle Option Usage

Capturing Time Series Metrics

Customized AWR Tuning Reports

Exception Threshold Reporting

Trend Identification with the AWR

Conclusion

Chapter 3: The Time Model Tuning Approach

Inside the Oracle Time Model Views

Displaying session details with time model data

Real-time Reporting

Oracle 12c ASH Analytics

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 of Oracle tuning 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: Inside Oracle SQL Tuning

Introduction to Oracle SQL

What is your ideal optimizer_mode?

Holistic SQL Tuning

Understanding SQL Tuning

Holistic Oracle SQL Tuning

Best Practices for SQL Optimization

Is your SQL workload static or dynamic?

Successful SQL Tuning Methods

The Static Database Workload

The Dynamic Database Workload

Goals of SQL Tuning

Determine Optimal Table Join Order

Optimal Table Join Order and Transitive Closure

Remove Unnecessary Large-table Full-table Scans

Locating full-scan operations

Tuning large-table full-table scans

Tuning small-table full-table scans

Cache Small-table Full-table Scans

Verify Optimal Index Usage

Verify Optimal Join Techniques

Evaluating Oracle Physical Join Performance

Tuning by Simplifying SQL Syntax

Roadblocks to SQL Tuning

The Library Cache and Oracle SQL Performance

Optimizing SQL RAM Resources

Using cursor_sharing=force

Using Adaptive Cursor Sharing

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

Views and join elimination

Interrogating SQL Execution Plans

Oracle Tuning with Hints

When hints appear to be ignored

Tuning with the rule hint

Why the rule Hint is still popular

SQL Tuning with the ordered Hint

SQL Tuning with the ordered_predicates Hint

How is SQL join cardinality estimated?

Tuning with the cardinality hint

Oracle12c temporary table enhancements

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

High Impact Techniques (over 20% faster):

Low Impact Techniques (between 5% and 20% faster)

Using Bulking for Delete Performance

Oracle Update Tuning

High impact techniques (over 20% faster):

Low-impact techniques (between 5% and 20% faster):

CTAS vs. SQL Update statements

Bulking SQL Updates

Bulking SQL Inserts

Tuning with SQL Profiles

Tuning When the SQL Cannot Be Touched

Swapping execution plans

Swapping SQL Profiles

Towards Automated SQL Tuning

Conclusion

Chapter 6: Inside STATSPACK and AWR

Introduction to Tuning with STATSPACK and AWR

STATSPACK vs. AWR

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

Statistic Management in AWR and STATSPACK

Managing STATSPACK

The snap_level Parameter

The session_id

The num_sql

What SQL is Captured?

STATSPACK SQL Collection Thresholds

AWR SQL Collection Thresholds

Purging from AWR

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

Generating a STATSPACK Report

Create the 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 Ratios

Top 5 Timed Foreground Events

CPU and memory section

About load averages

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 STATSPACK / AWR Report

PGA Section

Process Memory Summary Section

Enqueue Statistics

Rollback Segments/UNDO Logs

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: Tuning with Oracle Optimizer Statistics

Oracle SQL Optimizer Statistics

The Oracle dbms_stats Package

Oracle 12c Improved statistics

Managing Schema Statistics with dbms_stats

Column Skew and histograms

Automating Histogram Sampling with dbms_stats

Oracle extended statistics

Functions vs. expressions

Hybrid histograms:  Expression-based data relationships

Using dbms_stats.create_extended_stats

Queries on extended statistics

Reanalyzing Optimizer Statistics

Oracle System Statistics

External Costing with the Optimizer

Using Dynamic Sampling

Sampling Table Scans

Oracle12c adaptive execution plans

Maintaining a Metadata Infrastructure

Conclusion

Chapter 10: Monitoring Oracle SQL

Identifying Problem SQL

Find the Problem Sessions

AWR and SQL Tuning

Viewing Table and Index Access with AWR

Tracing SQL Execution History

The full hash join

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

Oracle 12c Real-time ADDM

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

Understanding the load average

OS delta report

The Oracle OS Watcher utility

Starting Oracle OSWatcher

Oracle RAM Tuning

Memory Limits

File Caching

RAM swapping

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

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

Manual File Placement

Oracle 12c Intelligent File Placement

ASM File Striping

File Segregation

Oracle redo log I/O

RAID Technology and Oracle

RAID 5 is Not for Every Database

Oracle and Direct I/O

Enabling Oracle Direct I/O

Calibrating your disk I/O

Monitoring External Disk I/O

Capturing external iostat Information

Generating iostat Reports

Generating ASM disk 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

Setting the db_block_size with Multiple Blocksizes

Allocating Objects into Multiple Block Buffers

Oracle Blocksize & Index I/O

The Latest Consensus on Using Multiple Blocksizes

Vendor Notes on Oracle Multiple Blocksizes

Reducing Disk I/O with SSD

SSD Vendors for Oracle

Oracle Disk Monitoring

Examining Global I/O

Tracking I/O for Specific Tables

Measuring Disk I/O Speed

Time Series Monitoring of logical I/O

Monitoring Disk I/O with AWR

Conclusion

Chapter 14: Oracle Instance Tuning

Inside Instance Tuning

Tune the Instance Before Tuning SQL

Automatic Memory Management

Manual RAM allocation vs. AMM

Sizing the Oracle SGA and PGA Regions

Viewing Server RAM Resources

Viewing RAM  on Solaris

Viewing Oracle RAM on IBM-AIX UNIX

Sizing your SGA

SGA Sizing on a Dedicated Server

Using AMM

RAM and Virtual Memory for Oracle

Finding the High Water Mark of Oracle User Connections

Determining the Optimal PGA Size

A Script for Estimating Total PGA RAM

Optimizing pga_aggregate_target

Rules for adjusting

The memory_max_target and memory_target parameters

Important caveats in PGA management

Sizing your PGA for hash joins

Sizing the PGA for Batch Processing

Supersizing the PGA

A case study RAM hash joins

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

SQL Optimizer Parameters

Instance Wait Event Tuning

Tuning the Oracle Data Buffer Pools

Data Buffer Cache Hidden Parameters

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 Eight 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

Oracle 12c flash_cache Internals

Flash cache; The new data buffer in town

The Downside of Mega Data Buffers

Allocating Oracle Objects into Multiple RAM data Buffers

Automating KEEP Pool Assignment

Sizing the KEEP Pool

Oracle 12c flash cache

Enabling the flash_cache

Tuning the RECYCLE Pool

Large Blocks and Oracle Instance Caching

Finding Baselines

Learning Instance Tuning from TPC 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 Tuning

Oracle Tablespace Tuning

Inside Oracle Tablespace Tuning

ASSM and Tablespace Performance

Internal Freelist Management

Characteristics of Bitmap Segment Management

New High Watermark Pointers

Extent Control Header Block

Using ASSM with RAC Databases

Potential Performance Issues with ASSM

Detecting Tablespace Fragmentation

Tablespace tuning tips

Conclusion

Chapter 16: Oracle Table Tuning

Oracle table performance

The Freelist Unlink Process

The Issue of pctused

Setting pctfree and pctused

Freelists and segment Performance

Oracle 11g table compression

The overhead of Oracle table compression

Faster SQL with Database Reorganizations

Managing Row Chaining in Oracle

Function-based virtual columns

Features and limitations of 11g virtual columns

A Summary of Object Tuning Rules

Reorganizing Tables for High Performance

Oracle Partitioning and table tuning

Partition-wise joins

Partition pruning

Partition Pruning Internals

Online Reorganization

Tuning SQL Access with clustering_factor

Sorted hash cluster tables

Creating a sorted hash cluster

External Tables and SQL

Defining an External Table

Internals of External Tables

Conclusion

Chapter 17: Tuning with indexes

SQL Tuning with Indexes

Do I have missing 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

Not all Indexes are Used in Range Scans

Why doesn’t Oracle use my index?

When to Rebuild Indexes

Tuning with Index Organized Tables

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

Locating function-based indexes

Conclusions on function-based indexes

Doing case sensitive searches with indexes

SQL Tuning with Oracle*Text Indexes

Oracle Text Index re-synchronization

Testing new Oracle indexes

Testing SQL workloads with invisible indexes

Monitoring index usage

Monitoring for Index Range Scans

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

Dropping un-used indexes

Locating infrequently used indexes

The problem of too many indexes

Determining which indexes to delete

Large Multi-column Indexes

Table Rows and the clustering factor

Index reorganization and SQL Performance

When rebuilding indexes may help SQL performance

When rebuilding indexes might hurt performance

Index behavior and Oracle blocksize

Choosing candidates for index maintenance

Conclusion

Chapter 18: Oracle Troubleshooting

Introduction to Oracle Troubleshooting

What to do with a hung database

Emergency Troubleshooting Methods

Troubleshooting Limitations

Troubleshooting Triage

Accurately Measuring Oracle Performance

Locating Top Resource Sessions

Chapter Conclusion

Book Conclusion

Index

About the Author

 

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:


Errata:


 

 
 
 
 

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