Buddy Yuan的个人技术博客

SQL_ID和PREV_SQL_ID在我们日常监控会话的SQL语句中非常重要,有时候我们经常看到SQL_ID是空的情况,那么我们就会去找 PREV_SQL_ID的值来获取最后一次执行的SQL语句.那么有一个疑问?什么时候SQL_ID有值,什么时候它又是空的呢？下面我们会来做一个小实验,我的实验环境是Oracle 11gR2.通过实验来说明这个问题.

1.首先我们来模拟单会话查询;

>>>>>>>>session1>>>>>>>>
SQL> select sid from v$mystat where rownum=1;
SID
----------
403

select * from v$datafile where rownum=1;
select * from dba_tables where rownum=1;
select * from t1 where rownum=1; -------普通表
select * from aaa;               -------普通视图
select * from dba_tables;        ------------长查询

>>>>>>>>session2>>>>>>>>>
SQL> select sid,sql_id,PREV_SQL_ID from v$session where sid=403;
SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 80hfhzra2uv3z
SQL> /

SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 18s1d1fq9nzp2 18s1d1fq9nzp2
SQL> /

SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 b61hdn491z56k
SQL> /

SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 4njm39vk9m633

SQL> /
SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 7tz9tdx2npk9t 4njm39vk9m633

在这里我们可以很清晰的看到,在我们运行长时间的sql查询时,我们正在运行的SQL_ID会是当前正在运行的sql语句.不过有个例外,在执行dba_tables这样的视图时,执行完之后,我们的 SQL_ID和PREV_SQL_ID都是有值的,而且保持一致.

2.接下来是DML测试.

>>>>>>>>session1>>>>>>>>
update test set id=5 where id=3;
commit;
update t4 set OWNER='ttt'; ---------t4 75273行
commit

>>>>>>>>session2>>>>>>>>>
SQL> select sid,sql_id,PREV_SQL_ID from v$session where sid=403;
SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 38yz7yvht2268

SQL> /
SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 38yz7yvht2268

SQL> /

SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 60hhc0y04465g 60hhc0y04465g
SQL> /

SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 60hhc0y04465g

只要一运行就会有PREV_SQL_ID,不管是提交还是回滚.如果是大批量并且不提交的情况下,SQL_ID和PREV_SQL_ID保持一致,提交或者回滚之后SQL_ID变为空.
下面是模拟锁定的情况.

>>>>>>>>session1>>>>>>>>
SQL> update test set id=5 where id=3;
1 row updated.
>>>>>>>>session2>>>>>>>>
SQL> select sid from v$mystat where rownum=1;
SID
----------
407
SQL> update test set id=5 where id=3;
>>>>>>>>session3>>>>>>>>
SQL> select sid,sql_id,PREV_SQL_ID from v$session where sid in (403,407);
SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 38yz7yvht2268
407 38yz7yvht2268 dyk4dprp70d74

在DML语句出现锁定情况时,我们的阻塞者只会有PREV_SQL_ID,而被阻塞者会有SQL_ID.提交或者回滚之后,两者都只会有PREV_SQL_ID.

>>>>>>>>session1>>>>>>>>
SQL> commit;
Commit complete.
>>>>>>>>session3>>>>>>>>
SQL> select sid,sql_id,PREV_SQL_ID from v$session where sid in (403,407);

SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 38yz7yvht2268
407 38yz7yvht2268

3.DDL语句的测试

>>>>>>>>session1>>>>>>>>
SQL> create table t3
2 ( id number);
Table created.
SQL> alter table t3 add name varchar2(20);
Table altered.
SQL> truncate table t3;
Table truncated.
SQL> drop table t3;
Table dropped.
>>>>>>>>session3>>>>>>>>
SQL> select sid,sql_id,PREV_SQL_ID from v$session where sid =403;
SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 0xmrqqc98s98c 0xmrqqc98s98c
SQL> /
SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 g1d3zhz7wq0d4 g1d3zhz7wq0d4
SQL> /
SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 b3pbmwjuzmw24 b3pbmwjuzmw24
SQL> /
SID SQL_ID PREV_SQL_ID
---------- ------------- -------------
403 7ajbnzrag3jrw

可以看到在运行create,alter,truncate的时候,SQL_ID和PREV_SQL_ID都是保持一致的,然而在运行drop语句的时候,SQL_ID是空的.

其实还有很多种情况,大家可以自己去模拟.我这里只是做个总结:
1.在基本普通查询中,查询完之后,SQL_ID和PREV_SQL_ID都有值,并且保持一致.
2.较长时间的查询,在查询过程中,SQL_ID存在,查完后SQL_ID将不复存在,取而代之的是PREV_SQL_ID.
3.短小DML语句,执行完后,只会有PREV_SQL_ID,不管你是提交还是回滚,都会存在.
4.长DML语句,执行完后,SQL_ID和PREV_SQL_ID都有值,并且保持一致.提交和回滚后,只有PREV_SQL_ID.
5.产生锁的时候, 阻塞者只会有PREV_SQL_ID,而被阻塞者会有SQL_ID和PREV_SQL_ID
6.DDL语句,create,alter,truncate,会有SQL_ID和PREV_SQL_ID,drop只会有PREV_SQL_ID.