V$SESSION SQL_ID 為空白,找不到SQL_ID,sessionsql_id
兄弟,是不是遇到過查詢 V$SESSION.SQL_ID 但是呢 SQL_ID 是空,然後找不到SQL的尷尬情況?太多人問這個問題了。
我相信你們也沒百度/GOOGLE到好的解決辦法,今天就分享一個方法,教大家抓SQL(本方法基於ORACLE11G,10G 就洗洗睡吧)。
首先我們來做個實驗:
SQL> select sid from v$mystat where rownum=1;
SID
----------
1150
SQL> update test set owner='BIGSB' where object_id<100;
98 rows updated
在1150這個SESSION裡面執行一個UPDATE,不要提交。
SQL> select sid from v$mystat where rownum=1;
SID
----------
1338
SQL> update test set owner='SB' where object_id<10;
在1338裡面跑另外一個UPDATE,因為1150沒提交,1138處於行鎖等待。
這個時候通過如下指令碼去查詢資料庫:
SQL> select inst_id, 2 sid, 3 sql_id, 4 event, 5 blocking_session, 6 blocking_instance 7 from gv$session a 8 where blocking_session is not null; INST_ID SID SQL_ID EVENT BLOCKING_SESSION BLOCKING_INSTANCE---------- ---------- ------------- --------------------------------------- ---------------- ---------------- 1 1338 852mvmth18w37 enq: TX - row lock contention 1150 1 SQL> select sql_id from gv$session where inst_id=1 and sid=1150; SQL_ID-------------
確實,SQL_ID是空的,也許有人會說,那我去查詢PREV_SQL_ID,恩你去試一試吧,那個SQL_ID是事物的SQL_ID,並不是UPDATE的SQL_ID
SQL> select prev_sql_id from gv$session where inst_id=1 and sid=1150; PREV_SQL_ID-------------9m7787camwh4m SQL> select sql_text from gv$sql where sql_id='9m7787camwh4m'; SQL_TEXT--------------------------------------------------------------------------------begin :id := sys.dbms_transaction.local_transaction_id; end;
所以很多人這個時候就蛋疼了,不知道咋辦。現在教大家另外一種方法
SQL> select PREV_EXEC_START,USERNAME,MODULE,ACTION FROM GV$SESSION WHERE INST_ID=1 AND SID=1150; PREV_EXEC_START USERNAME MODULE ACTION--------------- ------------------------------ -----------------------2015-04-10 18:01:44 SCOTT PL/SQL Developer Command Window - NewSQL> SELECT SQL_ID,SQL_TEXT,LAST_ACTIVE_TIME,MODULE,ACTION FROM GV$SQL WHERE INST_ID=1 AND LAST_ACTIVE_TIME=TO_DATE('2015-04-10 18:01:44','YYYY-MM-DD HH24:MI:SS'); SQL_ID SQL_TEXT LAST_ACTIVE_TIME MODULE ACTION------------- -------------------------------------------------------------------------------- ---------------- ---------------------------------------------------------------- ----------------------------------------------------------------2syvqzbxp4k9z select u.name, o.name, a.interface_version#, o.obj# from association$ a, us 2015/4/10 18:01: 6c9wx6z8w9qpu select a.default_selectivity from association$ a 2015/4/10 18:01: 2xyb5d6xg9srh select a.default_cpu_cost, a.default_io_cost from association$ a 2015/4/10 18:01: d1s917pgj7650 update test set owner='BIGSB' where object_id<100 2015/4/10 18:01: PL/SQL Developer Command Window - New
現在就可以把SQL 抓到了
請注意:
1.在高並發的情況下,可能會出現多個可疑SQL
2.LAST_ACTIVE_TIME與PREV_EXEC_START可能有小的誤差(秒計)
這個時候,請自己判斷,腦袋不要太笨。
select a.inst_id, a.sid, a.sql_id, b.sql_id, b.sql_text from gv$session a, gv$sql b where a.inst_id = b.inst_id and a.PREV_EXEC_START = b.LAST_ACTIVE_TIME and a.USERNAME = b.PARSING_SCHEMA_NAME and a.MODULE_HASH = b.MODULE_HASH and a.ACTION_HASH = b.ACTION_HASH select a.inst_id, a.sid, a.event, a.sql_id, b.sql_text running_sql, c.sql_in_session, c.sql_id_in_v$sql, c.sql_text blocking_sql, a.blocking_session, a.blocking_instance from gv$session a, (select sql_id, sql_text from (select sql_id, sql_text, row_number() over(partition by sql_id order by sql_id) as rn from gv$sql) where rn = 1) b, (select a.inst_id, a.sid, a.sql_id sql_in_session, b.sql_id sql_id_in_v$sql, b.sql_text from gv$session a, gv$sql b where a.inst_id = b.inst_id and a.PREV_EXEC_START =b.LAST_ACTIVE_TIME and a.USERNAME = b.PARSING_SCHEMA_NAME and a.MODULE_HASH = b.MODULE_HASH and a.ACTION_HASH = b.ACTION_HASH) c where a.sql_id = b.sql_id and a.blocking_session is not null and a.BLOCKING_SESSION = c.sid and a.BLOCKING_INSTANCE = c.inst_id;