optimizing oracle e-business suite on exadata - · pdf fileabout the presenter !john clarke,...
TRANSCRIPT
Optimizing Oracle E-Business Suite on Exadata
John Clarke Oracle Architect Centroid
Now What?
About the Presenter
Ø John Clarke, Sr. Oracle Architect at Centroid Systems, Inc. Ø Specialized in:
Ø Oracle Exadata Ø Performance Tuning Ø Oracle Real Application Clusters Ø Data Warehousing Ø Oracle Linux Ø Oracle Database 11g
Ø [email protected] Ø www.centroid.com Ø Blogs at www.centroid.com/knowledgebase Ø Sporadically adds random Oracle content at twitter.com/#!/jccclarke
Optimizing EBS on Exadata
One Size Fits all
Best Practices Tuning EBS
On Exadata
Why Not?
Alternate Title: Some Performance-Related Things to Think About with Oracle EBS and Exadata …
Today’s Goal
Ø Exadata fundamentals Ø What makes Exadata Different Ø Exadata Software Features Ø How the Features work with EBS
Exadata Hardware Compute Grid
Dual-core x64 servers running Oracle 11gR2
InfiniBand Network • Redundant 40 Gb/s switches • Unified fabric for storage and servers
Storage Grid • 12 HP or HC SAS drives • 384Gb PCIe Flash/cell • Data mirrored across cells with ASM
Exadata Software Exadata Software Goals
Fully and evenly utilize all Computing resources in
Exadata Database Machine to eliminate bottlenecks and
deliver consistent high performance
Reduce demand for resources by eliminating any IO that can be discarded without impacting
result
Smart Scan
Ø Probably the most important software feature with Exadata Ø One of several “cell offload” features in Exadata Ø “cell offload” is defined as Exadata’s shifting of database work
from the database servers to Exadata storage servers Ø Goal of Smart Scan is to perform the majority of IO processing on
storage servers and return smaller amounts of data from the storage infrastructure to the database servers
Ø “IO no longer a bottleneck” Ø Provides dramatic performance improvements for eligible SQL
operations (full table scans, fast full index scans, joins, etc.) Ø Smart Scan is implemented automatically on Exadata Ø Smart Scan only works on Exadata
Traditional SQL Processing
User submits a query select flow_status_code,count(*)!
from oe_order_headers!
group by flow_status_code!
The query is parsed and an execution path is determined. Extents are identified
IO is issued – Oracle retrieves blocks based on extents identified. A block is the smallest unit of transfer from storage system, contains rows and columns
Database instance processes blocks – all required blocks read from storage system to buffer cache
Oracle filters rows and columns, once loaded to buffer cache, and returns to user
Unit of IO = Database Block
Smart Scan Processing
User submits a query select flow_status_code,count(*)!
from oe_order_headers!
group by flow_status_code!
An iDB command is constructed and sent to Exadata cells
Each Exadata cell scans data blocks and extracts relevant rows and columns that satisfy the SQL query
Each Exadata cell returns to the database instance an iDB message containing the requested rows and columns. These are not block images and are returned to the PGA
The database consolidates results from across each Exadata cell and returns rows to the client
Unit of IO = Rows/Columns
IO: Non-Exadata
IO: Exadata
Smart Scan
Ø Filtering operations are offloaded to the Exadata storage cell Ø Column Filtering Ø Predicate Filtering (i.e., row filtering) Ø Join Filtering
Ø Only requested rows and columns are returned to DB server Ø Significantly less IO transfer over storage network Ø Less memory and CPU required on database tier nodes Ø Rows/columns retrieved to user’s PGA via direct path read
mechanism, not through buffer cache. Ø Large IO requests don’t saturate buffer cache
What Operations are Eligible for Smart Scan?
Ø Single-table full table scans *** Ø Fast full index scans *** Ø Scans must qualify for serial or parallel direct reads !!! Ø Other stuff like bloom filtering … Ø Look at V$SQLFN_METADATA
• Oracle is a C program • kcfis_read is the function
that does smart scan • kcfis_read is called by
direct path read function, klbldrget
• klbldrget is called from full scan functions
Smart Scans and Oracle EBS
Ø CBO must choose full scan Ø Must qualify for direct reads Ø Direct reads are influenced by:
Ø Size of segment being scanned, and … Ø Whether BLOCKS > “_small_table_threshold”, and … Ø How many blocks in buffer cache, and … Ø How many dirty blocks in buffer cache
So segments must be large, and not already read into buffer cache by other means (i.e., large index range scans) Check out http://www.centroid.com/knowledgebase/blog/direct-reads-and-smart-scan-with-exadata
Digression: The History of Tuning Oracle EBS
Ø Oracle EBS code is index-heavy and hint-heavy Ø Oracle code tries to make every problem a “single-row access”
problem – after all, it’s an OLTP application, right? Ø On Exadata, IO no longer the problem (or is it?) … Ø But code-base doesn’t make it easy for Smart Scan to kick in
Oracle launches EBS, long time ago
Customers around the world have
performance issues
Performance is largely IO waits
Oracle over-indexes and over-hints to force index scans
Smart Scans and “_small_table_threshold”
Ø “_small_table_threshold” = 2% db_cache_size Ø Direct reads are done in 11g when blocks >
“_small_table_threshold” Ø Large cache sizes mean fewer Smart Scan “qualifiers” Ø Exadata DBCA templates typically use 16GB SGA_TARGET Ø DB_CACHE_SIZE usually 10-12GB after cache warming Ø (10GB * 2% ) / 8k = 26,244 blocks – this is how large our
segments need to be for Smart Scan
Smart Scan Qualifiers, Mid-Sized EBS Environment
Ø Smart scan qualifiers = full scans with direct read Ø Check DBA_HIST_SEG_STAT.TABLE_SCANS_TOTAL Ø Join to DBA_SEGMENTS, get BLOCKS Ø Determine your target buffer cache size on Exadata Ø Test case: 98 segments had full table or index scans Ø 19 of these would qualify for direct reads with 10G buffer cache
Ø 35 would qualify for direct reads with 4GB buffer cache
Smart Scan Qualifiers, Large-Sized EBS Environment
Ø Smart scan qualifiers = full scans with direct read Ø Check DBA_HIST_SEG_STAT.TABLE_SCANS_TOTAL Ø Join to DBA_SEGMENTS, get BLOCKS Ø Determine your target buffer cache size on Exadata Ø Test case: 760 segments had full table or index scans Ø 227 of these would qualify for direct reads with 10G buffer cache Ø 329 would qualify for direct reads with 4GB buffer cache
Smart Scan and Multi-Org
Ø In R12, Multi-Org uses SYS_CONTEXT call and RLS Ø SYS_CONTEXT not offloadable (check V$SQLFN_METADATA) Ø Full scans can be offloaded, but row-filtering done via RLS and
handled on compute node
Influencing Smart Scan
If you’re not seeing Smart Scans, don’t try to force it! Ø Make buffer cache smaller (!) Ø Test with “_serial_direct_read”=ALWAYS Ø Make indexes invisible Ø Test with “optimizer_ignore_hints”=TRUE These are all hacks, except possibly the index tip
Smart Scan: Is hope lost with EBS?
Ø Local buffer cache access isn’t such a bad thing, and often appropriate
Ø Smart Flash Cache can help … Ø Configuring hugepages can help grow even larger Ø Each compute node has 96GB RAM Ø Think about mixed workloads and EBS – more on this later …
Smart Scan Test Case, dropping OLTP indexes
Ø Load 100k rows into MTL_TRANSACTIONS_INTERFACE Ø Measure inventory/cost transaction worker request timings Ø Gather AAS/AWR data
Smart Scan Test Case, dropping OLTP indexes
Ø Load 100k rows into MTL_TRANSACTIONS_INTERFACE Ø Make INV/BOM indexes invisible and run same test
Ø Yikes! Force serial direct reads …
Smart Scan Test Case, dropping OLTP indexes
AWR compare …
Smart Scan Test Case, Material Transactions Form
Ø Test: Query material transactions based on a date Ø Enter a date range in which we know there are a lot of
transactions Ø Enable trace Ø Make buffer cache small
Smart Scan Test Case, Material Transactions Form
Ø First Test: Query transactions for 2 different date ranges, one with many rows, one with few
Ø No Smart Scan
SBR/MBR
Smart Scan Test Case, Material Transactions Form
Ø Second Test: Disable Indexes on MMT and Repeat
Ø Still No Smart Scan !!!
SBR/MBR
Smart Scan Test Case, Material Transactions Form
Ø 3rd Test: Run same query from SQL*Plus
Ø Smart Scan … hmm
Smart Scan
Smart Scan Test Case, Material Transactions Form
Ø 4th Test: Set “_serial_direct_read”=always, test from Form
Ø Smart Scan!
SBR/MBR
Smart Scan: Forcing Serial Direct Reads
What’s the impact of forcing serial direct reads?
Smart Scan Test Case, Material Transactions Form
What does it all mean? Ø Making indexes invisible allows full scans Ø Even though our block count made it eligible for cell offload, serial
direct reads didn’t happen … Ø Setting “_serial_direct_read”=always forced direct reads, enabled
storage servers to do Smart Scan Ø Why is it sometimes difficult to get direct reads? Ø … Direct read algorithms are complicated
Ø Segment size is important Ø Cached blocks also important Ø Buffer cache load plays a role
Ø If you force it, be wary of concurrency issues
Measuring Smart Scan
Ø Execution plans have “TABLE ACCESS STORAGE FULL” or “INDEX STORAGE FAST FULL SCAN”, etc
Ø V$SQL.IO_CELL_OFFLOAD_ELIGIBLE_BYTES > 0 Ø Statistics from V$SYSSTAT, AWR
Ø “cell physical IO bytes eligible for predicate offload” Ø “cell physical IO bytes returned by smart scan”
Ø See http://www.centroid.com/knowledgebase/blog/exadata-smart-scan-processing
Hybrid Columnar Compression
The Basics: Ø Only available on Exadata *** Ø Data is compressed by column, not row, and stored in
“Compression Unit” (CU) Ø CU = set of blocks Ø Standard or Advanced compression unit = database block Ø HCC compression unit = CU Ø Compression ratios are much better than standard or OLTP
compression Ø Better compression = less disk space = sometimes better
performance
Hybrid Columnar Compression What You Need to Know: Ø HCC compressed when loaded via direct path loads Ø HCC achieves higher compression because:
Ø Data is grouped into arrays upon direct path loads – the “input set” is larger, giving Oracle more data to consider for dupes
Ø Compression algorithms are more aggressive than other Oracle compression flavors
Ø DML against HCC tables Ø Does a row migration for current row and inserts into a OLTP-
compressed block Ø Locks the entire CU
Ø Selects retrieve column per CU, not entire block Ø When retrieved via SS, decompression happens on cell. When
done via SBR, decompression occurs on database server
Hybrid Columnar Compression
Hybrid Columnar Compression
What it Means for EBS Ø You only want to HCC-compress read-only data Ø … think about “old” partitions and ILM policies Ø Don’t compress with HCC if you’re doing DML Ø Target candidates that full-scan via Smart Scan – otherwise, CPU
penalty on compute node
Hybrid Columnar Compression and EBS
Ø HCC can save massive amounts of disk space Ø HCC can improve performance Ø Partition large EBS tables according to ILM policies Ø HCC-compress old partitions Ø Examples
Ø Partition GL_BALANCES and GL_JE_LINES by PERIOD_NAME
Ø Partition GL_IMPORT_REFERENCES by JE_HEADER_ID Ø Composite-Partition XLA_AE_HEADERS, XLA_AE_LINES,
and XLA_EVENTS by (APPLICATION_ID,ACCOUNTING_DATE)
Ø … and more
HCC Case Study
Ø Partition and HCC-compressed based on previous example Ø HCC “compress for query high” for all partitions containing data
from 1995 through 2009 Ø Leaving 2010 and 2011 partitions uncompressed Results Ø 10x disk space savings Ø 4x PIO savings for Trial Balance, Journal Entries Detail, and
Create Accounting concurrent programs Ø 6x PIO savings for drill-down month end reports Ø CPU/LIO savings =~ 10-20% Ø Concurrent request improvement =~ 40% *** FND_FILE.PUT Ø SQL performance improvement =~ 70%
IORM
Ø IORM = IO Resource Management Ø Means to meter IO to Exadata cells Ø Works with DBRM Ø If consolidating, IORM is imperative Ø If not consolidating, DBRM/IORM still makes sense! Don’t be afraid …
Why IORM?
Ø Many databases Ø Different types of users Ø Different workloads Ø Different SLAs Ø Over-provision storage … ?
Ø … but how economical is this? DBRM + IORM is the answer
IORM
Some basics … Ø Resource consumer groups map sessions by workload Ø Consumer groups + session mappings = resource plan Ø Consumer groups assigned to categories – key for IORM Ø Exadata IORM plan = database plan + category plan
IORM and EBS
DESIGN and PLANNING are critical – consider example … Ø PROD, DEV, and TEST Ø PROD should take priority Ø PROD is comprised of:
Ø OLTP/online users => EBS_OLTP CG Ø Reporting users = EBS_REPORTING
Ø DEV and TEST take back-seat Ø Same types of consumers as PROD Ø Different % allocations per consumer
IORM and EBS: Step 1:
IORM and EBS: Step 2:
IORM and EBS: Step 3:
IORM and EBS: Things to Think About
Ø Do you have a dedicated reporting environment? Ø How robust is it? Ø How much time did it take to build and test? Ø Don’t you wish you didn’t have one? Ø IORM = ability to run production reporting in production and not
worry about it! Ø Can same be said about DBRM? Maybe …
Storage Indexes
Ø Storage indexes <> normal indexes. Not at all … Ø Goal: Eliminate unnecessary IO
Ø Not by directing you specifically to table rows/blocks, but Ø By telling you with absolute certainty where not to look
Storage Indexes
Ø Data stored in ASM Allocation Unit Ø Each 4M AU comprised of four 1MB storage regions Ø Storage regions track high and low values for columns in region
indexes Ø Region indexes are your storage indexes
Storage Indexes
Ø When blocks access via Smart Scan, Exadata automatically tracks high and low values in region index
Ø When queries access blocks, Exadata cellsrv checks these region indexes to see if any rows are contained in the storage region – if not, IO to storage region is skipped
Things to Know about Storage Indexes
Ø Maintained over time as data is accessed via Smart Scan Ø Region indexes maintain high and low values for up to 8 columns
on a table Ø Storage indexes are automatically maintained by Exadata –
there’s nothing you can do to directly influence them Ø They’re just memory structures – not persistent across cell
reboots Ø Can return false-positives, never false negatives Ø Can provide dramatic performance under the right conditions:
Ø Region indexes populated during Smart Scans Ø Data needs to be well-ordered to offer elimination candidates
Storage Indexes and Oracle EBS
Ø Need Smart Scan (serial direct reads, no index lookups, etc.) Ø Since data needs to be well-ordered, FIFO columns like
TRANSACTION_DATE, ORDER_DATE, ACCOUNTING_DATE, etc. may work well
Ø Shouldn’t rely on them for optimization – difficult to predict and
not deterministic
Storage Index Example
Ø Build a copy of MTL_MATERIAL_TRANSACTIONS and order by ORGANIZATION_ID
Ø Ensure Smart Scan/Direct Reads
Smart Flash Cache
Smart Flash Cache
Ø Smart Flash Cache is both HW and SW Ø Each Storage cell has 384GB of PCIe Flash Ø The storage server software determines what to cache in Flash
Cache Ø Frequently accessed data or index block Ø Control file reads and writes Ø File header blocks Ø Anything a DBA/developer chooses to “KEEP”
Ø Smart Flash Cache won’t cache anything it shouldn’t: Ø ASM mirror copies Ø Backup-related IO Ø Data Pump stuff
Ø Goal – cache what makes sense!
Smart Flash Cache – What you Need to Know
Ø Data in Flash Cache persists across instance/database bounce Ø By default, the entire capacity of PCI Flash Cards is dedicated to
Smart Flash Cache – look at this as a secondary, high speed cache
Ø You can KEEP or UNKEEP specific segments from Flash Cache Ø You can place permanent objects in Flash Cache by building
flash grid disks
Smart Flash Cache – Writes and Reads on Un-cached Data
Smart Flash Cache – Reads from Cached Data
Smart Flash Cache and Oracle EBS
Ø Configure all of your PCI flash for Smart Flash Cache Ø Don’t KEEP segments in Smart Flash Cache with “storage
(flash_cache keep)” unless specific testing shows benefits Ø Don’t put applications tablespaces in flash; i.e. don’t permanently
store objects in ASM disk groups based on flash grid disks Ø In short – let Flash Cache automatically do its thing unless
specific throughput requirements mandate a deviation
Exadata Smart Flash Logging
Ø Starting with Oracle 11.2.0.3 and Cell version 11.2.2.4, Oracle introduced Smart Flash Logging
Ø LGWR writes to Flash and Disk simultaneously Ø By “Disk” I mean “cell server DRAM cache”, = 512MB Ø The first one done sends write acknowledgement to LGWR Ø On latest versions, don’t put redo logs on Flash Grid Disks Ø Potentially a very good improvement …
So What in EBS Will Work Well on Exadata?
Ø Long-running batch jobs on large tables that do full scans Ø Think month-end, year-end close Ø Things like MRP, Create Accounting, BIS Refresh, Payroll, etc. Ø Optimize application code, not HW Ø Use the tools you’ve got to evaluate:
Ø Create a SQL Tuning Set on your current platform Ø Use SQL Performance Analyzer – use the Exadata Simulation Ø Look at AWR reports for historical SQL doing full-scans
So What in EBS Will Work Well on Exadata?
Optimizing Oracle E-Business Suite on Exadata
Questions?