Odd one - URGENT pls...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Odd one - URGENT pls...

  1. #1
    Join Date
    Jul 2002
    Posts
    335

    Question 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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    version?

  3. #3
    Join Date
    Jul 2002
    Posts
    335
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    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?

  5. #5
    Join Date
    Jul 2002
    Posts
    335
    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)?

  6. #6
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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?

  7. #7
    Join Date
    Jul 2002
    Posts
    335
    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?
    Attached Files Attached Files

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jul 2002
    Posts
    335
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width