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-4-3
ISBN 13
978-0972751346
Library of Congress Number: 2003105334
306 Pages - perfect bind 9x7
PD 503
Shelving Databases/Oracle
Oracle In-Focus Series  # 5

  Oracle Performance Troubleshooting
with Dictionary Internals SQL & Tuning Scripts

by Robin Schumacher 

Retail Price $27.95 /  £17.95

Order now and get immediate online access to the code depot! Only $19.95
(30% off)
 
       
Key Features   About the Author 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


If you're a DBA who's looking for real world Oracle tuning techniques, Oracle scripts, and advice on how to get to the heart of critical Oracle performance problems, then you've come to the right place. Written by one the world's most widely-read DBAs and Oracle internals experts, Robin Schumacher focuses his incredible knowledge of the Oracle data dictionary into a superb book that show how to quickly troubleshoot and correct Oracle performance problems.

As a noted Oracle expert, Robin Schumacher has written the internals for some of the world's most powerful Oracle performance software and now he shows you how to use the most recent advancements in Oracle8i and 9i to make your Oracle database run as fast as possible.

 

 
Key Features

* Provides a winning performance methodology for use in analyzing any Oracle database

* Demonstrates how to properly use ratio-based and bottleneck analysis

* Offers insight into how to design a fast-running database from the ground up

* Supplies methods for making storage and reorganization problems a thing of the past

* Presents new techniques for monitoring and optimizing memory usage

* Shows how to quickly pinpoint and resolve I/O hotspots at the database, storage and object level

* Presents improved methods for uncovering session-related bottlenecks

* Provides new tips and techniques for locating and fixing problem SQL

* Comes with online code depot with dozens of new performance and monitoring scripts

 

About the Author:

 

Robin Schumacher

  Robin Schumacher serves as the Worldwide Director of Product Management for MySQL, the world's leading open-source database management system.  Robin has over fourteen years experience in database administration, development, monitoring, and tuning with Oracle, DB2, Teradata, Sybase, and Microsoft SQL Server. 

He has authored countless performance-related articles for many database-centric magazines as well as serving as a database software reviewer and feature writer for the likes of Intelligent Enterprise, eWeek, DM Review, and others. 

Table of Contents:

Accurately Measuring Performance

What’s the right way to really measure the overall performance of a database and how is this accomplished when you have a large database farm to manage?  This chapter answers these questions and highlights the critical areas a DBA needs to stay focused on at all times. 

  • Modeling Peak Efficiency
  • Gauging Availability with v$ views
  • Gauging Transaction Speed with v$sqlarea
  • Availability Impact
  • Speed Impact

Correcting Foundational Flaws

Although some may disagree, most database gurus feel that laying the proper physical foundation for your database is the way to get the most performance bang for the buck. This chapter discusses the importance of proper physical design and helps DBAs diagnose physical design performance issues.

  • The Impact of Solid Database Design with v$bh analysis
  • The Number One Contributor to Database Performance
  • The Link Between Design and Performance Tuning with materialized views
  • Boosting Database Performance Through Design
  • How to Pinpoint Database Design Errors with v$sql analysis


 
Chapter 3: Optimizing Storage

Storage-related headaches are often the single biggest contributor to database downtime.  This chapter discusses the best ways to avoid storage problems through proactive planning and also provides fast diagnostic procedures that can be used to eliminate space problems before they reach critical mass.

  • The Contribution of Storage to Performance with RAID
  • Storage Design Considerations
  • Smart Ways to Avoid Database Downtime
  • Critical Storage Headaches to Avoid
  • Removing Storage-related Performance Vampires

Maximizing Oracle SGA Memory

Throwing more memory at a database used to be an acceptable form of performance tuning, but such is not the case any longer.  This chapter shows how to use memory correctly and also provides techniques and scripts to troubleshoot memory-related performance problems the right way.    

  • When Does Less Become More?
  • New Performance-Boosting Memory Options
  • How to Keep Data Where You Want It
  • How to Keep Code Where You Want It
  • Uncovering Memory Hogs

Pinpointing I/O Hotspots

How can a DBA quickly find the I/O bottlenecks in their database and then make them permanently go away?  This chapter outlines a bulletproof roadmap that any DBA can use to get a handle on their database I/O.  

  • Starting with Global Basics
  • Determining Global Object Access Patterns
  • Examining Storage-Level Statistics
  • Uncovering the Hot Database Objects
  • Locating I/O Resource Hogs
  • Miscellaneous I/O Considerations

Eliminating Critical Bottlenecks

A DBA needs to keep their database running effectively at all times, but sometimes this is easier said than done.  This chapter shows how to quickly find and eliminate critical bottlenecks in a database that may be causing painful slowdowns.

  • Using Ratio-based Methods
  • Using Wait-Event Methods
  • Other Bottleneck Identification Techniques
  • The Winning Solution


Uncovering Problem Sessions

Left unchecked, a few rogue users in a database can make life miserable for everyone else.  This chapter provides all the right ammunition a DBA needs to ferret out these problem sessions so that their database is kept secure and running well at all times. 

  • Uncovering Security Holes
  • Finding Storage Hogs
  • Locating Top Resource Sessions
  • Pinpointing Sessions with Problem SQL

Identifying Problem SQL

If it’s true that eighty-percent of a database’s performance is derived from the SQL code running within it, then a DBA has to know how to find and correct the problem SQL in their critical databases.  This chapter provides everything a DBA needs to quickly pinpoint the SQL code in their database that needs critical attention.  

  • Locating the Top SQL Contributors
  • Pinpointing Bad SQL
  • New Techniques for Analyzing SQL Execution
  • SQL Tuning Roadmap

Index:

A
ALL ROWS
ALTER INDEX
alter session
alter system flush shared_pool
ANALYZE
ARCH
autoextend
Automatic segment management

B
badstorage
BLOB
Blocking lock ratio
Bottleneck analysis    
Bubble fragmentation
Buffer busy waits
Buffer cache hit ratio
buffer_pool_keep
buffer_pool_recycle

C
Cardinality
Cartesian joins
Chained row counts       
chained/migrated rows
CLOB
Coalesce
compute statistics
COST
CREATE INDEX
cursor_sharing

D
Data models
db file scattered read
db_block_buffers
db_cache_advice
db_cache_size
db_keep_cache_size
db_nk_cache_size
db_recycle_cache_size
dbms_shared_pool
DBMS_STATS
dbms_utility
dbms_utility.analyze_database
dbms_utility.analyze_schema       
D
BWR
DDL command
Default buffer cache 
d
ictionary-managed tablespace
dispatcher timer
DISTINCT 

E
enqueue
EXPLAIN
extract-transform-load 

F
FIRST
fragmentation
freelists 

G
GROUP BY 

H
hash_area_size
HASH_VALUE
High Water Mark
Honeycomb fragmentation

I
IN    
init.ora  
initrans 
INTERSECT   

J
Java pool  
java_pool_size   
JBOD  

K
Keep buffer cache 

L
Large pool  
large_pool_size  
latch free          
LGWR   
Listener process 
lock element cleanup  
log_buffer    

M
MINUS

N
NOT IN 
Null event           

O
Object extent fragmentation
Object fragmentation
Object-based fragmentation
Oracle Managed Files    
ORDER BY   
ORDERED             

P

PARALLEL 
parallel query dequeue wait
parallel query idle wait - Slaves 
PCTFREE    
PCTUSED     
Performance model
PGA management
pga_aggregate_target   
workarea_size_policy
PGA memory management
pga_aggregate_target
pipe get
PL/SQL lock timer 
pmon timer    

R
RAID   
Ratio-based analysis
rdbms ipc message
Recycle buffer cache  
Redo log buffer
Row counts   
ROWS
RULE

S
SET AUTOTRACE ON 
shared pool  
shared_pool_size
show sga   
slave wait   
smon timer  
sort_area_retained_size
sort_area_size
Space Extents  
Space fragmentation 
spfile  
SQL Scripts
9icartcount.sql
9icartsql.sql 
9iexpl.sql    
9ilarge_scanusers.sql
9iltabscan.sql  
9iplanstats.sql  
9itabscan.sql
9iunused_indx.sql   
archhist.sql   
badstorage.sql  
bgact.sql    
buffratio.sql  
buffutl.sql  
bufobjwaits.sql  
cacheadvice.sql  
cacheobjcnt.sql  
cartsession.sql  
cartsql.sql 

chaintables.sql 
csesswaits.sql
curriosql.sql
currlwaits.sql 
datafileae.sql 
dbagranted.sql 
dffrag.sql  
dictdet.sql 
fileio7.sql   
fileio8plus.sql 
fullsql.sql  
globaccpatt.sql
globiostats.sql  
idxreorg7.sql  
idxreorg8.sql  
largescan9i.sql 
latchdet.sql  
libdet.sql        
libwait.sql     
lockcnt.sql   
maxext7.sql
maxext8.sql
memhog.sql 
memsnap.sql  
objdef.sql
objwait.sql
physpctio.sql 
poolhit.sql 
rolldet.sql 
scatwait.sql
sensprivs.sql
sesshitrate.sql
sesswaits.sql
sgasize.sql
sortdet.sql
sortusage.sql
spacesum7.sql 
spacesum8i.sql 
sqlhitrate.sql 
syswaits.sql  
tabreorg7.sql 
tabreorg8.sql 
top9isql.sql
topiousers.sql
topsess.sql
topsessdet.sql
toptables.sql 
totpctio.sql  
totuserspace.sql
tsfrag.sql 
tsmap7.sql 
tsmap8.sql 
userscans.sql 
SQL*Net break/reset to client   
SQL*Net message from client   
SQL*Net message to client 
SQL*Net more data to client
SQL*Plus  
statistics_level
sys.dbms_space_admin 

T
table fetch continued row
Tablespace fragmentation
timed_statistics   

U
UNDO tablespace 
undo_management  
UNION 
UNION ALL  
unlimited tablespace  

V
Views
v$db_cache_advice  
v$sessstat view  
v$sql_plan  
v$sql_plan_statistics 
v$sqlarea  
v$sysstat   
v_$sql_plan   
x$kcbrbh 
virtual circuit status   

W
workarea_size_policy    
Workload analysis          

Reader Comments:

Christopher Lawson - Noted Oracle expert and author of
The Art & Science of Oracle Performance Tuning

The purpose of this book by Robin Schumacher is to suggest and illustrate a robust, comprehensive performance tuning methodology.

With this methodology in hand, the author then provide examples and scripts to show how this works in practice. Robin points out the importance of proactively monitoring a database, and modeling database performance. He shows the difference between two key ways to approach performance tuning: Ratio based versus Bottleneck Analysis. Each have their place, he shows.

For instance, he notes that Ratios are good for getting a quick "bird's eye" view of the performance. He correctly cautions the reader, in the section entitled "The Dangers of Blanket Ratios," about overly simplistic reliance on simple ratios.

I found the discussion of the V$Sql_Plan view especially helpful. I admit that I have not used this in my own performance troubleshooting. After reading this book, I see that I have really missed out on a powerful tactic! Robin shows how to use this view to best effect. He includes numerous scripts, which are available from the Rampant website (I successfully downloaded all the scripts without a hitch).

For example, in the script "9iCartSql" Robin shows how easy it is to find completed queries that used Cartesian Products as part of the execution plan. He points out that this could be an indication of improper join syntax. In another script, he shows how to get a list of the exact SQL that caused scans of large tables. In another example, he introduces the new view V$Sql_Plan Statistics, and illustrates a way to obtain the exact statistics for each step in the execution plan for all SQL executed on the instance.

Do you get the idea? A lot of good ideas, along with the exact scripts to use. All in all, an impressive volume to add to your DBA "Tool Kit." I found the discussions clear and to the point, and intend to use many of the the numerous, practical scripts provided with this book.

As Mr. Schumacher points out in Chapter 1, "The IT consultants who make the really big money are the ones who can take a lethargic system and quickly turn it into one that runs as fast as greased lightning." Thank you, Robin, for heading us in the right direction!


Donald K. Burleson - Oracle guru and author of 32 books

I have always been an admirer of Robin Schumacher, and this book show his real talent for getting to the heart of any Oracle problem. This is a very practical book, with many novel code scripts, and a online code deport to save typing.

His approach is very practical; and the scripts are ready-to-run and contain explanations so the output is understandable.


Rob Young - Louisville Kentucky

This is the perfect read for my DBA brethren who are confused by Oracle's built-in version specific, performance-related features. Robin Schumacher does an excellent job of boiling down where you could versus where you should concentrate your performance analysis efforts and then provides simple, practical alternatives and methods for diagnosing and correcting headaches familiar to most DBAs. Where many authors limit the scope of such topics to "down-in-the-database" mechanics, Schumacher explores how external factors, such as foundational design and infrastructure, need to be folded into the optimal application/database performance mix.

A must read for anyone serious about managing and optimizing performance across all versions of Oracle!


Matt Brody - Clearwater Florida

This book makes finding those buried nuggets of Oracle's keys to performance optimization very fast and easy. I found the scripts to be an invaluable tool for learning by seeing and a great way to understand what's going on under the covers of the DB engine. It has fast become one of my most used reference books and I highly recommend it.


 Murugu Manjurman - Great Book for troubleshooting

I have always been a fan of Robin Schumacher, and I had to get this book. I was not disappointed. Unlike many of the self-proclaimed guru's, Robin makes all of the material clear and simple. I really liked the online code depot and the scripts ran right away.

 

The premier Oracle troubleshooting book.  Also covers access rules, buffer cache, hot buffer objects, Oracle security, storage performance, tablespace fragmentation,

The top oracle9i troubleshooting guide and Oracle performance book for Oracle tuning.

 

   

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