Database Reference
In-Depth Information
Figure 15-14.
The time needed to load data into the database is strongly dependent on the number of rows processed by
each execution
It's essential to understand that poor performance of the load without array processing (in other words, row by
row processing) is
not
due to the database engine. Instead, it's the application that causes and suffers from it. You can
clearly see this by looking at the execution statistics generated with SQL trace. The following execution statistics show
that even if the client-side elapsed time lasted more than 50 seconds (see Figure
15-14
), only 3.1 seconds were spent
processing the database side inserts:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 3.06 3.10 2 2075 114173 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100001 3.06 3.10 2 2075 114173 100000
Even if the array interface is much more effective for the client, the database engine also profits from it. In fact,
the array interface reduces the number of logical reads (from 116,248 to 18,143). The following execution statistics
show the reduction when the rows are inserted in batches of 50:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2000 0.26 0.38 0 3132 15011 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2001 0.26 0.38 0 3132 15011 100000
The next sections provide some basic information on how to take advantage of the array interface with PL/SQL,
OCI, JDBC, and ODP.NET. Note that PHP, through the PECL OCI8 extension, doesn't support the array interface.
Given the kind of applications that are developed with PHP, I don't consider this a major issue.
