Oracle Exadata Survival Guide

271 Pages·2013·2.661 MB·English

by  David Fitzjarrell, Mary Mikell Spence (auth.)

BOOKS FOR PROFESSIONALS BY PROFESSIONALS®
Fitzjarrell
Spence
Oracle Exadata Survival Guide
RELATED
Oracle Exadata Survival Guide is a hands-on guide for busy Oracle database
administrators who are migrating their skill sets to Oracle’s Exadata database
appliance. The book covers the concepts behind Exadata, and the available con-
figurations for features such as smart scans, storage indexes, Smart Flash Cache,
hybrid columnar compression, and more. You’ll learn about performance metrics
and execution plans, and how to optimize SQL running in Oracle’s powerful new
environment. The authors also cover migration from other servers.
Oracle Exadata is fast becoming the standard for large installations such as
those running data warehouse, business intelligence, and large-scale OLTP sys-
tems. Exadata is like no other platform, and is new ground even for experienced
Oracle database administrators. The Oracle Exadata Survival Guide helps you nav-
igate the ins and outs of this new platform, de-mystifying this amazing appliance
and its exceptional performance. The book takes a highly practical approach, not
diving too deeply into the details, but giving you just the right depth of information
to quickly transfer your skills to Oracle’s important new platform.
• Helps transfer your skills to the platform of the future
• Covers the important ground without going too deep
• Takes a practical and hands-on approach to everyday tasks
What You’ll Learn:
• Master the components and basic architecture of an Exadata machine
• Reduce data transfer overhead by processing queries in the storage layer
• Examine and take action on Exadata-specific performance metrics
• Deploy Hybrid Columnar Compression to reduce storage and I/O needs
• Create worry-free migrations from existing databases into Exadata
• Understand and address issues specific to ERP migrations
Shelve in ISBN 978-1-4302-6010-3
Databases/Oracle
User level:
Intermediate–Advanced
9 781430 260103
SOURCE CODE ONLINE
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 ��������������������������������������������������������������������������������������������������������������xiii
About the Technical Reviewer ��������������������������������������������������������������������������������������������xv
Acknowledgments ������������������������������������������������������������������������������������������������������������xvii
Introduction �����������������������������������������������������������������������������������������������������������������������xix
■ Chapter 1: Exadata Basics �������������������������������������������������������������������������������������������������1
■ Chapter 2: Smart Scans and Offloading ����������������������������������������������������������������������������5
■ Chapter 3: Storage Indexes ���������������������������������������������������������������������������������������������29
■ Chapter 4: Smart Flash Cache �����������������������������������������������������������������������������������������53
■ Chapter 5: Parallel Query �������������������������������������������������������������������������������������������������71
■ Chapter 6: Compression ��������������������������������������������������������������������������������������������������83
■ Chapter 7: Exadata Cell Wait Events ������������������������������������������������������������������������������119
■ Chapter 8: Measuring Performance �������������������������������������������������������������������������������135
■ Chapter 9: Storage Cell Monitoring �������������������������������������������������������������������������������151
■ Chapter 10: Monitoring Exadata ������������������������������������������������������������������������������������173
■ Chapter 11: Storage Reconfiguration ����������������������������������������������������������������������������207
■ Chapter 12: Migrating Databases to Exadata ����������������������������������������������������������������219
■ Chapter 13: ERP Setup—a Practical Guide ��������������������������������������������������������������������237
■ Chapter 14: Final Thoughts �������������������������������������������������������������������������������������������245
Index ���������������������������������������������������������������������������������������������������������������������������������257
v
Introduction
This book was borne from our personal experiences with managing, configuring, and migrating databases and
applications to the Exadata platform. Along the way, we took notes, tried this, tried that, and eventually came to an
understanding of what Exadata could do and how it does it. We didn’t go into great detail on the inner workings of the
system (Kerry Osborne, Randy Johnson, and Tanel Pöder have already provided an excellent text at that level), opting
instead to provide a guide for the working DBA who may not have time to read through and digest a “nuts and bolts”
book. Thus, this book is designed to lead the DBA through what we feel are the most important aspects and concepts
of Exadata, without getting bogged down in details. We recommend having access to an Exadata machine, so you can
try the scripts, queries, and commands provided and see the results firsthand. Having an interactive experience with
Exadata will, we think, make some of the concepts easier to understand.
Our goal in writing this book was to provide experienced Oracle DBAs with the tools and knowledge to take
on Exadata, hopefully without fear. No attempt is made to explain how Oracle works—this is not a “learn Oracle by
doing” text. Our intent is to leverage the existing knowledge of field-proven and time-tested Oracle professionals, so
that they can successfully adapt what they already know to the Exadata platform.
xix
Chapter 1
Exadata Basics
Since its introduction in September 2008, Exadata has fast become both a familiar term and a familiar presence in the
IT/database realm. The system has undergone several changes in its short history, from storage solution to complete
database appliance. Although it is not yet a household name, the number of Exadata installations has increased to
the point where it will soon become commonplace in data centers across the country. So, what is Exadata? It might be
better to begin by stating what Exadata isn’t. Exadata is not
• the greatest thing since sliced bread;
• the only database machine that can single-handedly eliminate every occurrence of contention
in your application;
• the long-awaited silver bullet to solve all of your other database performance problems;
• a black box, constructed by the wizards of Middle Earth, understandable only to the
anointed few.
What Is Exadata?
Now that you know what Exadata isn’t, let’s discuss what it is. Exadata is a system, composed of matched and tuned
components providing enhancements available with no other configuration, that can improve the performance
of the database tier. This system includes database servers, storage servers, an internal InfiniBand network with
switches, and storage devices (disks), all configured by Oracle Advanced Customer Support personnel to meet the
customer’s requirements. (Changes to that configuration, including changing the default storage allocations between
the data and recovery disk groups, can be made with assistance from Oracle Advanced Customer Support.) Figure 1-1
illustrates the general layout of an Exadata system.
1
Chapter 1 ■ exadata BasiCs
DB Node 8 of these DB Node
Infiniband Switches
14 of these
Storage Cell Storage Cell
Disks under ASM Disks under ASM
Flash Disks Flash Disks
Figure 1-1. General layout of an Exadata system
Can Exadata improve every situation? No, but it wasn’t designed to. Originally designed as a data warehouse/
business intelligence appliance, the releases from V2 on have added Online Transaction Processing (OLTP)
applications. Yet not every feature of Exadata is applicable to every query, application, or situation that may arise.
Chances are good that if your application is not suffering from contention issues, Exadata may provide reduced
response time and better throughput than systems using off-the-shelf components.
Exadata did not start out being what it is today; originally, it was conceived as an open-source storage solution for
RAC (Real Application Clusters) installations, intended to address the problem of transferring large volumes of data
across the grid infrastructure, known internally within Oracle as SAGE (Storage Appliance for Grid Environments). In
September 2008, Oracle unveiled the HP Oracle Database Machine, spotlighting the Exadata Storage Servers. At that
time, Exadata referred only to the storage server components, although the HP Oracle Database Machine included all
of the components found in later releases of the Exadata product.
The system was marketed as a data warehouse solution. Then, a year later, Exadata V2 was released, this time
marketed as a complete and integrated database appliance, including database servers, storage servers, an internal
InfiniBand network, and software designed so the components worked in concert as a unified whole. Oracle
marketed this appliance as the first database machine for OLTP, so now Exadata was the machine of choice for both
data warehousing and OLTP systems. The following year (2010) saw the release of Exadata X2, which continued the
improvements by adding a second configuration (X2-8) to the mix. This provided customers with two options for a
full-rack implementation. And in September 2012, Exadata X3 hit the market, improving performance yet again and
offering a fifth configuration option, the Eighth Rack.
Available Configurations
The X3 series of Exadata machines, which replaced the X2 series, is available in the following five configurations:
X3-2 Eighth Rack: Two database servers, each with two eight-core Xeon processors with
eight cores enabled and 256GB of RAM, three storage servers, and thirty-six disk drives
X3-2 Quarter Rack: Two database servers, each with two eight-core Xeon processors and
256GB of RAM, three storage servers, and thirty-six disk drives
2
Chapter 1 ■ exadata BasiCs
X3-2 Half Rack: Four database servers, each with two eight-core Xeon processors and
256GB of RAM, seven storage servers, eighty-four disk drives, and a spine switch for
expansion
X3-2 Full Rack: Eight database servers, each with two eight-core Xeon processors and
256GB of RAM, fourteen storage servers, one hundred sixty-eight disk drives, and a spine
switch for expansion
X3-8 Full Rack: Two database servers, each with eight ten-core Xeon processors and 2TB
of RAM, fourteen storage servers, one hundred sixty-eight disk drives, a spine switch for
expansion, and no keyboard/video/mouse module
In general, the X3 series of Exadata machines is twice as powerful as the discontinued X2 series on a “like for
like” comparison. As mentioned earlier, in the X3-2 series, there is a new Eighth Rack configuration that provides
slightly less computing power (a total of sixteen processor cores, eight of which are enabled) than the X2-2 Quarter
Rack (which offered a total of twelve processor cores, all of which were enabled). This reduces the licensing costs as
compared to the X3-2 Quarter Rack, making the Eighth Rack a very suitable and cost-effective X3-2 entry point into
the Exadata arena.
Storage
How much raw storage you have depends on whether you choose High Capacity or High Performance drives—
High Capacity Serial Attached SCSI (SAS) drives have 3TB each of raw storage running at 7,200RPM and the High
Performance drives have 600GB each running at 15,000RPM. For a Quarter Rack configuration with High Capacity
disks, 108TB of total raw storage is provided, with roughly 40TB available for data after normal Automatic Storage
Management (ASM) redundancy is configured. Using High Performance disks, the total raw storage for a Quarter Rack
machine is 21.1TB, with approximately 8.4TB of usable data storage with normal ASM redundancy. High redundancy
reduces the storage by roughly another third on both configurations; the tradeoff is the additional ASM mirror in case
of disk failure, as high redundancy provides two copies of the data. Normal redundancy provides one copy.
The disks are accessed through the storage servers (or cells), running their own version of Linux with a subset of
the Oracle kernel built in. It is interesting to note that there is no direct access to the storage from the database servers;
the only way they can “see” the disks is through ASM. In the X3-2 Quarter Rack and Eighth Rack configurations, there
are three storage cells, with each storage cell controlling twelve disks. Each storage server provides two six-core Xeon
processors and 24GB of RAM. Between the various configurations of Exadata, the differences become the number
of database servers (often referred to as compute nodes) and the number of storage servers or cells—the greater
the number of storage cells, the more storage the Exadata machine can control internally. As previously noted, the
storage servers also run an integrated Oracle kernel. This allows the database servers to “pass off” (or offload) parts of
qualifying queries, so that the database servers only have to handle the reduced data volume of the result sets, rather
than scanning every data or index block for the objects of interest. This is known as a Smart Scan. How Smart Scans
work and what triggers them are covered in Chapter 2.
Smart Flash Cache
Another part of the Exadata performance package is the Smart Flash Cache, 384GB of solid-state flash storage for each
storage cell, configured across four Sun Flash Accelerator F20 PCIe cards. With a Quarter Rack configuration (three
storage servers/cells), 1.1TB of flash storage is available; a Full Rack provides 5.3TB of flash storage. The flash cache
is usable as a smart cache to service large volumes of random reads, or it can be configured as flash disk devices and
mounted as an ASM disk group. That topic will be covered in greater depth in Chapter 4.
3
Chapter 1 ■ exadata BasiCs
Even More Storage
Expansion racks consist of both storage servers and disk drives. There are three different configurations available:
Quarter Rack, Half Rack, and Full Rack, any of which can connect to any Oracle Exadata machine. For the Quarter
Rack configuration, an additional spine switch will be necessary; the IP address for the spine switch is left unassigned
during configuration, so that if one is installed, the address will be available, with no need to reconfigure the machine.
Besides adding storage, these racks also add computing power for Smart Scan operations, with the smallest
expansion rack containing four storage servers and forty-eight disk drives, adding eight six-core CPUs to the mix. The
largest expansion rack provides 18 storage servers with 216 disk drives. Actual storage will depend on whether the
system is using High Capacity or High Performance disk drives; the drive types cannot be mixed in a single Exadata
machine/expansion rack configuration, so if the Exadata system is using High Capacity drives, the expansion rack
must also contain High Capacity disks, if those disks are being added to existing disk groups.
One reason for this requirement is that ASM stripes the data across the total number of drives in a disk group, thus
the size and geometry of the disk units must be uniform across the storage tier. It is not necessary for the disks of the
expansion rack to be added to an existing disk group; a completely separate disk group can be created from that storage.
You cannot mix storage types within the expansion rack, but the disk type does not need to match that of the host system,
if a separate disk group is to be created from that storage. The beauty of these expansion racks is that they integrate
seamlessly with the existing storage on the host Exadata system. If these disks are added to existing disk groups, ASM
automatically triggers a rebalance to evenly distribute the extents across the total number of available disks.
Things to Know
An Exadata system, available in four configurations, is a complex arrangement of database servers, storage servers,
disk drives, and an internal InfiniBand network with modifications designed to address many performance issues in
a unique way. It’s the first system with a “divide-and-conquer” approach to query processing that can dramatically
improve performance and reduce query response time. It also includes Smart Flash Cache, a write-back cache that
can handle large volumes of reads and is designed for Online Transaction Processing (OLTP) systems. This cache can
also be configured as flash disk devices. Additional storage is available in the form of Exadata Expansion Racks, which
can be added to any Exadata configuration to extend the storage and add storage-cell computing power. The storage
in the expansion rack can be the same type (High Capacity, High Performance) as in the Exadata Machine; however,
in some cases, Oracle recommends High Capacity drives for expansion racks, regardless of the storage found in the
host Exadata system.
Moving on, we’ll discuss the various performance enhancements Exadata provides and how, at least in a limited
way, these enhancements are implemented. This is not meant to be an exhaustive text but a “getting started” guide
to help lead you through the maze. There are other, more technical, texts you can read to gain a deeper knowledge of
the machine, but with this book’s background in hand, it will be easier to understand what Exadata does that other
systems can’t.
4
Chapter 2
Smart Scans and Offloading
One of the enhancements provided by Exadata is the Smart Scan, a mechanism by which parts of a query can be
offloaded, or handed off, to the storage servers for processing. This “divide-and-conquer” approach is one reason
Exadata is able to provide such stellar performance. It’s accomplished by the configuration of Exadata, where
database servers and storage cells offer computing power and the ability to process queries, owing to the unique
storage server software running on the storage cells.
Smart Scans
Not every query qualifies for a Smart Scan, as certain conditions must be met. Those conditions are as follows:
• A full table scan or full index scan must be used, in addition to direct-path reads.
• One or more of the following simple comparison operators must be in use:
• =
• <
• >
• >=
• =<
• BETWEEN
• IN
• IS NULL
• IS NOT NULL
Smart Scans will also be available when queries are run in parallel, because direct-path reads are executed by
default, by parallel query slaves. Of course, the other conditions must also be met: parallel only ensures that direct-
path reads are used. What does a Smart Scan do to improve performance? It reduces the amount of data the database
servers must process to return results. The offloading process divides the workload among the compute nodes and the
storage cells, involves more CPU resources, and returns smaller sets of data to the receiving process.
Instead of reading 10,000 blocks of data to return 1,000 rows, offloading allows the storage cells to perform some
of the work with access and filter predicates and to send back only the rows that meet the provided criteria. Similar in
operation to parallel query slaves, the offloading process divides the work among the available storage cells, and each
cell returns any qualifying rows stored in that particular subset of disks. And, like parallel query, the result “pieces” are
merged into the final result set. Offloading also reduces inter-instance transfer between nodes, which, in turn, reduces
5
Chapter 2 ■ Smart SCanS and OfflOading
latching and global locking. Latching, in particular, consumes CPU cycles. Less latching equates to a further reduction
in CPU cycles, enhancing performance. The net “savings” in CPU work and execution time can be substantial; queries
that take minutes to execute on non-Exadata systems can sometimes be completed in seconds as a result of using
Smart Scans.
Plans and Metrics
Execution plans can report Smart Scan activity, if they are the actual plans generated by the optimizer at runtime.
Qualifying plans will be found in the V$SQL_PLAN and DBA_HIST_SQL_PLAN views and will be generated by
autotrace, when the ON option is used, or can be found by enabling a 10046 trace and processing the resulting trace
file through tkprof. Using autotrace in EXPLAIN mode may not provide the same plan as generated at runtime,
because it can still use rule-based optimizer decisions to generate plans. The same holds true for EXPLAIN PLAN.
(We have seen cases where EXPLAIN PLAN and a 10046 trace differed in the reported execution plan.) The tkprof
utility also offers an explain mode, and it, too, can provide misleading plans. By default, tkprof provides the actual
plan from the execution, so using the command-line explain option is unnecessary.
Smart Scans are noted in the execution plan in one of three ways:
• TABLE ACCESS STORAGE FULL
• INDEX STORAGE FULL SCAN
• INDEX STORAGE FAST FULL SCAN
The presence of one or more of these operations does not mean that a Smart Scan actually occurred; other
metrics should be used to verify Smart Scan execution. The V$SQL view (and, for RAC databases, GV$SQL) has two
columns that provide further information on cell offload execution, io_cell_offload_eligible_bytes and io_cell_offload_
returned_bytes. These are populated with relevant information regarding cell offload activity for a given sql_id,
provided a Smart Scan was actually executed.
The io_cell_offload_eligible_bytes column reports the bytes of data that qualify for offload. This is the volume of
data that can be offloaded to the storage cells during query execution. The io_cell_offload_returned_bytes column
reports the number of bytes returned by the regular I/O path. These are the bytes that were not offloaded to the
cells. The difference between these two values provides the bytes actually offloaded during query execution. If
no Smart Scan were used, the values for both columns would be 0. There will be cases where the column io_cell_
offload_eligible_bytes will be equal to 0, but the column io_cell_offload_returned_bytes will not. Such cases will
usually, but not always, be referencing either fixed views (such as GV$SESSION_WAIT) or other data dictionary views
(V$TEMPSEG_USAGE, for example). Such queries are not considered eligible for offload. (The view isn’t offloadable,
because it may expose memory structures resident on the database compute nodes, but that doesn’t indicate they
don’t qualify for a Smart Scan [one or more of the base tables to that view might qualify].) The presence of projection
data in V$SQL_PLAN/GV$SQL_PLAN is proof enough that a Smart Scan was executed.
Looking at a query where a Smart Scan is executed, using the smart_scan_ex.sql script, we see

SQL> select *
2 from emp
3 where empid = 7934;

EMPID EMPNAME DEPTNO
---------- ---------------------------------------- ----------
7934 Smorthorper7934 15

Elapsed: 00:00:00.21

6