The Geek Diary

Smart scan for ASM disk group in Exadata

by

What is Smart Scan

Exadata Smart Scan is a feature of the Database Machine. It offloads the data search and retrieval processing to the storage cell. Exadata Cell evaluates query predicates at the storage level to optimize the performance of certain classes of bulk data processing. For example, the performance of queries that require a full table or index scans to evaluate selective predicates can be improved by pushing the database expression evaluations to the storage cell. These expressions include simple SQL command predicates, such as id > 10, and column projections, such as SELECT customer_name.

For example:

sql> select object_name from test_objects where object_id='54'

In the preceding example, only rows satisfying the predicate, and only the column specified, are returned to the database server, eliminating unproductive I/O operations.

What operations benefit from Smart Scan

  • Full scan of a heap table.
  • Fast full scan of a B-Tree or bitmap index.

What operations do not benefit from Smart Scan

  • Scans of IOTs or clustered tables.
  • Index range scans.
  • Access to a compressed index.
  • Access to a reverse key index.
  • Secure Enterprise Search.

Enable smart scan for ASM Disk group while creating disk group in Exadata

The query:

SQL> CREATE DISKGROUP data HIGH REDUNDANCY
DISK 'o/*/DATA*'
ATTRIBUTE 'AU_SIZE' = '4M',
          'content.type' = 'data',
          'cell.smart_scan_capable'='TRUE',
          'compatible.rdbms'='11.2.0.2',
          'compatible.asm'='11.2.0.3';

Check cell smart scan is enables for ASM disk or not

Make sure to connect ASM instance, if you connect to DB instance you won’t find value.

SQL> SELECT dg.name AS diskgroup, SUBSTR(a.name,1,24) AS name, SUBSTR(a.value,1,24) AS value FROM V$ASM_DISKGROUP dg, V$ASM_ATTRIBUTE a
WHERE dg.group_number = a.group_number and a.NAME = 'cell.smart_scan_capable';

EXADATA – Importance of Cellinit.ora and Cellip.ora files

  • Cellinit.ora and Cellip.ora – After Oracle Exadata Storage Server is configured, the database server host must be configured with the cellinit.ora and the cellip.ora files to use the cell. The files are located in the /etc/oracle/cell/network-config directory of the database server host.These configuration files contain IP addresses, not host names.
  • cellinit.ora – This file contains the database IP addresses.
  • cellip.ora – This file contains the storage cell IP addresses.

You May Also Like