One of the key performance enhancing features of Oracle Exadata Database is the offloading of query processing down to the storage layer. Only relevant rows and columns are returned to the database server. As a result, much less data travels over the Infiniband interconnect, dramatically improving the performance and concurrency of queries.

However, there is a known bug causing certain select statements embedded in an insert statement to ignore this Smart Scan functionality, even though the same query successfully offloads when run independently. See Oracle Support Note 1303570.1 for details.

One workaround is to bulk collect the results of the query using a cursor in PL/SQL, then use the forall loop to perform the insert statement. Here's a simple example:

Session statistics will reveal the impact of Smart Scan. Here's a query to run to compare performance. Run it once after the problematic "insert as select" statement, then compare results after running the bulk collect statement.

Exadata Smart Scan technology vastly improves the performance of queries. Monitor your SQL statements to ensure they conform to Oracle's rules for using the feature. Once the application is tuned for Exadata, the time spent loading data will be a fraction of what you could expect from a non-Exadata Oracle environment.