Home » RDBMS Server » Performance Tuning » All v$session_event numbers don't add to total time
All v$session_event numbers don't add to total time [message #229280] Fri, 06 April 2007 07:52 Go to next message
madchaz
Messages: 65
Registered: October 2005
Member
I am building a query that is giving me a view of all sessions wait time in oracle 9.

I am using the v$session_event view to build my query, but I am running into a bit of a weird issue.

If I add all the wait event's time together and compare that to the time the session as lasted, the numbers don't add up.

I get the session duration by subtracting the logon_time in v$session from sysdate and converting the result to seconds as such.

(sysdate - LOGON_TIME)*86400

The original goal was to use that difference, minus the waits, to estimate time spent in CPU. But I will sometime get a negative number. Other times, the number looks abnormally large, so I ran a comparison between the CPU time listed in v$sesstat and what I was getting.

select value/100 Seconds
from v$sesstat
where
sid = se.sid -- where se.sid is the sid for the line I am at
STATISTIC# = 12

The numbers here do NOT line up at all. Anyone as an idea?

BTW, I am converting everything to seconds. Going from time_waited in v$session_event and dividing by 100. I also have a catch all that will add up all the waits I didn't catch in my categories by doing a not in, so I am sure I captured all the wait events.
Re: All v$session_event numbers don't add to total time [message #229283 is a reply to message #229280] Fri, 06 April 2007 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the world of instrumentalisation.
Bias is the key word.
Buy and read Optimizing Oracle Performance from Cary Millsap and you will know the reasons.

Regards
Michel
Re: All v$session_event numbers don't add to total time [message #229289 is a reply to message #229280] Fri, 06 April 2007 09:07 Go to previous messageGo to next message
madchaz
Messages: 65
Registered: October 2005
Member
Thanks for the answer.

Unfortunately, it doesn't really address my issue because

1: I don't currently have this book. Getting it from inside the company I work for will take forever and a half. While I agree getting it would be ideal, it's not practical atm.

2: It doesn't give me much to search on.

If you can give me at least an int I can search on, it would be appreciated.
Re: All v$session_event numbers don't add to total time [message #229310 is a reply to message #229289] Fri, 06 April 2007 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Well, all I can say is: it's life.
You have it and have to live with it.
Nothing personal, we are all in this case.

Regards
Michel
Re: All v$session_event numbers don't add to total time [message #229312 is a reply to message #229310] Fri, 06 April 2007 10:37 Go to previous messageGo to next message
madchaz
Messages: 65
Registered: October 2005
Member
Michel Cadot wrote on Fri, 06 April 2007 10:31

Well, all I can say is: it's life.
You have it and have to live with it.
Nothing personal, we are all in this case.

Regards
Michel


I don't mean to be rude, but this is probably the single most useless and cryptic awnser I have ever goten.
Re: All v$session_event numbers don't add to total time [message #229317 is a reply to message #229312] Fri, 06 April 2007 10:50 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I meant counters are not exact.
Counters do not count all events.
Some code path are missing instrumentalisation so are not counted.
All time cannot be physically counted.
GetTime and GetCPUTime are approximative and precision depends on OS.
Quantization (only full ticks are counted) introduced errors.
And so on.

Regards
Michel

Previous Topic: table and its count
Next Topic: Selecting different columns behave differently.
Goto Forum:
  


Current Time: Sat Dec 03 16:01:41 CST 2016

Total time taken to generate the page: 0.09589 seconds