CODEWITHSUNDEEP
springoracle-databasejdbcspring-datadynatrace
dobedo
dobedo

Reputation: 135

JDBC: fetchSize actually used by driver or fetch counts (roundtrips)

Env: Oracle 19c, ojdbc8-19.3.0.0, JDK 17, Spring boot 3 web, k8s

Context: we have an spring-web-rest-app doing merely queries against database and producing JSON responses. The queries are done with dynamically set fetchSize on spring's JdbcTemplate. The value is picked from a pre-configured set of values. There are times that we see via Dynatrace that database fetch counts don't match our expected fetch counts, given the fetchSize we set. Such cases lead to degraded response time. (Solved by increasing memory after consulting Oracle JDBC Memory Management)

We now want to monitor this as metrics and get notified as soon as the ratio of actual fetch counts to expected fetch counts exceeds a threshold.

Question: we know fetchSize we set for each query. How could we find out:

  1. the actual fetchSize used by driver?
  2. or fetch counts (roundtrips) (how does Dynatrace do it)?
  3. other ways to get this done?

Upvotes: 0

Views: 159

Answers (1)

Paul W
Paul W

Reputation: 11373

You can monitor the actual fetch size by querying v$sql (if you have multiple child cursors and want to see them all in aggregate, you can use v$sqlarea instead). Identify the sql_id of your query either by finding it with v$sql.sql_fulltext or by seeing it active for your session in v$session or recently active in v$active_session_history. Once you have the sql_id, simply watch the fetches vs. rows_processed to get your fetch size:

SELECT child_number, rows_processed/NULLIF(fetches,0) rows_per_fetch
  FROM v$sql
 WHERE sql_id = 'cr9n9traa800b';

As these numbers are cumulative, your calculation will be an average since the cursor was loaded in the shared pool. To get minute-by-minute averages you'd need to capture this info every minute and compute the deltas on both columns before doing the division. You can also get this info for every hour (or more or less, depending on the AWR interval set in your database) already time-lapsed and delta'd for you by querying dba_hist_sqlstat:

  SELECT s.begin_interval_time,
         ss.rows_processed_delta/NULLIF(ss.fetches_delta,0) rows_per_fetch
    FROM dba_hist_sqlstat ss,
         dba_hist_snapshot s
   WHERE ss.snap_id = s.snap_id
     AND ss.sql_id = 'cr9n9traa800b'
   ORDER BY 1

But as that won't have information until the next AWR flush it isn't very useful for trial-and-error testing.

Note: if your database is RAC (multiple instances) you'll want to change all v$ views to gv$ views to see across all instances. If you get an error that the table does not exist, you may need to ask your DBA for "SELECT ANY DICTIONARY" privilege to be able to run these queries.

Upvotes: 1

Related Questions