Oracle Magazine - May/June 2011

DBA
ORACLE EXADATA BY ARUP NANDA

CONTINUED FROM PAGE 35

memory performance, and physics is one of them. A disk can spin only so fast, and each platter in the disk array can hold only a finite amount of information, so the current weakest link in the processing chain will continue to be storage and will account for the biggest part of compute response time.

Consider what happens when a user issues the following query against an Oracle Database instance that is not running on Oracle Exadata:

select avg(amt) from sales where cust_level = 3.

Assume there is no index on the cust_ level column, meaning that the optimizer will choose a full table scan for this query. The Oracle Database server process corresponding to the session will issue a request to get all the database blocks of the sales table from the storage, examine the data, and discard the rows that do not satisfy the query. (When a row is requested by Oracle Database, that entire database block— typically 8 KB in size—has to go into memory from disk.)

The storage subsystem is concerned with bits and bytes—it does not have any idea about the actual data stored inside the database blocks. The database server alone can determine what data is in what blocks.

ORACLE EXADATA I/O AND SMART SCAN Storage in Oracle Exadata changes query processing so that not all blocks have to go to the database server for that server to determine which rows might satisfy a query. Oracle Exadata’s Smart Scan feature enables certain types of query processing to be done in the storage cell. With Smart Scan technology, the database nodes send query details to the storage cells via a protocol known as iDB (Intelligent Database). With this information, the storage cells can take over a large portion of the data-intensive query processing. Oracle Exadata storage cells can search storage disks with added intelligence about the query and send only the relevant bytes, not all the database blocks, to the database nodes—hence the term smart scan.

With Oracle Exadata, full table scans with selected functions and operators such as =, >, and so on in predicates and index fast full scans can use smart scans. To see functions that can benefit from Smart Scan, you can issue the query

select name from v$sqlfn_metadata

STORAGE INDEX

While scanning storage, Oracle Exadata storage cells can identify which areas of the disk storage will definitely not contain the values the query is interested in and avoid reading those areas.

How do the storage cells know how to avoid reading the areas of disk storage that do not include the data a query is interested in? They use storage indexes. A storage index is an in-memory structure that holds some information about the data inside specified regions of physical storage. This information tells the storage cell which areas of the disk do not contain the values the query is interested in, so those areas are not accessed during a scan.

Figure 1 shows how the data inside a storage index is maintained. There are storage index entries for different columns: PROD_CODE, SALES_DT, and CUST_LEVEL. Each storage index entry covers a physical region of the table, contains minimum and maximum values of the columns in that region, and also indicates whether any of the rows in that region contain nulls. In this example, region 1 of the storage index represents rows 1 through 3 of the table and region 2 of the storage index represents table

PROD_CODE SALES_DT

10023 13-Mar- 11 1

12345

34291

23-Mar- 11

12-Mar- 11

2

1

ROW

1

2

3

CUST_LEVEL

4

5

6

39023

56320

87431

13-Feb- 11

11-Jan- 11

12-Dec- 10

3

4

Null

Table
Figure 1: Conceptual representation of a storage index

rows 4 through 6. The storage index entry for region 1 in Figure 1 shows the minimum and maximum values of the cust_level column in the region to be 1 and 2, respectively.

The entry also shows that there are no null values in the rows of the table in region 1. The storage index entry for region 2 shows the minimum and maximum values of the cust_level column to be 3 and 4, respectively. One row of the table in region 2 includes a null value, so the null indicator in the storage index entry for region 2 shows Yes. For the sake of simplicity, Figure 1 shows the complete details of only one column (CUST_LEVEL) inside the storage index and the next column (PROD_CODE) is only partially displayed.

In the earlier example query, the user issued the statement

select avg(amt) from sales where cust_level = 3

When this query is run against Oracle Exadata storage, the storage index entry on the CUS T_LEVEL column in Figure 1 indicates that region 1 has values between 1 and 2, so no cust_level = 3 results will be found in that storage index region. Therefore, the storage cell does not access that region of the disk. Region 2 of the storage index shows the values to be between 3 and 4, so cust_level = 3 will be found in at least one row there. The storage cell reads region 2 from disk.