Testing Exadata with cell.smart_scan_capable=FALSE ASM Disk Groups

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.