Order Dietz & Watson Landjaeger Sausages

Each time an application needs to obtain data from the database, it asks the driver and it executes a certain statement that returns the result row by row, or better yet, it returns a set of rows that are stored client side (caching of application) and then processed.

The mechanism of returning a rowset to time is called "row prefetching" and serves mainly to minimize round trips to the base (round trips), the data will be stored in memory and consumed from there by the application with thereby improving performance by minimizing communication with the database. In this note I will also show examples using PL / SQL, Java and C #.

I will make a comparison using PL / SQL to implement cursors to process data in a table T with 100,000 records and created as follows:

 
 create table t (id int, pad varchar2 ( 200)); 
 insert 
 Into 
 t select rownum, dbms_random.string ('a', 200) 
 from dual connect by rownum 
 <= 100000; 

With the table created, I am going to run the block to process data obtained with an explicit cursor, and I to enable trace to see the number of fetches required:

 
 
 declare cursor cur1 is select * from t; 
 l_rec t% ROWTYPE; 
 
 begin open cur1; 
 
 
 loop fetch cur1 Into l_rec; 
 when to exit cur1% notfound; 
 null; 
 end loop; 
 close cur1; 
 end; 
 
 
 
 SELECT * FROM T 
 
 
 
 call count cpu elapsed disk query current 
 rows ------- ------ -------- ---------- ---------- ------- 
 --- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 
 Execute 1 0.00 0.00 0 0 0 0 Fetch 
   100001 0.99 0.76 0 100 016 
 0 100000 ------- ------ -------- ---------- ---------- ------ 
 ---- ---------- ---------- total 100003 0.99 0.76 0 100017 0 100000 
 

is observed from the output of trace (previously processed tkprof) the number of fetches is equal to the amount of records, ie, there was a fetch for each row. Let's try perform the same op but now using implicit cursors:

 
 
 begin for i in (select * from t) loop 
 
 null; 
 end loop; 
 end; 
 
 
 
 SELECT * FROM T 
 
 
 
 call count cpu elapsed disk query current 
 rows ------- ------ -------- ---------- ---------- 
 ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 
 Execute 1 0.00 0.00 0 0 0 0 Fetch 
   1001 0.23 0.22 0 3899 0 100000 ------- ------ -------- ---------- 
 
 ---------- ---------- ---------- ---------- total 1003 0.23 0.22 0 3899 0 100000 
 

with implicit cursors are needed to 1001, thus making a simple mind can say that the prefetch was 100 rows, fixed value and provided the plsql_optimize_level parameter is 2 (default from 10g R2). Also note that the logical reads and the total processing time is less when using prefetch.

This could be one of the many justifications to entice them to use implicit cursors, no?. In general I tend to use implicit cursors (CI), and I write less, and I've proven to be more efficient than explicit cursors (CE). Of course, in individual cases we have no choice but to use EC if we have more control over the stages (declare, open, fetch and close). However with the introduction of BULK COLLECT can improve performance with EC so to parallelize. Also with bulk collect can easily define the size of the fetch. Here is an example:

 
 
 declare cursor cur1 is select * from t; 
 t_type type is table of t% ROWTYPE; 
 l_t t_type; 
 
 begin open cur1; 
 
 loop fetch cur1 LIMIT BULK COLLECT Into l_t 100; 
 when to exit cur1% notfound; 
 for i in loop 
 l_t.first .. l_t.last 
 null; 
 end loop; 
 end loop; 
 close cur1; 
 end; 
 
 
 call count cpu elapsed disk query current rows ------- ------ -------- ------- 
 --- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 
 0 0 
 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 
   0.25 0.24 1001 0 3899 0 100 000 ------- ------ -------- ------ 
 ---- ---------- ---------- ---------- ---------- total 1003 0.25 0.24 
 0 3899 0 100000 
 

In the above example defined a prefetch size 100, as it can be verified in the trace output. Now define tambaño of fetch more (1000):

 
 
 declare cursor cur1 is select * from t; 
 t_type type is table of t% ROWTYPE; 
 l_t t_type; 
 
 begin open cur1; 
 
 loop fetch cur1 LIMIT BULK COLLECT Into l_t 1000; 
 when to exit cur1% notfound; 
 for i in loop 
 l_t.first .. l_t.last 
 null; 
 end loop; 
 end loop; 
 close cur1; 
 end; 
 call 
 
 count cpu elapsed disk query current rows ------- ------ -------- ---------- 
 ---------- - --------- ---------- ---------- Parse 1 0.00 
 0.00 0 0 0 0 
 Execute 1 0.00 0.00 0 0 0 0 Fetch 
   101 0.24 0.24 0 3052 0 100 000 ------- ------ -------- --- 
 ------- ---------- ---------- ---------- ---------- total 103 
 0.24 0.24 0 3052 0 100 000 
 

It took 101 calls to the base, each 1000 rows returned to the client. These rows are stored in client memory for later use.

Finally, I will show how to define the size of the fetch in java and C #:

Portion of java code using prefetch:

 
 
 try {
 sql = "select id, pad from t" = Connection.prepareStatement 
 statement (sql); 
 statement.setFetchSize (100); 
 Statement.executeQuery  resultset = ();  
 while (ResultSet.next ()) {
 
 id = resultset.getLong ("id"); 
 ResultSet.getString pad = ("pad"); 
 / / Implementation of logic in the loop body} 
 
 ResultSet.close (); 
 statement.close (); 
 
} catch (SQLException e) 
 
 {throw new Exception ("Error:" + e.getMessage ());} 
 
 

Portion Code C # (. NET) to use prefetch:

 
 sql = "select id, pad from t" command = new OracleCommand 
 (sql, connection); 
 command.AddToStatementCache = false; 
 reader = Command.ExecuteReader (); 
  reader.FetchSize = command.RowSize * 100;  
 while (reader.Read ()) {
 
 id = reader.GetDecimal (0); 
 pad = reader.GetString (1); 
 / / Implementation of logic in the body of buclejavascript: void (0) 
 
} reader.Close (); 
 

To see more on this and other issues of performance, I recommend reading the excellent book by Christian Antognini: Troubleshooting Oracle Performance (by Christian-Antognini)