why exadata is not so quite good for EBS and other OLTPs

http://www.centroid.com/knowledgebase/blog/oracle-e-business-suite-exadata-and-smart-scan

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”