Sunday, February 09, 2014

Understanding Oracle Exadata function shipping principal

When looking at the Oracle Exadata architecture it shows a couple of things that ensures that the Exadata Database machine can provide the performance that it is known for. Numerous design decisions are note worthy however function shipping is most likely one that is the most ingenuous and is currently only available on Oracle hardware and cannot be reproduced to other hardware platforms. We do see other companies then Oracle who are building hardware platforms that are capable or providing extreme performance however non of them are able to implement the function shipping in a manner that Oracle is capable of doing.

Main reason for this is that Oracle both owns the proprietary rights on the software and the hardware included in the Exadata platform. Function shipping is, simply put, moving SQL instructions from the database nodes to the storage nodes. In the below diagram you see a common way of deploying a database in combination with the storage, which in this case is a storage appliance however this could also be local storage on the database server.

If we, for example, need to execute a select statement on the table "TBL_SALE" and retrieve all records where the "country_of_sale" is "NL" the database will retrieve all data which is in "TBL_SALE" fro the storage and when it retrieves this it will filter on "country_of_sale" and finally only show the records where this contains "NL".

In essence there is nothing wrong with this mechanism, the issue however is two folded. The first issue is that if your table is large (multi terabyte) all this data has to be moved from your storage appliance to your database instance. This can result in a serious performance loss. The second issue is that the CPU's that are busy with (A) handling all the incoming data and (B) sorting this data to verify if the "country_of_sale" equals "NL" are unable to do any other tasks.

When we look at the way the Exadata machine is developed we have a model as shown below which will mitigate against the performance loss as described above.

In the above diagram, as we execute the same query as we do on a traditional database setup. the function shipping comes into play. Instead of requesting all data from the table to be send to your database engine Oracle makes use of the function shipping principal to send the majority of the SQL execution to the Exadata storage cell. The major benefit of this that, in our example case, not the entire multi terabyte table needs to be shipped from the storage layer to the database layer. The SQL statement parts that can be executed on the storage cell will be executed right there and only the rows that satisfy the SQL select statement.

By using this function shipping the system is not providing traditional block serving services to the database but rather, in a smart way, providing options to only transfer the applicable rows and records from the storage to the database. This limits the load on the CPU's on the database server and limits the bandwidth usage between the database server and the storage appliance.

Reason that this is only available on Oracle hardware in combination with the Oracle database is that Oracle build both the hardware and the software. Due to this Oracle is able to place propriatary code on the storage cell layer wich can communicate with the database instance.

To achieve this The database servers and Exadata Storage Server Software communicate using the iDB (Intelligent DataBase) protocol. iDB is implemented in the database kernel and transparently maps database operations to Exadata-enhanced operations. iDB implements a function shipping architecture in addition to the traditional data block shipping provided by the database. iDB is used to ship SQL operations down to the Exadata cells for execution and to return query result sets to the database kernel. Instead of returning database blocks, Exadata cells return only the rows and columns that satisfy the SQL query. Like existing I/O protocols, iDB can also directly read and write ranges of bytes to and from disk so when offload processing is not possible Exadata operates like a traditional storage device for the Oracle Database. But when feasible, the intelligence in the database kernel enables, for example, table scans to be passed down to execute on the Exadata Storage Server so only requested data is returned to the database server.

No comments: