SYS_FBA_为前缀表如何服务于Flashback Data Archive
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 AM
SYS@tstdb1-SQL> select * from DBA_FLASHBACK_ARCHIVE_TS;
FLASHBACK_ FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
---------- ------------------ ------------------------------ ----------------------------------------
FBA0516_1 1 TS0512_1 300
SYS@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 ENABLED
select 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 NO
SCOTT@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(*)
--------
0
SCOTT@tstdb1-SQL> select count(*) from SYS_FBA_HIST_36945;
COUNT(*)
--------
0
SCOTT@tstdb1-SQL> select count(*) from SYS_FBA_DDL_COLMAP_36945;
COUNT(*)
--------
2
SCOTT@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) C2
SCOTT@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 12723378739636
SYS_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 I
SCOTT@tstdb1-SQL> select ora_rowscn from T0516_5;
ORA_ROWSCN
----------------
12723378739723
12723378739723
12723378739723
SYS@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 NO
SYS_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 NONE
SCOTT@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 selected
SCOTT@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 RID
SCOTT@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 RID
SCOTT@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
------------------------
12723378820886
delete 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 37584
select owner,table_name,partitioned from dba_tables where table_name like '%37584';
no rows selected
explain 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
------------------------
12723380596675
delete 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 95824
SCOTT@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 NO
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: 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表有时间再研究。。。
评论暂时关闭