SYS_FBA_为前缀表如何服务于Flashback Data Archive

undo里的before-image受系统负荷等因素的影响保留时间较短有的时候无法完全满足flashback query、flashback version query等闪回查询较早前数据的功能要求，flashback data archive的引入正是为了解决这个问题，将before image从undo定时归档到archive table。
数据库里如果创建了flashback data archive，那么后台进程FBDA（Flashback Data Archiver Process）就会启动，alert.log也会有下面的输出：
Sun May 17 13:35:18 2015
Starting background process FBDA
Sun May 17 13:35:18 2015
FBDA started with pid=35, OS id=12257378可以在create table的同时启用flashback archive功能，也可以在建完表之后通过Alter table .. flashback archive ...打开flashback archive功能。
当表里的数据块被修改时before image在写入到undo的同时，会在undo block里打上标记，表明这个undo block需要被归档到flashback data archive，这个归档过程就是由FBDA进程完成的，在完成归档之前这个undo block是不能被其他transaction重用的。把undo block归档到flashback data archive的过程是异步进行的，所以对transaction的性能影响可以忽略不计，FBDA每5分钟扫描一次等待被归档的undo block，并将其写入到flashback data archive，随后把该undo block标记为可以重用，如果在系统的修改量较大时扫描的间隔会小于5分钟，具体由Oracle自己控制。在flashback data archive的技术实现过程中，SYS_FBT_为前缀的表起到了不小的作用，通过下面的实验了解一下/////////////Part 1. SYS_FBA_表基本介绍////////////////###数据库已有一个名为FBA0516_1的flashback archive，quota为300M，存放在TS0512_1表空间，FBA0516_1里目前尚未存放任何表的历史数据
SYS@tstdb1-SQL> select * from DBA_FLASHBACK_ARCHIVE;OWNER_NAME FLASHBACK_ FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS
---------- ---------- ------------------ ----------------- ----------------------------------- ----------------------------------- -------
SYS FBA0516_1 1 1 16-MAY-15 11.46.01.000000000 AM 16-MAY-15 11.46.01.000000000 AMSYS@tstdb1-SQL> select * from DBA_FLASHBACK_ARCHIVE_TS;FLASHBACK_ FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
---------- ------------------ ------------------------------ ----------------------------------------
FBA0516_1 1 TS0512_1 300SYS@tstdb1-SQL> select * from DBA_FLASHBACK_ARCHIVE_TABLES;no rows selected###创建测试表
create table t0516_5 （id number,c2 varchar2（3）） flashback archive fba0516_1;col object_name format a20
set linesize 100
select object_name,created,object_id from dba_objects where object_name="T0516_5";
OBJECT_NAME CREATED OBJECT_ID
-------------------- ----------------- ----------
T0516_5 20150516 20:45:54 36945---T0516_5对应的archive table是SYS_FBA_HIST_36937，但我们在dba_tables还没有查到SYS_FBA_HIST_36937
col OWNER_NAME format a10
set numwidth 4
col FLASHBACK_ARCHIVE_NAME format a10
col create_time format a35
col last_purge_time format a35
set linesize 140
select * from DBA_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME OWNER_NAME FLASHBACK_ ARCHIVE_TABLE_NAME STATUS
------------------------------ ---------- ---------- ----------------------------------------------------- -------------
T0516_5 SCOTT FBA0516_1 SYS_FBA_HIST_36945 ENABLEDselect owner,table_name,partitioned from dba_tables where table_name like "%36945";no rows selected根据官方的说法后台进程FBDA会每隔5分钟检测一次是否有新的archive table要创建，这里等待超过了10分钟也未见SYS_FBA_HIST_36937表创建出来，下面进行一些DML操作后再观察
---插入若干数据
insert into t0516_5 values（1,"AAA"）;
insert into t0516_5 values（2,"BBB"）;
insert into t0516_5 values（3,"CCC"）;
commit;SCOTT@tstdb1-SQL> select sysdate from dual;SYSDATE
-----------------
20150516 20:47:06---没有马上查询到SYS_FBA开头的表，直到20150516 20:50:19，SYS_FBA才被创建出来，与上次的insert操作的时间相隔<5分钟
SCOTT@tstdb1-SQL> select owner,table_name,partitioned from dba_tables where table_name like "%36945";no rows selected。。。。等待片刻SCOTT@tstdb1-SQL> select owner,table_name,partitioned from dba_tables where table_name like "%36945";OWNER TABLE_NAME PAR
------------------------------ ------------------------------ ---
SCOTT SYS_FBA_HIST_36945 YES
SYS SYS_MFBA_NHIST_36945 NO
SCOTT SYS_FBA_TCRV_36945 NO
SCOTT SYS_FBA_DDL_COLMAP_36945 NOSCOTT@tstdb1-SQL> col object_name format a30
SCOTT@tstdb1-SQL> col owner format a10
SCOTT@tstdb1-SQL> set linesize 150
SCOTT@tstdb1-SQL> select owner,object_name,subobject_name,created from dba_objects where object_name in （"SYS_FBA_HIST_36945","SYS_MFBA_NHIST_36945","SYS_FBA_TCRV_36945","SYS_FBA_DDL_COLMAP_36945"）;OWNER OBJECT_NAME SUBOBJECT_NAME CREATED
---------- ------------------------------ ------------------------------ -----------------
SYS SYS_MFBA_NHIST_36945 20150516 20:50:19
SCOTT SYS_FBA_HIST_36945 HIGH_PART 20150516 20:50:19
SCOTT SYS_FBA_DDL_COLMAP_36945 20150516 20:50:19
SCOTT SYS_FBA_HIST_36945 20150516 20:50:19
SCOTT SYS_FBA_TCRV_36945 20150516 20:50:19---仅SYS_FBA_DDL_COLMAP_36945、SYS_FBA_TCRV_36945有记录
SCOTT@tstdb1-SQL> select count（*） from sys.SYS_MFBA_NHIST_36945;COUNT（*）
--------
0SCOTT@tstdb1-SQL> select count（*） from SYS_FBA_HIST_36945;COUNT（*）
--------
0SCOTT@tstdb1-SQL> select count（*） from SYS_FBA_DDL_COLMAP_36945;COUNT（*）
--------
2SCOTT@tstdb1-SQL> select count（*） from SYS_FBA_TCRV_36945;COUNT（*）
--------
3---SYS_FBA_DDL_COLMAP_36945表
SCOTT@tstdb1-SQL> col column_name format a20
SCOTT@tstdb1-SQL> col type format a20
SCOTT@tstdb1-SQL> col HISTORICAL_COLUMN_NAME format a20
SCOTT@tstdb1-SQL> set linesize 120
SCOTT@tstdb1-SQL> set numwidth 16
SCOTT@tstdb1-SQL> select * from SYS_FBA_DDL_COLMAP_36945; STARTSCN ENDSCN XID O COLUMN_NAME TYPE HISTORICAL_COLUMN_NA
---------------- ---------------- ---------------- - -------------------- -------------------- --------------------
12723378739636 ID NUMBER ID
12723378739636 C2 VARCHAR2（3） C2SCOTT@tstdb1-SQL> col object_name format a30
SCOTT@tstdb1-SQL> col owner format a10
SCOTT@tstdb1-SQL> set linesize 150
SCOTT@tstdb1-SQL> select owner,object_name,subobject_name,timestamp_to_scn（created） from dba_objects where object_name in （"T0516_5"）;
OWNER OBJECT_NAME SUBOBJECT_NAME TIMESTAMP_TO_SCN（CREATED）
---------- ------------------------------ ------------------------------ --------------------------
SCOTT T0516_5 12723378739636SYS_FBA_DDL_COLMAP_36945保存了源表和archive table列名的映射关系，startscn等于源表创建时刻的scn***修改源表的列名，测试一下SYS_FBA_DDL_COLMAP_36945保存的列名映射关系是否会跟着变，
SCOTT@tstdb1-SQL> alter table T0516_5 rename column c2 to c3;Table altered.SCOTT@tstdb1-SQL> select * from SYS_FBA_DDL_COLMAP_36945; STARTSCN ENDSCN XID O COLUMN_NAME TYPE HISTORICAL_COLUMN_NA
---------------- ---------------- ---------------- - -------------------- -------------------- --------------------
12723378739636 ID NUMBER ID
12723378739636 12723378742951 C3 VARCHAR2（3） C2
12723378742951 C3 VARCHAR2（3） C3结果表明在scn：12723378739636~12723378742951范围内源表的C3字段对应archive table的C2字段，从Scn：12723378742951开始源表的C3字段对应archive table的C3字段---SYS_FBA_TCRV_36945表
col rid format a20
set linesize 130
select * from SYS_FBA_TCRV_36945;
RID STARTSCN ENDSCN XID O
-------------------- ---------------- ---------------- ---------------- -
AAAJBRAAEAAAWjJAAA 12723378739723 000A000500015C8E I
AAAJBRAAEAAAWjJAAB 12723378739723 000A000500015C8E I
AAAJBRAAEAAAWjJAAC 12723378739723 000A000500015C8E ISCOTT@tstdb1-SQL> select ora_rowscn from T0516_5; ORA_ROWSCN
----------------
12723378739723
12723378739723
12723378739723SYS@tstdb1-SQL> select xid,row_id,operation,undo_sql from flashback_transaction_query where xid=hextoraw（"000A000500015C8E"）;XID ROW_ID OPERATION UNDO_SQL
---------------- ------------------- ---------- ----------------------------------------------------------------------
000A000500015C8E AAAJBRAAEAAAWjJAAC INSERT delete from "SCOTT"."T0516_5" where ROWID = "AAAJBRAAEAAAWjJAAC";
000A000500015C8E AAAJBRAAEAAAWjJAAB INSERT delete from "SCOTT"."T0516_5" where ROWID = "AAAJBRAAEAAAWjJAAB";
000A000500015C8E AAAJBRAAEAAAWjJAAA INSERT delete from "SCOTT"."T0516_5" where ROWID = "AAAJBRAAEAAAWjJAAA";
000A000500015C8E BEGIN结合flashback_transaction_query，发现SYS_FBA_TCRV_36945记录了执行insert语句的transaction_id，行的rowid、以及插入的时间

---update一条记录
SCOTT@tstdb1-SQL> select * from t0516_5; ID C3
---------------- ---
1 AAA
2 BBB
3 CCC

update t0516_5 set c3="DDD" where id=3;
commit;---再delete一条记录
delete t0516_5 where id=2;
commit;SCOTT@tstdb1-SQL> select * from t0516_5; ID C3
---------------- ---
1 AAA
3 DDD

---继续跟踪SYS_FBA_表的变化情况，最多等待5分钟能观察到下列表中的记录变化情况
SCOTT@tstdb1-SQL> select * from sys.SYS_MFBA_NHIST_36945;no rows selected***SYS_FBA_HIST_36945保存的是before-image，scn: 12723378739723~12723378743689范围内表里存在c3="CCC"的记录，scn: 12723378739723~12723378743708范围内表里存在C3="BBB"的记录，scn：12723378743708时刻C3="BBB"的记录被XID=000A001A00015D0B的Transaction delete掉，这些记录现在都已经不在表中了
SCOTT@tstdb1-SQL> select * from SYS_FBA_HIST_36945;RID STARTSCN ENDSCN XID O ID C3
-------------------- ---------------- ---------------- ---------------- - ---------------- ---
AAAJBRAAEAAAWjJAAB 12723378743708 12723378743708 000A001A00015D0B D 2 BBB
AAAJBRAAEAAAWjJAAB 12723378739723 12723378743708 000A000500015C8E I 2 BBB
AAAJBRAAEAAAWjJAAC 12723378739723 12723378743689 000A000500015C8E I 3 CCC***映射关系维持不变
SCOTT@tstdb1-SQL> select * from SYS_FBA_DDL_COLMAP_36945; STARTSCN ENDSCN XID O COLUMN_NAME TYPE HISTORICAL_COLUMN_NA
---------------- ---------------- ---------------- - -------------------- -------------------- --------------------
12723378739636 ID NUMBER ID
12723378739636 12723378742951 C3 VARCHAR2（3） C2
12723378742951 C3 VARCHAR2（3） C3***SYS_FBA_TCRV_36945与flashback version query的结果及其相似，记录了源表的操作历史，结合SYS_FBA_HIST_36945能够准确的找到过去某个scn下的before image
SCOTT@tstdb1-SQL> select * from SYS_FBA_TCRV_36945;RID STARTSCN ENDSCN XID O
-------------------- ---------------- ---------------- ---------------- -
AAAJBRAAEAAAWjJAAA 12723378739723 000A000500015C8E I
AAAJBRAAEAAAWjJAAB 12723378739723 12723378743708 000A000500015C8E I
AAAJBRAAEAAAWjJAAC 12723378739723 12723378743689 000A000500015C8E I
AAAJBRAAEAAAWjJAAC 12723378743689 000A000D00015C87 U执行select * from t0516_5 as of scn 12723378743688语句时寻找过程大致如下：rowid="AAAJBRAAEAAAWjJAAA"的行12723378743688>=startscn，endscn为空，表示这行从表里取现值id=1、C3="AAA"，无需访问archive table；
rowid="AAAJBRAAEAAAWjJAAB"的行endscn>12723378743688>=startscn，表示这行在scn:12723378743688时刻不在表里，需要访问archive table（SYS_FBA_HIST_36945）里rowid="AAAJBRAAEAAAWjJAAB" and XID=000A000500015C8E对应行获取before-image:id=2、C3="BBB"
rowid="AAAJBRAAEAAAWjJAAC"有两行，根据scn：12723378743688对应到startscn=12723378739723 and endscn=12723378743689这一行，然后去SYS_FBA_HIST_36945找到rowid="AAAJBRAAEAAAWjJAAC" and xid="000A000500015C8E"返回before-image:id=3、C3="CCC"SCOTT@tstdb1-SQL> select * from t0516_5 as of scn 12723378743688; ID C3
---------- ---
2 BBB
3 CCC
1 AAA/////////////Part 2. SYS_FBA_表结构说明////////////////
select owner,table_name,partitioned from dba_tables where table_name like "%36945";OWNER TABLE_NAME PAR
------------------------------ ------------------------------ ---
SCOTT SYS_FBA_HIST_36945 YES
SYS SYS_MFBA_NHIST_36945 NO
SCOTT SYS_FBA_TCRV_36945 NO
SCOTT SYS_FBA_DDL_COLMAP_36945 NOSYS_FBA_HIST_121239表是分区表，before-image保留在这张表里，为何提高访问性能oracle把它建成了分区表，初始只有一个分区---SYS_FBA_HIST_36945采用的是range分区，endscn作为partition key
set linesize 100
select owner,table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE from dba_part_tables where table_name="SYS_FBA_HIST_36945";
OWNER TABLE_NAME PARTITION SUBPARTIT
------------------------------ ------------------------------ --------- ---------
SCOTT SYS_FBA_HIST_36945 RANGE NONESCOTT@tstdb1-SQL> col column_name format a20
SCOTT@tstdb1-SQL> select name,column_name from dba_part_key_columns where name="SYS_FBA_HIST_36945";NAME COLUMN_NAME
------------------------------ --------------------
SYS_FBA_HIST_36945 ENDSCN---存放历史数据的分区启用了compress for oltp方式的压缩
set long 2000 linesize 150
col TABLE_OWNER format a20
col TABLE_NAME format a25
col partition_name format a15
col high_value format a40
select TABLE_OWNER,TABLE_name,PARTITION_NAME,compression,compress_for,COMPOSITE,HIGH_VALUE from dba_tab_partitions where table_name="SYS_FBA_HIST_36945";
TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR COM HIGH_VALUE
-------------------- ------------------------- --------------- -------- ------------ --- ----------------------------------------
SCOTT SYS_FBA_HIST_36945 HIGH_PART ENABLED OLTP NO MAXVALUE注：测试环境是11.2.0.3，如果是11.2.0.4及以后版本，因为引入了optimize data的功能，默认情况下创建的archive table是不压缩的，除非在create flashback archive时指定了optimize data，在11.2.0.4及以后可以在创建flashback archive时加入"optimize data"选项：create flashback archive fba0516_2 tablespace tbs0516_1 optimize data retention 1 day;---SYS_MFBA_NHIST_36945、SYS_FBA_TCRV_36945表在RID字段上建有索引，数据量大的时候提高基于rowid的检索效率
SCOTT@tstdb1-SQL> select index_name,column_name from dba_ind_columns where table_name="SYS_FBA_HIST_36945";no rows selectedSCOTT@tstdb1-SQL> select index_name,column_name from dba_ind_columns where table_name="SYS_MFBA_NHIST_36945";INDEX_NAME COLUMN_NAME
------------------------------ --------------------
SYS_MFBA_NHIST_36945_IDX RIDSCOTT@tstdb1-SQL> select index_name,column_name from dba_ind_columns where table_name="SYS_FBA_TCRV_36945";INDEX_NAME COLUMN_NAME
------------------------------ --------------------
SYS_FBA_TCRV_IDX_36945 RIDSCOTT@tstdb1-SQL> select index_name,column_name from dba_ind_columns where table_name="SYS_FBA_DDL_COLMAP_36945";no rows selected/////////////Part 3. SYS_FBA_表是如何被使用的/////////////
1、如果SYS_FBA_已经由FBDA进程创建，在flashback query的时候就会去访问SYS_FBA系列表，从执行计划中很容易看出来
explain plan for select * from t0516_5 as of scn 12723378743688;SQL> set pagesize 100 linesize 150
SQL> select * from table（dbms_xplan.display）;PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2508115242---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 48 | 21 （10）| 00:00:01 | | |
| 1 | VIEW | | 3 | 48 | 21 （10）| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION RANGE SINGLE| | 2 | 54 | 14 （0）| 00:00:01 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | SYS_FBA_HIST_36945 | 2 | 54 | 14 （0）| 00:00:01 | 1 | 1 |
|* 5 | FILTER | | | | | | | |
| 6 | MERGE JOIN OUTER | | 1 | 40 | 7 （29）| 00:00:01 | | |
| 7 | SORT JOIN | | 1 | 7 | 3 （34）| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | T0516_5 | 1 | 7 | 2 （0）| 00:00:01 | | |
|* 9 | SORT JOIN | | 2 | 66 | 4 （25）| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | SYS_FBA_TCRV_36945 | 2 | 66 | 3 （0）| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 4 - filter（（"OPERATION"<>"D" OR "OPERATION" IS NULL） AND （"STARTSCN"<=12723378743688 OR "STARTSCN"
IS NULL） AND "ENDSCN">12723378743688 AND "ENDSCN"<=12723378801092）
5 - filter（"STARTSCN"<=12723378743688 OR "STARTSCN" IS NULL）
8 - filter（"T"."VERSIONS_STARTSCN" IS NULL）
9 - access（"RID"（+）=ROWIDTOCHAR（"T".ROWID））
filter（"RID"（+）=ROWIDTOCHAR（"T".ROWID））
10 - filter（（"ENDSCN"（+） IS NULL OR "ENDSCN"（+）>12723378801092） AND （"STARTSCN"（+）<12723378801092 OR
"STARTSCN"（+） IS NULL））29 rows selected.2、如果flashback archive被purge了，那么flashback query还是会通过SYS_FBT系列表访问before-image
SQL> explain plan for select * from t0516_7 as of scn 12723393908514;Explained.SQL> set pagesize 100 linesize 150
SQL> select * from table（dbms_xplan.display）;PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4190489988----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 80 | 21 （10）| 00:00:01 | | |
| 1 | VIEW | | 5 | 80 | 21 （10）| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION RANGE SINGLE| | 1 | 44 | 14 （0）| 00:00:01 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | SYS_FBA_HIST_549255 | 1 | 44 | 14 （0）| 00:00:01 | 1 | 1 |
|* 5 | FILTER | | | | | | | |
| 6 | MERGE JOIN OUTER | | 4 | 8224 | 7 （29）| 00:00:01 | | |
| 7 | SORT JOIN | | 4 | 112 | 3 （34）| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | T0516_7 | 4 | 112 | 2 （0）| 00:00:01 | | |
|* 9 | SORT JOIN | | 2 | 4056 | 4 （25）| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | SYS_FBA_TCRV_549255 | 2 | 4056 | 3 （0）| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 4 - filter（"ENDSCN">12723393908514 AND "ENDSCN"<=12723394060501 AND （"STARTSCN" IS NULL OR
"STARTSCN"<=12723393908514） AND （"OPERATION" IS NULL OR "OPERATION"<>"D"））
5 - filter（"STARTSCN"<=12723393908514 OR "STARTSCN" IS NULL）
8 - filter（"T"."VERSIONS_STARTSCN" IS NULL）
9 - access（"RID"（+）=ROWIDTOCHAR（"T".ROWID））
filter（"RID"（+）=ROWIDTOCHAR（"T".ROWID））
10 - filter（（"ENDSCN"（+） IS NULL OR "ENDSCN"（+）>12723394060501） AND （"STARTSCN"（+） IS NULL OR
"STARTSCN"（+）<12723394060501））

col OWNER_NAME format a10
set numwidth 4
col FLASHBACK_ARCHIVE_NAME format a10
col create_time format a35
col last_purge_time format a35
set linesize 140
select * from dba_flashback_archive_tables where table_name="T0516_7";
TABLE_NAME OWNER_NAME FLASHBACK_ ARCHIVE_TABLE_NAME STATUS
------------------------------ ---------- ---------- ----------------------------------------------------- -------------
T0516_7 SCOTT FBA0513 SYS_FBA_HIST_549255 ENABLED--清空flashback archive
SYS@tstdb1-SQL> alter flashback archive FBA0513 purge all;Flashback archive altered.SQL> select count（*） from SYS_FBA_HIST_549255; COUNT（*）
----------
0--再次查看执行计划SYS_FBA还在列，期间尝试过程flush shared_pool，修改undo_tablespace和重启instance，结果还是如此，这就有点不解了，本来认为Flashback archive被清空后flashback query应该去读取undo的
SQL> explain plan for select * from t0516_7 as of scn 12723393908514;Explained.SQL> set pagesize 100 linesize 150
SQL> select * from table（dbms_xplan.display）;PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4190489988----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 80 | 9 （23）| 00:00:01 | | |
| 1 | VIEW | | 5 | 80 | 9 （23）| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION RANGE SINGLE| | 1 | 44 | 2 （0）| 00:00:01 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | SYS_FBA_HIST_549255 | 1 | 44 | 2 （0）| 00:00:01 | 1 | 1 |
|* 5 | FILTER | | | | | | | |
| 6 | MERGE JOIN OUTER | | 4 | 8224 | 7 （29）| 00:00:01 | | |
| 7 | SORT JOIN | | 4 | 112 | 3 （34）| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | T0516_7 | 4 | 112 | 2 （0）| 00:00:01 | | |
|* 9 | SORT JOIN | | 2 | 4056 | 4 （25）| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | SYS_FBA_TCRV_549255 | 2 | 4056 | 3 （0）| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 4 - filter（"ENDSCN">12723393908514 AND "ENDSCN"<=12723394613470 AND （"STARTSCN" IS NULL OR
"STARTSCN"<=12723393908514） AND （"OPERATION" IS NULL OR "OPERATION"<>"D"））
5 - filter（"STARTSCN"<=12723393908514 OR "STARTSCN" IS NULL）
8 - filter（"T"."VERSIONS_STARTSCN" IS NULL）
9 - access（"RID"（+）=ROWIDTOCHAR（"T".ROWID））
filter（"RID"（+）=ROWIDTOCHAR（"T".ROWID））
10 - filter（（"ENDSCN"（+） IS NULL OR "ENDSCN"（+）>12723394613470） AND （"STARTSCN"（+） IS NULL OR
"STARTSCN"（+）<12723394613470））3、如果SYS_FBA_还没有被创建，在flashback query的时候会到undo里获取（假设undo retention足够大，undo segment未被循环利用）
alter table t0517_1 no flashback archive;drop table t0517_1;create table t0517_1 （id number） tablespace TS0422_1 flashback archive fba0517_1;insert into t0517_1 values（1）;
insert into t0517_1 values（2）;
insert into t0517_1 values（3）;
commit;select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
12723378820886delete from t0517_1 where id>=2;
commit;update t0517_1 set id=11 where id=1;
commit;col object_name format a20
set linesize 100
select object_name,created,object_id from dba_objects where object_name="T0517_1";
OBJECT_NAME CREATED OBJECT_ID
-------------------- ----------------- ----------------
T0517_1 20150517 08:39:46 37584select owner,table_name,partitioned from dba_tables where table_name like "%37584";no rows selectedexplain plan for select * from t0517_1 as of scn 12723378820886;SYS@tstdb1-SQL> set pagesize 100 linesize 150
SYS@tstdb1-SQL> select * from table（dbms_xplan.display）;PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1027524507-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 1066 | 2 （0）| 00:00:01 |
| 1 | TABLE ACCESS FULL| T0517_1 | 82 | 1066 | 2 （0）| 00:00:01 |
-----------------------------------------------------------------------------//////////////// Part 3. SYS_FBA表的性能优化 /////////////////
能够对SYS_FBA表进行的操作十分有限，除了select之外，就只有create index和收集统计信息，就连导入导出都受到限制（只能用exp/imp，不能用expdp/impdp）
alter table scott.t0517_2 no flashback archive;drop table scott.t0517_2;create table t0517_2 tablespace TS0512_1 flashback archive fba0516_1 as select * from dba_objects where 1=2;
insert into t0517_2 select * from dba_objects where object_id is not null;
commit;create unique index ind_t0517_2 on t0517_2（object_id） tablespace TS0512_1;exec dbms_stats.gather_table_stats（ownname=>"SCOTT",tabname=>"T0517_2",cascade=>TRUE）;explain plan for select * from t0517_2 where object_id=100;set pagesize 100 linesize 150
select * from table（dbms_xplan.display）;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1917533861-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 2 （0）| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T0517_2 | 1 | 91 | 2 （0）| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IND_T0517_2 | 1 | | 1 （0）| 00:00:01 |
-------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 2 - access（"OBJECT_ID"=100）select count（*） from t0517_2;
COUNT（*）
----------------
20176

set numwidth 16
select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
12723380596675delete t0517_2;
commit;col object_name format a20
set linesize 100
SCOTT@tstdb1-SQL> select object_name,created,object_id from dba_objects where object_name="T0517_2";OBJECT_NAME CREATED OBJECT_ID
-------------------- ----------------- ----------------
T0517_2 20150517 11:52:32 95824SCOTT@tstdb1-SQL> select owner,table_name,partitioned from dba_tables where table_name like "%95824";OWNER TABLE_NAME PAR
------------------------------ ------------------------------ ---
SYS SYS_MFBA_NHIST_95824 NO
SCOTT SYS_FBA_HIST_95824 YES
SCOTT SYS_FBA_TCRV_95824 NO
SCOTT SYS_FBA_DDL_COLMAP_95824 NOexplain plan for select * from t0517_2 as of scn 12723380596675 where object_id=100;
set pagesize 100 linesize 150
select * from table（dbms_xplan.display）;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 153423369-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 414 | 162 （5）| 00:00:02 | | |
| 1 | VIEW | | 2 | 414 | 162 （5）| 00:00:02 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION RANGE SINGLE | | 1 | 235 | 89 （6）| 00:00:02 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | SYS_FBA_HIST_95824 | 1 | 235 | 89 （6）| 00:00:02 | 1 | 1 |
|* 5 | FILTER | | | | | | | |
| 6 | MERGE JOIN OUTER | | 1 | 2119 | 73 （5）| 00:00:01 | | |
| 7 | SORT JOIN | | 1 | 91 | 3 （34）| 00:00:01 | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| T0517_2 | 1 | 91 | 2 （0）| 00:00:01 | | |
|* 9 | INDEX UNIQUE SCAN | IND_T0517_2 | 1 | | 1 （0）| 00:00:01 | | |
|* 10 | SORT JOIN | | 3 | 6084 | 70 （3）| 00:00:01 | | |
|* 11 | TABLE ACCESS FULL | SYS_FBA_TCRV_95824 | 3 | 6084 | 69 （2）| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 4 - filter（"OBJECT_ID"=100 AND "ENDSCN">12723380596675 AND "ENDSCN"<=12723380675473 AND （"STARTSCN" IS NULL
OR "STARTSCN"<=12723380596675） AND （"OPERATION" IS NULL OR "OPERATION"<>"D"））
5 - filter（"STARTSCN"<=12723380596675 OR "STARTSCN" IS NULL）
8 - filter（"T"."VERSIONS_STARTSCN" IS NULL）
9 - access（"T"."OBJECT_ID"=100）
10 - access（"RID"（+）=ROWIDTOCHAR（"T".ROWID））
filter（"RID"（+）=ROWIDTOCHAR（"T".ROWID））
11 - filter（（"ENDSCN"（+） IS NULL OR "ENDSCN"（+）>12723380675473） AND （"STARTSCN"（+） IS NULL OR
"STARTSCN"（+）<12723380675473））SYS_FBA_HIST_95824表的访问时FTS，我们可以对SYS_FBA_HIST_95824表在object_id上创建index，并收集统计信息---在archive table上创建索引、收集统计
SQL> create unique index ind_SYS_FBA_HIST_95824 on SYS_FBA_HIST_95824（object_id）; <--unique index也是不被允许的
create unique index ind_SYS_FBA_HIST_68841 on SYS_FBA_HIST_95824（object_id）
*
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "SCOTT"."SYS_FBA_HIST_95824"SQL> create index ind_SYS_FBA_HIST_95824 on SYS_FBA_HIST_95824（object_id）;Index created.exec dbms_stats.gather_table_stats（ownname=>"SCOTT",tabname=>"SYS_FBA_HIST_95824",cascade=>TRUE）;---使用到了索引
explain plan for select * from t0517_2 as of scn 12723380596675 where object_id=100;
set pagesize 100 linesize 150
select * from table（dbms_xplan.display）;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3579223519-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 414 | 75 （4）| 00:00:01 | | |
| 1 | VIEW | | 2 | 414 | 75 （4）| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| SYS_FBA_HIST_95824 | 1 | 132 | 2 （0）| 00:00:01 | 1 | 1 |
|* 4 | INDEX RANGE SCAN | IND_SYS_FBA_HIST_95824 | 2 | | 1 （0）| 00:00:01 | | |
|* 5 | FILTER | | | | | | | |
| 6 | MERGE JOIN OUTER | | 1 | 2119 | 73 （5）| 00:00:01 | | |
| 7 | SORT JOIN | | 1 | 91 | 3 （34）| 00:00:01 | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | T0517_2 | 1 | 91 | 2 （0）| 00:00:01 | | |
|* 9 | INDEX UNIQUE SCAN | IND_T0517_2 | 1 | | 1 （0）| 00:00:01 | | |
|* 10 | SORT JOIN | | 3 | 6084 | 70 （3）| 00:00:01 | | |
|* 11 | TABLE ACCESS FULL | SYS_FBA_TCRV_95824 | 3 | 6084 | 69 （2）| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 3 - filter（（"OPERATION"<>"D" OR "OPERATION" IS NULL） AND （"STARTSCN"<=12723380596675 OR "STARTSCN" IS NULL） AND
"ENDSCN">12723380596675 AND "ENDSCN"<=12723381193707）
4 - access（"OBJECT_ID"=100）
5 - filter（"STARTSCN"<=12723380596675 OR "STARTSCN" IS NULL）
8 - filter（"T"."VERSIONS_STARTSCN" IS NULL）
9 - access（"T"."OBJECT_ID"=100）
10 - access（"RID"（+）=ROWIDTOCHAR（"T".ROWID））
filter（"RID"（+）=ROWIDTOCHAR（"T".ROWID））
11 - filter（（"ENDSCN"（+） IS NULL OR "ENDSCN"（+）>12723381193707） AND （"STARTSCN"（+） IS NULL OR
"STARTSCN"（+）<12723381193707））先到这里，对于SYS_MFBA_NHIST_XX表有时间再研究。。。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址