Testing Exadata with cell.smart_scan_capable=FALSE ASM Disk Groups

In this post we’ll perform tests for full-scanning a table when it’s stored in an ASM disk group with CELL.SMART_SCAN_CAPABLE=FALSE and compare with the same table stored in a tablespace residing on an ASM disk group with CELL.SMART_SCAN_CAPABLE=TRUE. Let’s create an ASM disk group to match this requirement:

 

The control-case was done with SYSTEM.MYOBJ, which has the following characteristics:

  • Table: SYSTEM.MYOBJ
  • Size (Gb): 14.8
  • Blocks: 1,940,352
  • Tablespace Name: USERS
  • ASM Disk Group: DATA_CM01
  • ASM AU Size: 4MB

 

Let’s create a tablespace in the NOSSDATA_CM01 ASM disk group:

 

 

Now let’s create a copy of MYOBJ in this tablespace:

 

 

Let’s measure how large the new table is:

 

 

And finally, let’s test the same full-scan operation that yielded a 99%+ cell scan efficiency:

 

 

As we can see from the above test:

  • The query execution time went from 4.32 seconds to 21.16 seconds without Smart Scan.
  • Approximately 14.74 GB was returned without Smart Scan, compared with .09 GB.
  • Clearly, to take advantage of Exadata features for operations like these, we need to set CELL.SMART_SCAN_CAPABLE=TRUE on the ASM disk groups in which tablespaces are created.

Summary

Set CELL.SMART_SCAN_CAPABLE=TRUE on ASM disk groups which reside on Exadata Grid Disks.