Flashback Data Archive（FBDA）新特性是11G新加入Flashback大家族的小弟弟。
在我看来，他是作为了对flashback query的一个补充，
来存储更长更稳定更高效的历史数据。
首先Step by Step来测试一下functional的特性。
--------Functional Part-----------
1.FBDA引入了一个新的权限--flashback archive administer
如果你很不幸没有这个系统权限，会报ORA-55612
SQL> create flashback archive haofbda
2 tablespace FBDATBS
3 quota 20m
retention 10 year; 4
tablespace FBDATBS
*
ERROR at line 2:
ORA-55612: No privilege to manage Flashback Archive
2.用户需要有建立FBDA的tablespace的quota
当grant了权限之后：
SQL> grant flashback archive administer to haozhu_user;
Grant succeeded.
再次建立FBDA，会报另一个错ORA-01950
SQL> /
tablespace FBDATBS
*
ERROR at line 2:
ORA-01950: no privileges on tablespace 'FBDATBS'
3.用户在tablespace上拥有的quota必须大于等于FBDA做标明的quota。
如果只给10m quota给用户，而FBDA注明需要20M quota时，
会报另一种错ORA-55621：
SQL> alter user haozhu_user quota 10m on fbdatbs;
User altered.
SQL> create flashback archive haofbda
2 tablespace fbdatbs
3 quota 20m
retention 10 year; 4
tablespace fbdatbs
*
ERROR at line 2:
ORA-55621: User quota on tablespace "FBDATBS" is not enough for Flashback
Archive
4.FBDA只能放在ASSM的tablespace里。
否则会报第四种错ORA-55627
SQL> create tablespace MSSMTBS datafile '/xxx/xxx/xxx/xxx/mssmtbs_01.dbf' size 50m segment space management manual;
Tablespace created.
SQL> create flashback archive haofbda2
2 tablespace MSSMTBS
3 quota 20m
retention 10 year; 4
tablespace MSSMTBS
*
ERROR at line 2:
ORA-55627: Flashback Archive tablespace must be ASSM tablespace
5.FBDA只能在undo_management=auto时才能建立。
否则会报第五种错ORA-55628
SQL> create flashback archive haofbda
2 tablespace fbdatbs
3 quota 20m
retention 10 year; 4
create flashback archive haofbda
*
ERROR at line 1:
ORA-55628: Flashback Archive supports Oracle 11g or higher
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
SQL> show parameter compati
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.1.0
6.flashback archive administer系统权限包含flashback archive对象权限。
只有拥有flashback archive在某个FBDA上的对象权限，才能使用这个FBDA。
如果不具备flashback archive对象权限，会报第六种错：ORA-55620
SQL> revoke flashback archive administer from haozhu_user;
Revoke succeeded.
SQL> alter table testfbda flashback archive haofbda;
alter table testfbda flashback archive haofbda
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
SQL> grant flashback archive on HAOFBDA to haozhu_user;
Grant succeeded.
SQL> alter table testfbda flashback archive haofbda;
Table altered.
虽然Oracle建议说我们需要区分开拥有这两种权限的用户，
但是对于失去flashback archive administer系统权限的用户，
他只能查询user_flashback_xxx视图，而在dba_相关视图里，却没有任何东西。
SQL> select * from dba_flashback_archive_ts;
no rows selected
SQL> select * from dba_flashback_archive;
no rows selected
SQL> select * from dba_flashback_archive_tables;
no rows selected
7.Default的FBDA需要用sysdba登陆才能建立
否则会报第七种错：ORA-55611
SQL> create flashback archive default haofbda_def
2 tablespace fbdatbs
3 quota 10m
retention 1 month; 4
tablespace fbdatbs
*
ERROR at line 2:
ORA-55611: No privilege to manage default Flashback Archive
SQL> show user
USER is "HAOZHU_USER"
SQL> conn HAOZHU_USER/xxx as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> create flashback archive default haofbda_def
2 tablespace fbdatbs
quota 10m
retention 1 month; 3 4
Flashback archive created.
8.只能有一个default的FBDA
如果试图建立第二个default FBDA，
会报第八种错：ORA-55609
SQL> create flashback archive default haofbda_def2
2 tablespace fbdatbs
3 quota 10m
retention 1 month; 4
tablespace fbdatbs
*
ERROR at line 2:
ORA-55609: Attempt to create duplicate default Flashback Archive
但是可以通过这条set default命令切换default FBDA，而原来的default FBDA失去default特性。
SQL> select FLASHBACK_ARCHIVE_NAME,STATUS from dba_flashback_archive;
FLASHBACK_ARCHIVE_NA STATUS
-------------------- -------
HAOFBDA
HAOFBDA_DEF DEFAULT
SQL> alter flashback archive HAOFBDA set default;
Flashback archive altered.
SQL> select FLASHBACK_ARCHIVE_NAME,STATUS from dba_flashback_archive;
FLASHBACK_ARCHIVE_NA STATUS
-------------------- -------
HAOFBDA DEFAULT
HAOFBDA_DEF
9.许多DDL不允许使用在被FBDA跟踪的table上。
否则会报第九种错：ORA-55610
SQL> alter table TESTFBDA drop column object_id;
alter table TESTFBDA drop column object_id
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
但是特别注意有三种DDL是可以执行的，他们分别是：
add column,rename和grant。
SQL> RENAME TESTFBDA to TESTFBDA_RENAMED;
Table renamed.
SQL> select TABLE_NAME,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME from dba_flashback_archive_tables;
TABLE_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME
------------------------------ -------------------- ------------------------------
TESTFBDA_RENAMED HAOFBDA SYS_FBA_HIST_24020
SQL> RENAME TESTFBDA_RENAMED to TESTFBDA;
Table renamed.
SQL> alter table TESTFBDA add object_id2 number;
Table altered.
SQL> grant select on TESTFBDA to dba;
Grant succeeded.
10.若干种table不能使用FBDA
从11.1官档上摘取：
The table is neither nested, clustered, temporary, remote, or external.
The table contains neither LONG nor nested columns.
但经过我在11.1.6的平台下实验，cluster table和temporary table已经可以使用FBDA了。
于是又一次验证了实践是检验oracle官档的唯一标准：
包含Long的表仍然不能使用FBDA，否则会报第十种错：ORA-55631
SQL> create table haolong(id long);
Table created.
SQL> alter table haolong flashback archive haofbda;
alter table haolong flashback archive haofbda
*
ERROR at line 1:
ORA-55631: Table has columns with data types that are not supported by Flashback Data Archive
neested table也不能使用FBDA：
SQL> CREATE TYPE haotype AS OBJECT
2 (id1 number,
3 id2 number);
4
5 /
Type created.
SQL> CREATE TYPE haotype_table AS TABLE OF haotype;
2 /
Type created.
SQL> CREATE TABLE haonest (
2 Name VARCHAR2(20),
3 mytype haotype_table)
NESTED TABLE mytype STORE AS mytype_storage; 4
Table created.
SQL> desc HAONEST
Name Null? Type
----------------------- -------- ----------------
NAME VARCHAR2(20)
MYTYPE HAOTYPE_TABLE
SQL> alter table HAONEST flashback archive haofbda;
alter table HAONEST flashback archive haofbda
*
ERROR at line 1:
ORA-55631: Table has columns with data types that are not supported by Flashback Data Archive
remote table也不能使用FBDA：
SQL> alter tabletmphao@haodblinkflashback archive haofbda;
alter tabletmphao@haodblinkflashback archive haofbda
*
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database
external table也不能使用FBDA：
SQL> CREATE TABLE haoext
2 (
3 ID NUMBER
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY haodir
location
(
'123.txt'
)
)REJECT LIMIT UNLIMITED; 4 5 6 7 8 9 10 11 12 13
Table created.
SQL> select * from haoext;
ID
----------
1
2
3
4
SQL> alter table haoext flashback archive haofbda;
alter table haoext flashback archive haofbda
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
但是，temporary table却可以使用FBDA：
SQL> create global temporary table haotemp(id number);
Table created.
SQL> alter table haotemp flashback archive haofbda;
Table altered.
并且，cluster table也可以使用FBDA：
SQL> CREATE CLUSTER haocluster (id number) tablespace MSSMTBS;
Cluster created.
SQL> CREATE INDEX idx_haocluster ON CLUSTER haocluster tablespace MSSMTBS;
Index created.
SQL> CREATE TABLE c1 CLUSTER haocluster (id) as select 1 id from dual;
Table created.
SQL> alter table c1 flashback archive haofbda;
Table altered.
11.FBDA会产生两个内部表。
对某一个表使用FBDA后，我们可以根据视图dba_flashback_archive_tables找出其中一个内部表SYS_FBA_HIST_XXX。
SQL> select table_name,FLASHBACK_ARCHIVE_NAME,ARCHIVE_TABLE_NAME from dba_flashback_archive_tables;
TABLE_NAME FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME
------------------------------ -------------------- ------------------------------
TESTFBDA HAOFBDA SYS_FBA_HIST_24020
当然我们知道他是用来存储所有的在这个表上发生过的数据变化。
但我又发现了其实还有另一个内部表也被使用，这个表叫做SYS_FBA_TCRV_XXX。
其实很容易发现，当我查看一条flashback query语句的执行计划时：
SQL> set autotrace on
SQL> select object_name from haozhu_user.TESTFBDA
2 as of timestamp
3 to_timestamp('2009-10-04 00:20:41','yyyy-mm-dd hh24:mi:ss')
where object_id=10
;
4 5
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
C_USER#
Execution Plan
----------------------------------------------------------
Plan hash value: 1727830218
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 158 | 188 (1)| 00:00:03 | | |
| 1 | VIEW | | 2 | 158 | 188 (1)| 00:00:03 | | |
| 2 | UNION-ALL | | | | | | | |
|* 3 | FILTER | | | | | | | |
| 4 | PARTITION RANGE SINGLE| | 1 | 105 | 3 (0)| 00:00:01 | KEY | 1 |
|* 5 | TABLE ACCESS FULL | SYS_FBA_HIST_24020 | 1 | 105 | 3 (0)| 00:00:01 | KEY | 1 |
|* 6 | FILTER | | | | | | | |
|* 7 | HASH JOIN OUTER | | 1 | 2106 | 185 (1)| 00:00:03 | | |
|* 8 | TABLE ACCESS FULL | TESTFBDA | 1 | 91 | 181 (0)| 00:00:03 | | |
| 9 | VIEW | | 2000 | 3935K| 3 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | SYS_FBA_TCRV_24020 | 1 | 2028 | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TIMESTAMP_TO_SCN"(TO_TIMESTAMP('2009-10-04 00:20:41','yyyy-mm-dd hh24:mi:ss'))<11857344)
5 - filter("OBJECT_ID"=10 AND "ENDSCN"<=11857344 AND
"ENDSCN">"TIMESTAMP_TO_SCN"(TO_TIMESTAMP('2009-10-04 00:20:41','yyyy-mm-dd hh24:mi:ss')) AND
("STARTSCN" IS NULL OR "STARTSCN"<="TIMESTAMP_TO_SCN"(TO_TIMESTAMP('2009-10-04 00:20:41','yyyy-mm-dd
hh24:mi:ss'))))
6 - filter("F"."STARTSCN"<="TIMESTAMP_TO_SCN"(TO_TIMESTAMP('2009-10-04 00:20:41','yyyy-mm-dd
hh24:mi:ss')) OR "F"."STARTSCN" IS NULL)
7 - access("T".ROWID=CHARTOROWID("F"."RID"(+)))
8 - filter("T"."VERSIONS_STARTSCN" IS NULL AND "T"."OBJECT_ID"=10)
10 - filter(("ENDSCN" IS NULL OR "ENDSCN">11857344) AND ("STARTSCN" IS NULL OR "STARTSCN"<11857344))
原来，所谓的利用FBDA的flashback query其实是原表union all发生过的数据变化的表SYS_FBA_HIST_XXX。
SYS_FBA_HIST_XXX是存放发生过的transaction的数据更改的前镜像。
SYS_FBA_TCRV_XXX则是存放transaction的信息。
SQL> select rid,STARTSCN,op from SYS_FBA_TCRV_24020;
RID STARTSCN O
-------------------- ---------- -
AAAF3UAAJAAAAOVAA6 11857251 U
AAAF3UAAEAAAAVnAAA 11857826 I
以上表明我做过一次update和一次insert。
12.FBDA可以通过purge命令清除不用保存的数据。
但是只会清除SYS_FBA_HIST_XXX，不会清除SYS_FBA_TCRV_XXX。
这点让我很诡异。经过我测试，我即使drop flashback archive，
SYS_FBA_TCRV_XXX依然在，而且drop不掉。我顿时晕倒。。
SQL> select count(*) from
2 SYS_FBA_HIST_24020;
COUNT(*)
----------
1
SQL> ALTER FLASHBACK ARCHIVE haofbda PURGE ALL;
Flashback archive altered.
SQL> select * from SYS_FBA_HIST_24020;
no rows selected
SQL> select count(*) from SYS_FBA_TCRV_24020;
COUNT(*)
----------
2
SQL> drop flashback archive haofbda;
Flashback archive dropped.
SQL> drop table haozhu_user.SYS_FBA_TCRV_24020;
drop table haozhu_user.SYS_FBA_TCRV_24020
*
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "HAOZHU_USER"."SYS_FBA_TCRV_24020"
SQL> select count(*) from haozhu_user.SYS_FBA_TCRV_24020;
COUNT(*)
----------
2
而此时SYS_FBA_HIST_XXX已经乖乖地消失了。
SQL> desc SYS_FBA_HIST_24020
ERROR:
ORA-04043: object SYS_FBA_HIST_24020 does not exist
其他alter flashback archive命令及功能如下：
SQL> ALTER FLASHBACK ARCHIVE haofbda ADD TABLESPACE data01 QUOTA 10m;
Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE haofbda MODIFY TABLESPACE data01 QUOTA 20m;
Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE haofbda remove TABLESPACE data01;
Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE haofbda
2 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE haofbda PURGE BEFORE SCN 11858232;
Flashback archive altered.
13.FBDA只记录update和delete，不会记录insert到SYS_FBA_HIST_XXX.
前一点我说SYS_FBA_HIST_XXX是存放一个前镜像的数据，
所以没有必要为insert语句建立一条更改记录到内部表中，
因为反正Oracle都要FTS原表。
这一点其实在FBDA的白皮书里写出了的：
It is important to note that UPDATE and DELETE operations generate a new record in the history table.
Flashback Data Archive does not create a new history record for INSERT operations.
但是，却会有一条记录到SYS_FBA_TCRV_XXX这个记录transaction的表中。
SQL> select rid,STARTSCN,op from SYS_FBA_TCRV_24020;
RID STARTSCN O
-------------------- ---------- -
AAAF3UAAJAAAAOVAA6 11857251 U
AAAF3UAAEAAAAVnAAA 11857826I
14.当FBDA的内部表超过quota的大小时，会block transaction
这一点我认为是不能容忍的。
当它超出quota大小时，第十一种错报出：ORA-55617
SQL> update TESTFBDA set object_name='hao6';
update TESTFBDA set object_name='hao6'
*
ERROR at line 1:
ORA-55617: Flashback Archive "HAOFBDA" runs out of space and tracking on
"TESTFBDA" is suspended
试想当这种事情发生在生产数据库最重要的几个表上时，
我们或许会非常后悔开启了FBDA。
即使quota unlimited，我们可能也吧FBDA的tablespace的space usage作为最重要的几个监控对象。
BTW，在这里，这个quota仅仅只针对SYS_FBA_HIST_XXX表，不包括SYS_FBA_TCRV_XXX及其index。
在我的实验中，我的quota是50m，在如下情况就无法继续拓展了：
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_MB INIEXT MAXEXT NUM_EXTS
--------------- ------------------------------ ------------------ ---------- ---------- ---------- ----------
HAOZHU_USER SYS_FBA_HIST_24063 TABLE PARTITION 47 65536 62
但我的tablespace却使用了89M：
FBDATBS PERMANENT .06/ LMT:SYSTEM:ASSM 250 89 161 158 36 3
原来这些空间都被他们所占用了：
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_MB INIEXT MAXEXT NUM_EXTS
--------------- ------------------------------ ------------------ ---------- ---------- ---------- ----------
HAOZHU_USER SYS_FBA_TCRV_24063 TABLE 17 65536 32
HAOZHU_USER SYS_FBA_TCRV_IDX_24063 INDEX 21 65536 36
这些垃圾表，我至今不知道怎么删除，oracle是否会删除，何时删除？
15.如果在undo里的transaction前镜像信息还没有被FBDA归档，
并且undo满掉了，那么也会block transaction。
这是会报第十二种错：ORA-30036
SQL> update TESTFBDA set object_name='03:07:30';
update TESTFBDA set object_name='03:07:30'
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo
tablespace 'UNDOTBS01'
和14点一样，这一点也是无法容忍的。
我们都知道，undo在没有开启guarantee并且没有autoextend on的时候，
即使没有达到undo_retention的时间，Oracle也会复写已经commit的数据。
从白皮书上摘自：
In order to guarantee that every transaction is archived,the undo records are not recycled until the history is generated and stored in the database.
而我们又知道FBDA进程是一个异步归档undo的，这样做是为了提供最好的performance。
所以，当一个表的transaction突然增多时，是有可能FBDA进程来不及归档undo，导致应用的transaction报错。
16.ORA-00600: internal error code, arguments: [12811], [24063], [], [], [], [], [], []
我很庆幸自己是第一个发现并公开这个可以算是Oracle FBDA新特性在11.1.0.6下面的一个bug，
至少从网上没有找到类似案例公布出来。
做了这么久FBDA的实验，终于把Oracle给弄得生病了。。我的错。
而且为了reproduce这个error，我重复了两次。
首先，我们知道FBDA进程是异步归档undo的，而在FBDA发现quota不够之前，
我们是可以进行疯狂多的transaction。
这时，FBDA积累了大量的undo需要归档，但是quota的限制让他无法写hist table。
就在这时，我drop flashback archive，Oracle告诉我drop成功。
SQL> drop flashback archive HAOFBDA;
Flashback archive dropped.
再查询下系统视图，发现HAOFBDA仍然在：
QL> select * from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME
------------------ ----------------- ---------------------------------------------------------------------------
LAST_PURGE_TIME STATUS
--------------------------------------------------------------------------- -------
HAOFBDA
3 30 04-OCT-09 04.11.37.000000000 AM
04-OCT-09 04.11.37.000000000 AM
HAOFBDA_DEF
2 30 03-OCT-09 10.47.03.000000000 PM
03-OCT-09 10.47.03.000000000 PM DEFAULT
再查询下发现表还跟HAOFBDA有关联：
SQL> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME
-----------------------------------------------------
TESTFBDA HAOZHU_USER
HAOFBDA
SYS_FBA_HIST_24063
这时我郁闷了，鼓起勇气把tracked table给删掉：
SQL> drop table TESTFBDA purge;
Table dropped.
再查询视图发现关联的tracked table已经没有了：
SQL> select * from dba_flashback_archive_tables;
no rows selected
于是再度尝试drop flashback archive时：
SQL> drop flashback archive HAOFBDA;
drop flashback archive HAOFBDA
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [12811], [24063], [], [], [], [], [], []
这时候，我郁闷了。
即使重启数据库都再也无法删除这个flashback archive了。
最后我的解决方法：
先重新建立被我drop的tracked table，
然后重新：
SQL> alter table TESTFBDA flashback archive HAOFBDA;
Table altered.
然后接下来干什么呢？等！
一直等到SYS_FBA_HIST_XXX table消失为止。
这时再drop flashback archive：
SQL> drop flashback archive HAOFBDA;
Flashback archive dropped.
终于删掉了，一阵狂喜。
由此得出drop flashback archive是多么危险的操作啊！
这个bug的成因应该是FBDA进程积累了太多undo需要写到内部表里。
最正确的做法是先alter table no flashback archive，
然后再等待最新的undo信息写入内部表，
如果quota不够，先ALTER FLASHBACK ARCHIVE haofbda PURGE ALL;
最后再drop flashback archive。
--------End of Functional Part-----------
以上是自己functional的一些测试和经验，可见每一个新东西都不是完美的。
尤其是对于比较鸡肋的flashback data archive，bug和缺点都非常明显。
虽然将来不太会用这个特性，但姑且研究一下期待Oracle更加完善的版本。
最后在结尾处，我随便测试了一下写和读的performance的表现。
我对两个一样的表更新大约10000行，其中一个是enable FBDA的。
结果表明，FBDA的表现还算是令人满意的，overhead并没有想象中的太大。
physical reads可能会是最大的overhead。
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> alter table t1 flashback archive haofbda;
Table altered.
SQL> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> update haozhu_user.t1 set object_name='hao';
13362 rows updated.
SQL> commit;
Commit complete.
SQL> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> update haozhu_user.t2 set object_name='hao';
13363 rows updated.
SQL> commit;
Commit complete.
SQL> exec runStats_pkg.rs_stop(10000);
STAT...session uga memory max 224,040 184,968 -39,072
STAT...redo size 6,188,748 6,135,948 -52,800
STAT...session pga memory 202,640 312,128 109,488
STAT...undo change vector size 4,140,196 4,021,520 -118,676
STAT...session pga memory max 319,360 195,408 -123,952
STAT...session uga memory 193,448 33,048 -160,400
STAT...physical read bytes 1,736,704 1,556,480 -180,224
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
10,728 8,046 -2,682 133.33%
而对于读，白皮书上也提出，可以在内部表建立index来提高查询的performance。
我想这也是DBA该做的事情。
例如：对于一个简单的查询
select object_name from haozhu_user.t1
as of timestamp
to_timestamp('2009-10-04 06:01:00','yyyy-mm-dd hh24:mi:ss')
where object_id=10
;
plan is：
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 158 | 259 (2)| 00:00:04 | | |
| 1 | VIEW | | 2 | 158 | 259 (2)| 00:00:04 | | |
| 2 | UNION-ALL | | | | | | | |
|* 3 | FILTER | | | | | | | |
| 4 | PARTITION RANGE SINGLE| | 1 | 105 | 56 (2)| 00:00:01 | KEY | 1 |
|* 5 | TABLE ACCESS FULL | SYS_FBA_HIST_24074 | 1 | 105 | 56 (2)| 00:00:01 | KEY | 1 |
|* 6 | FILTER | | | | | | | |
|* 7 | HASH JOIN OUTER | | 1 | 2106 | 203 (1)| 00:00:03 | | |
|* 8 | TABLE ACCESS FULL | T1 | 1 | 91 | 182 (0)| 00:00:03 | | |
| 9 | VIEW | | 2000 | 3935K| 20 (5)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | SYS_FBA_TCRV_24074 | 12599 | 24M| 20 (5)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TIMESTAMP_TO_SCN"(TO_TIMESTAMP('2009-10-04 06:01:00','yyyy-mm-dd hh24:mi:ss'))<14129188)
5 - filter("OBJECT_ID"=10 AND "ENDSCN"<=14129188 AND
"ENDSCN">"TIMESTAMP_TO_SCN"(TO_TIMESTAMP('2009-10-04 06:01:00','yyyy-mm-dd hh24:mi:ss')) AND
("STARTSCN" IS NULL OR "STARTSCN"<="TIMESTAMP_TO_SCN"(TO_TIMESTAMP('2009-10-04 06:01:00','yyyy-mm-dd
hh24:mi:ss'))))
6 - filter("F"."STARTSCN"<="TIMESTAMP_TO_SCN"(TO_TIMESTAMP('2009-10-04 06:01:00','yyyy-mm-dd
hh24:mi:ss')) OR "F"."STARTSCN" IS NULL)
7 - access("T".ROWID=CHARTOROWID("F"."RID"(+)))
8 - filter("T"."VERSIONS_STARTSCN" IS NULL AND "T"."OBJECT_ID"=10)
10 - filter(("ENDSCN" IS NULL OR "ENDSCN">14129188) AND ("STARTSCN" IS NULL OR "STARTSCN"<14129188))
Statistics
----------------------------------------------------------
94 recursive calls
0 db block gets
600 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
我们可以看出对SYS_FBA_HIST_24074的FTS将来肯定会成为瓶颈。
于是我在其上根据plan里写的endscn和startscn上建立index。
SQL> create index haoidx on SYS_FBA_HIST_24074(ENDSCN,STARTSCN);
Index created.
SQL> analyze table SYS_FBA_HIST_24074 compute statistics;
Table analyzed.
再执行同一个查询：
Execution Plan
----------------------------------------------------------
Plan hash value: 2445997481
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 158 | 207 (1)| 00:00:03 | | |
| 1 | VIEW | | 2 | 158 | 207 (1)| 00:00:03 | | |
| 2 | UNION-ALL | | | | | | | |
|* 3 | FILTER | | | | | | | |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| SYS_FBA_HIST_24074 | 1 | 26 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 5 | INDEX RANGE SCAN | HAOIDX | 120 | | 2 (0)| 00:00:01 | | |
|* 6 | FILTER | | | | | | | |
|* 7 | HASH JOIN OUTER | | 1 | 2106 | 203 (1)| 00:00:03 | | |
|* 8 | TABLE ACCESS FULL | T1 | 1 | 91 | 182 (0)| 00:00:03 | | |
| 9 | VIEW | | 2000 | 3935K| 20 (5)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | SYS_FBA_TCRV_24074 | 12599 | 24M| 20 (5)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TIMESTAMP_TO_SCN"(TO_TIMESTAMP('2009-10-04 06:01:00','yyyy-mm-dd hh24:mi:ss'))<14129188)
4 - filter("OBJECT_ID"=10 AND ("STARTSCN" IS NULL OR "STARTSCN"<="TIMESTAMP_TO_SCN"(TO_TIMESTAMP('2009-10-04
06:01:00','yyyy-mm-dd hh24:mi:ss'))))
5 - access("ENDSCN">"TIMESTAMP_TO_SCN"(TO_TIMESTAMP('2009-10-04 06:01:00','yyyy-mm-dd hh24:mi:ss')) AND
"ENDSCN"<=14129188)
6 - filter("F"."STARTSCN"<="TIMESTAMP_TO_SCN"(TO_TIMESTAMP('2009-10-04 06:01:00','yyyy-mm-dd hh24:mi:ss')) OR
"F"."STARTSCN" IS NULL)
7 - access("T".ROWID=CHARTOROWID("F"."RID"(+)))
8 - filter("T"."VERSIONS_STARTSCN" IS NULL AND "T"."OBJECT_ID"=10)
10 - filter(("ENDSCN" IS NULL OR "ENDSCN">14129188) AND ("STARTSCN" IS NULL OR "STARTSCN"<14129188))
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
89 recursive calls
0 db block gets
296 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
可以看出，使用了index后，LIO由600降到了300.
但尽管如此，FBDA特性缺点还是比较突出，
而带来的优点却并不吸引我。
在我看来，这个特性也并不能够用来代替我们现在的delay若干小时的standby。
Oracle还需要再接再厉啊。
--Hao写于2009.10.4于office
418
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
