Toad World® Forums

Session browser, Long Ops, negative times are displayed

Sometime, in session browser, long ops, a negative values is displayed for “time remaining”, probably because Toad uses a wrong type variable ( most likely a 4 bytes signed integer ).

Its very same query returns the correct value ( for example, 2451475525 against the wrong -1843491771 value displayed in the grid )

Best regards

Mauro

I can change it, but it seems that the value in the database is wrong. That column is supposed to show estimated time remaining in seconds. The max value that Toad allows in this field is 2147483648, which would come to about 70 years!

Morning All,

On 10/03/16 07:03, John Dorlon wrote:

I can change it, but it seems that the value in the database is wrong.

That column is supposed to show estimated time remaining in seconds.

The max value that Toad allows in this field is 2147483648, which would

come to about 70 years!

There’s nothing to change John, there are

(always/sometimes/occasionally) problems with V$SESSION_LONGOPS. See here:

www.gplivna.eu/…/v$session_longops.htm

The formula for working out TIME_REMAINING is:

(ELAPSED_SECONDS * (TOTALWORK - SOFAR))/SOFAR.

So, if Oracle estimates TOTALWORK incorrectly, which it can do, then

when the session has done more work than the estimate, SOFAR is bigger

than TOTALWORK, so the subtraction goes negative as does the result.

It would be interesting to check those figures when the OP next sees a

negative value. And just to show that “granny can indeed suck eggs”:

If:

ELAPSED_SECONDS is 100,

TOTALWORK is 1,000 and

SOFAR is 1,250

then:

TIME_REMAINING = (100 * (1,000 - 1,250) / 1,250)

= -20 seconds…

There is a lot of interesting information about V$SESSION_LONGOPS in the

link above.

HTH

Cheers,

Norm. [TeamT]

Sigh, when I replied by email, it seems to have removed the content! Try again… I originally said the following:

Morning All,

There’s nothing to change John, there are (always/sometimes/occasionally) problems with V$SESSION_LONGOPS. See here:

www.gplivna.eu/…/v$session_longops.htm

The formula for working out TIME_REMAINING is:

(ELAPSED_SECONDS * (TOTALWORK - SOFAR))/SOFAR.

So, if Oracle estimates TOTALWORK incorrectly, which it can do, then when the session has done more work than the estimate, SOFAR is bigger than TOTALWORK, so the subtraction goes negative as does the result.

It would be interesting to check those figures when the OP next sees a negative value. And just to show that “granny can indeed suck eggs”:

If:

ELAPSED_SECONDS is 100,

TOTALWORK is 1,000 and

SOFAR is 1,250

then:

TIME_REMAINING = (100 * (1,000 - 1,250) / 1,250)

= -20 seconds…

There is a lot of interesting information about V$SESSION_LONGOPS in the link above.

HTH

However, now that I’m posting from the web page, I can see that this is not a problem with the values in longops being negative, but looks to be a problem in the grid perhaps? Oracle define TIME_REMAINING as a NUMBER with no precision or scale, so it can get quite big.

I’ll go back to sleep now!