Download - Partner Webcast – Oracle Exadata Database Machine X3: Architecture and Optimization - 03 Oct 2013
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 1
Stay Connected
BLOGS.ORACLE.COM/IMC
TWITTER.COM/ORACLEIMC
YOUTUBE.COM/ORACLEIMCTEAM
FACEBOOK.COM/OPN.PARTNERHUB.MIGRATION.CENTER
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 2
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 3
Oracle Exadata Database Machine X3: Architecture and Optimization
Sezgi Gecer Ozseyhan, Sales Consultant
ISV/OEM Sales - Turkey
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 4
Program Agenda
Introduction to Oracle Exadata Database Machine X3
Exadata Engineered System Architecture
Exadata Configuration & Optimization
Exadata Monitoring and Management
Q&A
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 5
DATA WAREHOUSING
OLTP
MIXED WORKLOADS
DATABASE CONSOLIDATION
DATABASE CLOUD
Exadata - One Machine | Many Workloads
Oracle’s strategic platform for ALL
Database workloads
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 6
A Complete, Integrated, Optimized Platform 100% Supported by Oracle
Scale-Out Database Servers
High Volume 2 or 8 socket servers
Oracle Database 11g, RAC, ASM, EM12c
Compatible with all 11g databases, app’s, tools
Scale-Out Intelligent Storage Servers
High Volume 2-socket storage servers
Exadata Storage Server Software
InfiniBand Network
Unified internal connectivity ( 40 Gb/sec )
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 7
Highly Engineered and Standardized Less Risk, Better Results
Hundreds of engineer years spent optimizing and
hardening the system end-to-end
– Frees I/T talent to focus on business needs
Standard platform improves support experience
Runs all existing Oracle Database workloads
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 8
Exadata X3 “Database In-Memory Machine”
X3 mass memory hierarchy delivers extreme
performance – Automatically moves all active data from disk to memory
DRAM memory expanded to 2 or 4 TB for hottest data
– 4 to 40 TB of compressed user data
Flash memory expanded 4X to 22 TB per rack
– 40 to 200 TB of compressed user data – ALL active data
– 1.5 Million SQL random read I/Os per second for OLTP
Comparable to 15,000 disk drives in 150 array frames
– 100 GB/sec SQL data scan rate for reporting and warehouses
Comparable to 1,000 disk drives in 10 array frames
500 TB
DISK
22 TB PCI
FLASH
2 or 4 TB
DRAM
Cold Data
Hottest Data
Active Data
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 9
Exadata Evolution X3 “Database In-Memory Machine”
2008
Warehouse Smart Storage
InfiniBand Scale-Out
2010
Scale-Up 80-core SMPs
OLTP & VLDB Flash
Columnar
2009
2012
Massive Flash
All I/Os to Flash
Database On Disk
X3
Database In-Memory
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 10
Complete Optimized Enterprise Scalability & Reliability
Scalable
– Scales from a quarter rack to a full rack to 8 rack cluster by just adding wires
– Scales to hundreds of storage servers
– Multi-petabyte databases
Redundant System
– Failure of any component
is tolerated
– Data is mirrored across
storage servers
Server Storage Network Software
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 11
Program Agenda
Introduction to Oracle Exadata Database Machine X3
Exadata Engineered System Architecture
Partner Webcast - Oracle Exadata X3 Database In-Memory Machine
Exadata Configuration & Optimization
Exadata Monitoring and Management
Q&A
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 12
Powered by hardware and configuration
Powered by software
• Transparent features
• Non-transparent features
Exadata Optimizations
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 13
InfiniBand network
– Connects the Exadata cells to the Database nodes
– Connects Database nodes for RAC interconnect
High speed, low latency (< 6 microsec) data transport
– Fast access to storage
– Extremely fast for RAC interconnect
Exadata Optimizations Powered by Hardware Infiniband technology
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 14
Direct access to network from applications
– No OS layer in between, no CPU usage
– Direct buffer-to-buffer communication, no CPU usage
You can optimize further by using infiniband
connectivity for
Rman backup/restore
Application server connection
File transfer ( etl, end-of-day, etc. )
Exadata Optimizations Powered by Software Using infiniband technology
• Using Flash card instead of SSD
• No problems with disk controller !
• 400GB Storage Capacity / card
• 4 cards in each Exadata cell
• 4 x 400GB Flash modules / card
• Capacity – performance
• 22.4 TB capacity/full machine
• 1,500,000 read IOPs/full machine
• 1,000,000 write IOPs/full machine
• 100 GB/s throughput /full machine
Copyright © 2009, Oracle Corporation and/or its affiliates – 15 –
Exadata Optimizations Powered by Hardware Flashcard technology
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 16
HOT
COLD
WARM
Smart Flash Cache
Smart Flash Logging
Write-Back Flash Cache
Smart Flash
Exadata Optimizations Powered by Software Using flashcard technology
All transparent Just enable Write-back
feature if you have
excessive write
operations that is buffer
busy waits
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 17
• Data Intensive processing runs in Exadata Storage Grid
• Filter rows and columns as data streams from disks (168 Intel Cores)
• Example: How much product X sold last quarter
• Exadata Storage Reads 10TB from disk
• Exadata Storage Filters rows by Product & Date
• Sends 100GB of matching data to DB Servers
• Scale-out storage parallelizes execution and removes bottlenecks
Exadata Optimizations Powered by Software Smart Scan technology
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 18
Simple Query Example
Select sum (sales) where Date=‘24-Sept’
Optimizer Chooses Partitions & Indexes to Access
• Scan compressed blocks in partitions / indexes
• Retrieve sales amounts for Sept 24
• 10 TB scanned • 1 GB returned
to servers
What were
my sales
yesterday?
Oracle DB Grid
Exadata
Storage
Grid
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 19
Exadata Optimizations Powered by Software Using Smartscan technology
Smart Scan capable Intelligent storage
– Scale-out InfiniBand storage
– Smart Scan query offload
+ + + Smart scan is transparent •Be careful about index usage.
•Use bulk read SQL for bulk
operations instead of row-by-row
processing.
•Direct path read operations
benefits from smart scans.
•Use partitioning and parallelism
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 20
SQL> show parameter cell_offload_processing
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
cell_offload_processing boolean TRUE
select /* testsql1 */ cust_first_name,cust_last_name,sum(order_total)
from soe.customers c,soe.orders o
where c.CUSTOMER_ID=o.CUSTOMER_ID
and c.customer_id=1
group by cust_first_name,cust_last_name;
Exadata Optimizations Powered by Software Enable Smartscan
select /* testsql2 */ cust_first_name,cust_last_name
from soe.customers
where customer_id=1;
select /* testsql3 */ sum(order_total) from soe.orders;
select SQL_TEXT,PHYSICAL_READ_BYTES,IO_CELL_OFFLOAD_ELIGIBLE_BYTES,IO_INTERCONNECT_BYTES
from gv$sql
where sql_text like '%testsql1%';
SELECT c.cust_name, s.date,
s.amount
FROM sales s, customers c
WHERE s.cust_id = c.cust_id;
Producers
Consumers
Exadata Optimizations Powered by Software Parallel Execution of a Query
Sales Table
May 22nd
2008
May 23rd
2008
May 24th
2008
May 18th
2008
May 19th
2008
May 20th
2008
May 21st
2008
Select sum(sales_amount)
From SALES
Where sales_date between
to_date(‘05/20/2008’,’MM/DD/YYYY’)
And
to_date(‘05/23/2008’,’MM/DD/YYYY’);
Q: What was the total sales
for the weekend of May 20 -
22 2008?
Only the 3
relevant
partitions are
accessed
Exadata Optimizations Powered by Software Partition Pruning
Select sum(sales_amount)
From
SALES s, CUSTOMER c
Where s.cust_id = c.cust_id;
Both tables have the same degree
of parallelism and are partitioned
the same way on the join column
(cust_id)
Sales
Range
partition May
18th 2008
Customer Hash
Partitioned
Sub part 1
A large join is divided into
multiple smaller joins, each
joins a pair of partitions in
parallel
Sub part 1
Sub part 2
Sub part 3
Sub part 4
Sub part 2
Sub part 3
Sub part 4
Sub part 2
Sub part 3
Sub part 4
Sub part 1
Sub part 1
Sub part 2
Sub part 3
Sub part 4
Exadata Optimizations Powered by Software Partition Wise Join
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 24
• Exadata Storage Indexes maintain summary
information about table data in memory • Store MIN and MAX values of columns
• Typically one index entry for every MB of disk
• Eliminates disk I/Os if MIN and MAX can never
match “where” clause of a query
A B C D
1
3
5
5
8
3
Min B = 1
Max B =5
Table Index
Min B = 3
Max B =8
Select * from Table where B<2 - Only first set of rows can match
Exadata Optimizations Powered by Software Storage Index technology
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 25
• Completely automatic and transparent
• Measure performance by selecting
V$SYSSTAT for information cell physical IO bytes saved by storage index
select name, value from v$sysstat where name like
'%storage%';
Optimized_phy_read_requests in v$sql
includes IOs skipped by storage index, IOs helped by exadata flash cache AND IOs helped
by DB server side L2 (flash) cache
Exadata Optimizations Powered by Software Using storage indexes Transparent I/O Elimination with No Overhead
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 26
• Data is organized and compressed by column
• Dramatically better compression
• Speed Optimized Query Mode for Data Warehousing
• 10X compression typical
• Runs faster because of Exadata offload!
• Space Optimized Archival Mode for infrequently accessed data
• 15X to 50X compression typical
Qu
ery
Faster and Simpler
Backup, DR, Caching,
Reorg, Clone Benefits Multiply
Exadata Optimizations Powered by Software Hybrid Columnar Compression
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 27
Qu
ery
Exadata Optimizations Powered by Software Using Hybrid Columnar Compression
HCC usage for Data Warehousing •All data are compressed with HCC by default in
some cases
HCC usage for OLTP •Recommended to use with partitioning.
Compress historical partitions or tables.
Automated with Oracle Database 12c
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 28
Exadata Optimizations Powered by Software Using Hybrid Columnar Compression
HCC syntax
Warehouse Compression Syntax:
CREATE TABLE emp (…)
COMPRESS FOR QUERY [LOW | HIGH];
Online Archival Compression Syntax:
CREATE TABLE emp (…)
COMPRESS FOR ARCHIVE [LOW | HIGH];
HCC advisor
DBMS_COMPRESSION PL/SQL Package
Estimates Hybrid Columnar Compress
storage savings on non-Exadata hardware
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 29
Exadata Optimizations Powered by Software IORM for Mixed Workloads and Multi-Database Environment
Exadata Cell
InfiniBand Switch/Network
Database A Database B
Exadata Cell Exadata Cell
• Ensure different databases are allocated the
correct relative amount of I/O bandwidth • Database A: 33% I/O resources
• Database B: 67% I/O resources
• Ensure different users and tasks within a
database are allocated the correct relative
amount of I/O bandwidth • Database A:
• Reporting: 60% of I/O resources
• ETL: 40% of I/O resources
• Database B:
• Interactive: 30% of I/O resources
• Batch: 70% of I/O resources
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 30
Exadata Optimizations Powered by Software Using IORM for Mixed Workloads and Multi-Database Environment
All Workloads, All Applications
SAP, Siebel, PeopleSoft, JDE, E-business Suite, Fusion
Applications
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 31
DB
OS
Server
Consolidation in the Database Cloud
Consolidation
Shared
Database
Shared
Server
Shared
Cluster
Storage
Dedicated
Silos
Oracle
Exadata
Consolidates
Servers, Storage,
OS and DB
ROI
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 32
Exadata Optimizations Powered by Monitoring Using Enterprise Manager 12c Cloud Control
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 33
Exadata Optimizations Powered by Monitoring Using Exachk utility
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 34
Exadata Optimizations Powered by Software Using Statistics
GATHER_SYSTEM_STATS mode exclusively for Exadata
exec dbms_stats.gather_system_stats(‘EXADATA’);
35 © 2013 Oracle Corporation 35
DBFS - Scalable Shared File System
• Database Machine comes with DBFS shared Linux file system – Shared storage for ETL staging, scripts, reports and other application files
• Files stored as SecureFile LOBs in database tables stored in
Exadata – Protected like any DB data – mirroring, DataGuard, Flashback, etc.
• 5 to 7 GB/sec file system I/O throughput
ETL Files in DBFS
Load into database using External Tables
New
ETL
More File Throughput than High-End NAS Filer
36
Extreme Performance Disruptive Technology = Integrated HW + SW Innovation
• Dynamically Scalable
• Unlimited Linear Scaling of Data Bandwidth
• Allows data to reside in extended memory
for 10x better IO response time
• 5.25 Terabytes Flash per rack
• 10X to 100X speedup for queries
• Database aware storage executes queries at the disk layer
• 40 Gb/sec fault-tolerant unified server and storage network
• Breaks data bandwidth bottleneck
• 10x to 50x compression ratios
• Optimized for speed and space reduction
Massively Parallel
Architecture
Hybrid Columnar
Compression
Infiniband Network
Flash Cache
Smart Scans
RAC
Database Parallelism
ASM
Partitioning
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 37
Oracle Exastack Program
Logo Example Products Benefits Messaging
• Oracle Database Ready
• Oracle WebLogic Ready
• Oracle Solaris Ready
• Oracle Linux Ready
• Oracle VM Ready
• Press Release template with Oracle quote • 50 Support SRs (app/tech) • Logo • Solution Brief • OPN Solutions Catalog search • Applied toward Platinum upgrade
Runs Better on the Latest Oracle Products
Supports latest major release
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 38
Oracle Exastack Program
Logo Example Products Benefits Messaging
• Oracle Exadata Ready
• Oracle Exalogic Ready
• Oracle SPARC SuperCluster Ready
• High visibility with customers & Oracle sales • Exec quote for Press Release • Featured on OPN Solutions Catalog entry page • Logo • Datasheet • Applied toward Platinum upgrade
Tested and Ready to Run on Latest Oracle Platform
Reference or Verify support & runs with Exadata/Exalogic
Logo Example Products Benefits Messaging
• Oracle Database Ready
• Oracle WebLogic Ready
• Oracle Solaris Ready
• Oracle Linux Ready
• Oracle VM Ready
• Press Release template with Oracle quote • 50 Support SRs (app/tech) • Logo • Solution Brief • OPN Solutions Catalog search • Applied toward Platinum upgrade
Runs Better on the Latest Oracle Products
Supports latest major release
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 39
Oracle Exastack Program
Logo Example Products Benefits Messaging
• Oracle Exadata Optimized
• Oracle Exalogic Optimized
• Oracle SuperCluster Optimized
• Access to Oracle Performance Experts • Optimization Labs • Maximum visibility with customers & Oracle sales • Success Story with Performance Metrics • Exec quote for Press Release stating Performance
Metrics • Featured on OPN Solutions Catalog entry page • Applied toward Platinum or Diamond upgrade
Runs Best on Oracle
Optimized to run faster and more reliably
Logo Example Products Benefits Messaging
• Oracle Exadata Ready
• Oracle Exalogic Ready
• Oracle SPARC SuperCluster Ready
• High visibility with customers & Oracle sales • Exec quote for Press Release • Featured on OPN Solutions Catalog entry page • Logo • Datasheet • Applied toward Platinum upgrade
Tested and Ready to Run on Latest Oracle Platform
Reference or Verify support & runs with Exadata/Exalogic
Logo Example Products Benefits Messaging
• Oracle Database Ready
• Oracle WebLogic Ready
• Oracle Solaris Ready
• Oracle Linux Ready
• Oracle VM Ready
• Press Release template with Oracle quote • 50 Support SRs (app/tech) • Logo • Solution Brief • OPN Solutions Catalog search • Applied toward Platinum upgrade
Runs Better on the Latest Oracle Products
Supports latest major release
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 40
Oracle Exadata TV
– http://www.youtube.com/exadataTV
– Direct from the Oracle Exadata Product Management & Development Team Oracle Exadata Database Machine Knowledge Zone
– http://www.oracle.com/partners/en/products/servers-and-storage/exadata-
database-machine/get-started/index.html
– Oracle Partner Network Guided Learning Path, Specialization Oracle ExaStack Program
– http://www.oracle.com/partners/en/opn-program/oracle-exastack
– Enables Oracle ISV partners to leverage a scalable, integrated infrastructure
to deliver their applications tuned, tested and optimized for high-performance
Exadata Resources
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 41
CONNECT WITH US
COMMUNICATE WITH US [email protected]
ORACLE.COM/SUBSCRIBE
Adopt & Implement the latest by Oracle
blogs.oracle.com/imc
twitter.com/oracleimc
youtube.com/OracleIMCTeam
facebook.com/OPN.PartnerHub.Migration.Center
ORACLE.COM/PARTNERS/GOTO/HUB-ECEMEA
Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 42