ORACLE中如何找到未提交事务的SQL语句详解

在oracle数据库中，我们能否找到未提交事务（uncommit transactin）的sql语句或其他相关信息呢？关于这个问题，我们先来看看实验测试吧。实践出真知。

首先，我们在会话1（sid=63)中构造一个未提交的事务，如下所：

sql> create table test
 2 as
 3 select * from dba_objects;
 
table created.
sql> select userenv('sid') from dual;
 
userenv('sid')
--------------
   63
 
sql> delete from test where object_id=12;
 
1 row deleted.
 
sql>

然后我们在会话2（sid=70)中，我们使用下面sql查询未提交的sql语句。如下所示：

sql> select userenv('sid') from dual;
 
userenv('sid')
--------------
   70
 
sql> 
sql> set serveroutput on size 99999;
sql> execute print_table('select sql_text from v$sql s,v$transaction t where s.last_active_time=t.start_date');
sql_text      : delete from test where object_id=12
-----------------
sql_text      : select
grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where
obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
-----------------
sql_text      : select /* opt_dyn_samp */ /*+ all_rows
ignore_where_clause no_parallel(samplesub)
opt_param('parallel_execution_enabled', 'false') no_parallel_index(samplesub)
no_sql_tune */ nvl(sum(c1),0), nvl(sum(c2),0) from (select /*+
ignore_where_clause no_parallel("test") full("test") no_parallel_index("test")
*/ 1 as c1, case when "test"."object_id"=12 then 1 else 0 end as c2 from "test"
sample block (6.134372 , 1) seed (1) "test") samplesub
-----------------
sql_text      : select col#, grantee#,
privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is
not null group by privilege#, col#, grantee# order by col#, grantee#
-----------------
sql_text      : select
type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,nvl(lists,6553
5),nvl(groups,65535),cachehint,hwmincr,
nvl(spare1,0),nvl(scanhint,0),nvl(bitmapranges,0) from seg$ where ts#=:1 and
file#=:2 and block#=:3
-----------------
pl/sql procedure successfully completed.

如上所示，这个sql我们会查出很多不相关的sql语句，接下来我们可以用下面的sql查询（改用sql developer展示，因为sql*plus，不方便展示），如下所示，这个sql倒不会查出不相关的sql。但是这个sql能胜任任何场景吗？答案是否定的。

select s.sid
  ,s.serial#
  ,s.username
  ,s.osuser 
  ,s.program 
  ,s.event
  ,to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss') 
  ,to_char(t.start_date,'yyyy-mm-dd hh24:mi:ss') 
  ,s.last_call_et 
  ,s.blocking_session 
  ,s.status
  ,( 
    select q.sql_text 
    from v$sql q 
    where q.last_active_time=t.start_date 
    and rownum<=1) as sql_text 
from v$session s, 
  v$transaction t 
where s.saddr = t.ses_addr;

我们知道，在oracle里第一次执行一条sql语句后，该sql语句会被硬解析，而且执行计划和解析树会被缓存到shared pool里。方便以后再次执行这条sql语句时不需要再做硬解析。但是shared pool的大小也是有限制的，不可能无限制的缓存所有sql的执行计划，它使用lru算法管理库高速缓存区。所以有可能你要找的sql语句已经不在shared pool里面了，它从shared pool被移除出去了。如下所示，我们使用sys.dbms_shared_pool.purge人为构造sql被移除出shared pool的情况。如下所示：

sql> col sql_text for a80;
sql> select sql_text
 2  ,sql_id
 3  ,version_count
 4  ,executions 
 5  ,address
 6  ,hash_value
 7 from v$sqlarea where sql_text 
 8 like 'delete from test%';
 
sql_text        sql_id  version_count executions address   hash_value
------------------------------------ ------------- ------------- ---------- ---------------- ----------
delete from test where object_id=12 5xaqyzz8p863u    1   1 0000000097fae648 3511949434
 
sql> exec sys.dbms_shared_pool.purge('0000000097fae648,3511949434','c');
 
pl/sql procedure successfully completed.
 
sql>

此时我们查询到的sql语句，是一个不相关的sql或者其值为null。

接下来我们回滚sql语句，然后继续新的实验测试，如下所示，在会话1（sid=63)里面执行了两个dml操作语句，都未提交事务。

sql> delete from test where object_id=12;
 
1 row deleted.
 
sql> update test set object_name='kkk' where object_id=14;
 
1 row updated.
 
sql>

接下来，我们使用sql语句去查找未提交的sql，发现只能捕获最开始执行的delete语句，不能捕获到后面执行的update语句。这个实验也从侧面印证了，我们不一定能准确的找出未提交事务的sql语句。

所以结合上面实验，我们基本上可以给出结论，我们不一定能准确找出未提交事务的sql语句，这个要视情况或场景而定。存在这不确定性。

参考资料：

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:9523503800346688981

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对www.887551.com的支持。

ORACLE中如何找到未提交事务的SQL语句详解

相关推荐