dbms_utility.get_time 可以滚动吗？

发布于 2024-07-26 03:20:32 字数 793 浏览 4 评论 0原文

我遇到了一个庞大的遗留 PL/SQL 过程的问题，该过程具有以下逻辑：

l_elapsed := dbms_utility.get_time - l_timestamp;

其中 l_elapsed 和 l_timestamp 的类型为 PLS_INTEGER 和 < code>l_timestamp 保存先前调用 get_time 的结果。

在批处理运行期间，此行突然开始失败，并出现 ORA-01426: numeric Overflow

有关的文档get_time 有点模糊，可能是故意的，但它强烈表明返回值没有绝对意义，并且几乎可以是任何数值。所以我很怀疑它被分配给 PLS_INTEGER，它只能支持 32 位整数。然而，互联网上充满了人们做这种事情的例子。

当我手动调用 get_time 时，发现了确凿的证据，它返回了一个 -214512572 值，该值可疑地接近 32 位有符号整数的最小值。我想知道在第一次调用 get_time 和下一次调用之间的时间间隔内，Oracle 的内部计数器是否从其最大值和最小值翻转，导致在尝试从中减去一个时发生溢出另一个。

这是一个可能的解释吗？如果是这样，这是否是 get_time 函数的固有缺陷？我可以等着看今晚批次是否再次失败，但我渴望在此之前得到对此行为的解释。

原文

I'm having problems with a mammoth legacy PL/SQL procedure which has the following logic:

l_elapsed := dbms_utility.get_time - l_timestamp;

where l_elapsed and l_timestamp are of type PLS_INTEGER and l_timestamp holds the result of a previous call to get_time

This line suddenly started failing during a batch run with a ORA-01426: numeric overflow

The documentation on get_time is a bit vague, possibly deliberately so, but it strongly suggests that the return value has no absolute significance, and can be pretty much any numeric value. So I was suspicious to see it being assigned to a PLS_INTEGER, which can only support 32 bit integers. However, the interweb is replete with examples of people doing exactly this kind of thing.

The smoking gun is found when I invoke get_time manually, it is returning a value of -214512572, which is suspiciously close to the min value of a 32 bit signed integer. I'm wondering if during the time elapsed between the first call to get_time and the next, Oracle's internal counter rolled over from its max value and its min value, resulting in an overflow when trying to subtract one from the other.

Is this a likely explanation? If so, is this an inherent flaw in the get_time function? I could just wait and see if the batch fails again tonight, but I'm keen to get an explanation for this behaviour before then.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

咿呀咿呀哟 2024-08-02 03:20:32

也许晚了，但这可能有利于搜索同一问题的人。

底层实现是一个简单的 32 位二进制计数器，从数据库上次启动时开始，每 100 秒递增一次。

该二进制计数器被映射到 PL/SQL BINARY_INTEGER 类型 - 这是一个带符号的 32 位整数（在 64 位机器上没有任何迹象表明它被更改为 64 位）。

因此，假设时钟从零开始，大约 248 天后它将达到 +ve 整数限制，然后翻转成为 -ve 值，回落到零。

好消息是，只要两个数字具有相同的符号，您就可以进行简单的减法来计算持续时间 - 否则您可以使用 32 位余数。

    IF SIGN(:now) = SIGN(:then) THEN
        RETURN :now - :then;
    ELSE
        RETURN MOD(:now - :then + POWER(2,32),POWER(2,32));
    END IF;

编辑：如果时间之间的差距太大（248 天），此代码将超出 int 限制并失败，但无论如何您不应该使用 GET_TIME 来比较以天为单位的持续时间度量（见下文）。

最后 - 还有一个问题是为什么要使用 GET_TIME。

从历史上看，这是获得亚秒级时间的唯一方法，但自从引入 SYSTIMESTAMP 以来，您使用 GET_TIME 的唯一原因是它速度快 - 它是 32 位计数器的简单映射，没有真正的类型转换，并且不会对底层操作系统时钟功能造成任何影响（SYSTIMESTAMP 似乎如此）。

由于它仅测量相对时间，因此它的唯一用途是测量两点之间的持续时间。对于任何需要大量时间（您知道，超过 1/1000 秒左右）的任务，使用时间戳的成本是微不足道的。

它实际上有用的场合很少（我发现的唯一一个是检查缓存中数据的年龄，其中为每次访问进行时钟命中变得很重要）。

Maybe late, but this may benefit someone searching on the same question.

The underlying implementation is a simple 32 bit binary counter, which is incremented every 100th of a second, starting from when the database was last started.

This binary counter is is being mapped onto a PL/SQL BINARY_INTEGER type - which is a signed 32-bit integer (there is no sign of it being changed to 64-bit on 64-bit machines).

So, presuming the clock starts at zero it will hit the +ve integer limit after about 248 days, and then flip over to become a -ve value falling back down to zero.

The good news is that provided both numbers are the same sign, you can do a simple subtraction to find duration - otherwise you can use the 32-bit remainder.

    IF SIGN(:now) = SIGN(:then) THEN
        RETURN :now - :then;
    ELSE
        RETURN MOD(:now - :then + POWER(2,32),POWER(2,32));
    END IF;

Edit : This code will blow the int limit and fail if the gap between the times is too large (248 days) but you shouldn't be using GET_TIME to compare durations measure in days anyway (see below).

Lastly - there's the question of why you would ever use GET_TIME.

Historically, it was the only way to get a sub-second time, but since the introduction of SYSTIMESTAMP, the only reason you would ever use GET_TIME is because it's fast - it is a simple mapping of a 32-bit counter, with no real type conversion, and doesn't make any hit on the underlying OS clock functions (SYSTIMESTAMP seems to).

As it only measures relative time, it's only use is for measuring the duration between two points. For any task that takes a significant amount of time (you know, over 1/1000th of a second or so) the cost of using a timestamp instead is insignificant.

The number of occasions on where it is actually useful is minimal (the only one I've found is checking the age of data in a cache, where doing a clock hit for every access becomes significant).

回复收藏 0 原文

メ斷腸人バ 2024-08-02 03:20:32

来自 10g 文档：

返回的数字范围为 -2147483648 到 2147483647，具体取决于平台和计算机，并且您的应用程序在确定间隔时必须考虑数字的符号。例如，在两个负数的情况下，应用程序逻辑必须允许第一个（较早的）数字大于第二个（较晚的）数字，后者更接近于零。出于同样的原因，您的应用程序还应该允许第一个（较早的）数字为负数，第二个（较晚的）数字为正数。

因此，虽然将 dbms_utility.get_time 的结果分配给 PLS_INTEGER 是安全的，但理论上有可能（但不太可能）在执行批处理运行期间发生溢出。两个值之间的差异将大于 2^31。

如果您的工作需要大量时间（因此增加了发生溢出的可能性），您可能需要切换到 TIMESTAMP 数据类型。

回复收藏 0 原文

看海 2024-08-02 03:20:32

为 PLS_INTEGER 变量分配负值确实会引发 ORA-01426：

SQL> l
  1  declare
  2    a pls_integer;
  3  begin
  4    a := -power(2,33);
  5* end;
SQL> /
declare
*
FOUT in regel 1:
.ORA-01426: numeric overflow
ORA-06512: at line 4

但是，您似乎建议 -214512572 接近 -2^31，但事实并非如此，除非您忘记输入数字。我们正在寻找确凿证据吗？

问候，
抢。

Assigning a negative value to your PLS_INTEGER variable does raise an ORA-01426:

SQL> l
  1  declare
  2    a pls_integer;
  3  begin
  4    a := -power(2,33);
  5* end;
SQL> /
declare
*
FOUT in regel 1:
.ORA-01426: numeric overflow
ORA-06512: at line 4

However, you seem to suggest that -214512572 is close to -2^31, but it's not, unless you forgot to typ a digit. Are we looking at a smoking gun?

Regards,
Rob.

回复收藏 0 原文

~没有更多了~