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
