ITPub博客

首页 > 数据库 > Oracle > SYS_FBA_为前缀表如何服务于Flashback Data Archive

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

原创 Oracle 作者:oliseh 时间:2015-05-17 16:05:12 0 删除 编辑
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表有时间再研究。。。

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

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1616855