闪回事务查询Flashback Transaction Query
闪回事务查询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是我们在事务粒度级别进行逻辑恢复的手段。
评论暂时关闭