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'.