Expert Oracle Exadata

579 Pages·2011·14.26 MB·English

BOOKS FOR PROFESSIONALS BY PROFESSIONALS®
Osborne
Johnson
Põder
Expert Oracle Exadata
RELATED
This book clearly explains Exadata, detailing how the system combines servers,
storage and database software into a unified system for both transaction process-
ing and data warehousing. It will change the way you think about managing SQL
performance and processing.
Authors Kerry Osborne, Randy Johnson and Tanel Põder share their real world
experience gained through multiple Exadata implementations with you. They pro-
vide a roadmap to laying out the Exadata platform to best support your existing
systems.
With Expert Oracle Exadata, you’ll learn how to:
• Configure Exadata from the ground up
• Migrate large data sets efficiently
• Connect Exadata to external systems
• Configure high-availability features such as RAC and ASM
• Support consolidation using the I/O Resource Manager
• Apply tuning strategies based upon the unique features of Exadata
Expert Oracle Exadata gives you the knowledge you need to take full advantage of
this game-changing database appliance platform.
Shelve in
Databases/Oracle
User level:
Intermediate–Advanced
www.apress.com
For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
Contents at a Glance
 About the Authors................................................................................................xvi
 About the Technical Reviewer............................................................................xvii
 Acknowledgments.............................................................................................xviii
 Introduction.........................................................................................................xix
 Chapter 1: What Is Exadata?...................................................................................1
 Chapter 2: Offloading / Smart Scan......................................................................23
 Chapter 3: Hybrid Columnar Compression............................................................65
 Chapter 4: Storage Indexes.................................................................................105
 Chapter 5: Exadata Smart Flash Cache...............................................................125
 Chapter 6: Exadata Parallel Operations..............................................................143
 Chapter 7: Resource Management......................................................................175
 Chapter 8: Configuring Exadata..........................................................................237
 Chapter 9: Recovering Exadata...........................................................................275
 Chapter 10: Exadata Wait Events........................................................................319
 Chapter 11: Understanding Exadata Performance Metrics.................................345
 Chapter 12: Monitoring Exadata Performance....................................................379
 Chapter 13: Migrating to Exadata.......................................................................419
 Chapter 14: Storage Layout................................................................................467
 Chapter 15: Compute Node Layout .....................................................................497
 Chapter 16: Unlearning Some Things We Thought We Knew.............................511
iii
 CONTENTS AT A GLANCE
 Appendix A: CellCLI and dcli...............................................................................535
 Appendix B: Online Exadata Resources..............................................................545
 Appendix C: Diagnostic Scripts...........................................................................547
 Index...................................................................................................................551
iv
Introduction
Thank you for purchasing this book. We worked hard on it for a long time. Our hope is that you find it
useful as you begin to work with Exadata. We’ve tried to introduce the topics in a methodical manner
and move from generalizations to specific technical details. While some of the material paints a very
broad picture of how Exadata works, some is very technical in nature, and you may find that having
access to an Exadata system where you can try some of the techniques presented will make it easier to
understand. Note that we’ve used many undocumented parameters and features to demonstrate how
various pieces of the software work. Do not take this as a recommended approach for managing a
production system. Remember that we have had access to a system that we could tear apart with little
worry about the consequences that resulted from our actions. This gave us a huge advantage in our
investigations into how Exadata works. In addition to this privileged access, we were provided a great
deal of support from people both inside and outside of Oracle for which we are extremely grateful.
The Intended Audience
This book is intended for experienced Oracle people. We do not attempt to explain how Oracle works
except as it relates to the Exadata platform. This means that we have made some assumptions about the
reader’s knowledge. We do not assume that you are an expert at performance tuning on Oracle, but we
do expect that you are proficient with SQL and have a good understanding of basic Oracle architecture.
How We Came to Write This Book
In the spring of 2010, Enkitec bought an Exadata V2 Quarter Rack. We put it in the tiny computer room at
our office in Dallas. We don’t have a raised floor or anything very fancy, but the room does have its own
air conditioning system. It was actually more difficult than you might think to get Oracle to let us
purchase one. They had many customers that wanted them, and they were understandably protective of
their new baby. We didn’t have a top-notch data center to put it in, and even the power requirements
had to be dealt with before they would deliver one to us. At any rate, shortly after we took delivery,
through a series of conversations with Jonathan Gennick, Randy and I agreed to write this book for
Apress. There was not a whole lot of documentation available at that time, and so we found ourselves
pestering anyone we could find who knew anything about it. Kevin Closson and Dan Norris were both
gracious enough to answer many of our questions at the Hotsos Symposium in the spring of 2010. Kevin
contacted me some time later and offered to be the official technical reviewer. So Randy and I struggled
through the summer and early fall attempting to learn everything could.
I ran into Tanel at Oracle Open World in September, 2010, and we talked about a client using
Exadata that he had done some migration work for. One thing led to another, and eventually he agreed
to join the team as a co-author. At Open World, Oracle announced the availability of the new X2 models,
so we had barely gotten started and we were already behind on the technology.
xix
 INTRODUCTION
In January of 2011, the X2 platform was beginning to show up at customer sites. Enkitec again
decided to invest in the technology, and we became the proud parents of an X2-2 quarter rack. Actually,
we decided to upgrade our existing V2 quarter rack to a half rack with X2 components. This seemed like a
good way to learn about doing upgrades and to see if there would be any problems mixing components
from the two versions (there weren’t). This brings me to an important point.
A Moving Target
Like most new software, Exadata has evolved rapidly since its introduction in late 2009. The changes
have included significant new functionality. In fact, one of the most difficult parts of this project has
been keeping up with the changes. Several chapters underwent multiple revisions because of changes in
behavior introduced while we were writing the material. The last version we have attempted to cover in
this book is database version 11.2.0.2 with bundle patch 6 and cellsrv version 11.2.2.3.2. Note that there
have been many patches over the last two years and that there are many possible combinations of
database version, patch level, and cellsrv versions. So if you are observing some different behavior than
we have documented, this is a potential cause. Nevertheless, we welcome your feedback and will be
happy to address any inconsistencies that you find. In fact, this book has been available as part of
Apress’s Alpha program, which allows readers to download early drafts of the material. Participants in
this program have provided quite a bit of feedback during the writing and editing process. We are very
thankful for that feedback and somewhat surprised at the detailed information many of you provided.
Thanks to the Unofficial Editors
We have had a great deal of support from a number of people on this project. Having our official
technical reviewer actually writing bits that were destined to end up in the book was a little weird. In
such a case, who reviews the reviewer’s writing? Fortunately, Arup Nanda volunteered early in the
project to be an unofficial editor. So in addition to the authors reviewing each other’s stuff, and Kevin
reviewing our chapters, Arup read and commented on everything, including Kevin’s comments. In
addition, many of the Oak Table Network members gave us feedback on various chapters throughout
the process. Most notably, Frits Hoogland and Peter Bach provided valuable input.
When the book was added to Apress’s Alpha Program, we gained a whole new set of reviewers.
Several people gave us feedback based on the early versions of chapters that were published in this
format. Thanks to all of you who asked us questions and helped us clarify our thoughts on specific
issues. In particular, Tyler Muth at Oracle took a very active interest in the project and provided us with
very detailed feedback. He was also instrumental in helping to connect us with other resources inside
Oracle, such as Sue Lee, who provided a very detailed review of the Resource Management chapter.
Finally I’d like to thank the technical team at Enkitec. There were many who helped us keep on
track and helped pick up the slack while Randy and I were working on this project (instead of doing our
real jobs). The list of people who helped is pretty long, so I won’t call everyone by name. If you work at
Enkitec and you have been involved with the Exadata work over the last couple of years, you have
contributed to this book. I would like to specifically thank Tim Fox, who generated a lot of the graphics
for us in spite of the fact that he had numerous other irons in the fire, including his own book project.
We also owe Andy Colvin a very special thanks as a major contributor to the project. He was
instrumental in several capacities. First, he was primarily responsible for maintaining our test
environment, including upgrading and patching the platform so that we could test the newest features
and changes as they became available. Second, he helped us hold down the fort with our customers who
xx
 INTRODUCTION
were implementing Exadata while Randy and I were busy writing. Third, he was instrumental in helping
us figure out how various features worked, particularly with regard to installation, configuration, and
connections to external systems. It would have been difficult to complete the project without him.
Who Wrote That?
There are three authors of this book, four if you count Kevin. It was really a collaborative effort among
the four of us. But in order to divide the work we each agreed to do a number of chapters. Initially Randy
and I started the project and Tanel joined a little later (so he got a lighter load in terms of the
assignments, but was a very valuable part of team, helping with research on areas that were not
specifically assigned to him). So here’s how the assignments worked out:

Kerry: Chapters 1–6, 10, 16.
Randy: Chapters 7–9, 14–15, and about half of 13
Tanel: Chapters 11–12, and about half of 13
Kevin: Easily identifiable in the “Kevin Says” sections
Online Resources
We used a number of scripts in this book. When they were short or we felt the scripts themselves were of
interest, we included their contents in the text. When they were long or just not very interesting, we
sometimes left the contents of the scripts out of the text. You can find the source code for all of the
scripts we used in the book online at www.ExpertOracleExadata.com. Appendix C also contains a listing of
all the diagnostic scripts along with a brief description of their purpose.
A Note on “Kevin Says”
Kevin Closson served as our primary technical reviewer for the book. Kevin was the chief performance
architect at Oracle for the SAGE project, which eventually turned into Exadata, so he is extremely
knowledgeable not only about how it works, but also about how it should work and why. His duties as
technical reviewer were to review what we wrote and verify it for correctness. The general workflow
consisted of one of the authors submitting a first draft of a chapter and then Kevin would review it and
mark it up with comments. As we started working together, we realized that it might be a good idea to
actually include some of Kevin’s comments in the book, which provides you with a somewhat unique
look into the process. Kevin has a unique way of saying a lot in very few words. Over the course of the
project I found myself going back to short comments or emails multiple times, and often found them
more meaningful after I was more familiar with the topic. So I would recommend that you do the same.
Read his comments as you’re going through a chapter, but try to come back and reread his comments
after finishing the chapter; I think you’ll find that you will get more out of them on the second pass.
How We Tested
When we began the project, the current release of the database was 11.2.0.1. So several of the chapters
were initially tested with that version of the database and various patch levels on the storage cells. When
xxi
 INTRODUCTION
11.2.0.2 became available, we went back and retested. Where there were significant differences we tried
to point that out, but there are some sections that were not written until after 11.2.0.2 was available. So
on those topics we may not have mentioned differences with 11.2.0.1 behavior. We used a combination
of V2 and X2 hardware components for our testing. There was basically no difference other than the X2
being faster.
Schemas and Tables
You will see a couple of database tables used in several examples throughout the book. Tanel used a
table called T that looks like this:

SYS@SANDBOX1> @table_stats
Owner : TANEL
Table : T
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
ROWNUM NUMBER

==========================================================================
Table Statistics
==========================================================================
TABLE_NAME : T
LAST_ANALYZED : 10-APR-2011 13:28:55
DEGREE : 1
PARTITIONED : NO
NUM_ROWS : 62985999
CHAIN_CNT : 0
BLOCKS : 1085255
EMPTY_BLOCKS : 0
AVG_SPACE : 0
AVG_ROW_LEN : 104
MONITORING : YES
SAMPLE_SIZE : 62985999
-----------------
==========================================================================
Column Statistics
==========================================================================
Name Analyzed NDV Density # Nulls # Buckets Sample
==========================================================================
OWNER 04/10/2011 21 .047619 0 1 62985999
NAME 04/10/2011 5417 .000185 0 1 62985999
TYPE 04/10/2011 9 .111111 0 1 62985999
LINE 04/10/2011 23548 .000042 0 1 62985999
xxii
 INTRODUCTION
TEXT 04/10/2011 303648 .000003 0 1 62985999
ROWNUM 04/10/2011 100 .010000 0 1 62985999

I used several variations on a table called SKEW. The one I used most often is SKEW3, and it
looked like this:

SYS@SANDBOX1> @table_stats
Owner : KSO
Table : SKEW3
Name Null? Type
----------------------------------------- -------- ----------------------------
PK_COL NUMBER
COL1 NUMBER
COL2 VARCHAR2(30)
COL3 DATE
COL4 VARCHAR2(1)
NULL_COL VARCHAR2(10)

==============================================================================
Table Statistics
==============================================================================
TABLE_NAME : SKEW3
LAST_ANALYZED : 10-JAN-2011 19:49:00
DEGREE : 1
PARTITIONED : NO
NUM_ROWS : 384000048
CHAIN_CNT : 0
BLOCKS : 1958654
EMPTY_BLOCKS : 0
AVG_SPACE : 0
AVG_ROW_LEN : 33
MONITORING : YES
SAMPLE_SIZE : 384000048
-----------------
==============================================================================
Column Statistics
==============================================================================
Name Analyzed NDV Density # Nulls # Buckets Sample
==============================================================================
PK_COL 01/10/2011 31909888 .000000 12 1 384000036
COL1 01/10/2011 902848 .000001 4 1 384000044
COL2 01/10/2011 2 .500000 12 1 384000036
COL3 01/10/2011 1000512 .000001 12 1 384000036
COL4 01/10/2011 3 .333333 12 1 384000036
NULL_COL 01/10/2011 1 1.000000 383999049 1 999

xxiii
 INTRODUCTION
This detailed information should not be necessary for understanding any of our examples, but
if you have any questions about the tables, they are here for your reference. Also be aware that we used
other tables as well, but these are the ones we used most often.
Good Luck
We have had a blast discovering how Exadata works. I hope you enjoy your explorations as much as we
have, and I hope this book provides a platform from which you can build your own body of knowledge. I
feel like we are just beginning to scratch the surface of the possibilities that have been opened up by
Exadata. Good luck with your investigations and please feel free to ask us questions and share your
discoveries with us at www.ExpertOracleExadata.com.

xxiv