开发反应原来执行2个小时的存储过程，今天执行了10个小时都还没有结束，要求分析原因。
1,查看正在执行执行过程的会话
查看正在执行的存储过程，需要使用到dba_kgllock与x$kglob，v$session视图，只通过v$session sql_id来定位正在执行的存储过程是不确定的。
SQL> @sess_kgl_object.sql
Enter value for inst_id:
Enter value for sid:
Enter value for owner:
Enter value for object_name: HUANGTINGZHONGHT
INST
SID SESS_SADDR KGLLKHDL EVENT KGLLKMOD KGLLKREQ KGLLKTYPE OWNER:OBJECT_NAME
--------------- ---------------- ---------------- ------------------------- -------- -------- ---------- ---------------------
1.892 07000003CE53CEA0 0700000396DF1A40 read by other session 1 0 Lock EOMS.HUANGTINGZHONGHT
read by other session 2 0 Pin EOMS.HUANGTINGZHONGHT
这里可以看到在节点1会话为892正在执行这个会话
2，查看会话的信息
2.1 查看会话的详细信息
SQL> @print_table.sql
Enter value for query_sql: select * from v$session where sid=892
SADDR : 07000003CE53CEA0
SID : 892
SERIAL# : 42259
AUDSID : 18812540
PADDR : 07000003CF3B6AA0
USER# : 43
USERNAME : HTZ1
COMMAND : 7
OWNERID : 2147483644
TADDR : 07000003BA83AD28
LOCKWAIT :
STATUS : ACTIVE
SERVER : DEDICATED
SCHEMA# : 43
SCHEMANAME : HTZ1
OSUSER : oracle
PROCESS : 459500
MACHINE : HTZ.PW1
TERMINAL : UNKNOWN
PROGRAM : oracle@HTZ.PW1 (J005)
TYPE : USER
SQL_ADDRESS : 070000030BDCB578
SQL_HASH_VALUE : 1443821121
SQL_ID : cr121bbb0xxx1
SQL_CHILD_NUMBER : 0
PREV_SQL_ADDR : 0700000384AD2CE8
PREV_HASH_VALUE : 551443034
PREV_SQL_ID : 1231tzchdwqkk
PREV_CHILD_NUMBER : 0
PLSQL_ENTRY_OBJECT_ID : 1396908
PLSQL_ENTRY_SUBPROGRAM_ID : 1
PLSQL_OBJECT_ID :
PLSQL_SUBPROGRAM_ID :
MODULE :
MODULE_HASH : 0
ACTION :
ACTION_HASH : 0
CLIENT_INFO :
FIXED_TABLE_SEQUENCE : 16495040
ROW_WAIT_OBJ# : 1169971
ROW_WAIT_FILE# : 174
ROW_WAIT_BLOCK# : 274559
ROW_WAIT_ROW# : 0
LOGON_TIME : 2014-10-09 01:57:48
LAST_CALL_ET : 115411
PDML_ENABLED : NO
FAILOVER_TYPE : NONE
FAILOVER_METHOD : NONE
FAILED_OVER : NO
RESOURCE_CONSUMER_GROUP :
PDML_STATUS : DISABLED
PDDL_STATUS : DISABLED
PQ_STATUS : DISABLED
CURRENT_QUEUE_DURATION : 0
CLIENT_IDENTIFIER :
BLOCKING_SESSION_STATUS : UNKNOWN
BLOCKING_INSTANCE :
BLOCKING_SESSION :
SEQ# : 12570
EVENT# : 67
EVENT : read by other session
P1TEXT : file#
P1 : 174
P1RAW : 00000000000000AE
P2TEXT : block#
P2 : 274559
P2RAW : 000000000004307F
P3TEXT : class#
P3 : 1
P3RAW : 0000000000000001
WAIT_CLASS_ID : 1740759767
WAIT_CLASS# : 8
WAIT_CLASS : User I/O
WAIT_TIME : 0
SECONDS_IN_WAIT : 112822
STATE : WAITING
SERVICE_NAME : SYS$USERS
SQL_TRACE : DISABLED
SQL_TRACE_WAITS : FALSE
SQL_TRACE_BINDS : FALSE
可以得到HTZ1用户正在执行cr121bbb0xxx1这条sql，正在等待read by other session，last_call_et远远大于SEQ#的值，所以怀疑会话一直在等待read by other session死了。
2.2 通过ASH查询会话的历史seq#的值
select session_id,seq#,wait_time from v$active_session_history where session_id=892 order by SAMPLE_TIME;
SESSION_ID SEQ# WAIT_TIME
---------- ---------- ----------
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
892 12570 0
通过ASH中的SEQ#可以确认,会话一直等待read by other session.
3 查询阻塞者
这里通过we这个脚本来查询数据库的等待事件
SQL> @we.sql
USERNMAE
LAST_CALL SESS_SERIAL ROW_WAIT
I EVENT SEQ# OSPID FILE#:OBJ#:BLOCK#:ROW#
-- ------------------------- ---------------------- -------------------------------------------
1 SQL*Net message from clie HTZ1:0:37254 887:51173:463866 79:5791639:8022:0
kksfbc child completion HTZ1:758441:45477 1027:20336:565778 31:1049059:194315:0
Streams AQ: waiting for m SYS:4:42529 1062:3:290870 3:31091:11972:0
db file sequential read HTZ1:136554:41682 1008:48636:447046 174:1169971:274559:0
SQL*Net message from clie HTZ1:0:24929 922:47522:405506 32:1049059:229799:0
PX Deq: Execute Reply SYS:0:48497 823:40081:307530 1006:-1:414731:0
PX Deq: Execution Msg SYS:0:4 795:30460:279154 0:-1:0:0
db file sequential read STAT:36282:6735 861:6087:545000 77:232752:493164:0
kksfbc child completion HTZ1:781784:31407 1065:22800:487926 107:1046915:95713:0
read by other session HTZ1:115605:12570 892:42259:459500 174:1169971:274559:0
db file sequential read HTZ1:7:62475 843:2364:607122 239:1388452:273613:0
2 db file sequential read HTZ1:1:1201 1022:33270:99132 206:1075194:117325:0
SQL*Net message from clie HTZ1:0:56610 1025:40965:434358 228:1076377:324554:0
SQL*Net message from clie HTZ1:1:2319 983:32863:483456 45:701370:3840:0
SQL*Net message from clie HTZ1:1:16639 1042:45749:131360 0:-1:0:0
SQL*Net more data from cl HTZ1:1:7 993:20195:237660 0:-1:0:0
SQL*Net message from clie HTZ1:0:21373 938:44952:471910 60:1324140:259727:0
log file sync HTZ1:0:50035 926:3825:467954 0:-1:0:0
PX Deq: reap credit SYS:1:20 910:64687:192958 0:-1:0:0
SQL*Net message from clie HTZ1:1:47225 927:63915:500646 240:466862:308718:0
这里我们查询到阻塞进程为1008这个进程
4，查看会话1008的信息
查询方法跟上面一样，下面只贴出了ASH中的信息
select session_id,seq# from v$active_session_history where session_id=1008 order by SAMPLE_TIME;
1008 41682
1008 41682
1008 41682
1008 41682
1008 41682
1008 41682
1008 41682
1008 41682
1008 41682
1008 41682
1008 41682
1008 41682
1008 41682
1008 41682
说明 1008这个会话已经读取的时候给HANG住了。
5，KILL会话
SQL> !ps -ef|grep 447046
oracle 471118 344342 0 10:12:59 pts/11 0:00 grep 447046
oracle 447046 1 0 Oct 08 - 0:44 oracleHTZ11 (LOCAL=NO)
SQL> !kill -9 447046
SQL> !ps -ef|grep 447046
oracle 525054 344342 0 10:13:13 pts/11 0:00 grep 447046
KILL 1008这个SESSION后，恢复正常。
一次特殊的read by other session等待事件处理：等您坐沙发呢！