启用表的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 编辑 ]
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/