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

本文是小编为大家收集整理的关于确定查询的进度(Oracle PL/SQL)的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我是使用Oracle数据库的Web应用程序上的开发人员.但是,UI通常会触发需要一段时间来处理的数据库操作.结果,客户在发生这些情况时想要一个进度栏.

我最近发现我可以从第二个连接中查询v $ session_longops,这很棒,但是它仅适用于需要超过6秒的操作.这意味着我无法更新UI中的进度栏​​,直到6秒通过.

我已经在V $ Session中对等待时间进行了研究,但据我所知,这不包括等待查询.

有没有办法获得会话当前运行查询的进度?还是我应该隐藏进度栏直到6秒通过?

推荐答案

这些操作是PL/SQL调用还是长期运行的SQL?

使用PL/SQL操作,我们可以在DBMS_APPLICATION_INFO软件包中使用SET_SESSION_LONGOPS()编写消息.我们可以在V$SESSION_LONGOPS中监视这些消息. 找出更多.

为此,您需要能够以工作单位量化操作.这些必须是混凝土的迭代,而数字不是时间.因此,如果操作是插入10000行,则可以将其分为10批. totalwork参数是批处理的数量(即10),并且您在每1000行后调用set_session_longops()递增sofar参数.这将使您能够渲染十个街区的温度计.

这些消息是基于会话的,但是没有自动方法可以将当前消息与以前的消息与同一会话和SID区分开.但是,如果将UID分配给context参数,则可以使用该值来过滤视图.


这对一个长期运行的查询不起作用,因为我们无法将其分成块.

其他推荐答案

我发现这个非常有用的

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

您可以使用

监视进度
select module , action from v$session where sid = :yoursessionid

其他推荐答案

多年来,我已经使用Oracle进行了很多网络开发,发现大多数用户更喜欢不确定的进度栏,而不是不准确的确定栏(几乎所有进步条,这使我无休止),不幸的是,没有可靠的方法可以准确确定查询进度.

尽管您对长期操作功能的研究令人钦佩,并且肯定会帮助使数据库查询的进度更加可靠,但它不能考虑到可能/可能会影响Web操作进度的其他无数变量(网络加载,数据库加载,应用程序服务器加载,客户端数据解析,用户单击提交按钮1,000次等等).

我会使用JavaScript回调来坚持不确定的进度方法.实施要容易得多,它将适当地管理用户的期望.

本文地址:https://www.itbaoku.cn/post/597751.html

问题描述

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.

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.

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?

推荐答案

Are these operations Pl/SQL calls or just long-running 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.

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.

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.

其他推荐答案

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

其他推荐答案

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.

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).

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.