Re: seconds_in_wait

  • From: Stefan Koehler <contact@xxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, hostetter.jay@xxxxxxxxx
  • Date: Tue, 22 Sep 2015 22:59:28 +0200 (CEST)

Hi Jay,

So it looks like the wait time is not unreasonable for this session that is
calling dbms_lock.sleep.

Do you mean that it shows 122 seconds, but you have waited much less? It would
be a reproducible test case and you could identify an issue with the
epoch time stamp by tracing vktm before and while running this test case.


And the time seems correct.

If this is the current (correct) time and the query still returns the wrong
seconds_in_wait at this point in time then it may likely be caused by the
stored start time of the wait event (which is also based on epoch time).


strace on vktm shows no gettimeofday calls

This is caused by vDSO / vsyscall64. Please check out this article for more
detailed information about it: https://lwn.net/Articles/446528/
You can by-pass vDSO with the following procedure - afterwards you will see the
syscall gettimeofday():
shell> echo 0 > /proc/sys/kernel/vsyscall64


I have this issue in two databases. I bounced one, and now it is fine. I
have not yet bounced the other, and my query continues to catch the
issue.

Are these two databases virtualized in some kind of way (e.g. VMware, etc.)?

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK

Jay Hostetter <hostetter.jay@xxxxxxxxx> hat am 22. September 2015 um 19:18
geschrieben:

Thank you for the responses. I have this issue in two databases. I bounced
one, and now it is fine. I have not yet bounced the other, and my
query continues to catch the issue. Now for testing in this database, per
Stefan's suggestion:

SQL> run
1 select s.indx, w.kslwtstime, w.kslwtltime,
decode(w.kslwtinwait,0,round((w.kslwtstime+w.kslwtltime)/1000000),
2 round(w.kslwtstime/1000000)) as SEC, e.kslednam
3 from x$ksuse s, x$ksled e, x$kslwt w
4* where bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and
s.indx=w.kslwtsid and w.kslwtevt=e.indx and s.indx =47

INDX KSLWTSTIME KSLWTLTIME SEC KSLEDNAM
---------- ---------- ---------- ----------
----------------------------------------------------------------
47 121597780 0 122 PL/SQL lock timer


So it looks like the wait time is not unreasonable for this session that is
calling dbms_lock.sleep.

SQL> oradebug setmypid
oradebug dumpvar sga ksudbrmseccnt
ub4 ksudbrmseccnt_ [06000ABA0, 06000ABA4) = 56017B56

in decimal: 1442937686
epoch time: 4:01:26 pm UTC Tuesday 9/22/15

And the time seems correct.

strace on vktm shows no gettimeofday calls.

I'll continue to poke around.

Thank you,
Jay
--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2015