Odd one - URGENT pls...

Ok,

- user logs on at time x
- User does nothing i.e. session is idle.
- 10 minutes later, you look at the last activity and its BEFORE X!

Ran the following script off metalink (Doc id = 16728.1):

select sid, to_char((sysdate - (hsecs - value)/(100*60*60*24)),
'dd-mon-yy hh:mi:ss') last, to_char(sysdate, 'dd-mon-yy hh:mi:ss') curr,
(hsecs - value)/(100) secs, (hsecs - value)/(100*60) mins
from v$timer, v$sesstat
where statistic# = (select statistic# from v$statname
where name = 'process last non-idle time');

and get the following:


SID Last non-idle time
--- -------------------
1 03-nov-04 07:53:48
2 03-nov-04 07:53:47
3 03-nov-04 07:53:47
4 03-nov-04 07:53:47
5 03-nov-04 07:53:47
6 03-nov-04 07:53:47
7 03-nov-04 07:53:47
8 03-nov-04 07:53:47
9 03-nov-04 07:53:47
15 11-mar-05 02:15:27
16 11-mar-05 02:15:31
18 11-mar-05 02:14:54
19 11-mar-05 02:15:28
23 11-mar-05 02:14:54

(the ouput is edited to only show last non idle time). Note the 2005 entries

Ok, this is tru64 unix, running 'date'on the server brings the correct date and time, i.e. today's date and time in gmt.

What could cause this? The knock on effect is that we have a job that kills idle sessions after 60 mintes, but in reality these sessions are being wiped out much earlier than this.

Any help, this is kinda urgent!

Bazza

version?

Touche!

Its oracle 9.2.0.3 (and 9.2.0.4, another database displays the same sysmptons) on Compaq Tru64.

I can't tell if that metalink script is a pile of crap, and whether when looking at lastactivity through OEM is using the same mechanism. We've only noticied this since the clock change at the weekend, although the clock on the server was not changed.

Have you tried bouncing the db (if its an option), might have got itself into a mess with the daylight saving.

Sure someone didnt use the date command on unix to manually change the time and date?

A colleague bounced it yesterday, and it seemed ok for a while, that is to say no sessions where timing out. This morning sessions' started to be sniped prematurely.

The time comes back correctly when you issue the 'date' command on the server. How else can I check the server clock?

Is that metalink script valid (it seems to be)?

Looks like when the server was bounced your server started to read the date in USA format (MM-DD-YYYY)... 11-Mar-2005 is really 03-Nov-2004, but with the date the wrong* way round Oracle resolves it as the next date it can be (i.e. 2005).


*wrong in the UK..

...or have I misunderstood what you have asked?

Right, I don't think that metalink script is right, using v$timer in this way seems flawed, and some threads on metalink also say the script is pants.

The time and date appears correct on the server, i.e. its in the correct uk format:

os> date
Thu Nov 4 14:51:37 GMT 2004

I've attached a screen shot of OEM 9i standalone, clearly showing the last activity time which is BEFORE the log on time.

Any ideas people?

Yes, that metalink query is a bunch of crap. Actualy, if you look at its creation date, you'll see that it's from 1994!!!! Those were the days of Oracle7.0, werent they? So to be fair - that query is probably still valid for Oracle7 (and maybe for some later releases), but it certanly is not valid for 9i any more. Since Oracle7, some of the statistics meaning have drasticaly changed, obviously also including "'process last non-idle time".

Besides, when that query was written, that was the only way to obtain the information when a particular session was last active. But nowadays (at least since 8i, maybe even earlier) you have much easier way available to get that information. In V$SESSION there is a column LAST_CALL_ET which displays the time (in seconds) since a particular session isued the last request to the database.

Its a bug on Tru64, my colleague raised a tar at the same time as I posted.

Oracle say upgrade to 9205. Odd thing was, we never noticed until after the clock change, i.e. out job was killing idle sessions prematurely.