No Response from the Server, Does it Hang or Spin?

 

Diagnostics -----------

If an operation is taking significantly more time than expected or is compromising the performance of other operations, then the best place to check is v$session_wait. This view shows information about what each session in the system is waiting for at the current moment in time. The following SQL*Plus script gathers and formats the required information:

查看V$SESSION_WAIT是第一选择

 

column sid format 990

column seq# format 99990

column wait_time heading 'WTime' format 99990

column event format a30

column p1 format 9999999990

column p2 format 9999999990

column p3 format 9990

select sid,event,seq#,p1,p2,p3,wait_timefrom V$session_waitorder by sid

/

This select should be repeated at least 3 times and the results compared.

至少查看3次  并做对比

 

If the above script reveals about an ENQUEUE wait then you will need to checkfor any locks related to your hanging session:

如果从上述看出是ENQUEUE的等待 则继续查看是否有锁

 

column sid format 990

column type format a2

column id1 format 9999999990

column id2 format 9999999990

column Lmode format 990

column request format 990

select * from v$lock

/

 

Spinning--------

In the case of a Spin situation the session events would normally be staticand the session would not be waiting for an event - rather it would be on CPU.(note in rare circumstances, the event may or may not be static depending on where in the code the spinning is taking place). It would be expected that the session would be utilizing resources heavily such as CPU and memory.

 

For a Spin situation it is important to determine which area of the code thesession is spinning in. Some indication of this may be derived from the eventhowever it is usually necessary to produce an errorstack of the process afew times for analysis by support:

 

connect sys/sys as sysdba     

oradebug setospid <SPID>     

oradebug unlimit     

oradebug dump errorstack 3     

oradebug dump errorstack 3     

oradebug dump errorstack 3

 

where SPID is the operating system process identifier, you can get it fromv$process. Support recommend collecting at least 3 errorstack dumps for each spin occurrence.

 

做3次的ERRORSTACK 每当SPIN发生的时候

 

Hanging

Note that significantly more detailed information can be found by dumping systemstate information for the instance:

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL XX'

;where XX is 266 if the oracle version is 9.2.0.6 or greater or 10.1.0.4 or greater in other versions use 10.

note:3797523.8a systemstate tracefile will be created  in your USER_DUMP_DEST directory.

Support Recommend collecting 3 systemstate dumps for each hang occurrence.Get the Process ID of the problem session from the V$PROCESS 

SELECT pid FROM v$process  WHERE addr =        (SELECT paddr FROM v$session         WHERE  sid = sid_of_problem_session);The systemstate dump includes information for each process.Find details for an individual process by searching for 'PROCESS <id>' Find details of currently waiting wait event by doing a search on 'waiting for'.