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-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)  
       
Key Features   About the Authors Reader Comments
Table of Contents   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 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.

 
Key Features

* 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.

 

Free Oracle Tips

HTML Text

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.



   

 Copyright © 1996 -2017 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