|
ISBN
0-9727513-2-7
ISBN 13
978-0972751322 |
Library of Congress
Number: 2003090827 |
|
200 pages
Perfect bind 9x7 |
PD 303 |
|
Shelving
Databases/Oracle |
Oracle In-Focus Series #
3 |
|
|
Creating a Self-Tuning
Oracle Database
Automating Oracle
Dynamic SGA Performance
by
Donald K. Burleson
Retail Price $16.95
/ £10.95
| Order now for 40% and
get immediate access to the
code depot! |
Only $9.95
(40%
off) |
 |
Get 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
and db_cache_size.
* 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:
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.
Table of Contents:
Chapter 1 - Overview of the
Oracle9i SGA Regions
RAM Allocation at Oracle Instance
Startup
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
Oracle9i Sorting
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
|
|
|
Index:
|
_
_db_percent_hot_default
_db_percent_hot_keep
_db_percent_hot_recycle
A
alter system
commands
B
BLOB
buffer_pool_keep
C
chained/migrated rows
CLOB
Create Table As Select(CTAS)
D
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
db_2k_cache_size
db_aging_touch_time
db_cache_size
DEFAULT pool
KEEP pool
RECYCLE pool
DEFAULT pool
H
hash_area_size
K
KEEP pool
L
large_pool_size
Library Cache Misses Alert
O
Online code
depot
optimizer_index_cost_adj
optimizer_index_cost_adj
Oracle database connections RAM
Oracle SGA RAM
OS
Reserved RAM
P
PGA
management
pga_aggregate_target
v$pgastat
v$process
v$sql_plan
v$sysstat
v$workarea
v$workarea_active
workarea_size_policy
PGA memory management
pga_aggregate_target
Program Global Area (PGA)
Hash_area_size
Sort_area_size
Program Global Area (PGA)
Cursor
state
OS Overhead
Session information
Sort area
Stack space
|
R
RECYCLE
pool
Redo Log Space Requests Alert
S
SGA
parameters
buffer_pool_keep
buffer_pool_recycle
db_16k_cache_size
db_2k_cache_size
db_32k_cache_size
db_4k_cache_size
db_8k_cache_size
db_cache_size
hash_area_size
large_pool_size
log_buffer
sga_max_size
shared_pool_size
sort_area_size
SGA Regions
Data Buffer Caches
Log Buffer
Shared
Pool
Shared Pool
Contention Alert
shared_pool_size
Shared_pool_size
show parameters buffer
show sga
sort_area_size
stats$buffer_pool_statistics
stats$buffer_pool_statistics table
stats$sql_summary table
stats$sysstat
STATSPACK
STATSPACK utility
stats$ sql_summary
stats$buffer_pool_statistics
swap disk
System Waits Alert
T
The Data
Buffer Caches
The
Shared Pool
Dictionary cache
Library cache
Session information
v$session view
Transient data blocks
V
v$db_cache_advice
v$db_cache_advice view
v$pgastat View
v$process view
v$sql_plan view
v$sysstat View
v$system_event
v$workarea view
X
x$bh internal view
x$bh view
|
|
Reader Comments:
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,
Inc. says:
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!
Kind Regards,
Richard Armstrong-Finnerty
GE Life
Oracle DBA
Errata:
Page 1: Misleading use of malloc() statement:
Old text:
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.
New Text:
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.
Revised Text:
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.
