英文:
JDBC: fetchSize actually used by driver or fetch counts (roundtrips)
问题
- 驱动程序实际使用的获取大小是多少？
- 或者获取次数（往返次数）（Dynatrace 是如何做到的）？
- 其他完成此操作的方法？
英文:
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:
- the actual fetchSize used by driver?
- or fetch counts (roundtrips) (how does Dynatrace do it)?
- other ways to get this done?
答案1
得分: 1
你可以通过查询 v$sql（如果你有多个子游标并想查看它们的累积值，可以使用 v$sqlarea 代替）来监视实际的提取大小。通过查找具有 v$sql.sql_fulltext 的 sql_id 来识别你的查询，或者在 v$session 中查看它是否在你的会话中活动，或者在 v$active_session_history 中最近是否活动。一旦你有了 sql_id，只需观察 fetches 与 rows_processed 之间的关系以获取提取大小：
SELECT child_number, rows_processed/NULLIF(fetches,0) rows_per_fetch
FROM v$sql
WHERE sql_id = 'cr9n9traa800b';
由于这些数字是累积的，所以你的计算将是自游标加载到共享池中以来的平均值。要获得每分钟的平均值，你需要每分钟捕获这些信息，并在执行除法操作之前计算这两列的增量。你还可以通过查询 dba_hist_sqlstat 来获取每小时（或其他时间间隔，取决于数据库中设置的AWR间隔）的信息，已经过时间处理并为你计算增量：
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;
但是，由于这些信息要等到下次AWR刷新才会有，所以在试错测试中并不是非常有用。
注意: 如果你的数据库是RAC（多个实例），你需要将所有的 v$ 视图更改为 gv$ 视图以跨所有实例查看。如果出现表不存在的错误，你可能需要请求DBA授予你 "SELECT ANY DICTIONARY" 权限以运行这些查询。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论