闪回事务查询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 ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL 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 AUTOundo_retention integer 9000undo_tablespace string UNDOTBS1SQL> 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 createdSQL> select * from test;EMPNO SAL----- --------- 7369 800.00 7499 1600.00 7521 1250.00（篇幅原因，有省略……） 7934 1300.0014 rows selected2、操作实验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 updatedSQL> 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 updatedSQL> commit;Commit completeSQL> 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.000900130035060000 1939857 1940037 U 7369 100.00 1939857 7369 800.00 7499 1600.00最后确定一下数据行和事务关系。SQL> delete test;14 rows deletedSQL> select xid from v$transaction;XID----------------060016002F060000 –事务XIDSQL> 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 selected3、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 selectedExecuted 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 selectedExecuted 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";ExplainedExecuted in 0.172 secondsSQL> 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 selectedExecuted 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"）;ExplainedExecuted in 0 secondsSQL> 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 selectedExecuted in 0.093 seconds执行计划中FIXED TABLE FIXED INDEX，显然是数据表固定索引路径，性能速度快也就可想而知了。对于一些事务量比较大，flashback transaction记录比较多的情况，出于性能考量需要对字符串进行处理。4、结论Oracle Flashback Transaction Query是我们在事务粒度级别进行逻辑恢复的手段。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址