ORACLE中如何找到未提交事務(wù)的SQL語(yǔ)句詳解

2020-07-26 13:58:21

字體：大中小

供稿：網(wǎng)友

在Oracle數(shù)據(jù)庫(kù)中，我們能否找到未提交事務(wù)（uncommit transactin）的SQL語(yǔ)句或其他相關(guān)信息呢？關(guān)于這個(gè)問(wèn)題，我們先來(lái)看看實(shí)驗(yàn)測(cè)試吧。實(shí)踐出真知。

首先，我們?cè)跁?huì)話1（SID=63)中構(gòu)造一個(gè)未提交的事務(wù)，如下所：

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>

然后我們?cè)跁?huì)話2（SID=70)中，我們使用下面SQL查詢(xún)未提交的SQL語(yǔ)句。如下所示：

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      : selectgrantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ whereobj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#-----------------SQL_TEXT      : SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWSIGNORE_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# isnot null group by privilege#, col#, grantee# order by col#, grantee#-----------------SQL_TEXT      : selecttype#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr,NVL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where ts#=:1 andfile#=:2 and block#=:3-----------------PL/SQL procedure successfully completed.

如上所示，這個(gè)SQL我們會(huì)查出很多不相關(guān)的SQL語(yǔ)句，接下來(lái)我們可以用下面的SQL查詢(xún)（改用SQL Developer展示，因?yàn)镾QL*Plus，不方便展示），如下所示，這個(gè)SQL倒不會(huì)查出不相關(guān)的SQL。但是這個(gè)SQL能勝任任何場(chǎng)景嗎？答案是否定的。

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里第一次執(zhí)行一條SQL語(yǔ)句后，該SQL語(yǔ)句會(huì)被硬解析，而且執(zhí)行計(jì)劃和解析樹(shù)會(huì)被緩存到Shared Pool里。方便以后再次執(zhí)行這條SQL語(yǔ)句時(shí)不需要再做硬解析。但是Shared Pool的大小也是有限制的，不可能無(wú)限制的緩存所有SQL的執(zhí)行計(jì)劃，它使用LRU算法管理庫(kù)高速緩存區(qū)。所以有可能你要找的SQL語(yǔ)句已經(jīng)不在Shared Pool里面了，它從Shared Pool被移除出去了。如下所示，我們使用sys.dbms_shared_pool.purge人為構(gòu)造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>

此時(shí)我們查詢(xún)到的SQL語(yǔ)句，是一個(gè)不相關(guān)的SQL或者其值為Null。

接下來(lái)我們回滾SQL語(yǔ)句，然后繼續(xù)新的實(shí)驗(yàn)測(cè)試，如下所示，在會(huì)話1（SID=63)里面執(zhí)行了兩個(gè)DML操作語(yǔ)句，都未提交事務(wù)。

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>

接下來(lái)，我們使用SQL語(yǔ)句去查找未提交的SQL，發(fā)現(xiàn)只能捕獲最開(kāi)始執(zhí)行的DELETE語(yǔ)句，不能捕獲到后面執(zhí)行的UPDATE語(yǔ)句。這個(gè)實(shí)驗(yàn)也從側(cè)面印證了，我們不一定能準(zhǔn)確的找出未提交事務(wù)的SQL語(yǔ)句。