| Is this a 10g bug? [message #359906] |
Tue, 18 November 2008 13:01  |
efachim
Messages: 42
Registered: July 2008
|
Member |
|
|
Has anyone seen this before? I tried to present the wait state data as a percentage of time_waited, and I get the ora-01476 error, that the sum(t2.time_waited) in the query below is zero, which does not make sense.
SELECT t1.sid,
t1.event,
t1.total_waits "WAITS",
t1.time_waited "TOTAL TIME",
to_char((t1.time_waited / (SELECT SUM(t2.time_waited) FROM v$session_event t2
WHERE t1.sid = t2.sid and t2.event NOT IN (SELECT name FROM v$event_name WHERE name LIKE '%null%' OR name LIKE '%timer%' OR name LIKE '%SQL*Net%' OR
name LIKE '%rdbms ipc%' OR name LIKE '%ispatcher%' OR name LIKE '%virtual circuit%' OR name LIKE '%PX%' OR name LIKE '%pipe%' OR name
LIKE '%message%' OR name LIKE '%jobq%' OR name LIKE '%idle%' OR name LIKE '%Idle%' ) ) )*100,990.9999) "PERCENTAGE"
FROM v$session_event t1
WHERE t1.event NOT IN
(SELECT name FROM v$event_name WHERE name LIKE '%null%' OR name LIKE '%timer%' OR name LIKE '%SQL*Net%' OR
name LIKE '%rdbms ipc%' OR name LIKE '%ispatcher%' OR name LIKE '%virtual circuit%' OR name LIKE '%PX%' OR name LIKE '%pipe%' OR name
LIKE '%message%' OR name LIKE '%jobq%' OR name LIKE '%idle%' OR name LIKE '%Idle%');
ERROR:
ORA-01476: divisor is equal to zero
[Updated on: Tue, 18 November 2008 13:06] Report message to a moderator |
|
|
|
|
|
| Re: Is this a 10g bug? [message #359912 is a reply to message #359910] |
Tue, 18 November 2008 15:17  |
efachim
Messages: 42
Registered: July 2008
|
Member |
|
|
Here you go...
SELECT t1.sId,
t1.Event,
t1.Total_Waits "WAITS",
t1.Time_Waited "TOTAL TIME",
To_char((t1.Time_Waited / (SELECT SUM(t2.Time_Waited)
FROM v$Session_Event t2
WHERE t1.sId = t2.sId
AND t2.Event NOT IN
(SELECT NAME
FROM v$Event_Name
WHERE NAME LIKE '%null%'
OR NAME LIKE '%timer%'
OR NAME LIKE '%SQL*Net%'
OR NAME LIKE '%rdbms ipc%'
OR NAME LIKE '%ispatcher%'
OR NAME LIKE '%virtual circuit%'
OR NAME LIKE '%PX%'
OR NAME LIKE '%pipe%'
OR NAME LIKE '%message%'
OR NAME LIKE '%jobq%'
OR NAME LIKE '%idle%'
OR NAME LIKE '%Idle%'))) * 100,
990.9999) "PERCENTAGE"
FROM v$Session_Event t1
WHERE t1.Event NOT IN (SELECT NAME
FROM v$Event_Name
WHERE NAME LIKE '%null%'
OR NAME LIKE '%timer%'
OR NAME LIKE '%SQL*Net%'
OR NAME LIKE '%rdbms ipc%'
OR NAME LIKE '%ispatcher%'
OR NAME LIKE '%virtual circuit%'
OR NAME LIKE '%PX%'
OR NAME LIKE '%pipe%'
OR NAME LIKE '%message%'
OR NAME LIKE '%jobq%'
OR NAME LIKE '%idle%'
OR NAME LIKE '%Idle%');
[Updated on: Tue, 18 November 2008 15:22] Report message to a moderator |
|
|
|
|
|
| Re: Is this a 10g bug? [message #360086 is a reply to message #359956] |
Wed, 19 November 2008 09:19 |
efachim
Messages: 42
Registered: July 2008
|
Member |
|
|
Hi Michel,
I guess that depends on the system you tested the query on, right?
I presume you did not get the error below?
ERROR:
ORA-01476: divisor is equal to zero
| Michel Cadot wrote on Tue, 18 November 2008 22:37 | It is far much better.
What is surprising in the fact that some sessions never waited?
Regards
Michel
|
|
|
|
|
| Re: Is this a 10g bug? [message #360104 is a reply to message #359906] |
Wed, 19 November 2008 11:06 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Sessions that never waited shouldn't appear in this query since it's specifically querying wait events.
However it is possible to have a session where the total amount of time spent waiting is so small that it's recorded in the db as 0.
Which'll be your problem I would imagine.
Try running this SQL and see if it makes things any clearer:
SELECT t1.sId,
t1.Event,
t1.Total_Waits no_of_waits,
t1.Time_Waited total_wait_for_event,
SUM(t1.Time_Waited) over(PARTITION BY t1.sid) total_wait_for_session
FROM v$Session_Event t1
WHERE t1.Event NOT IN (SELECT NAME
FROM v$Event_Name
WHERE NAME LIKE '%null%'
OR NAME LIKE '%timer%'
OR NAME LIKE '%SQL*Net%'
OR NAME LIKE '%rdbms ipc%'
OR NAME LIKE '%ispatcher%'
OR NAME LIKE '%virtual circuit%'
OR NAME LIKE '%PX%'
OR NAME LIKE '%pipe%'
OR NAME LIKE '%message%'
OR NAME LIKE '%jobq%'
OR NAME LIKE '%idle%'
OR NAME LIKE '%Idle%')
ORDER BY 1,2;
EDIT: fixed some typos
[Updated on: Wed, 19 November 2008 11:10] Report message to a moderator |
|
|
|
|
|
| Re: Is this a 10g bug? [message #360150 is a reply to message #359906] |
Wed, 19 November 2008 16:59 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
What about something like this? (not at display so cant test, but you shoudl get the idea)
SELECT t1.sId,
t1.Event,
t1.Total_Waits "WAITS",
t1.Time_Waited "TOTAL TIME",
To_char((t1.Time_Waited / (SELECT case when SUM(t2.Time_Waited) = 0 then 1 else SUM(t2.Time_Waited) end
FROM v$Session_Event t2
WHERE t1.sId = t2.sId
AND t2.Event NOT IN
(SELECT NAME
FROM v$Event_Name
WHERE NAME LIKE '%null%'
OR NAME LIKE '%timer%'
OR NAME LIKE '%SQL*Net%'
OR NAME LIKE '%rdbms ipc%'
OR NAME LIKE '%ispatcher%'
OR NAME LIKE '%virtual circuit%'
OR NAME LIKE '%PX%'
OR NAME LIKE '%pipe%'
OR NAME LIKE '%message%'
OR NAME LIKE '%jobq%'
OR NAME LIKE '%idle%'
OR NAME LIKE '%Idle%'))) * 100,
990.9999) "PERCENTAGE"
FROM v$Session_Event t1
WHERE t1.Event NOT IN (SELECT NAME
FROM v$Event_Name
WHERE NAME LIKE '%null%'
OR NAME LIKE '%timer%'
OR NAME LIKE '%SQL*Net%'
OR NAME LIKE '%rdbms ipc%'
OR NAME LIKE '%ispatcher%'
OR NAME LIKE '%virtual circuit%'
OR NAME LIKE '%PX%'
OR NAME LIKE '%pipe%'
OR NAME LIKE '%message%'
OR NAME LIKE '%jobq%'
OR NAME LIKE '%idle%'
OR NAME LIKE '%Idle%');
|
|
|
|
| Re: Is this a 10g bug? [message #360272 is a reply to message #360110] |
Thu, 20 November 2008 04:31 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
| Michel Cadot wrote on Wed, 19 November 2008 17:43 | Your query is not equivalent to OP's one.
|
I didn't claim it to be exactly equivalent since I wasn't trying to give the OP a sql he could use to replace his exisiting one, merely trying to illustrate the fact that you can have sessions where the total time waited summed to 0.
That said, I'm fairly sure that this:
SUM(t1.Time_Waited) over(PARTITION BY t1.sid) total_wait_for_session
Is equivalent to the sub-query in the original query.
Both should get the total time waited per session for the wait events we're interested in.
Or am I missing something?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
