Oracle E-Business Suite, Exadata, and Smart Scan

February 21th, 2012 | Written by John Clarke

 

You've taken the plunge and are planning on moving your Oracle EBS environment to Exadata.  In this post, I'll talk about some of the things you should think about with Oracle EBS and Exadata Smart Scan
 
What is Smart Scan?
 
Smart Scan is probably the most important software feature on Exadata with respect to being able to provide extreme performance.  Smart Scan processing is one of the components of Exadata Cell Offload Processing whose goal is to offload processing to storage cells instead of performing block IO requests in the database tier.  The goal of Smart Scan processing is to do all the “heavy lifting”, IO-wise, in the storage cell and only send back data to the compute nodes that is actually needed and requested.  This reduces interconnect traffic between the storage tier (storage cells) and database tier, reduces the clutter in the database buffer cache, improves query performance, and improves concurrency for multiple full-scan types of queries happening at once. With Smart Scan processing, row filtering, column filtering, some join processing, and other functions are performed in the Exadata cells.  Results are returned to the foreground session’s PGA instead of the shared buffer cache.  With Smart Scan:
 
1) User issues a SQL statement
2) Optimizer determines whether SQL statement is offloadable based on predicate(s).
3) An iDB command is constructed and sent to Exadata cells
4) SQL is processed in Exadata cells
5) Filtered result set is returned from disks (or FastCache or Storage Index region) to cell server
6) A consolidated result set is built from all Exadata cells
7) Rows are returned to compute nodes.
 
What's Eligible for Smart Scan?
 
- Single-table full table scans, as long as there are no IOTs, BLOBs/CLOBs, or hash clusters
- Fast full index scans
- Join filtering using Bloom Filters
- Check out V$SQLFN_METADATA for a list of all operations that are off-loadable
 
In addition, the segment(s) being full-scanned must qualify for serial direct reads.  In 11g, Oracle introduced a performance enhancement to allow data being requested via full table/index scan to be read via serial direct reads; in other words, the blocks are read into the user’s PGA and not the database buffer cache.  This serial direct read enhancement is not specific to Exadata; it’s an Oracle 11g enhancement.  But what’s important is this – the code path for Smart Scan resides under the serial direct read stack:
 
- Oracle is a C program
- kcfis_read is the function that performs Smart Scan
- kcfis_read is called by the direct read function, klbldrget
- klbldrget is called from full scan functions
 
So for Smart Scan to work:
 
- The optimizer must choose a full scan
- The segments must qualify for serial direct reads
 
Smart Scan and Oracle EBS
 
We know that Smart Scan only works when serial direct reads are done on a table or index segment.   This means that CBO must choose a full scan, and the full scans must do serial direct reads.  How does Oracle choose to do serial direct reads?  It doesn’t perform these for all full-scan operations – it uses a complex formula that’s based on the below:
 
- The size of the table/index segment must be larger than 5 * “_small_table_threshold”
- “_small_table_threshold” is compute as 2% of the size of the buffer cache
- Additionally, Oracle must not be able to find a large portion of the segments blocks already in the buffer cache
T
he above implies that the larger the buffer cache, the fewer serial direct reads will take place.  Exadata compute nodes have 96GB of memory.  The standard DBCA template for database on Exadata creates a 16GB SGA, and after cache warming in EBS we typically see the size of the buffer cache between 10Gb and 12Gb.  Assuming the lower-range of this, this means:
 
(2% * 5 * 10GB)  / 8k block size = 131,072 blocks = minimum size at which tables/indexes will qualify for serial direct reads.  
 
If you’re an Oracle EBS customer, think about this - how many tables do you have whose block count is greater than 5 * 2% of your buffer cache?
 
Some High Level History of Optimizing Oracle EBS
 
- Oracle EBS is extremely heavily indexed
- Oracle EBS is hint-heavy
- Oracle EBS developers have tried to make every problem a single-row problem, to address historical IO issues
- This, in combination with table sizes, makes it difficult for Smart Scan to “kick in” 
 
Influencing Smart Scan
 
Influencing Smart Scan is all about influencing serial direct reads.  Here are some things to try:
 
- Make buffer cache smaller (!)
- Test with “_serial_direct_read”=ALWAYS
- Make indexes invisible
- Test with “optimizer_ignore_hints”=TRUE
- Seed misrepresentative statistics with FND_STATS
 
With the exception of possibly the 3rd bullet, these are all hacks and should be used carefully – a bit disappointing.
 
Final Words about Smart Scan and EBS
 
You may have difficult getting Smart Scan to happen in Oracle EBS because you may have trouble getting your queries to do full scans via serial direct reads.  Understand what influences serial direct reads, and construct test cases to prove things out.  You *may* end up dropping indexes, but test carefully when doing so - local buffer cache access and single-row lookups is still significantly better than cell offload in many cases.
 
And before launching into a test plan, make sure you know how to measure Smart Scan:
 
- V$SQL.IO_CELL_OFFLOAD_ELIGIBLE_BYTES > 0
- "cell smart table scan" and other "cell%smart%" wait events
- "cell physical IO bytes eligible for predicate offload" statistic
- "cell physical IO bytes returned by smart scan" statistic
 

Comments

Hi John,     There are huge

Hi John,

 

    There are huge stumbling blocks in front of your logic on this topic. Doug and Mark have tried to help you see that.

    Your thesis all seams to be around cramming Smart Scan into the solution. Your assertion that Smart Scan requires serial direct read. It does not. It just needs a direct read because the payload produced from a Smart Scan is not block and thus cannot be buffered (cached) in the SGA. 

     When I was working with Kerry and Tanel on Expert Oracle Exadata (Apress) I had in mind folks like you that would benefit from good, clear explaination of the product. One of the things I recall bringing to the authors attention (I served the lowly role of technical reviewer of the book) is the fact that many (if not most) Smart Scans in products like E Business Suite are actually second-quessed by cellsrv because of FIRST_ROW hints. If the cells see that hint in the Smart Scan request metadata they read the first 1M of the segment and return it to the SGA. Think of a Smart Scan that is down-graded to a non-Smart Scan by storage. 

      Unfortunately I can't take this topic on any more in a point-counterpoint manner. I would highly encourage you to read Expert Oracle Exadata and my blog. You'll get a lot further in your understanding of Exadata.

      This topic can be boiled down quite quickly. Exadata is not specialized for E Business Suite. To add to that I'll point out that while it might not matter to people, Exadata has been a production product for four years and yet Oracle corporation runs their E Business Suite GSI implementation on M9000 gear attached to conventional storage.

      E-Biz was not the design center.

I wouldn't usually leave a

I wouldn't usually leave a comment on a blog that requires me to register (sigh, why do companies do that?) but I found this post slightly baffling.

>> You've taken the plunge and are planning on moving your Oracle EBS environment to Exadata.

Given what the rest of the post goes on to say, the question that leaps into my mind is 'Why?' Why would you choose to port your well-tuned OLTP system to a configuration designed to maximise throughput against large data sets? Maybe part of this 'taking the plunge' might have been better happening after you'd established the facts and didn't need to hack around to fix an inappropiate platform?

>>- Oracle EBS is extremely heavily indexed

>> - Oracle EBS is hint-heavy

>> - Oracle EBS developers have tried to make every problem a single-row problem, to address historical IO issues
>> - This, in combination with table sizes, makes it difficult for Smart Scan to “kick in”
 
So why on earth write a blog post about how to shoe-horn a system that has been tuned to perform well using a specific strategy into something that might be able to use the latest-and-greatest-but-entirely-inappropriate technology? Don't you think there might be a reason why Oracle doesn't use Smart Scan or serial direct path reads for *everything*?

>> some things to try:
 
>> - Make buffer cache smaller (!)
>> - Test with “_serial_direct_read”=ALWAYS
>> - Make indexes invisible
>> - Test with “optimizer_ignore_hints”=TRUE
>> - Seed misrepresentative statistics with FND_STATS
 
>> With the exception of possibly the 3rd bullet, these are all hacks and should be used carefully – a bit disappointing.
 
So, in summary, wouldn't it be fairer to say that Exadata is not the best EBS platform?

It just seems madness to me to say that Oracle have sadly decided not to Smart Scan small tables (mmm, wonder why?) but here are a few hacks to try to make it do so *even if it's not a good idea*. Tweaking underscore parameters? Making your buffer cache smaller? (Hardly going to help with all those hints) .... It all feels so *90s* and evidence-free ....

Doug,Agree 100% that

Doug,

Agree 100% that "establishing the facts" before taking the plunge is great advice - something customers don't always do, however, which is sometimes puzzling considering their investment.   In addition to being sold based on performance, Exadata is also positioned for database tier consolidation and in my experience, a (maybe small) percentage of companies buy and simply *assume* they'll realize performance gains downstream.

 

The point of the post was to attempt to illustrate that *despite* all the marketing claims, not all workloads will run faster on Exadata "just because it's Exadata" - there's a substantial code-base that's truly OLTP-centric that will always work best with single-row reads from buffer cache.  And an LIO on Exadata is pretty much the same cost as an LIO on anything else, all memory configuration being equal ...

 

This being said, there *are* true, OLTP-workload processes in EBS that will qualify for things like Smart Scan, and will run significantly faster with it.  Processes like MRP, Payroll, Create Accounting, Demantra forecasting, etc. are all cases when, for large EBS customers with multi-TB databases, even the indices are large enough to qualify for offload under index full scan conditions.  Not to mention ad-hoc or (otherwise) reporting solutions, which exist in some degree at many large, mature EBS sites.

 

I'm simply trying to convey this message ... here's what EBS looks like from a standard code-base perspective, here's what Exadata's features are, and here's how some of them marry nicely performance-wise and here are some of your obstacles.

 

Am I proposing hacks and workarounds, across the board,  to shoe-horn EBS into Exadata?  I'm not - I'm just trying to arm folks with testing and troubleshooting techniques to help them figure out why their shiny new Exadata may not make things magically faster.

 

The post was not intended to show people how to *not* do things right - apologies if it came across this way.  

 

Having said all of this, am I a fan of EBS on Exadata?  I am, because:

 

  • OLTP-ish parts of it work just as well if not better (with Smart Flash Cache)
  • The "outliers", under the right conditiions (namely data volume), will qualify for offload and give much better results.  
  • You've got the ability to save tremendous amounts of storage (and performance) for "old' data in many problematic areas, like Subledger Accounting 
  •  

A couple other comments .. when you classify Oracle EBS as a "well-tuned OLTP application",  I agree for most of the code, but not all of it.  There are more cases than I can count where Oracle's tried to forced a multi-row problem into a single-row optimization to avoid disk reads at all costs - and this is a challenge that EBS customers on Exadata face.   An MRP process that does a billion LIOs and runs in an hour because it's hinted and indexed up and down doesn't make it right.

 

As to why Oracle doesn't allow Smart Scan on all tables - it all has to do with where Smart Scan code is in the kernel (under serial direct read functions, under full scan). It'd be interesting to see a design where it didn't rely on serial direct reads, but forcing direct reads for *everything* would likely lead to some ugly "enq: KO – fast object checkpoint" waits on popular tables that have blocks in cache via index scans and different access patterns.

 

 

Hi John, It's not clear to me

Hi John,

 

It's not clear to me what your central point is here.

 

Yes, smart scans are great, in cases where you have a huge database and really big full table scans happening.  However, in a system like eBS, which is generally thought of as OLTP, I don't see any reason for smart scans.  Why?  Is it likely that forcing FTS and smart scan in place of an indexed table lookup will improve performance?  Of course, every case is different, but, in general, I don't think it ever makes sense to use smart scans where you had an efficient index driven lookup.

 

It seems to me, if you don't have a large Data Warehouse or Decision Support System where FTS is the primary data access method, I don't see how you can get the bang for the buck out of Exadata.

 

I'm not saying that OLTP won't run on Exadata, but I am saying that it probably won't run any better on Exadata than it will on any other equivalent-sized database server.  The "special sauce" of Exadata, i.e., smart scans, storage indexes, etc, really aren't relevant for OLTP, yet with Exadata, you need to pay those licensing costs, whether you need those features or not.

 

So, for OLTP workloads, I simply fail to see how Exadata is a cost-effective solution.

 

-Mark

The central point is to

The central point is to attempt to illustrate that Exadata isn't going to make everything faster with Oracle EBS environments ...

 

EBS *is* generally thought of as an OLTP application and if it were truly just this, the benefit of Exadata probably boils down to Smart Flash Cache and HCC.

 

But we've got EBS customers in which it's commonplace to have 500-million or billion+ row tables that they (gasp) full-scan or partition full-scan regularly during planning/forecasting runs, month-end close reports, etc.  And in these cases, Exadata (and Smart Scan, specifically, with maybe a bit of HCC thrown in) can and do shave days off their month-end close cycle and enable them to forecast demand at a much greater frequencies - both having a direct benefit to the bottom line.

 

What Exadata gives these customers is:

  • Great performance for their *true* OLTP-ish code base
  • Business-enabling performance for the boundary cases
  •  

EBS is an OLTP system 25 days out of the month.  But for the remaining 4-6 days, it's mixed, OLTP and "warehouse"-ish.  And the large, FTS-type queries run against relational tables,  unsummarized data, etc.  The larger the company, or more precisely, the higher the transaction volumes during that 25-day period, the more data to sift through during month-end (via full-scan).  Traditionally, these types of companies implement dedicated reporting environment strategies (i.e., spend money to move data away from OLTP so OLTP can survive),  implement code-intensive purge and archive strategies (i.e., spend money on expensive developers), and at the end of the day it's a constant struggle to keep that 5-day period from turning into 6 days, and then 10, and then spend more money to try to shrink it.  With Exadata, the 5 days can turn into 2 days and stay at 2 days for years.  Anyone who's struggled with Create Accounting running for a week can attest to the potential of offloading full-scans ...

 

Does Exadata make sense for the "normal-sized" EBS implementation though?  Considering all the stuff in the post, I'd say probably not, unless you're going to invest anyway for other types of applications and are thinking consolidation.

 

The bang for the buck is there, under the right circumstances.  The blog was an attempt to help folks understand what those circumstances are and what some of the challenges may be in realizing the benefit.

 

 

the benefit of Exadata

the benefit of Exadata probably boils down to Smart Flash Cache and HCC

Well, HCC is not OLTP feature. On the contrary, it's great for mass data loading and mass data scans later.

Anonymous, I understand the

Anonymous,

 

I understand the use-case for HCC ... consider this type of situation:

 

  • Transactional data trickles in to transactional tables on a constant basis every day (i.e., orders, receipts, invoices, material transactions, etc.)
  • At the end of each month, the data is summarized and inserted somewhere else (i.e., posted to GL)
  • After this, the transactional data never sees another DML.  Ever. 
  • ... Yet, from time to time, reporting needs may someday require drill-down into or summarization of this transcational data

This scenario pretty much summarizes how data is transacted and queried in Oracle EBS.  The data posted to GL from subledgers ceases to resemble anything close to "OLTP data", because no "TP" ever takes place on it - all that's ever done with it is reporting and large, full-scan types of queries.

 

In cases like these, a nice application of HCC is to:

 

  • Partition the transactional tables by transaction_date, accounting_date, event_date, or any other DATE data type that represents the unit that serves as the line of demarcation between when it's "active" and when it's "historical"
  • HCC-compress the old partitions

There's some detail and maintenance considerations involved with how to accomplish this with minimal downtime and so forth, but in a nutshell this is an approach that can save you a tremendous amount of storage as well as improve efficiency of your historical reporting.