问题描述
我是使用 Oracle 数据库的 Web 应用程序的开发人员.但是，UI 通常会触发需要一段时间来处理的数据库操作.因此，当这些情况发生时，客户会想要一个进度条.
I am a developer on a web app that uses an Oracle database. However, often the UI will trigger database operations that take a while to process. As a result, the client would like a progress bar when these situations occur.
我最近发现我可以从第二个连接查询 V$SESSION_LONGOPS，这很好，但它只适用于需要超过 6 秒的操作.这意味着在 6 秒后我无法更新 UI 中的进度条.
I recently discovered that I can query V$SESSION_LONGOPS from a second connection, and this is great, but it only works on operations that take longer than 6 seconds. This means that I can't update the progress bar in the UI until 6 seconds has passed.
我已经对 V$SESSION 中的等待时间进行了研究，但据我所知，这不包括等待查询.
I've done research on wait times in V$SESSION but as far as I've seen, that doesn't include the waiting for the query.
有没有办法获取当前正在运行的会话查询的进度?或者我应该隐藏进度条直到 6 秒过去?
Is there a way to get the progress of the currently running query of a session? Or should I just hide the progress bar until 6 seconds has passed?
推荐答案
这些操作是 Pl/SQL 调用还是只是长时间运行的 SQL?
Are these operations Pl/SQL calls or just long-running SQL?
通过 PL/SQL 操作，我们可以使用 DBMS_APPLICATION_INFO 包中的 SET_SESSION_LONGOPS() 编写消息.我们可以在 V$SESSION_LONGOPS 中监控这些消息.了解更多信息.
With PL/SQL operations we can write messages with SET_SESSION_LONGOPS() in the DBMS_APPLICATION_INFO package. We can monitor these messages in V$SESSION_LONGOPS. Find out more.
为此，您需要能够以工作单位量化操作.这些必须是具体事物的迭代，而非时间的数字.因此，如果操作是插入 10000 行，您可以将其分成 10 个批次.totalwork 参数是批次数(即 10)，每 1000 行后调用 SET_SESSION_LONGOPS() 以增加 sofar 参数.这将允许您渲染十个块的温度计.
For this to work you need to be able to quantify the operation in units of work. These must be iterations of something concrete, and numeric not time. So if the operation is insert 10000 rows you could split that up into 10 batches. The totalwork parameter is the number of batches (i.e. 10) and you call SET_SESSION_LONGOPS() after every 1000 rows to increment the sofar parameter. This will allow you to render a thermometer of ten blocks.
这些消息是基于会话的，但没有自动方式将当前消息与来自同一会话的先前消息区分开来 &身份证.但是，如果将 UID 分配给 context 参数，则可以使用该值来过滤视图.
These messages are session-based but there's no automatic way of distinguishing the current message from previous messages from the same session & SID. However if you assign a UID to the context parameter you can then use that value to filter the view.
这不适用于单个长时间运行的查询，因为我们无法将其分成块.
This won't work for a single long running query, because there's no way for us to divide it into chunks.
这篇关于确定查询的进度 (Oracle PL/SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！