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!