英文:
Oracle End To End metrics with Jooq
问题
我正在处理一个批处理应用程序，其中DAO层使用jooq与Oracle数据库交互。我已经配置了setClientInfo到Oracle连接中，其中包含所有必要的OCSID密钥。我正在使用一个扩展了DelegatingDataSource的包装类，在批处理应用程序的不同阶段动态更改模块、操作、ecid和client_id，例如我将“操作”设置为当前步骤名称，“模块”设置为当前作业名称等等。我还使用过程名设置了“操作”。
我希望从这些指标中跟踪哪个作业/步骤/过程存在性能问题。我观察到在批处理应用程序运行期间，某些指标会保存在v$session表中。在批处理应用程序完成后，我只能在V$ACTIVE_SESSION_HISTORY中看到一些条目。我还尝试让过程休眠超过2分钟，使用APEX_UTIL.PAUSE(120)，但是在批处理应用程序运行后，我无法在V$ACTIVE_SESSION_HISTORY表中找到所有条目。即使在批处理应用程序运行时进行了10到15次以上的数据库调用，我在V$ACTIVE_SESSION_HISTORY中只能看到3到4个条目。这些指标是如何保存在V$ACTIVE_SESSION_HISTORY中的？我如何实现针对批处理应用程序中使用的作业/步骤/过程的性能跟踪？
英文:
I am working on a batch application where DAO layers uses jooq to interact with the oracle database. I have configured setClientInfo to the oracle connection with all the necessary OCSID keys. I am using a wrapper class that extends DelegatingDataSource to dynamically change the module, action, ecid & client_id during different stages of the batch application like example i am setting action as a current step name and module as a current job name and so on. I have also set action with procedure names.
I want to track from these metrics that which job/step/procedure is having performance issues. I have observed that during the batch application is running some metrics are saved in v$session table. After batch application is completed i can only see some entries in V$ACTIVE_SESSION_HISTORY. I have also tried by making procedures sleep for more than 2 mins using APEX_UTIL.PAUSE(120) but i am not able to find all the entries in the V$ACTIVE_SESSION_HISTORY table after batch application is run. Even if there are more than 10 to 15 DB calls during the batch application was run, i can only see 3 to 4 entries in V$ACTIVE_SESSION_HISTORY. How these metrics are saved in V$ACTIVE_SESSION_HISTORY? How can i achieve tracking the performance with respect to the job/step/procedure used in the batch application?
答案1
得分: 1
如果您正在使用jOOQ的DataSourceConnectionProvider与您的DataSource，jOOQ将始终在每个语句结束时调用Connection.close()，这可能会根据您的配置将连接放回池中并可能释放一些资源。如果您希望确保您的“会话”持续更长时间，请确保连接池不释放这些资源，或者甚至将JDBC Connection传递给jOOQ，这种情况下jOOQ将不会调用Connection.close()。您将会受益。
也许，如果较低精度的采样对您来说是可以接受的，您可能更有兴趣查询DBA_HIST_ACTIVE_SESS_HISTORY，请参阅：https://oracle-base.com/articles/10g/active-session-history。
英文:
If you're using your DataSource with jOOQ's DataSourceConnectionProvider, jOOQ will always call Connection.close() at the end of each statement, which might, depending on your configuration, put the connection back in the pool and maybe free some resources. If you want to ensure your "session" lasts longer, make sure the connection pool doesn't free those resources, or even pass a JDBC Connection to jOOQ instead of a DataSource, in case of which jOOQ will not call Connection.close(). You will.
Perhaps, if lower precision sampling is fine with you, you might be more interested in querying DBA_HIST_ACTIVE_SESS_HISTORY instead, see: https://oracle-base.com/articles/10g/active-session-history
答案2
得分: 0
我已通过移除 APEX_UTIL.PAUSE 来解决此问题。在阅读了这篇文章后，我明白如果会话进入休眠状态，它将在 v$session 中显示状态为 'ACTIVE'，状态为 'WAITING'，但不会在 v$active_session_history 中记录。
英文:
I have solved this by removing APEX_UTIL.PAUSE. After going though this https://jonathanlewis.wordpress.com/2015/03/27/ash/ i understood that if session goes to sleep it will appear in v$session as status = ‘ACTIVE’ and state = ‘WAITING’ but it will not be recorded in v$active_session_history.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论