The newly introduced Oracle Exadata storage servers rightly fit into the “working smarter” method of improving performance and scalability. Traditional database implementations use the storage as a plain container to dump and retrieve data, and the storage containers are relatively “dumb” about the data they often store. All the database processing is handled in database host memory, and often this involves transferring huge amounts of data from the storage to host machines that do the actual number crunching. The summarized results are then passed to the upper layers of end-user applications. An extremely large volume of raw data is processed to validate the required business intelligence operations by applying filtering conditions on the column-level data.
Special-purpose storage servers are built with additional intelligence at the storage layer where the storage is fully “aware” of the data that is stored in the disks. While processing the complex business intelligence reports, the host machines talk to the storage servers and provide additional information about the dataset being requested from the storage servers. The storage servers filter the data in the storage (known as “smart scan” processing) and pass the records matching the conditions specified by the host machines. Some amount of the processing is handled in the storage layer, and the database host machines do not need to process the huge amount of raw data to process the queries.
In a traditional storage-hosted database, the SQL query processing is handled at the database server. The data stored in the disks is retrieved as blocks to the database server and loaded in database buffer cache memory for processing. The following illustration shows the table scans processing in the conventional storage architecture.
- The application layer (end user client) issues a SQL query. For simplicity, we assume this is a SELECT statement with a WHERE condition.
- The database kernel queries the data dictionary and identifies the file and extents where the table data is stored.
- The database kernel issues I/O calls to read all the physical blocks from the disk.
- The physical data blocks from disk are loaded to the database server’s memory (buffer cache in the SGA).
- The database server reads the memory buffers and filters the rows satisfied by the predicate (WHERE condition).
- The matching rows are returned to client.
The traditional SQL processing works better if the tables are relatively small and well indexed. But for complex business intelligence queries involving multiples of larger tables, the reading of all the data from disk and transferring it to host memory is a very expensive task. It requires a huge amount of raw data transferred between storage to host memory over the network. Moreover, the records that do not meet the filter conditions are simply discarded at the host level. This is a totally unproductive I/O operation that impacts dearly the query response time. In this case, we often read multiple times the data required to match the filter conditions; poorly constructed queries put unnecessary overhead on the storage subsystem and affect the total system performance.
However, in the Exadata smart scan model the entire workflow is handled very intelligently and totally different. The queries that perform the table scans are offloaded to the Exadata storage server, and only records meeting the filter criteria are returned to the database server. Tasks such as row filtering, column filtering, and some amount of join processing are performed in the storage server. Also Exadata uses a special kind of highly efficient unbuffered direct-read mechanism for scanning the tables. This is similar to Oracle parallel query operations. The following illustration shows the operation.
- The application layer (end user application) issues a
SELECT query. - When the database server detects exadata, it builds an exadata construct (known as an iDB command) representing the SQL and sends this to Exadata storage server. iDB is a data-transfer mechanism based on the infiniband protocol (low latency, high bandwidth) used between an Exadata storage server and database server communications.
- The Exadata storage servers perform a “smart scan” on the tables and extract the rows of interest by applying the WHERE conditions directly on the storage.
- The resultset is transferred directly to the database instance. The resultset is the query result and not the blocks satisfying the filter condition. This is sent directly to the Progam Global Area (PGA) of the user session and is not cached in System Global Area (SGA).
- Matching rows are returned to the client.
Offloading SQL processing to the Exadata server tremendously increases the speed of the query processing and frees up the CPU cycles in the database server. Also, processing the data closer to the storage eliminates a massive amount of unproductive I/O and improves the scalability of the storage subsystem.
