godror

Go DRiver for ORacle User Guide

Godror Tuning

Correctness and simplicity is more important than speed, but the underlying ODPI-C library helps a lot with the lower levels, so the performance is not bad.

Some general recommendations:

Tune your SQL statements. See the SQL Tuning Guide.

Use bind variables to avoid statement reparsing.

Tune the PrefetchCount() and FetchArraySize() values for each query,

Do simple optimizations like limiting the number of rows returned by queries, and avoiding selecting columns not used in the application.

Make good use of PL/SQL to avoid executing many individual statements from godror.

Tune the statement cache size. This is currently hardcoded as 40, but the value can be overridden in an oraaccess.xml file.

Enable Client Result Caching for small lookup tables.
Tune your database. See the Database Performance Tuning Guide.
Tune your network. For example, when inserting or retrieving a large number of rows (or for large data), or when using a slow network, then tune the Oracle Network Session Data Unit (SDU) and socket buffer sizes, see Oracle Net Services: Best Practices for Database Performance and High Availability.

Round-trips between Godror and Oracle Database

A round-trip is defined as the trip from the Oracle Client libraries (used by godror) to the database and back. Calling each godror function, or accessing each attribute, will require zero or more round-trips. Along with tuning an application’s architecture and tuning its SQL statements, a general performance and scalability goal is to minimize round-trips.

Oracle’s Automatic Workload Repository (AWR) reports show ‘SQL*Net roundtrips to/from client’ and are useful for finding the overall behavior of a system. Sometimes you may wish to find the number of round-trips used for a specific application. Snapshots of the V$SESSTAT view taken before and after doing some work can be used for this:

SELECT ss.value, sn.display_name
FROM v$sesstat ss, v$statname sn
WHERE ss.sid = SYS_CONTEXT('USERENV','SID')
AND ss.statistic# = sn.statistic#
AND sn.name LIKE '%roundtrip%client%'

Note running this query will also affect the round-trip count. You may want to execute it in a second connection but replace the SYS_CONTEXT() call with the value of first connection’s SID.

Query Performance

When queries (SELECT or WITH statements) are executed, the performance of fetching the rows from the database can be tuned with PrefetchCount() and FetchArraySize(). The best values can be found by experimenting with your application under the expected load of normal application use. This is because the cost of the extra memory copy from the prefetch buffers when fetching a large quantity of rows or very “wide” rows may outweigh the cost of a round-trip for a single godror user on a fast network. However under production application load, the reduction of round-trips may help performance and overall system scalability.

Here are some suggestions for the starting point to begin your tuning:

To tune queries that return an unknown number of rows, estimate the number of rows returned and start with an appropriate FetchArraySize() value. The default is 100. Then set PrefetchCount() to the FetchArraySize() value. Do not make the sizes unnecessarily large. For example:
```
  sql := "SELECT * FROM very_big_table"
  rows, err := db.Query(sql, godror.PrefetchCount(1000), godror.FetchArraySize(1000))
```
Adjust the values as needed for performance, memory and round-trip usage. For a large quantity of rows or very “wide” rows on fast networks you may prefer to leave PrefetchCount() at its default value of 2. Keep FetchArraySize() equal to, or bigger than, PrefetchCount().

If you are fetching a fixed number of rows, start your tuning by setting FetchArraySize() to the number of expected rows, and set PrefetchCount() to one greater than this value. (Adding one removes the need for a round-trip to check for end-of-fetch). For example, if you are querying 20 rows then set PrefetchCount() to 21 and FetchArraySize() to 20:

  myoffset := 0       // do not skip any rows (start at row 1)
  mymaxnumrows := 20  // get 20 rows

  sql := `SELECT last_name
          FROM employees
          ORDER BY last_name
          OFFSET :offset ROWS FETCH NEXT :maxnumrows ROWS ONLY`
  rows, err := db.Query(sql, myoffset, mymaxnumrows,
      godror.PrefetchCount(mymaxnumrows+1), godror.FetchArraySize(mymaxnumrows))

This will return all rows for the query in one round-trip.

If you know that a query returns just one row then set FetchArraySize() to 1 to minimize memory usage. The default prefetch value of 2 allows minimal round-trips for single-row queries:
```
  sql := "SELECT last_nmae FROM employees WHERE employee_id = 100"
  err := db.QueryRow(sql, godror.FetchArraySize(1))
```

DML Performance

Instead of looping over DML statements (e.g. INSERT, UPDATE and DELETE), performance can be greatly improved by providing all the data at once using slices.

For example, instead of:

db.Exec("INSERT INTO table (a, b) VALUES (:1, :2)", 1, "a")
db.Exec("INSERT INTO table (a, b) VALUES (:1, :2)", 2, "b")

do:

db.Exec("INSERT INTO table (a, b) VALUES (:1, :2)", []int{1, 2}, []string{"a", "b"})

This site is open source. Improve this page.