sys.dbms_transaction.local_transaction_id出现的问题

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

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

Java代码  

1. SESS    ID1 ID2 LMODE   REQUEST TYPE    CTIME   BLOCK  
2. Holder: 389 17267   0   3   0   TM  8758    1  
3. 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的 

何時會觸發DBMS_TRANSACTION.LOCAL_TRANSACTION_ID 

我在session 876中執行完下面sql后 
select * from table 
在到另一session中執行 
SELECT /*+ ORDERED */ 
sql_text 
FROM v$sqltext a 
WHERE (a.hash_value, a.address) IN ( 
SELECT DECODE (sql_hash_value,0,prev_hash_value,sql_hash_value), 
DECODE (sql_hash_value,0,prev_sql_addr, sql_address) 
FROM v$session b where sid=876) 
order by a.piece; 
結果老是出來這個 
begin :id := sys.dbms_transaction.local_transaction_id; end; 
為什么不出現select * from table語句呢? 


原因:是因為在PL/SQL Developer使用的SESSION就會是這樣的，用SQLPLUS調用的查詢就可以查詢正確的結果.