Oracle Insights: Tales of the Oak Table

438 Pages·2004·15.64 MB·English

by  Mogens Nørgaard

Praise for Oracle Insights: Tales of the Oak Table:
"Their OakTable Press books, part ofA press, are highly recommended.
I read Oracle Insights: Tales of the Oak Table and the material is
insightful, and in some cases controversial. If you're looking for a
voice outside of Oracle Corporation to learn about how the product
really works, this is it."
-Oracle + Open Source (http://www.oracleopensource.com)
Oracle Insights:
Tales of the Oak Table
MOGENS N0RGAARD,
JAMES MORLE, DAVE ENSOR, TIM GORMAN,
KYLE HAILEY, ANJO KOLK, JONATHAN LEWIS, CONNOR MCDONALD,
CARY MILLSAP, DAVID RUTHVEN, GAJA KRISHNA VAIDYANATHA
APress Media, LLC
Oracle Insights: Tales of the Oak Table
Copyright © 2004 by Mogens N0rgaard, James Morle, Dave Ensor, Tim Gorman, Kyle Hailey,
Anjo Kolk, Jonathan Lewis, Connor McDonald, Cary Millsap, David Ruthven, Gaja Krishna
Vaidyanatha
Originally published by Apress in 2004
All rights reserved. No part of this work may be reproduced or transmitted in any form or by
any means, electronic or mechanical, including photocopying, recording, or by any information
storage or retrieval system, without the prior written permission of the copyright owner and
the publisher.
ISBN 978-1-59059-387-5 ISBN 978-1-4302-0738-2 (eBook)
DOI 10.1007/978-1-4302-0738-2
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the
benefit of the trademark owner, with no intention of infringement of the trademark.
Lead Editor: Tony Davis
Technical Reviewers: Richard Foote, Julian Dyke
Additional Material: Mario Broodbakker
Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Tony Davis,
Jason Gilmore, Jonathan Hassell, Chris Mills, Dominic Shakeshaft, Jim Sumser
Project Manager: Sofia Marchant
Copy Edit Manager: Nicole LeClerc
Production Manager: Kari Brooks
Production Editor: Janet Vail
Compositor: Diana Van Winkle
Proofreader: Katie Stence
Indexer: Kevin Broccoli
Cover Designer: Kurt Krames
Manufacturing Manager: Tom Debolski
In the United States: phone I-BOO-SPRINGER, e-mail [email protected].
or visit http: / /www.springer-ny.com. Outside the United States: fax [phone],
e-mail orders@springer . de, or visit http://www.springer.de.
For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219,
Berkeley, CA 947l0. Phone [phone], fax [phone], e-mail [email protected], or visit
http://www.apress.com.
The information in this book is distributed on an "as is" basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall
have any liability to any person or entity with respect to any loss or damage caused or alleged to
be caused directly or indirectly by the information contained in this work.
Contents at a Glance
Foreword ................................................................................................................... xi
About the Authors .............................................................................................. xv
About the Technical Reviewer ..................................................................... xxi
I ntroduction ...................................................................................................... xxiii
Chapter 1 A Brief History of Oracle ......................................... 1
Chapter 2 You Probably Don't Tune Right ........................... .71
Chapter 3 Waste Not, Want Not ...................................................... 95
Chapter 4 Why I Invented YAPP .................................................... 139
Chapter 5 Extended SQL Trace Data .......................................... 155
Chapter 6 Direct Memory Access ................................................. 183
Chapter 7 Compulsi ve Tuning Disorder .................................. 209
Chapter 8 New Releases and Big Projects .......................... 257
Chapter 9 Testing and Risk Management ............................... 297
Chapter 10 Design Disasters ............................................................ 337
Chapter 11 Bad CaRMa ..............................................................................3 77
Appendix Join the BAARF Party (or Not) .......................... 397
Index .................................................................................................................... 407
iii
Contents
Foreword ................................................................................................................... xi
About the Authors .............................................................................................. xv
About the Technical Reviewer ..................................................................... xxi
I ntroduct ion ...................................................................................................... xxiii
Chapter 1 A Brief History of Oracle ......................................... 1
Before the Relational Model .......................................................................... 5
Early RDBMS Implementations .......................................................................... 8
From Theory to Practice ..................................................................................... 9
The 12 Rules ......................................................................................................... 11
Rule 1: The Information Rule ........................................................................... 12
Rule 2: Guaranteed Access Rule ....................................................................... 12
Rule 3: Systematic Treatment of Null Values ................................................... 13
Rule 4: Dynamic On-Line Catalog ................................................................... 15
Rule 5: Comprehensive Data Sublanguage ..................................................... 16
Rule 6: View Updating Rule .............................................................................. 17
Rule 7: High-level Insert, Update, and Delete ................................................. 18
Rule 8: Physical Data Independence ............................................................... 18
Rule 9: Logical Data Independence ................................................................. 19
Rule 10: Integrity Independence ...................................................................... 19
Rule 11: Distribution Independence ............................................................... 20
Rule 12: Nonsubversion Rule ........................................................................... 22
What's Missing? .................................................................................................. 23
Transactional Integrity. ..................................................................................... 23
Isolation Levels .................................................................................................. 24
Privileges ............................................................................................................ 26
Early Days, Key Decisions ............................................................................. 28
The Rule Based Optimizer (RBO) ..................................................................... 32
What the World Wanted .................................................................................... 33
Evolution of Expectation of Ease ............................................................. 35
Outriders Become Insiders ............................................................................. 39
The ((Tipping Points" ........................................................................................ 40
Version 4: Portability Across Platforms. .......................................................... .41
Version 5: Subqueries, Tools, and SQL*Net.. .................................................. .45
Version 6: First Steps Towards Scalability. ...................................................... .48
Version 7: A More Active Database .................................................................. 55
Version 8: Partitioning and Object Support .................................................... 58
v
Contents
Version 9: Real Application Clusters (RAC) ..................................................... 62
Version lO: Grid Computing and Manageability ............................................ 66
A Briefer Technical History of Oracle ................................................... 68
The Last Word ....................................................................................................... 69
Chapter 2 You Probably Don't Tune Right ........................... .71
Correct Instrumentation Is Key .................................................................. 76
It's All a Question of Focus ................................................................................ 79
The Time Is Coming ......................................................................................... 80
History Repeats Itself ....................................................................................... 82
Overhead. ........................................................................................................... 83
Batch .................................................................................................................. 84
The Wild West Systems: VMS, UNIX, Windows ............................................... 85
Is There No Hope? ............................................................................................. 86
State of the Nation .......................................................................................... 90
VMS Is Dying ..................................................................................................... 90
Proprietary UNIX Is also Dying ........................................................................ 91
Unux and Windows Are on the Rise ................................................................ 91
What About Oracle? .......................................................................................... 92
Closing Thoughts ................................................................................................ 93
Chapter 3 Waste Not, Want Not ...................................................... 95
As Bad As It Gets .............................................................................................. 99
Resolutions ...................................................................................................... 102
({Ultra" Read Consistency ............................................................................. 103
Back to the Problem Application ................................................................... 106
A Closer Look at the Trace Files ...................................................................... 107
Resolutions ...................................................................................................... 109
The Reference Code Dumping Ground ......................................................... 111
Problem 1: Lookup Function Pervasion ........................................................ 112
Problem 2: The Loss of Domain ..................................................................... 114
Problem 2a: The Loss of Domain for Validation ........................................... 117
Resolution ........................................................................................................ 118
Parsing Proliferation: From XML to Oracle ........................................ 118
Background. ..................................................................................................... 119
The Problem .................................................................................................... 120
Resolutions ...................................................................................................... 125
Hidden DUAL Access .......................................................................................... 130
Background. ..................................................................................................... 130
Resolutions ...................................................................................................... 132
Conclusion ............................................................................................................ 137
vi
Contents
Chapter 4 Why I Invented yAPP .................................................... 139
Discovering the Wait Interface ................................................................ 141
Documenting the Wait Events ........................................................................ 142
Back to Oracle Parallel Server ......................................................................... 143
OPS in Tokyo ....................................................................................................... 145
Problems with Queues .................................................................................... 146
The Birth of Tuning by Response Time .......................................................... 14 7
The Birth ofYAPP Methodology ..................................................................... 150
Using YAPP Methodology ................................................................................. 150
The Wait Interface in the Mainstream ................................................... 151
Looking Forward ................................................................................................. 152
Summary .................................................................................................................. 153
Chapter 5 Extended SQL Trace Data .......................................... 155
Before the Microscope .................................................................................... 157
The Dark Ages ..................................................................................................... 158
The Enlightenment ............................................................................................ 162
The History of SQL Trace ............................................................................. 164
Version 5 ........................................................................................................... 164
Version 6 ........................................................................................................... 166
Version 7 ........................................................................................................... 169
Version 8 ........................................................................................................... 171
Version 9 ........................................................................................................... 172
Version 10 ......................................................................................................... 174
The Revolution ................................................................................................... 176
Diagnosis Beyond the Database .................................................................. 178
The New ((Step One)} .......................................................................................... 180
References ............................................................................................................ 182
Chapter 6 Direct Memory Access ................................................. 183
On Site at Brushco .......................................................................................... 185
Meeting Roger Sanders ................................................................................... 186
Roger and "m2" ............................................................................................... 188
m2 and Direct Memory Access ...................................................................... 190
m2 inAction. .................................................................................................... 192
Developing DMA in Anger ............................................................................... 194
How DMA Works ............................................................................................. 195
DMA in Action: Reading Buffer Cache Handles ................................... 206
DMA Pros and Cons ............................................................................................ 206
Closing Thoughts .............................................................................................. 207
vii
Contents
Chapter 7 Compulsive Tuning Disorder .................................. 209
What Is CTO? ....................................................................................................... 212
Why Do People Suffer from CTD? .................................................................. 214
Oracle 109 Automatic SGA Thning: An Oasis for a CTD Sufferer. ................ 215
Are You DBA Survivor Material? ..................................................................... 216
It Is Time to Get Logical .......................................................................... 217
Logical Performance Optimization ........................................................... 218
The Oracle Wait Interface ........................................................................... 220
Types ofW ait Events ........................................................................................ 220
V$SESSION_W AlT: The Goldmine of Symptoms. ......................................... 221
The OWL in Oracle 109 .................................................................................... 223
The Concept of a Wait Class ........................................................................... 223
V$ACTIVE_SESSION_HISTORY: The New Diagnostic Data Source ........... .226
V$EVENLHISTOGRAM: Wait Event Data Distribution .............................. 230
Oracle 109 DBMS_MONITOR: The New Frontier in Session Tracing ......... 232
The 2-pronged Methodology: The Cure for CTO .................................. .234
Set Reasonable Performance Goals .............................................................. .236
Measure and Document Current Performance ............................................ 236
Identifying the Bottlenecks within Oracle: Prong I ...................................... 237
Using Prong I: A Simple Yet Powerful Example ............................................. 241
Identifying the OS Bottlenecks: Prong II ....................................................... 247
CTO Case Notes ................................................................................................... 251
CTD Sufferer #1 ............................................................................................... 251
CTD Sufferer #2 ............................................................................................... 253
Conclusion ............................................................................................................ 255
References ............................................................................................................ 255
Chapter 8 New Releases and Big Projects .......................... 257
The Arrival ......................................................................................................... 260
The Project ......................................................................................................... 260
Rapid Application Development ................................................................... 262
RAD-ical Runway ............................................................................................ 263
Multi-Threaded Serverl .................................................................................. 264
System Sizing ..................................................................................................... 267
We Need Bigger Guns ..................................................................................... 268
Migrating to Sequent ...................................................................................... 269
Changing Character Sets ................................................................................ 269
Problems with RAID ........................................................................................ 270
Bugs .................................................................................................................. 273
No More MTS ................................................................................................... 274
Two Nodes: Using OPS in Anger .................................................................. 275
Functional Partitioning. .................................................................................. 276
viii
Contents
Germany! ................................................................................................................ 278
Shared Pool Problems ..................................................................................... 279
OPS Problems .................................................................................................. 280
More Trouble at the WANch ........................................................................... 286
Latency ............................................................................................................. 286
Centralized Client/Server ............................................................................... 287
Satellite Networks ............................................................................................ 287
A Multi-Prong Attack on OPS ...................................................................... 288
Shared SQL ...................................................................................................... 289
Bigfoot: Splitting the Rental Users ................................................................. 290
Thning the Operations .................................................................................... 291
Tuning the Database ....................................................................................... 292
Modern Day ............................................................................................................ 294
Putting the Project to Bed ........................................................................ .295
Chapter 9 Testing and Risk Management ............................... 297
A Little Background Muse ............................................................................. 301
Avoiding the Avoidable ................................................................................. 302
Strategic Problems .......................................................................................... 302
Operational Problems ..................................................................................... 303
Rules of Engagement ........................................................................................ 305
Cycle Faster ....................................................................................................... 307
Design Principles ............................................................................................ 308
Design Accurately. ........................................................................................... 308
Keep It Simple .................................................................................................. 309
Identify Bottlenecks ........................................................................................ 312
Generalize ........................................................................................................ 313
Design for the Rule Rather Than the Exception. ........................................... 314
Parameterize .................................................................................................... 314
Work with the Product, Not Against It ........................................................... 315
Instrument Your Code ..................................................................................... 316
Less Is More ..................................................................................................... 316
Instrumentation ................................................................................................. 318
Changes in Performance and Resource Usage ............................................. 324
Auditing ............................................................................................................ 325
Tracing on Failure ............................................................................................ 325
Sizing ................................................................................................................ 326
Testing .................................................................................................................. 326
Functionality Tests .......................................................................................... 327
Automated Testing .......................................................................................... 329
Scalability Testing ............................................................................................ 329
Keep It Real ...................................................................................................... 332
Testing Culture ................................................................................................ 332
ix