ebook img

Oracle Insights: Tales of the Oak Table PDF

438 Pages·2004·15.64 MB·English
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview Oracle Insights: Tales of the Oak Table

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 +49 6221 345229, 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 510-549-5930, fax 510-549-5939, 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

Description:
Announcing a new book from Apress and the OakTable Network:Oracle Insights: Tales of the Oak Table presents 11 world-renowned industry specialists proffering their own highly experienced views, input, and insights on Oracle—where it's been, where it's going, how (and how not) to use it successfull
See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.