[svn:dbd-oracle] r13049 - dbd-oracle/branches/rs_array

Author: byterock
Date: Mon Jul 13 08:27:40 2009
New Revision: 13049

Modified:
   dbd-oracle/branches/rs_array/Oracle.pm

Log:
doc setting

Modified: dbd-oracle/branches/rs_array/Oracle.pm
==============================================================================
--- dbd-oracle/branches/rs_array/Oracle.pm	(original)
+++ dbd-oracle/branches/rs_array/Oracle.pm	Mon Jul 13 08:27:40 2009
@@ -1803,55 +1803,75 @@
 
 Set L</ora_check_sql> to 0 in prepare() to enable this behaviour.
 
-=head1 Prefetching & Row Caching 
+=head1 Row Caching & Prefetching 
 
-DBD::Oracle now supports both Server pre-fetch and Client side row caching. By defualt both 
-are trurned on to give optimum performance. Most of the time one can just let DBD::Oracle
-figure out the best optimization. 
+DBD::Oracle has a better internal row caching mechannizm that dramatically cuts 
+down the number of round trips to the server when iterating trhought a result set. 
+This new mechanizme is on by default and works with prefetching to give optimal perforance. 
+Most of the time one can just let DBD::Oracle figure out the best optimization.
 
 =head2 Row Caching
 
-Row caching occures on the client side and the object of it is to cut down the number of round 
+Row caching uses the Database Handle's C<RowCacheSize> attribute to cut down the number of round 
 trips made to the server when fetching rows. At each fetch a set number of rows will be retreived
-from the server and stored locally. Further calls the server are made only when the end of the 
+from the server and stored locally. Further calls to the server are made only when the end of the 
 local buffer(cache) is reached.
 
-Rows up to the specified top level row 
-count C<RowCacheSize> are fetched if it occupies no more than the specified memory usage limit. 
-The default value is 0, which means that memory size is not included in computing the number of rows to prefetch. If
-the C<RowCacheSize> value is set to a negative number then the positive value of RowCacheSize is used 
-to compute the number of rows to prefetch.
+Setting the statements handle's ora_row_cache_off attibute to 1 will force DBD Oracle to only 
+fetch 1 row at a time.  You can also do this by setting the RowCacheSize to 1 as well.
 
-By default C<RowCacheSize> is automaticaly set. If you want to totaly turn off prefetching set this to 1.
+Some Data Types cannot be fetched more than one row at a time. In these few cases DBD::Oracle will automatically 
+turn off Row Caching. 
 
+Please note that this is a change to the way DBD::Oracle use to work. In version before 1.24 the C<RowCacheSize>
+attribute was used as a synymum ora_prefetch_rows.  DBD::Oracle now impliments the DBI Specifiction properly 
+as this value should be used as a local row cach. 
 
+Part of this change also means that the C<RowCacheSize> read only attribute is now available. So you 
+can dicover how many rows are left before the next fetch.
 
 =head2 Row Prefetching
 
-Row prefetching occurs on the server side and uses the DBI database handle attribute C<RowCacheSize> and or the 
-Prepare Attribute 'ora_prefetch_memory'. Tweaking these values may yield improved performance. 
+Row prefetching occurs on the server side and uses the Prepare Attributes 'ora_prefetch_memory'
+or 'ora_prefetch_rows' to  'prefetch' records before they are fetched by DBD::Oracle. Ora_prefetch_rows 
+will prefetch the number of rows entered. Ora_prefetch_memory works by fetching as many rows as will fit into
+the entered memory. When both of these attributes are set,DBD::Oracle prefetches rows up to the 
+ora_prefetch_rows limit unless the ora_prefetch_memory limit is reached, in which case the OCI returns 
+as many rows as will fit in the memory size.
 
-   $dbh->{RowCacheSize} = 100;
-   $sth=$dbh->prepare($SQL,{ora_exe_mode=>OCI_STMT_SCROLLABLE_READONLY,ora_prefetch_memory=>10000});
-   
-In the above example 10 rows will be prefetched up to a maximum of 10000 bytes of data.  The Oracle® Call Interface Programmer's Guide,
-suggests a good row cache value for a scrollable cursor is about 20% of expected size of the record set. 
+By default, prefetching is turned on, and will alway be set to the same value C<RowCacheSize> when it is computed.
+
+One can turn prefetching off, by setting both ora_prefetch_memory and ora_prefetch_rows to zero.
+
+The Ora_prefetch_memory can also be set by setting a negitive number for the value of C<RowCacheSize> as defined in the 
+DBI document.
+
+As with row caching some Data Tyepes cannot be prefetched. In these few cases DBD::Oracle will automatically turn 
+prefetching off.
+
+=head2 Treaking Performance
+One can now use the 'ora_oci_success_warn' statment attirbute to have a peak at the Row Caching and Prefetch value settings. 
+It will display the following when the Row and Prefetch is set;
+
+  cache settings RowCacheSize=NNN, OCI_ATTR_PREFETCH_ROWS=NNN, OCI_ATTR_PREFETCH_MEMORY=NNN, Rows per Fetch=NNN, Multiple Row Fetch=Yes/No
+  
+RowCacheSize, 'OCI_ATTR_PREFETCH_ROWS' ora_prefetch_rows() and 'OCI_ATTR_PREFETCH_MEMORY'(ora_prefetch_memory) will be the 
+values you entered or the computed values if you did not enter any, 'Rows per Fetch' will be the number of rows that will be
+retruned with each fetch and Multiple Row Fetch will tell you if the Row Cache is on or off.
+
+It will display this after each server side fetch;
+
+  ...Fetched NNN rows
 
-The prefetch settings tell the DBD::Oracle to grab x rows (or x-bytes) when it needs to get new rows. This happens on the first 
-fetch that sets the current_positon to any value other than 0. In the above example if we do a OCI_FETCH_FIRST the first 10 rows are
-loaded into the buffer and DBD::Oracle will not have to go back to the server for more rows. When record 11 is fetched DBD::Oracle
-fetches and returns this row and the next 9 rows are loaded into the buffer. In this case if you fetch backwards from 10 to 1 
-no server round trips are made.
-
-With large record sets it is best not to attempt to go to the last record as this may take some time, A large buffer size might even slow down
-the fetch. If you must get the number of rows in a large record set you might try using an few large OCI_FETCH_ABSOLUTEs and then an OCI_FETCH_LAST,
-this might save some time. So if you had a record set of 10000 rows and you set the buffer to 5000 and did a OCI_FETCH_LAST one would fetch the first 5000 rows into the buffer then the next 5000 rows.  
-If one requires only the first few rows there is no need to set a large prefetch value.  
-
-If the ora_prefetch_memory less than 1 or not present then memory size is not included in computing the 
-number of rows to prefetch otherwise the number of rows will be limited to memory size. Likewise if the RowCacheSize is less than 1 it
-is not included in the computing of the prefetch rows.  
+This value will only display if 'Multiple Row Fetch' is 'ON'
+
+Finally when all the data is fetchs it will display this
+
+  dbd_st_fetch no-more-data, fetch count=NNN
+  
+Fetch Count is the number of times DBD::Oracle has returend to the server for data.  
 
+			
 =back
 
 =head1 Spaces & Padding