如何在OCI中获取sql_id

发布于 2024-12-14 05:51:28 字数 293 浏览 0 评论 0原文

有没有办法在调用 OCIStmtExecute() 后获取 sql_id/child number/plan hash？我在 OCIAttrGet() 中看不到它。

注意：作为看不到 v$session 的普通用户 - 如果可以的话，就像执行 select prev_sql_id, prev_child_number from v$session where sid=sys_context('USERENV', 'SID')

谢谢！

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

逆夏时光 2024-12-21 05:51:28

无法使用 oci 或 sys_context 获取 sql_id 或 plan_hash_value。不过，向 Oracle 支持提交增强请求以添加该功能可能是个好主意。

可以使用以下语句跟踪会话的所有 sql 语句：

alter session set events '10046 trace name context forever, level 12'

根据跟踪级别，生成或多或少的跟踪（级别 4 和 8 创建较少的信息）。要关闭跟踪执行

alter session set events '10046 trace name context off'

另一个选项是创建一个函数来自己计算 sql_id

使用 sql 文本并计算 128 位 md5
低 64 位是 sql_id （如果您感兴趣，低 32 位是计划哈希）

当然，这很容易出错，因为 oracle 将来可能会改变计算 sql_id 的机制。

There is no means to get the sql_id or the plan_hash_value with oci or sys_context. However it might be a good idea to file an enhancement request with oracle support to add that feature.

There is the possibility to trace all sql statements of a session with the following statement:

alter session set events '10046 trace name context forever, level 12'

Depending on the trace level more or less trace is generated (Level 4 and 8 create less information). To turn off the tracing execute

alter session set events '10046 trace name context off'

The other option is to create a function to compute the sql_id yourself

Use the sql text and calculate a 128bit md5
The lower 64 bit are the sql_id (and if you are interested the lower 32 bits are the plan hash)

Of course this is error prone as oracle might change the mechanism to calculate the sql_id in the future.

回复收藏 0 原文

听风吹 2024-12-21 05:51:28

以下查询应该有效，但前提是它是您希望识别的语句之后的下一条语句执行。

select prev_sql_id, prev_child_number
  from v$session
 where sid = sys_context('userenv','sid')

而且它确实有效......大多数时候。我的客户为 Oracle 12c 编写了一个 PL/SQL 应用程序，并将上述查询放置在执行应用程序查询的代码部分中。他向我展示了输出，显示它有时会返回错误的 prev_child_number 值。我观察了一下，确实无法始终返回正确的数据。在超过 99 次不同的语句执行中，它返回了错误的 prev_child_number 6 次。

我正在寻找导致此查询返回错误数据的现有错误，但尚未找到任何错误。我可能需要在 Oracle 支持下记录新的 SR。

The following query is supposed to work but only if it is the very next statement execution following the one that you wish to identify.

select prev_sql_id, prev_child_number
  from v$session
 where sid = sys_context('userenv','sid')

And it does work...most of the time. My customer wrote a PL/SQL application for Oracle 12c and placed the above query in the part of the code that executes the application query. He showed me output that shows that it sometimes returns the wrong value for prev_child_number. I watched and it is indeed failing to always return the correct data. Over 99 distinct statement executions it returned the wrong prev_child_number 6 times.

I am in the process of looking for existing bugs that cause this query to return the wrong data and haven't found any yet. I may have to log a new SR with Oracle support.

回复收藏 0 原文

~没有更多了~