启用表的flashback data archive，会导致批量更新的提交操作超慢。

启用表的flashback data archive，会导致批量更新的提交操作超慢。
SQL> set timing on                           
SQL> update test set id=5 where rownum<30000;

29999 rows updated.

Elapsed: 00:00:00.30
SQL> commit;

Commit complete.

Elapsed: 00:00:44.21
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production



用10046跟踪以后，记录后台的信息如下，完整的参看附件：
insert /*+ append */ into SYS_MFBA_NHIST_70625  select /*+ leading(r)
  use_nl(v) */ v.ROWID "RID", v.VERSIONS_STARTSCN "STARTSCN",  
  v.VERSIONS_ENDSCN "ENDSCN", v.VERSIONS_XID "XID" ,v.VERSIONS_OPERATION
  "OPERATION",  v.ID "ID"  from SYS_MFBA_NROW r, TESTUSER.TEST versions
  between SCN :1 and MAXVALUE v where v.ROWID = r.rid


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     45.94      45.95          0   41565046       8962       89997
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     45.94      45.95          0   41565046       8962       89997

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  LOAD AS SELECT  (cr=41565078 pr=0 pw=546 time=0 us)
  89997   NESTED LOOPS  (cr=41563667 pr=0 pw=0 time=358789 us cost=8201 size=16556536 card=8168)
  29999    TABLE ACCESS FULL SYS_MFBA_NROW (cr=202 pr=0 pw=0 time=286 us cost=29 size=16352336 card=8168)
  89997    TABLE ACCESS BY USER ROWID TEST (cr=41563465 pr=0 pw=0 time=22645219 us cost=1 size=25 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  control file sequential read                   10        0.00          0.00
  direct path write temp                         18        0.00          0.00
  direct path sync                                1        0.00          0.00
********************************************************************************

insert  into TESTUSER.SYS_FBA_HIST_70625 select /*+ leading(r) use_nl(v) */
  v.RID "RID", v.STARTSCN "STARTSCN",  v.ENDSCN "ENDSCN", v .XID "XID" ,
  v.OPERATION "OPERATION",  v.ID "ID"  from SYS_MFBA_NCHANGE  r,
  SYS_MFBA_NHIST_70625 v where r.rid = v.RID and r.escn = v.ENDSCN and
  v.STARTSCN is not NULL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.75       0.75        546       5612       2713       29999
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.75       0.75        546       5612       2713       29999

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=5612 pr=546 pw=0 time=0 us)
  29999   NESTED LOOPS  (cr=5084 pr=546 pw=0 time=6044 us cost=40889 size=4064 card=1)
  29999    TABLE ACCESS FULL SYS_MFBA_NCHANGE (cr=127 pr=0 pw=0 time=210 us cost=29 size=16458520 card=8168)
  29999    TABLE ACCESS BY INDEX ROWID SYS_MFBA_NHIST_70625 (cr=4957 pr=546 pw=0 time=0 us cost=5 size=2049 card=1)
  89997     INDEX RANGE SCAN SYS_MFBA_NHIST_70625_IDX (cr=4410 pr=0 pw=0 time=31108 us cost=1 size=0 card=32)(object id 70637)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       546        0.00          0.00
********************************************************************************

delete /*+ use_nl(d) index(d) leading(VW_NSO_1) */ from
  TESTUSER.SYS_FBA_TCRV_70625 d
where
d.RID in (select /*+ unnest */ RID from (select RID from SYS_MFBA_NROW order
  by RID))


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.75       0.75          0        892      92826       29999
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.75       0.75          0        892      92826       29999

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)、


时间主要耗在了对SYS_MFBA_NHIST_70625  直接路径插入上。

[ 本帖最后由 wei-xh 于 2010-9-16 13:50 编辑 ]

crmg_ora_28532.rar
(2010-09-16 13:47:31, Size: 32 KB, Downloads: 0)

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

转载于:http://blog.itpub.net/22034023/viewspace-674142/