問題：V$SESSION SQL_ID 為空，找不到SQL_ID

首先我們來做個實驗：

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處於行鎖等待。

這個時候透過如下指令碼去查詢資料庫：

select inst_id,
       sid,
       sql_id,
       event,
       blocking_session,
       blocking_instance
    from gv$session a
   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

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

   select prev_sql_id from gv$session where inst_id=1 and sid=1150;

    PREV_SQL_ID
    -------------
    9m7787camwh4m

    select sql_text from gv$sql where sql_id='9m7787camwh4m';

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

所以很多人這個時候就蛋疼了，不知道咋辦。現在教大家另外一種方法

    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 - New

    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 :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 :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.UPDATE執行過後，又繼續執行新的SQL，就悲催了，這個時候要自己把所有SQL抓出來，按照時間線排序，CHECK

反正，提供了一種思路，具體的時候請自己判斷，腦袋不要太笨。

    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 = b.MODULE
       --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 = b.MODULE
            ) 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;

問題：V$SESSION SQL_ID 為空，找不到SQL_ID

相關文章