同事反映,刪除一條數(shù)據(jù)總是沒有反應(yīng),請求協(xié)助解決.?

問題非常明顯,肯定是有某個session在block他的session,導(dǎo)致一直在等待資源的釋放.于是很快將問題定位,得到如下數(shù)據(jù):?

Java代碼??

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究竟在干什么呢? 于是問題就來了,發(fā)現(xiàn)該session的狀態(tài)是inactive,沒有任何SQL在運(yùn)行,通過prev_hash_value找到給session上次執(zhí)行的SQL,看看是否能夠找到問題,發(fā)現(xiàn)SQL如下:?
引用 begin :id := sys.dbms_transaction.local_transaction_id; end;?

這是什么SQL??

當(dāng)前這個sesion現(xiàn)在沒有執(zhí)行任何SQL,怎么會block住另外一個session呢?原因是,我們?nèi)ゲ樵兞硗庖粋€表v$transaction就會知道,該session擁有一個事務(wù),事務(wù)的狀態(tài)是active的,session的狀態(tài)表示有沒有正在運(yùn)行的SQL,不能代表有沒有活動事務(wù)存在.?

而該事務(wù)有事在干什么呢?經(jīng)查詢得知,這個SQL是由client端發(fā)出的SQL,從而導(dǎo)致了一些輔助的輸出信息,導(dǎo)致了真正的SQL語句.?


---?
1.sqlplus 遠(yuǎn)程連接服務(wù)器?
2.pl/sql developer 遠(yuǎn)程連接服務(wù)器?
3.ssh 連接上服務(wù)器登陸 sqlplus?

1、2、3 有什么區(qū)別呢？?
為什么要看這個區(qū)別呢？起因是 我在plsql developer 執(zhí)行過sql后，總是通過v$session 和v$sql 連接總是找不到執(zhí)行過的sql，覺得很奇怪?
所有有了以下的測試過程?

--測試一下?


--要測試執(zhí)行的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，在執(zhí)行select * from t_order order by a desc,b;后? 進(jìn)行檢查?
select sql_id,prev_sql_id from v$session where SID=&sid;???????? --得到運(yùn)行過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);???????? --根據(jù)sql_id,prev_sql_id 得到sql?
select spid from v$process where addr=(select paddr from v$session where sid=&sid);??? --根據(jù)sid 得到后臺跟蹤日志的名字（sid_ora_spid.trc)?


1.sqlplus 遠(yuǎn)程連接服務(wù)器 執(zhí)行?
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執(zhí)行的內(nèi)部過程（不包含sys用戶執(zhí)行的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 遠(yuǎn)程連接服務(wù)器?
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執(zhí)行的內(nèi)部過程（不包含sys用戶執(zhí)行的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 連接上服務(wù)器登陸 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?????????????????????? --得到了執(zhí)行的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執(zhí)行的內(nèi)部過程（不包含sys用戶執(zhí)行的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;?

總結(jié)：1、在客戶端執(zhí)行sql過程中，我們比較難得到執(zhí)行過了什么sql，因為他們都被 dbms_output.get_line,DBMS_OUTPUT.GET_LINES 這樣的sql覆蓋了?
????? 2、在服務(wù)器上執(zhí)行sql過程中，是比較容易得到sql的?

何時會觸發(fā)DBMS_TRANSACTION.LOCAL_TRANSACTION_ID?

我在session 876中執(zhí)行完下面sql后?
select * from table?
在到另一session中執(zhí)行?
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;?
結(jié)果老是出來這個?
begin :id := sys.dbms_transaction.local_transaction_id; end;?
為什么不出現(xiàn)select * from table語句呢??


原因:是因為在PL/SQL Developer使用的SESSION就會是這樣的，用SQLPLUS調(diào)用的查詢就可以查詢正確的結(jié)果.?

總結(jié)

以上是生活随笔為你收集整理的sys.dbms_transaction.local_transaction_id出现的问题的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。