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:
DECLARE -- the cursor for the query we want to smart scan CURSOR cur_things IS (SELECT id, comments FROM test.things); -- type and variable declaration based on columns in source table TYPE t_things_tab IS TABLE OF test.things%ROWTYPE; v_things_tab t_things_tab; BEGIN OPEN cur_things; LOOP FETCH cur_things BULK COLLECT INTO v_things_tab; FORALL i IN 1..v_things_tab.COUNT INSERT INTO test.things_new VALUES v_things_tab(i); EXIT WHEN cur_things%NOTFOUND; END LOOP; CLOSE cur_things; COMMIT; END; /
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.
SELECT name, VALUE MB FROM v$mystat m JOIN v$statname s ON m.statistic# = s.statistic# WHERE s.name = 'cell physical IO interconnect bytes returned by smart scan';
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.