一进程阻塞有关问题解决

一进程阻塞问题解决
同事反映,删除一条数据总是没有反应,请求协助解决.

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

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的