ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 11G Flashback Data Archive新特性的研究

11G Flashback Data Archive新特性的研究

原创 Linux操作系统 作者:viadeazhu 时间:2009-10-04 11:40:43 0 删除 编辑

  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 tmphao@haodblink flashback archive haofbda;
alter table
tmphao@haodblink 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-08-22

  • 博文量
    79
  • 访问量
    368429