聊聊闪回事务查询Flashback Transaction Query

 

继续聊聊Flashback家庭成员。Flashback Version Query、Flashback Query和本次介绍的Flashback Transaction Query相同，都是依赖于Undo表空间的过期数据。和Version Query和Query不同的是，Flashback Transaction Query将数据变化的粒度细化到了事务级别，而且支持用户进行Undo操作，准备好相关的SQL语句。

 

1、实验环境

 

笔者使用Oracle 11g进行实验，具体实验版本是11.2.0.4。

 

 

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

 

 

使用Flashback Transaction有两个条件，一个是使用自动Automatic Undo Management，另一个不是必须，但是建议设置的是添加补充日志Supplemental Redo Log。

 

 

SQL> show parameter undo;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     9000

undo_tablespace                      string      UNDOTBS1

 

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

 

SUPPLEMENTAL_LOG_DATA_MIN

-------------------------

YES

 

 

默认的Undo_retention大小为900秒，为了实验方便设置为9000秒。

 

数据环境构建，创建简单数据表。

 

 

SQL> create table test as select empno, sal from scott.emp;

 

Table created

 

SQL> select * from test;

 

EMPNO       SAL

----- ---------

 7369    800.00

 7499   1600.00

 7521   1250.00

（篇幅原因，有省略……）

 7934   1300.00

 

14 rows selected

 

 

2、操作实验

 

Flashback Transaction Query的核心，就是将日志以事务+数据行的修改粒度在flashback_transaction_query中查询到。

 

Flashback_Transaction_Query视图是Oracle提供给用户进行操作日志查询的接口。在其中，可以看到对应一个数据表、数据行和事务进行的所有数据操作。

 

 

SQL> desc flashback_transaction_query;

Name             Type           Nullable Default Comments                                  

---------------- -------------- -------- ------- -----------------------------------------

XID              RAW(8)         Y                Transaction identifier                   

START_SCN        NUMBER         Y                Transaction start SCN                    

START_TIMESTAMP  DATE           Y                Transaction start timestamp              

COMMIT_SCN       NUMBER         Y                Transaction commit SCN                   

COMMIT_TIMESTAMP DATE           Y                Transaction commit timestamp             

LOGON_USER       VARCHAR2(30)   Y                Logon user for transaction               

UNDO_CHANGE#     NUMBER         Y                1-based undo change number               

OPERATION        VARCHAR2(32)   Y                forward operation for this undo          

TABLE_NAME       VARCHAR2(256)  Y                table name to which this undo applies    

TABLE_OWNER      VARCHAR2(32)   Y                owner of table to which this undo applies

ROW_ID           VARCHAR2(19)   Y                rowid to which this undo applies         

UNDO_SQL         VARCHAR2(4000) Y                SQL corresponding to this undo           

 

 

下面进行简单的修改。

 

 

SQL> update test set sal=100 where empno=7369;

1 row updated

 

SQL> commit;

Commit complete

 

 

根据owner和table_name，可以找到数据记录。

 

 

SQL> select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where table_owner='SYS' and table_name='TEST';

 

XID               START_SCN COMMIT_SCN ROW_ID              OPERATION  UNDO_SQL

---------------- ---------- ---------- ------------------- ------------------------------------------------------------------------------------

0900130035060000    1939850    1939857 AAAV4EAABAAARfpAAA  UPDATE     update "SYS"."TEST" set "SAL" = '800' where ROWID = 'AAAV4EAABAAARfpAAA';

 

 

在其中，可以看到对数据表test进行的操作事务信息，修改数据行rowid。最重要有意思的是Oracle还将逆转事务操作使用的SQL语句。

 

Undo_SQL的存在，就给用户提供一种手工逻辑恢复数据的能力。注意：如果supplemental log data不开启，这个数据是不会显示的。

 

下面借助flashback version query，检查一下刚刚修改。

 

SQL> select versions_xid xid,versions_startscn, versions_endscn, versions_operation, test.* from test versions between scn minvalue and maxvalue;

 

XID              VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_OPERATION EMPNO       SAL

---------------- ----------------- --------------- ------------------ ----- ---------

0900130035060000           1939857                 U                   7369    100.00

                                           1939857                     7369    800.00

                                                                       7499   1600.00

                                                                       7521   1250.00

                                                                      

（篇幅所限，有删减…..）

 

15 rows selected

 

 

提供的undo_sql，是可以直接执行的。

 

 

SQL> update "SYS"."TEST" set "SAL" = '800' where ROWID = 'AAAV4EAABAAARfpAAA';

 

1 row updated

 

SQL> commit;

 

Commit complete

 

SQL> select versions_xid xid,versions_startscn, versions_endscn, versions_operation, test.* from test versions between scn minvalue and maxvalue;

 

XID              VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_OPERATION EMPNO       SAL

---------------- ----------------- --------------- ------------------ ----- ---------

07000500D6050000           1940037                 U                   7369    800.00

0900130035060000           1939857         1940037 U                   7369    100.00

                                           1939857                     7369    800.00

                                                                       7499   1600.00

 

 

 

最后确定一下数据行和事务关系。

 

 

 

SQL> delete test;

14 rows deleted

 

SQL> select xid from v$transaction;

XID

----------------

060016002F060000 –事务XID

 

SQL> commit;

Commit complete

 

 

每条对应数据行，都存在与flashback_transaction_query中。

 

 

SQL> select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where table_owner='SYS' and table_name='TEST';

 

XID               START_SCN COMMIT_SCN ROW_ID              OPERATION                        UNDO_SQL

---------------- ---------- ---------- ------------------- -------------------------------- --------------------------------------------------------------------------------

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAN  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7934','1300');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAM  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7902','3000');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAL  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7900','950');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAK  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7876','1100');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAJ  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7844','1500');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAI  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7839','5000');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAH  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7788','3000');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAG  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7782','2450');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAF  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7698','2850');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAE  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7654','1250');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAD  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7566','2975');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAC  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7521','1250');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAB  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7499','1600');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAA  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7369','800');

07000500D6050000    1940035    1940037 AAAV4EAABAAARfpAAA  UPDATE                           update "SYS"."TEST" set "SAL" = '100' where ROWID = 'AAAV4EAABAAARfpAAA';

0900130035060000    1939850    1939857 AAAV4EAABAAARfpAAA  UPDATE                           update "SYS"."TEST" set "SAL" = '800' where ROWID = 'AAAV4EAABAAARfpAAA';

 

16 rows selected

 

 

3、xid检索

 

最后我们聊聊查询flashback_transaction_query视图使用XID事务唯一标记特点。视图中xid类型是一个RAW类型，表现出来通常是一个字符串。

 

在实际中，我们常常发现使用字符串标记进行检索的时候速度比较慢。

 

 

SQL> select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where xid='060016002F060000';

 

XID               START_SCN COMMIT_SCN ROW_ID              OPERATION                        UNDO_SQL

---------------- ---------- ---------- ------------------- -------------------------------- --------------------------------------------------------------------------------

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAN  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7934','1300');

（篇幅原因，有省略……）

15 rows selected

 

Executed in 10.686 seconds

 

 

在官方推荐的查询方式中，建议使用hextoraw函数对字符串进行处理一下。

 

 

SQL> select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where xid=hextoraw('060016002F060000');

 

XID               START_SCN COMMIT_SCN ROW_ID              OPERATION                        UNDO_SQL

---------------- ---------- ---------- ------------------- -------------------------------- --------------------------------------------------------------------------------

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAN  DELETE                           insert into "SYS"."TEST"("EMPNO","SAL") values ('7934','1300');

060016002F060000    1940047    1940079 AAAV4EAABAAARfpAAM  DELETE                          

（篇幅原因，有省略……）

 

15 rows selected

 

Executed in 0.094 seconds

 

 

从10s到0.09s，这就是巨大的性能差异。我们可以从执行计划角度分析一下原因。

 

 

SQL> explain plan for select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where xid='060016002F060000';

 

Explained

 

Executed in 0.172 seconds

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1115820779

------------------------------------------------------------------------------

| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |           |     1 |  2063 |     0   (0)| 00:00:01 |

|*  1 |  FIXED TABLE FULL| X$KTUQQRY |     1 |  2063 |     0   (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(RAWTOHEX("XID")='060016002F060000')

 

13 rows selected

 

Executed in 0.67 seconds

 

 

基础表x$ktuqqry显然是保存UNDO Transaction Log中基础数据的地方，如果使用字符串类型，发现Oracle会自动进行rawtohex操作，对列函数操作如果没有函数索引的话通常是直接进行全表扫描。

 

从执行计划上，FIXED TABLE FULL显然也就是执行基础表全表扫描过程。

 

 

如果我们对字符串进行处理一下呢？

 

 

SQL> explain plan for select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where xid=hextoraw('060016002F060000');

 

Explained

 

Executed in 0 seconds

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1747778896

--------------------------------------------------------------------------------

| Id  | Operation               | Name              | Rows  | Bytes | Cost (%CPU

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |                   |     1 |  2063 |     0   (0

|*  1 |  FIXED TABLE FIXED INDEX| X$KTUQQRY (ind:1) |     1 |  2063 |     0   (0

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("XID"=HEXTORAW('060016002F060000') )

 

13 rows selected

 

Executed in 0.093 seconds

 

 

执行计划中FIXED TABLE FIXED INDEX，显然是数据表固定索引路径，性能速度快也就可想而知了。对于一些事务量比较大，flashback transaction记录比较多的情况，出于性能考量需要对字符串进行处理。

 

 

4、结论

 

Oracle Flashback Transaction Query是我们在事务粒度级别进行逻辑恢复的手段。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-1715895/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-1715895/