确保 php 会话在使用 oci_pconnect 时获得相同的 oracle 会话 [英] Ensure php session gets the same oracle session when using oci_pconnect

问题描述

我想使用全局临时表来存储一些昂贵的中间数据.数据是暂时的，但在 php 会话期间很好，因此似乎使用具有 on commit preserve rows 的全局临时表是理想的.

I would like to use global temporary tables to store some expensive intermediary data. The data is transient, but good for the duration of the php session so it seems that using global temporary tables with on commit preserve rows would be ideal.

但是..看起来全局临时表数据只对创建它的 oracle 会话可用.

But.. it looks like global temporary table data is only available to the oracle session that created it.

因此，这提出了一个问题，即我如何确保 oci_pconnect 会返回相同的 oracle 会话，因为我已经读到每个持久连接的 oracle 会话都是唯一的?我对跨多个 php 执行使用事务不感兴趣，只对临时表感兴趣.

So this raises the issue of how could I ensure that oci_pconnect would get back the same oracle session, as I have read that the oracle sessions are unique per persistent connection? I'm not interested in using transactions across multiple php executions, just the temporary tables.

我正在使用 php 会话，因此可以将其用作选择 oracle 会话的标识符.

I am using php sessions so that could be used as an identifier for selecting the oracle session.

到目前为止，这似乎是不可能的，但问起来也无妨.

So far it seems that this just isn't possible, but it won't hurt to ask.

编辑:实施此操作的预期目的是加快对我的访问控制中使用的用户组成员身份信息的访问.
提前解析组成员身份并使用此临时数据可以在所有后续查询中消除 3 层以上的连接.由于返回的密钥是 RAW，将它们序列化到外部存储会导致多次调用 HEXTORAW() 再次使用，并且似乎无助于预期目的.

EDIT: The intended purpose behind implementing this is to speed access to user group membership information utilized in my access control.
Parsing the group membership ahead of time and using this temporary data instead eliminates 3 + layers of joins in all subsequent queries. As the returned keys are RAW, serializing them to external storage results in many calls to HEXTORAW() on usage again, and does not seem to help with the intended purpose.

为确定组级别访问而添加的查询部分在会话期间是静态的，并且自行运行会返回大约 600 行唯一的 16 字节 RAW 键.然后通过链接表将这些键与结果集连接起来，以确定用户是否对结果集具有任何组级别"权限.

The portion of the query added to determine group level access is static for the duration of the session and run by itself returns approximately 600 rows of unique, 16 byte RAW keys. These keys are then joined against the results set via a links table to determine if the user has any 'group level' privileges to the results set.

我使用 IN 并将键作为字符串传入，但由于它们是 RAW 键，因此每次查询必须调用 HEXTORAW() 600 次.性能几乎不如使用临时表并执行 JOIN.

I played with using IN and passing the keys in as a string, but as they are RAW keys I then have to call HEXTORAW() 600 times per query. The performance wasn't nearly as good as using a temporary table and doing a JOIN.

有没有其他方法可以告诉 Oracle 将查询的那部分结果缓存起来，而不是将它们写入永久"中间结果?

Is there any other way to tell Oracle to keep the result of that portion of the query cached short of writing them to a 'permanent' intermediary result?

推荐答案

尽管您可能会想出一些技巧来完成这项工作，但至少在某些时候，我建议这几乎可以肯定在某些时候引起问题，尤其是在从开发服务器过渡到生产服务器时.选项可能包括:

Although you may be able to come up with some trickery to make this work, at least some of the time, I suggest that this is something that will almost certainly cause issues at some point, especially when making the transition from development server to production server. Options might include:

1. 使用永久表，并在逻辑完成后清除数据.
2. 将数据写入平面文件，然后在需要时将其读回.
3. 将数据写入平面文件，然后将该文件挂载为外部表.

分享和享受.

这篇关于确保 php 会话在使用 oci_pconnect 时获得相同的 oracle 会话的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！