|Library of Congress
Perfect bind 9x7
||Oracle In-Focus Series #
Creating a Self-Tuning
Dynamic SGA Performance
Donald K. Burleson
Retail Price $16.95
|Order now for 40% and
get immediate access to the
Oracle Tuning Library
Save $100 - All four books for only $118.95
Oracle has become one
of the world’s most complex databases, and this book is for the
senior Oracle DBA who needs to automate the complex mechanisms
that govern the RAM memory regions of any Oracle database.
The dynamic memory
features of Oracle make it possible to create a self-tuning
database. This exciting book explores proven techniques for
monitoring the behavior of the Oracle System Global Area (SGA) and
shows proven techniques that can be used to anticipate upcoming
problems and adjust the SGA before a performance problem occurs.
Focusing on proactive
tuning and scripting, this books show you how to collect
historical data and use it to develop signatures for all memory
areas. Using these metrics, you can develop scripts that will
anticipate and correct upcoming SGA performance problems.
Presents a method for monitoring
important SGA metrics over long time periods.
* Lists the commands to modify the
Oracle9i SGA areas, including pga_aggregate_target, shared_pool
* Describes how to recognize patterns
in RAM usage.
* Explores the use of the free STATSPACK utility to monitor and collect SGA information.
* Offers sample scripts for
automating SGA tuning.
* Provides access
to an online code deport, full of interesting Oracle scripts.
About the Author:
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.
Table of Contents:
Chapter 1 - Overview of the
Oracle9i SGA Regions
RAM Allocation at Oracle Instance
Oracle SGA parameters
RAM and the Oracle Server
RAM on IBM-AIX UNIX
RAM in Linux
RAM on MS-Windows
RAM used by Oracle Connections
Chapter 2 -
Oracle9i Self-tuning basics
Monitoring the data buffers
The v$sysstat View
The v$pgastat View
The v$process view
The v$workarea view
Chapter 3 -
Oracle9i Data Buffer Internals
Tuning the Oracle9i data buffer Pools
Data Block Caching in the SGA
Full table Caching in Oracle9i
The Data Buffer Hit Ratio
Data Buffer Monitoring
Allocating Oracle9i data buffer caches
Internals of the Oracle Data Buffers
Finding Hot Blocks inside the Oracle Data Buffers
Full Data Caching Techniques
Data Buffer Monitoring with STATSPACK
Understand the KEEP and RECYCLE Pools
STATSPACK Tables and KEEP Pool Data
Advanced KEEP Pool Candidate Identification
Advanced RECYCLE Pool Tuning
Large Blocks and Oracle Indexes
Maximizing Oracle9i block space usage
Summary of block size rules
Mapping data blocks to data buffers
Chapter 4 -
Proactive SGA Reconfiguration
Scheduling SGA reconfiguration
Trend-based Oracle Reconfiguration
Plotting the Data Buffer Hit Ratio by Hour of the Day
Approaches to Self-tuning Oracle9i Databases
Create Table As Select(CTAS)
data buffer hit ratio
Data Buffer Hit Ratio Alert
Data Buffer Sizes
Data Dictionary Miss Ratio Alert
Data Dictionary Object Alert
Database Writer Contention Alert
Library Cache Misses Alert
Oracle database connections RAM
Oracle SGA RAM
PGA memory management
Program Global Area (PGA)
Program Global Area (PGA)
Redo Log Space Requests Alert
Data Buffer Caches
show parameters buffer
System Waits Alert
Transient data blocks
x$bh internal view
Ted from the Bay Area of California says:
This was very enjoyable read and I'm very happy to see that many
of the details about using the Oracle9i dynamic SGA are revealed. The problem
has always been that the Oracle documentation tells you about the new features
yet fail to tell you about how to apply them. After reading this book I was able to understand the
automatic-tuning methods in Oracle10g and they made sense because the
author revealed some of the mechanisms that they use. The online scripts
are very nice also, with many I had never seen before.
Rod Fredette, Sr. Database Administrator, Albertsons,
This has been the most enlightening book I have ever read.
The concise nature of the text gives me the chance to consume a
large amount of new information in much less time. Thank you ...
Mozahid Rabbani says:
Creating a Self Tuning Oracle Database' is a great book
that helped me a lot.
A reader from England says:
I was very impressed by the Plain English renditions in this book. It is
high structured and well organized with scripts to illustrate each concept. It
is very useful for multiple blocks in Oracle9i and concise and informative.
More Reader Comments:
Just wanted to say that Don's book "Creating a Self-Tuning Oracle Database" is,
without any doubt, the most important Oracle book I have ever bought!!!!
It's just a fantastic book! For me, it's not just the great scripts, the
easy-to-understand style, or the handy code deport that make this book so
marvellous, but that it has inspired me to roll up my sleeves and dig into the
database, so to speak, and find stuff out for myself.
This book is worth its weight in gold!
Page 1: Misleading use of malloc() statement:
When an Oracle database is started, the Oracle executable issues the
malloc( ) command to create a region of RAM memory. The SGA is
commonly called the Oracle region because it is a region of RAM memory
on the database server RAM heap.
When an Oracle database is started, Oracle
allocates a region of RAM memory called the System Global Area (SGA).
The SGA is commonly called the Oracle “region” because it is a region of
RAM memory on the database server RAM heap used for caching of data, SQL
and miscellaneous other functions.
Page 11: Incorrect Bullet List:
The sort_area_size and hash_area_size parameters are incorrectly
listed in the SGA parameter list.
If you are not using the pga_aggregate_target
feature or multi-threaded server, then Oracle will allocate space in the
Program Global Area (PGA) using these parameters:
sort_area_size - This parameter determines
the memory region that is allocated for in-memory sorting. When the
stats$sysstat value sorts (disk) becomes excessive, you may want to
allocate additional memory.
hash_area_size - This
parameter determines the memory region reserved for hash joins.
Starting with Oracle9i, Oracle Corporation does not recommend using
hash_area_size unless the instance is configured with the shared
server option. Oracle recommends that you enable automatic sizing of
SQL work areas by setting pga_aggregate_target and hash_area_size
is retained only for backward compatibility purposes.
Page 14: version-specific clarification:
Prior to Oracle9i, the KEEP and RECYCLE pools
were a sub-set of the DEFAULT pool. The DEFAULT pool
is created by the db_cache_size parameter in Oracle9i, while former versions use the
db_block_buffers parameter. Starting with Oracle9i, the KEEP and
RECYCLE pools are allocated in addition to the db_cache_size.
Also, note that Oracle9i allows the use of multiple block regions,
ranging in size from 2K-32K, each with its own distinct buffer cache.
Page 78: Version-specific statement clarification:
Note: The KEEP and RECYCLE pools were a sub-pool of
db_block_buffers in Oracle8i, but starting with Oracle9i Database
db_keep_cache_size and db_recycle_cache_size became a
separate RAM area from db_cache_size.