确定查询的进度(Oracle PL/SQL)

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.

我是一个使用Oracle数据库的web应用程序的开发人员。但是，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_LONGOPS，这很棒，但它只适用于耗时超过6秒的操作。这意味着我不能更新UI中的进度条，直到6秒过去。

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.

我在V$SESSION中做过等待时间的研究，但是就我所见，这还不包括等待查询。

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?

是否有一种方法可以获得当前运行的会话查询的进度?还是应该将进度条隐藏到6秒之后?

4 个解决方案

#1

Are these operations Pl/SQL calls or just long-running SQL?

这些操作是Pl/SQL调用还是长时间运行的SQL?

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.

使用PL/SQL操作，我们可以在DBMS_APPLICATION_INFO包中使用SET_SESSION_LONGOPS()编写消息。我们可以在V$SESSION_LONGOPS中监视这些消息。了解更多。

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.

要使其工作，你需要能够以工作单位来量化操作。这些必须是具体事物的迭代，而不是时间的数值。如果操作是插入10000行你可以把它分成10个批次。totalwork参数是批数(即10)，每1000行后调用SET_SESSION_LONGOPS()来增加sofar参数。这将允许你渲染一个十个块的温度计。

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.

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

This won't work for a single long running query, because there's no way for us to divide it into chunks.

这对于一个长时间运行的查询是行不通的，因为我们没有办法将它分割成块。

#2

i found this very usefull

我觉得这很有用

dbms_session.set_module("MY Program" , "Kicking off ... ")
..
dbms_session.set_action("Extracting data ... ")
..
dbms_session.set_action("Transforming data ... ")
..

you can monitor the progress using

您可以使用

select module , action from v$session where sid = :yoursessionid

#3

I've done quite a lot of web development with Oracle over the years and found that most users prefer an indeterminate progress bar, than a determinate bar that is inaccurate (a la pretty much any of Microsoft's progress bars which annoy me no end), and unfortunately there is no infallible way of accurately determining query progress.

我做了很多与Oracle web开发多年,发现大多数用户喜欢一个不确定的进度条,比一个决定性的酒吧是不准确的(几乎任何微软的进度条,惹恼我没有结束),不幸的是没有可靠的方法,准确地确定查询进度。

Whilst your research into the long ops capability is admirable and would definitely help to make the progress of the database query more reliable, it can't take into account the myriad of other variables that may/will affect the web operation's transactional progress (network load, database load, application server load, client-side data parsing, the user clicking on a submit button 1,000 times, etc and so on).

当你长期运维能力的研究是令人钦佩的,肯定会有助于使数据库查询的进展更可靠,它不能考虑到无数的其他变量,可能会影响网络运行的事务进展(网络负载、数据库加载应用程序服务器负载,解析客户端数据,用户点击提交按钮1000次,等等等等)。

I'd stick to the indeterminate progress method using Javascript callbacks. It's much easier to implement and it will manage your user's expectations as appropriate.

我将使用Javascript回调坚持使用不确定的进度方法。它更容易实现，并且它将适当地管理用户的期望。

#4

Using V$_SESSION_LONGOPS requires to set TIMED_STATISTICS=true or SQL_TRACE=true. Your database schema must be granted the ALTER SESSION system privilege to do so.

使用V$_SESSION_LONGOPS需要设置TIMED_STATISTICS=true或SQL_TRACE=true。您的数据库模式必须获得更改会话系统权限才能这样做。

I once tried using V$_SESSION_LONGOPS with a complex and long running query. But it turned up that V$_SESSION_LONGOPS may show the progress of parts of the query like full table scans, join operations, and the like.

我曾经尝试使用V$_SESSION_LONGOPS和一个复杂的长时间运行的查询。但是，我们发现V$_SESSION_LONGOPS可以显示查询的各个部分的进展情况，比如全表扫描、连接操作等等。

参见:http://www.dba-oracle.com/t_v_dollar_session_longops.htm

What you can do is just to show the user "the query is still running". I implemented a <DIV> nested into a <TD> that gets longer with every status request sent by the browser. Status requests are initiated by window.SetTimeout (every 3 seconds) and are AJAX calls to a server-side procedure. The status report returned by the server-side procedure simply says "we are still running". The progress bar's width (i.e. the <DIV>'s width) increments by 5% of the <TD>s width every time and is reset to 5% after showing 100%.

您可以做的只是向用户显示“查询仍在运行”。我实现了嵌套到中的

，随着浏览器发送的每个状态请求的时间越来越长。状态请求由窗口发起。SetTimeout(每3秒一次)是对服务器端过程的AJAX调用。服务器端过程返回的状态报告仅仅表示“我们仍在运行”。进度条的宽度(即

's宽度)每次增加5%的宽度，显示100%后重置为5%。

For long running queries you might track the time they took in a separate table, possibly with individual entries for varying where clauses. You could use this to display the average time plus the time that just elapsed in the client-side dialog.

对于长时间运行的查询，您可能会跟踪它们在一个单独的表中所花费的时间，可能是针对不同的where子句的单个条目。您可以使用它来显示平均时间加上刚刚在客户端对话框中经过的时间。

If you have a long running PL/SQL procedure or the like on the server side doing several steps, try this:

如果您有一个长时间运行的PL/SQL过程或类似的服务器端执行几个步骤，请尝试以下操作:

create a table for status messages
为状态消息创建一个表
use a unique key for any process the user starts. Suggestion: client side's javascript date in milliseconds + session ID.
对用户启动的任何进程使用唯一键。建议:客户端javascript日期(毫秒+会话ID)。
in case the long running procedure is to be started by a link in a browser window, create a job using DBMS_JOB.SUBMIT to run the procedure instead of running the procedure directly
如果长时间运行的过程是由浏览器窗口中的链接启动的，那么使用DBMS_JOB创建一个作业。提交运行过程，而不是直接运行过程
write a short procedure that updates the status table, using PRAGMA AUTONOMOUS_TRANSACTION. This pragma allows you to commit updates to the status table without committing your main procedure's updates. Each major step of your main procedure should have an entry of its own in this status table.
使用PRAGMA AUTONOMOUS_TRANSACTION编写一个更新状态表的简短过程。这个实用程序允许您向状态表提交更新，而无需提交主过程的更新。主过程的每个主要步骤都应该在这个状态表中有自己的条目。
write a procedure to query the status table to be called by the browser
编写一个过程来查询浏览器要调用的状态表
write a procedure that is called by an AJAX call if the use clicks "Cancel" or closes the window
如果use单击“取消”或关闭窗口，则编写一个由AJAX调用调用的过程
write a procedure that is called by the main procedure after completion of each step: it queries the status table and raises an exception with an number in the 20,000s if the cancel flag was set or the browser did not query the status for, say, 60 seconds. In the main procedure's exception handler look for this error, do a rollback, and update the status table.
在完成每一个步骤后，编写一个由主过程调用的过程:它查询状态表，并在2.0中引发一个异常，如果取消标记被设置，或者浏览器没有查询状态，比方说，60秒。在主程序的异常处理程序中查找此错误，执行回滚，并更新状态表。

#1