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

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐