Oracle's V$session_longops over system time processing method

Check a system, accidentally found the database v$session_longops time far more than the system time.

The results of the query are as follows:

[ORACLE@DATASD ~]$ Sqlplus/as SYSDBA

Sql*plus:release10.2.0.3.0-production on Monday December 20 14:57:04 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:

Oracle database10genterprise Edition release10.2.0.3.0-64bit Production

With the partitioning, OLAP and Data Mining options

Sql> alter session Set Nls_date_format = ' Yyyy-mm-dd hh24:mi:ss ';

Session altered.

Sql> select Sysdate from dual;

Sysdate

-------------------

2010-12-20 14:57:22

Sql> select Max (start_time), Max (Last_update_time)

2 from V$session_longops;

Max (start_time) Max (Last_update_tim

------------------- -------------------

2022-03-25 13:51:24 2022-03-25 13:51:25

The time to query from V$session_longops is more than 20 years faster than the time that Sysdate sees. The first reaction to seeing this phenomenon is a bug.

So inquires the Metalink, to see if there is no v$session_longops view of the time to quickly record, the whole metalink turned over and did not find valuable information.

Sql> Select instance_name, startup_time

2 from V$instance;

instance_name Startup_time

---------------- -------------------

Shandong 2008-01-15 15:19:28

Sql> Host Uptime

15:01:21 up 1069 days, 22:12, 2 users, Load average:0.00, 0.00, 0.00

Check the system further and discover that the database and the system have been started for nearly 3 years.

Because there is no information to draw on, you can only guess the cause of the problem:

One, the database bug, causes the V$session_longops record the time to change quickly;

Second, the operating system running more than 500 days, causing the operating system or Oracle database some variables overflow, resulting in this problem.

Third, the operating system time has been manually modified, found that after the modification error, adjusted back, but the V$session_longops view time does not automatically callback.

The first two are not very likely, because if they are, then it should be more common, and it is unlikely that there are any records in the Metalink.

The third possibility can be modeled by itself:

Sql> alter session Set Nls_date_format = ' Yyyy-mm-dd hh24:mi:ss ';

The session has changed.

Sql> select Sid from V$mystat where rownum = 1;

Sid

----------

18

sql> set Autot trace stat

Sql> select * from Ndmain.cat_product;

124350 rows have been selected.

Statistics

----------------------------------------------------------

0 Recursive calls

0 db Block gets

20274 consistent gets

12867 Physical Reads

0 Redo Size

75995724 Bytes sent via sql*net to client

91682 bytes received via sql*net from client

8291 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

124350 rows processed

Sql> set Autot off

Sql> select Max (start_time), Max (Last_update_time)

2 from V$session_longops

3 where sid = 18;

Max (start_time) Max (Last_update_tim

------------------- -------------------

2010-12-20 15:40:54 2010-12-20 15:41:06

Sql> select Sysdate from dual;

Sysdate

-------------------

2010-12-20 15:41:47