Oracle E-Business Suite, Exadata, and Smart Scan

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
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:
– “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