@@ -91,93 +91,74 @@ already buffered in the Oracle Client libraries. Reducing round-trips helps
9191performance and scalability. An overhead of prefetching is the need for an
9292additional data copy from Oracle Client's prefetch buffers.
9393
94- To tune queries that return an unknown number of rows, estimate the number of
95- rows returned and start with an appropriate :attr: `Cursor.arraysize ` value. The
96- default is 100. Then set :attr: `Cursor.prefetchrows ` to the ``arraysize ``
97- value. Do not make the sizes unnecessarily large. Keep ``arraysize `` as big,
98- or bigger than, ``prefetchrows ``. Adjust the values as needed for performance,
99- memory and round-trip usage. An example is:
94+ Choosing values for ``arraysize `` and ``prefetchrows ``
95+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++
10096
101- .. code-block :: python
97+ The best :attr: `Cursor.arraysize ` and :attr: `Cursor.prefetchrows ` values can be
98+ found by experimenting with your application under the expected load of normal
99+ application use. This is because the cost of the extra memory copy from the
100+ prefetch buffers when fetching a large quantity of rows or very "wide" rows may
101+ outweigh the cost of a round-trip for a single cx_Oracle user on a fast network.
102+ However under production application load, the reduction of round-trips may help
103+ performance and overall system scalability. The documentation in
104+ :ref: `round-trips <roundtrips >` shows how to measure round-trips.
102105
103- cur = connection.cursor()
106+ Here are some suggestions for the starting point to begin your tuning:
104107
105- cur.prefetchrows = 1000
106- cur.arraysize = 1000
108+ * To tune queries that return an unknown number of rows, estimate the number of
109+ rows returned and start with an appropriate :attr: `Cursor.arraysize ` value.
110+ The default is 100. Then set :attr: `Cursor.prefetchrows ` to the ``arraysize ``
111+ value. Do not make the sizes unnecessarily large. For example:
107112
108- for row in cur.execute(" SELECT * FROM very_big_table"
109- print (row)
113+ .. code-block :: python
110114
111-
112- to leave ``prefetchrows `` at its default value of 2. The documentation in
113- :ref: `roundtrips ` shows how to measure round-trips.
115+ cur = connection.cursor()
114116
115- If you are fetching a fixed number of rows, start your tuning by setting
116- ``arraysize `` to the number of expected rows, and set ``prefetchrows `` to one
117- greater than this value. (Adding one removes the need for a round-trip to check
118- for end-of-fetch). For example, if you are querying 20 rows, perhaps to
119- :ref: `display a page <rowlimit >` of data, set ``prefetchrows `` to 21 and
120- ``arraysize `` to 20:
117+ cur.prefetchrows = 1000
118+ cur.arraysize = 1000
121119
122- .. code-block :: python
120+ for row in cur.execute(" SELECT * FROM very_big_table"
121+ print (row)
123122
124- cur = connection.cursor()
123+
124+ a large quantity of rows or very "wide" rows on fast networks you may prefer
125+ to leave ``prefetchrows `` at its default value of 2. Keep ``arraysize `` as
126+ big, or bigger than, ``prefetchrows ``.
125127
126- cur.prefetchrows = 21
127- cur.arraysize = 20
128+ * If you are fetching a fixed number of rows, start your tuning by setting
129+ ``arraysize `` to the number of expected rows, and set ``prefetchrows `` to one
130+ greater than this value. (Adding one removes the need for a round-trip to check
131+ for end-of-fetch). For example, if you are querying 20 rows, perhaps to
132+ :ref: `display a page <rowlimit >` of data, set ``prefetchrows `` to 21 and
133+ ``arraysize `` to 20:
128134
129- for row in cur.execute("""
130- SELECT last_name
131- FROM employees
132- ORDER BY last_name
133- OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY""" ):
134- print (row)
135+ .. code-block :: python
135136
136- This will return all rows for the query in one round-trip.
137+ cur = connection.cursor()
137138
138- If you know that a query returns just one row then set :attr: `Cursor.arraysize `
139- to 1 to minimize memory usage. The default prefetch value of 2 allows minimal
140- round-trips for single-row queries:
139+ cur.prefetchrows = 21
140+ cur.arraysize = 20
141141
142- .. code-block :: python
142+ for row in cur.execute("""
143+ SELECT last_name
144+ FROM employees
145+ ORDER BY last_name
146+ OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY""" ):
147+ print (row)
143148
144- cur = connection.cursor()
145- cur.arraysize = 1
146- cur.execute(" select * from MyTable where id = 1"
147- row = cur.fetchone()
148- print (row)
149+
149150
150- :attr: `Cursor.arraysize ` and :attr: `Cursor.prefetchrows ` values can be
151- found by experimenting with your application under the expected load of normal
152- application use. This is because the cost of the extra memory copy from the
153- prefetch buffers when fetching a large quantity of rows or very "wide" rows may
154- outweigh the cost of a round-trip for a single cx_Oracle user on a fast network.
155- However under production application load, the reduction of round-trips may help
156- performance and overall system scalability.
151+ * If you know that a query returns just one row then set :attr: `Cursor.arraysize `
152+ to 1 to minimize memory usage. The default prefetch value of 2 allows minimal
153+ round-trips for single-row queries:
157154
158- Prefetching can also be enabled in an external :ref: `oraaccess.xml
159- <optclientfiles>` file, which may be useful for tuning an application when
160- modifying its code is not feasible. Setting the size in ``oraaccess.xml `` will
161- affect the whole application, so it should not be the first tuning choice.
162-
163- One place where increasing ``arraysize `` is particularly useful is in copying
164- data from one database to another:
155+ .. code-block :: python
165156
166- .. code-block :: python
167-
168- # setup cursors
169- sourceCursor = sourceConnection.cursor()
170- sourceCursor.arraysize = 1000
171- targetCursor = targetConnection.cursor()
172-
173- # perform fetch and bulk insertion
174- sourceCursor.execute(" select * from MyTable"
175- while True :
176- rows = sourceCursor.fetchmany()
177- if not rows:
178- break
179- targetCursor.executemany(" insert into MyTable values (:1, :2)"
180- targetConnection.commit()
157+ cur = connection.cursor()
158+ cur.arraysize = 1
159+ cur.execute(" select * from MyTable where id = 1"
160+ row = cur.fetchone()
161+ print (row)
181162
182163arraysize `` and ``prefetchrows `` values are only examined
183164when a statement is executed the first time. To change the values, create a new
@@ -207,6 +188,30 @@ to 0:
207188 function before cx_Oracle can return them to the application. Setting
208189 ``prefetchrows `` to 0 helps give a consistent flow of data to the application.
209190
191+ Prefetching can also be enabled in an external :ref: `oraaccess.xml
192+ <optclientfiles>` file, which may be useful for tuning an application when
193+ modifying its code is not feasible. Setting the size in ``oraaccess.xml `` will
194+ affect the whole application, so it should not be the first tuning choice.
195+
196+ One place where increasing ``arraysize `` is particularly useful is in copying
197+ data from one database to another:
198+
199+ .. code-block :: python
200+
201+ # setup cursors
202+ sourceCursor = sourceConnection.cursor()
203+ sourceCursor.arraysize = 1000
204+ targetCursor = targetConnection.cursor()
205+
206+ # perform fetch and bulk insertion
207+ sourceCursor.execute(" select * from MyTable"
208+ while True :
209+ rows = sourceCursor.fetchmany()
210+ if not rows:
211+ break
212+ targetCursor.executemany(" insert into MyTable values (:1, :2)"
213+ targetConnection.commit()
214+
210215roundtrips :
211216
212217Database Round-trips
@@ -305,8 +310,8 @@ cache.
305310
306311.. _clientresultcache :
307312
308- Client Result Cache
309- ===================
313+ Client Result Caching
314+ =====================
310315
311316cx_Oracle applications can use Oracle Database's `Client Result Cache
312317<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-35CB2592-7588-4C2D-9075-6F639F25425E> `__.
@@ -332,7 +337,7 @@ restarting the database, for example:
332337 SQL> STARTUP FORCE
333338
334339:ref: `oraaccess.xml <optclientfiles >`
335- or :ref: `sqlnet.ora <optnetfiles >` file on the Node.js host, see `Client
340+ or :ref: `sqlnet.ora <optnetfiles >` file on the Python host, see `Client
336341Configuration Parameters
337342<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-E63D75A1-FCAA-4A54-A3D2-B068442CE766> `__.
338343
0 commit comments