Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit db1b7ae

Browse filesBrowse files
anthony-tuiningaanthony-tuininga
committed
Various documentation improvements.
1 parent 6e4fb6c commit db1b7ae
Copy full SHA for db1b7ae

File tree

Expand file treeCollapse file tree

6 files changed

+106
-29
lines changed
Filter options
Expand file treeCollapse file tree

6 files changed

+106
-29
lines changed

‎doc/src/user_guide/batch_statement.rst

Copy file name to clipboardExpand all lines: doc/src/user_guide/batch_statement.rst
+8-8Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -51,14 +51,14 @@ The following example inserts five rows into the table ``ParentTable``:
5151
]
5252
cursor.executemany("insert into ParentTable values (:1, :2)", dataToInsert)
5353
54-
This code requires only one round-trip from the client to the database instead
55-
of the five round-trips that would be required for repeated calls to
56-
:meth:`~Cursor.execute()`. For very large data sets there may be an external
57-
buffer or network limits to how many rows can be processed, so repeated calls
58-
to ``executemany()`` may be required. The limits are based on both the number
59-
of rows being processed as well as the "size" of each row that is being
60-
processed. Repeated calls to :meth:`~Cursor.executemany()` are still
61-
better than repeated calls to :meth:`~Cursor.execute()`.
54+
This code requires only one :ref:`round-trip <roundtrips>` from the client to
55+
the database instead of the five round-trips that would be required for
56+
repeated calls to :meth:`~Cursor.execute()`. For very large data sets there
57+
may be an external buffer or network limits to how many rows can be processed,
58+
so repeated calls to ``executemany()`` may be required. The limits are based
59+
on both the number of rows being processed as well as the "size" of each row
60+
that is being processed. Repeated calls to :meth:`~Cursor.executemany()` are
61+
still better than repeated calls to :meth:`~Cursor.execute()`.
6262

6363

6464
Batch Execution of PL/SQL

‎doc/src/user_guide/connection_handling.rst

Copy file name to clipboardExpand all lines: doc/src/user_guide/connection_handling.rst
+10-7Lines changed: 10 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -436,9 +436,9 @@ it is not, then :meth:`~SessionPool.acquire()` will clean up the connection and
436436
return a different one. This check will not detect cases such as where the
437437
database session has been killed by the DBA, or reached a database resource
438438
manager quota limit. To help in those cases, :meth:`~SessionPool.acquire()`
439-
will also do a full round-trip ping to the database when it is about to return a
440-
connection that was unused in the pool for 60 seconds. If the ping fails, the
441-
connection will be discarded and another one obtained before
439+
will also do a full :ref:`round-trip <roundtrips>` ping to the database when it
440+
is about to return a connection that was unused in the pool for 60 seconds. If
441+
the ping fails, the connection will be discarded and another one obtained before
442442
:meth:`~SessionPool.acquire()` returns to the application. Because this full
443443
ping is time based, it won't catch every failure. Also since network timeouts
444444
and session kills may occur after :meth:`~SessionPool.acquire()` and before
@@ -462,6 +462,8 @@ Static Pools
462462
<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-7DFBA826-7CC0-4D16-B19C-31D168069B54>`__,
463463
which contains details about sizing of pools.
464464

465+
.. _sessioncallback:
466+
465467
Session CallBacks for Setting Pooled Connection State
466468
-----------------------------------------------------
467469

@@ -532,8 +534,9 @@ In this example tagging was not being used, so the ``requestedTag`` parameter
532534
is ignored.
533535

534536
Note: if you need to execute multiple SQL statements in the callback, use an
535-
anonymous PL/SQL block to save round-trips of repeated ``execute()`` calls.
536-
With ALTER SESSION, pass multiple settings in the one statement:
537+
anonymous PL/SQL block to save :ref:`round-trips <roundtrips>` of repeated
538+
``execute()`` calls. With ALTER SESSION, pass multiple settings in the one
539+
statement:
537540

538541
.. code-block:: python
539542
@@ -594,8 +597,8 @@ When cx_Oracle uses Oracle Client 12.2 or later, the session callback can also
594597
be the name of a PL/SQL procedure. A PL/SQL callback will be initiated only
595598
when the tag currently associated with a connection does not match the tag that
596599
is requested. A PL/SQL callback is most useful when using :ref:`drcp` because
597-
DRCP does not require a round-trip to invoke a PL/SQL session callback
598-
procedure.
600+
DRCP does not require a :ref:`round-trip <roundtrips>` to invoke a PL/SQL
601+
session callback procedure.
599602

600603
The PL/SQL session callback should accept two VARCHAR2 arguments:
601604

‎doc/src/user_guide/lob_data.rst

Copy file name to clipboardExpand all lines: doc/src/user_guide/lob_data.rst
+3-3Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -128,9 +128,9 @@ calling :meth:`LOB.size()` and the data can be read by calling
128128
print("BLOB data:", b.read())
129129
130130
This approach produces the same results as the previous example but it will
131-
perform more slowly because it requires more round-trips to Oracle Database and
132-
has higher overhead. It is needed, however, if the LOB data cannot be fetched as
133-
one block of data from the server.
131+
perform more slowly because it requires more :ref:`round-trips <roundtrips>` to
132+
Oracle Database and has higher overhead. It is needed, however, if the LOB data
133+
cannot be fetched as one block of data from the server.
134134

135135
To stream the BLOB column, the :meth:`LOB.read()` method can be called
136136
repeatedly until all of the data has been read, as shown below:

‎doc/src/user_guide/sql_execution.rst

Copy file name to clipboardExpand all lines: doc/src/user_guide/sql_execution.rst
+80-7Lines changed: 80 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -138,11 +138,11 @@ Tuning Fetch Performance
138138
For best performance, the cx_Oracle :attr:`Cursor.arraysize` value should be set
139139
before calling :meth:`Cursor.execute()`. The default value is 100. For queries
140140
that return a large number of rows, increasing ``arraysize`` can improve
141-
performance because it reduces the number of round-trips to the database.
142-
However increasing this value increases the amount of memory required. The best
143-
value for your system depends on factors like your network speed, the query row
144-
size, and available memory. An appropriate value can be found by experimenting
145-
with your application.
141+
performance because it reduces the number of :ref:`round-trips <roundtrips>` to
142+
the database. However increasing this value increases the amount of memory
143+
required. The best value for your system depends on factors like your network
144+
speed, the query row size, and available memory. An appropriate value can be
145+
found by experimenting with your application.
146146

147147
Regardless of which fetch method is used to get rows, internally all rows are
148148
fetched in batches corresponding to the value of ``arraysize``. The size does
@@ -597,6 +597,10 @@ This might produce output like::
597597
Other information on using Oracle objects is in :ref:`Using Bind Variables
598598
<bind>`.
599599

600+
Performance-sensitive applications should consider using scalar types instead of
601+
objects. If you do use objects, avoid calling :meth:`Connection.gettype()`
602+
unnecessarily, and avoid objects with large numbers of attributes.
603+
600604
.. _rowlimit:
601605

602606
Limiting Rows
@@ -690,8 +694,8 @@ Python cx_Oracle applications can use Oracle Database's `Client Result Cache
690694
The CRC enables client-side caching of SQL query (SELECT statement) results in
691695
client memory for immediate use when the same query is re-executed. This is
692696
useful for reducing the cost of queries for small, mostly static, lookup tables,
693-
such as for postal codes. CRC reduces network round-trips, and also reduces
694-
database server CPU usage.
697+
such as for postal codes. CRC reduces network :ref:`round-trips <roundtrips>`,
698+
and also reduces database server CPU usage.
695699

696700
The cache is at the application process level. Access and invalidation is
697701
managed by the Oracle Client libraries. This removes the need for extra
@@ -804,3 +808,72 @@ SDO_GEOMETRY <spatial>` object:
804808
cur = connection.cursor()
805809
cur.setinputsizes(typeObj)
806810
cur.execute("insert into sometable values (:1)", [None])
811+
812+
.. _roundtrips:
813+
814+
Database Round-trips
815+
====================
816+
817+
A round-trip is defined as the trip from the Oracle Client libraries (:ref:`used by
818+
cx_Oracle <archfig>`) to the database and back. Along with tuning an application's
819+
architecture and tuning its SQL statements, a general performance and
820+
scalability goal is to minimize `round-trips
821+
<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-9B2F05F9-D841-4493-A42D-A7D89694A2D1>`__.
822+
823+
Some general tips for reducing round-trips are:
824+
825+
- Tune :attr:`Cursor.arraysize`, see :ref:`Tuning Fetch Performance <tuningfetch>`.
826+
- Use :meth:`Cursor.executemany()` for optimal DML execution, see :ref:`Batch Statement Execution and Bulk Loading <batchstmnt>`.
827+
- Only commit when necessary. Use :attr:`Connection.autocommit` on the last statement of a transaction.
828+
- For connection pools, use a callback to set connection state, see :ref:`Session CallBacks for Setting Pooled Connection State <sessioncallback>`.
829+
- Make use of PL/SQL procedures which execute multiple SQL statements instead of executing them individually from cx_Oracle.
830+
- Use scalar types instead of :ref:`Oracle named object types <fetchobjects>`
831+
- Avoid overuse of :meth:`Connection.ping()`.
832+
833+
Oracle's `Automatic Workload Repository (AWR)
834+
<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-56AEF38E-9400-427B-A818-EDEC145F7ACD>`__
835+
reports show 'SQL*Net roundtrips to/from client' and are useful for finding the
836+
overall behavior of a system.
837+
838+
Sometimes you may wish to find the number of round-trips used for a
839+
specific application. Snapshots of the ``V$SESSTAT`` view taken before
840+
and after doing some work can be used for this.
841+
842+
First, find the session id of the current connection:
843+
844+
.. code-block:: python
845+
846+
cursor.execute("select sys_context('userenv','sid') from dual")
847+
sid, = cursor.fetchone();
848+
849+
This can be used with ``V$SESSTAT`` to find the current number of round-trips.
850+
A second connection should be used to avoid affecting the count. If your user
851+
does not have access to the V$ views, then use a SYSTEM connection:
852+
853+
.. code-block:: python
854+
855+
def getRT(conn, sid):
856+
cursor = conn.cursor()
857+
cursor.execute(
858+
"""SELECT ss.value
859+
FROM v$sesstat ss, v$statname sn
860+
WHERE ss.sid = :sid
861+
AND ss.statistic# = sn.statistic#
862+
AND sn.name LIKE '%roundtrip%client%'""", sid = sid)
863+
rt, = cursor.fetchone();
864+
return rt
865+
866+
The main part of a benchmark application can perform "work" and use ``getRT()``
867+
to calculate the number of round-trips the work required:
868+
869+
.. code-block:: python
870+
871+
rt = getRT(systemconn, sid)
872+
873+
cursor = conn.cursor()
874+
cursor.execute("select * from dual")
875+
row = cursor.fetchone()
876+
877+
rt = getRT(systemconn, sid) - rt
878+
879+
print("Round-trips", rt)

‎doc/src/user_guide/tracing_sql.rst

Copy file name to clipboardExpand all lines: doc/src/user_guide/tracing_sql.rst
+3-2Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -83,8 +83,9 @@ The connection attributes, :attr:`~Connection.client_identifier`,
8383
end-to-end tracing. You can use data dictionary and ``V$`` views to monitor
8484
tracing or use other application tracing utilities.
8585

86-
The attributes are sent to the database when the next round-trip to the
87-
database occurs, for example when the next SQL statement is executed.
86+
The attributes are sent to the database when the next :ref:`round-trip
87+
<roundtrips>` to the database occurs, for example when the next SQL statement is
88+
executed.
8889

8990
The attribute values will remain set in connections released back to connection
9091
pools. When the application re-acquires a connection from the pool it should

‎doc/src/user_guide/txn_management.rst

Copy file name to clipboardExpand all lines: doc/src/user_guide/txn_management.rst
+2-2Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -30,8 +30,8 @@ An alternative way to commit is to set the attribute
3030
:attr:`~Connection.autocommit` of the connection to ``True``. This ensures all
3131
:ref:`DML <dml>` statements (INSERT, UPDATE etc) are committed as they are
3232
executed. Unlike :meth:`Connection.commit()`, this does not require an
33-
additional round-trip to the database so it is more efficient when used
34-
appropriately.
33+
additional :ref:`round-trip <roundtrips>` to the database so it is more
34+
efficient when used appropriately.
3535

3636
Note that irrespective of the autocommit value, Oracle Database will always
3737
commit an open transaction when a DDL statement is executed.

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.