一进程阻塞问题解决

同事反映,删除一条数据总是没有反应,请求协助解决.

问题非常明显,肯定是有某个session在block他的session,导致一直在等待资源的释放.于是很快将问题定位,得到如下数据:

SESS	ID1	ID2	LMODE	REQUEST	TYPE	CTIME	BLOCK
Holder: 389 17267	0	3	0	TM	8758	1
Waiter: 182 17267	0	0	4	TM	1946	0

那session 389究竟在干什么呢? 于是问题就来了,发现该session的状态是inactive,没有任何SQL在运行,通过prev_hash_value找到给session上次执行的SQL,看看是否能够找到问题,发现SQL如下:

引用

begin :id := sys.dbms_transaction.local_transaction_id; end; 

这是什么SQL?

当前这个sesion现在没有执行任何SQL,怎么会block住另外一个session呢?原因是,我们去查询另外一个表v$transaction就会知道,该session拥有一个事务,事务的状态是active的,session的状态表示有没有正在运行的SQL,不能代表有没有活动事务存在.

而该事务有事在干什么呢?经查询得知,这个SQL是由client端发出的SQL,从而导致了一些辅助的输出信息,导致了真正的SQL语句.


---
1.sqlplus 远程连接服务器
2.pl/sql developer 远程连接服务器
3.ssh 连接上服务器登陆 sqlplus

1、2、3 有什么区别呢？
为什么要看这个区别呢？起因是 我在plsql developer 执行过sql后，总是通过v$session 和v$sql 连接总是找不到执行过的sql，觉得很奇怪
所有有了以下的测试过程

--测试一下


--要测试执行的sql
select * from v$MYSTAT WHERE ROWNUM<2;
exec dbms_monitor.session_trace_enable;
select * from t_order order by a desc,b;
exec dbms_monitor.session_trace_disable;

--测试过程中检查的sql，在执行select * from t_order order by a desc,b;后  进行检查
select sql_id,prev_sql_id from v$session where SID=&sid;         --得到运行过select * from t_order order by a desc,b;的 sql_id,prev_sql_id
SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE  SQL_ID IN(&sqlid1,&sqlid2);         --根据sql_id,prev_sql_id 得到sql
select spid from v$process where addr=(select paddr from v$session where sid=&sid);    --根据sid 得到后台跟踪日志的名字（sid_ora_spid.trc)


1.sqlplus 远程连接服务器 执行
SQL> COL SQL_TEXT FOR A60
SQL> select sql_id,prev_sql_id from v$session where SID=409;

SQL_ID        PREV_SQL_ID
------------- -------------
              9babjv8yq8ru3
SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE  SQL_ID IN('9babjv8yq8ru3','9babjv8yq8ru3');

SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------
9babjv8yq8ru3 BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;            --得到了 “BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;”  的sql
SQL> select spid from v$process where addr=(select paddr from v$session where sid=409);

SPID
------------
27909
--查看跟踪日志
sql执行的内部过程（不包含sys用户执行的sql）：
BEGIN dbms_monitor.session_trace_enable; END;
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
SELECT NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "T_ORDER" "T_ORDER") SAMPLESUB
select * from t_order order by a desc,b
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
BEGIN dbms_monitor.session_trace_disable; END;




2.pl/sql developer 远程连接服务器
SQL> COL SQL_TEXT FOR A60
SQL> select sql_id,prev_sql_id from v$session where SID=401;

SQL_ID        PREV_SQL_ID
------------- -------------
              9m7787camwh4m
SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE  SQL_ID IN('9m7787camwh4m','9m7787camwh4m');

SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------
9m7787camwh4m begin :id := sys.dbms_transaction.local_transaction_id; end;         --得到“begin :id := sys.dbms_transaction.local_transaction_id; end;”的sql

SQL> select spid from v$process where addr=(select paddr from v$session where sid=401);

SPID
------------
28716

--查看跟踪日志
sql执行的内部过程（不包含sys用户执行的sql）：
begin dbms_monitor.session_trace_enable; end;

begin
  sys.dbms_output.get_line(line => :line, status => :status);
end;
begin :id := sys.dbms_transaction.local_transaction_id; end;
select 'x' from dual
begin :id := sys.dbms_transaction.local_transaction_id; end;
SELECT NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "T_ORDER" "T_ORDER") SAMPLESUB
select * from t_order order by a desc,b
begin :id := sys.dbms_transaction.local_transaction_id; end;
begin
  sys.dbms_output.get_line(line => :line, status => :status);
end;
begin :id := sys.dbms_transaction.local_transaction_id; end;
begin dbms_monitor.session_trace_disable; end;



3.ssh 连接上服务器登陆 sqlplus
SQL> select sql_id,prev_sql_id from v$session where SID=417;

SQL_ID        PREV_SQL_ID
------------- -------------
              btm331qqa163c
SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE  SQL_ID IN('btm331qqa163c','btm331qqa163c');

SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------
btm331qqa163c select * from t_order order by a desc,b                       --得到了执行的sql ：select * from t_order order by a desc,b
btm331qqa163c                
SQL> select spid from v$process where addr=(select paddr from v$session where sid=417);

SPID
------------
26220



--查看跟踪日志
sql执行的内部过程（不包含sys用户执行的sql）：
BEGIN dbms_monitor.session_trace_enable; END;
SELECT NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "T_ORDER" "T_ORDER") SAMPLESUB
select * from t_order order by a desc,b
BEGIN dbms_monitor.session_trace_disable; END;

总结：1、在客户端执行sql过程中，我们比较难得到执行过了什么sql，因为他们都被 dbms_output.get_line,DBMS_OUTPUT.GET_LINES 这样的sql覆盖了
      2、在服务器上执行sql过程中，是比较容易得到sql的