确定查询的进度（Oracle PL / SQL） [英] Determining query's progress (Oracle PL/SQL)

问题描述

我是一个使用Oracle数据库的网络应用程序开发人员。但是，通常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秒的操作。这意味着我不能在UI中更新进度条，直到6秒过去了。

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.

有没有办法获取当前正在运行的会话的查询的进度？

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.

这些消息是基于会话的，但是没有自动的方法来区分当前消息与来自同一会话的先前消息& SID。但是，如果您为上下文参数分配一个UID，则可以使用该值过滤视图。

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）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！