The Blog from the DBA Classroom

By: Joel Goodman

DBMs for DBAs: Offloads are for you too!!

Posted by Joel Goodman on 05/11/2010

My last in depth post on Exadata discussed Measuring Exadata Offload Efficiency , explained how to measure the amount of “offload” processing using system stats and wait events on the server side. It also showed how to examine current requests ( list activerequests) and current metrics (list metric current) in the cells using the cellcli utility on the Exadata Storage servers.

Exadata supports several Smart Storage operations which are generally known as Smart Scan operations. Some of these provide performance improvements for SQL for the following access methods:

  1. Full Table Scans
  2. Full Partition Scans
  3. Index Fast Full Scans
  4. Index Partition Fast Full Scan

In all these cases the libcell library routine on the database server passes request to the cellsrv process on the cells to execute part or all of a sql statement and to return only the results. The types of offload processing that are done may benefit DBA workloads too and  include:

  • Column projections – only the  columns in the select list are returned
  • Predicate filtering – only the rows matching the “where clause” of the select statement are returned. The operators that are supported include “=, !=, <, >, <=, >=, is null , is not null, like, between, not between, in, not in, exists, is of <type>, not, and, or.
  • SQL Function evaluation during predicate filtering – Certain SQL functions including data mining scoring functions are processed in the cells to avoid sending all rows to the server for processing there. To determine which ones they are use the following query in your 11.2 database:
  • SELECT * FROM v$sqlfn_metadata WHERE offloadable = ‘YES’;
  • Join Processing – done for star schemas using Bloom filters. Support for Bloom filters was added in Oracle 10g R2 to improve parallel joins and is used now in Exadata cells as well. For an excellent discussion of Bloom filters I recommend reading the “Bloom Filters” paper by Christian Antognini
  • Scans on encrypted data – support exists for both encrypted columns and for tables in encrypted tablespaces. The blocks can be decrypted in the cells thereby offloading the work to the cells and saving CPU on the database servers in the process.

But there are some “Smart” capabilities in the cells specifically geared toward DBA oriented activities which will improve performance when those tasks are performed.

  • Datafile creation and extension – Exadata formats the data blocks when tablespaces are created, have datafiles added, or when files are auto-extended. This reduces IO operations by eliminating the writes of blocks from the database instance to initialise the data blocks. For large tablespaces or where large auto-extend operations occur, this can save considerable time. The benefit may be measure by examining the statistic “cell physical IO bytes saved during optimized file creation” in v$sysstat.
  • RMAN Fast Incremental Backups– Exadata tracks block changes at the granularity of an individual block, rather than tracking at the level of a range of blocks. This reduces IO performed during fast incremental backups. To determine the benefit examine the column v$backup_datafile.blocks_skipped_in_cell to list the blocks skipped on a file by file basis. Compare this to v$backup_datafile.datafile_blocks and v$backup_datafile.blocks_read to determine the percentage efficiency.
  • RMAN File Restore – When restoring files, blocks above the high watermark that have never been used since file creation need not be restored by RMAN. Instead the cell formats those blocks as it did when creating the datafile. The benefit may be measure by examining the statistic “cell physical IO bytes saved during optimized RMAN file restore” in v$sysstat.

To summarise, Exadata provides smart operations for DBA type activities as well as for application workloads. Furthermore, conventional DBA tasks will also benefit from some of the smart operations such as column projection or filtering. Index creation and index rebuild perform full scan operations so if there are done then the scans may be offloaded. Some of the Partition Maintenance Operations (PMOPs) also perform scans and include merging, splitting, and moving table partitions. Database Machines (DBMs) are for DBAs too!!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: