繼續聊聊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

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

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

| 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

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

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

| 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是我們在事務粒度級別進行邏輯恢復的手段。

聊聊閃回事務查詢Flashback Transaction Query

相關文章