What is DBFS?

DBFS stands for Database File System, and it’s Oracle’s method for housing an operating system-like file-system within an Oracle relational database.  It has its roots with iFS, but its functionality and capabilities have been extended.  Here are some reasons why DBFS is a good thing:

 

What is SQL Compiler Tracing?

SQL compiler tracing is a method to generate trace files that indicate what steps and actions the cost-base optimizer (CBO) used to determine a SQL statement’s execution plan.  It is an extension of the 10053 trace that’s been around for years, enhanced in 11g to provide arguably easier syntax and additional features.

 
What is SQL Test Case Builder?

SQL Test Case Builder is a diagnostic tool in 11g, back-ported to 10g, that is designed to be able to obtain a reproducible test case for developers, DBAs, or Oracle Support. 

 

Starting with 11g, Oracle introduced a new tool to add to the performance optimizer’s toolkit called “Real Time SQL Monitoring”.  Real Time SQL Monitoring, or just “SQL Monitoring”, enables you to measure the performance of SQL statements while they are executing.  SQL Monitoring is enabled any time a statement runs in parallel or when it consumes more than 5 seconds of CPU or IO time.

 

Exadata Smart Scan processing is one of the key components of Exadata's "secret sauce". It enables certain operations to be offloaded to the storage cell, reducing the amount of work required on the database tier nodes and reducing the impact of shipping blocks over the storage interconnect. Scripts are provided in this that can be used to monitor your own Exadata environment.

 
IO Resource Management (IORM) provides a means to govern and meter IO from different workloads in the Exadata Storage Server.
 
What is a Storage Index?

Storage Indexes are a feature unique to Oracle Exadata. A storage index is a memory-based structure that reduces the amount of physical IO required by the cell. A storage index keeps track of minimum and maximum values from query predicates and builds storage index regions based on usage.

 

Oracle publishes that Exadata IO works best with a 4MB allocation unit size for ASM disk groups, so ASM disk groups should be created with AU_SIZE=4M.  Further, Oracle also recommends that segment extents sizes should be a multiple of 4Mb if they’re to be accessed via Smart Scan. Here, we’ll attempt to demonstrate why this is the case.

 

We had quite a bit of discussion about where our extents resided on our Exadata Grid Disks.  The question was posed – if Exadata farms out IO requests to each cell in our storage grid, how does it know which extents reside on which disks? How do these get placed on grid disks, and is Exadata intelligent enough to know exactly where to look for the extents or will it simply search for matching extents regardless and allow the result set to determine where the data resides?

 

The “_small_table_threshold” setting controls a few cell offload functions, such as what types of tables will qualify for caching in Smart Flash Cache.  Here, I’ll try to reason out why it defaults to what it does and what the impact is of changing it for various Exadata features.