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

最新推荐文章于 2022-09-23 14:19:40 发布

cuiqiong6888

最新推荐文章于 2022-09-23 14:19:40 发布

阅读量187

点赞数

文章标签：数据库

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);
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表有时间再研究。。。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1659222/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1659222/