11G Flashback Data Archive新特性的研究
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 table flashback archive haofbda;
alter table flashback 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 11857826 I
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15415488/viewspace-615982/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 11g 新特性 Flashback Data Archive 說明OracleHive
- 11G新特性：FLASHBACK ARCHIVEHive
- 11G新特性：FLASHBACK ARCHIVE - 01Hive
- 11G新特性：FLASHBACK ARCHIVE – 02Hive
- Oracle 11g新特性--閃回資料歸檔(flashback data archive)[zt]OracleHive
- Oracle Flashback Data ArchiveOracleHive
- Flashback Data Archive RequirementsHiveUIREM
- 【FLASHBACK】Oracle flashback data archive 介紹OracleHive
- Flashback Data Archive原理詳解Hive
- Flashback Data Archive (Oracle Total Recall)HiveOracle
- oracle11g flashback archive feature新特性OracleHive
- 11g data guard 新特性
- 11G flashback data archive 導致產生大量歸檔日誌Hive
- How To Using Flashback Data Archive (Oracle Total Recall)HiveOracle
- 閃回資料歸檔-- Flashback Data ArchiveHive
- Playing with Flashback Data Archive on 11GR2Hive
- 11g feature: Flashback Data Archive Guide. (Doc ID 470199.1)HiveGUIIDE
- 【DataGuard】11g 新特性：Active Data Guard
- oracle 11g 新特性 data recover AdvisorOracle
- 11G result cache新特性的更多深入研究
- Oracle OCP IZ0-053 Q166(Flashback Data Archive)OracleHive
- Oracle OCP 1Z0-053 Q672(Flashback Data Archive)OracleHive
- Oracle OCP 1Z0 053 Q143(Flashback Data Archive)OracleHive
- flashback drop/query/table/database/archiveDatabaseHive
- 【ORACLE新特性】11G 分割槽新特性Oracle
- oracle 11g 的新特性Oracle
- (轉)oracle10g新特性之 flashbackOracle
- 10G新特性筆記之FLASHBACK筆記
- Oracle 11g 新特性Oracle
- 【Flashback】10G的新特性“閃回表”清除方法
- 9i新特性之Flashback Query的應用(1)
- 9i新特性之Flashback Query的應用(2)
- 11g新特性--active dataguard
- 10g新特性——Data Pump(轉)
- oracle flashback特性(2.1)--Flashback Table之RECYCLEBINOracle
- oracle flashback特性(3.2)--Flashback Database操作示例OracleDatabase
- Oracle Flashback Archive——Oracle閃迴歸檔（上）OracleHive
- Oracle Flashback Archive——Oracle閃迴歸檔（中）OracleHive