Home » RDBMS Server » Performance Tuning » Is this a 10g bug? (Oracle 10g, 10.2.0.2, Linux)
Is this a 10g bug? [message #359906] Tue, 18 November 2008 13:01 Go to next message
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 #359910 is a reply to message #359906] Tue, 18 November 2008 14:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL Formatter and format your query to make it easier to read.

Regards
Michel
Re: Is this a 10g bug? [message #359912 is a reply to message #359910] Tue, 18 November 2008 15:17 Go to previous messageGo to next message
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 #359956 is a reply to message #359912] Tue, 18 November 2008 23:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 #360086 is a reply to message #359956] Wed, 19 November 2008 09:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 #360110 is a reply to message #360104] Wed, 19 November 2008 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your query is not equivalent to OP's one.

To answer your question:
SQL> select sid, event, time_waited, time_waited_micro from v$Session_Event where Time_Waited = 0;
  SID EVENT                                                            TIME_WAITED TIME_WAITED_MICRO
----- ---------------------------------------------------------------- ----------- -----------------
  149 events in waitclass Other                                                  0                10
  151 class slave wait                                                           0               110
  156 class slave wait                                                           0                70
  156 events in waitclass Other                                                  0                94
  159 SQL*Net message to client                                                  0              4224
  159 SQL*Net more data from client                                              0              3710
  159 SQL*Net break/reset to client                                              0              3029
  159 events in waitclass Other                                                  0              1696
  160 db file sequential read                                                    0              4452
  160 events in waitclass Other                                                  0                38
  164 latch: shared pool                                                         0                 8
  164 events in waitclass Other                                                  0                97
  165 direct path read                                                           0                17
  166 direct path read                                                           0                46
  166 direct path read temp                                                      0                 4
  167 direct path read temp                                                      0                 8
  168 events in waitclass Other                                                  0                61
  169 events in waitclass Other                                                  0                 8

18 rows selected.

Regards
Michel
Re: Is this a 10g bug? [message #360150 is a reply to message #359906] Wed, 19 November 2008 16:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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?
Re: Is this a 10g bug? [message #360612 is a reply to message #359906] Fri, 21 November 2008 10:52 Go to previous message
efachim
Messages: 42
Registered: July 2008
Member
Thanks to you all! - Michel, coleing and Cookiemonster. I managed to get something that works from your feedback.

rgds, E.
Previous Topic: Understand how optimizer works
Next Topic: The difference between sql_text in v$sqlarea and v$sqltext
Goto Forum:
  


Current Time: Fri Dec 09 15:18:09 CST 2016

Total time taken to generate the page: 0.24533 seconds