oracle truncate很慢,truncate 为什么有时会很慢？

1)系统环境：HPUX 11.23 ORACLE 9.2.0.6 硬件：HP rx4640 32G内存，存储：XP20000

2) 建两个测试表：

SQL> insert into testtable (select object_id from dba_objects);

69679 rows created.

SQL> insert into testtable (select object_id from testtable);

69679 rows created.

SQL> /

139358 rows created.

SQL> /

278716 rows created.

SQL> /

557432 rows created.

SQL> /

1114864 rows created.

SQL> insert into testtable1 (select * from testtable);

2229728 rows created.

3) 测试

SQL> alter session set events '10046 trace name context forever ,level 12' ;

Session altered.

Elapsed: 00:00:00.03

SQL> truncate table testtable1;

Table truncated.

Elapsed: 00:00:00.76

SQL> truncate table testtable;

Table truncated.

Elapsed: 00:00:00.76

[在另一个窗口：insert inot testtable..., insert into testtable1 (select * from testtable);]

SQL> truncate table testtable1;

Table truncated.

Elapsed: 00:01:02.14

可以看到：快的是候在1秒之内，慢的时候需要1分多钟，有达人能帮助分析分析吗？

TRACE：

(有点长，选一部分吧, 开头和等待部分，全部内容放到附件里)

/oracle/admin/afcc/udump/afcc_ora_29907.trc

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.6.0 - Production

ORACLE_HOME = /oracle/product/9.2.0

System name:        HP-UX

Node name:        PDDB

Release:        B.11.23

Version:        U

Machine:        ia64

Instance name: afcc

Redo thread mounted by this instance: 1

Oracle process number: 13

Unix process pid: 29907, image: oracle@PDDB (TNS V1-V3)

*** 2011-06-03 12:55:09.025

*** SESSION ID

195.42648) 2011-06-03 12:55:09.006

APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240

=====================

PARSING IN CURSOR #1 len=70 dep=0 uid=37 oct=42 lid=37 tim=1844707248896 hv=3154874239 ad='54908688'

alter session set events '10046 trace name context forever ,level 12'

END OF STMT

EXEC #1:c=0,e=68,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1844707230506

WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0

*** 2011-06-03 12:55:24.754

WAIT #1: nam='SQL*Net message from client' ela= 15359952 p1=1650815232 p2=1 p3=0

XCTEND rlbk=0, rd_only=1

=====================

PARSING IN CURSOR #1 len=25 dep=0 uid=37 oct=85 lid=37 tim=1844722621028 hv=2733931833 ad='441b14f8'

truncate table testtable1

END OF STMT

PARSE #1:c=0,e=300,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1844722621023

BINDS #1:

=====================

PARSING IN CURSOR #2 len=105 dep=1 uid=0 oct=3 lid=0 tim=1844722621488 hv=3350194674 ad='71c1e4f0'

select log, sysdate, sysdate+1/86400, flag from sys.mlog$   where  mowner = :1 and master = :2 for update

END OF STMT

PARSE #2:c=0,e=346,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1844722621485

BINDS #2:

bind 0: dty=1 mxl=32(09) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=32 offset=0

bfp=9fffffff881e8018 bln=32 avl=09 flg=05

value="CHDB_PROC"

bind 1: dty=1 mxl=32(10) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=32 offset=0

bfp=9fffffff881e7fc8 bln=32 avl=10 flg=05

value="TESTTABLE1"

EXEC #2:c=0,e=269,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1844722621858

FETCH #2:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1844722621884

STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='FOR UPDATE  '

...................................................

=====================

PARSING IN CURSOR #2 len=116 dep=1 uid=0 oct=3 lid=0 tim=1844917535078 hv=431456802 ad='43c2d9f0'

select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1

END OF STMT

PARSE #2:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1844917535075

BINDS #2:

bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0

bfp=9fffffff881e3df0 bln=22 avl=05 flg=05

value=1572597

EXEC #2:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1844917535255

FETCH #2:c=0,e=20,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1844917535297

WAIT #1: nam='enqueue' ela= 424642 p1=1380909062 p2=1 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1956340 p1=5 p2=21474836 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962741 p1=5 p2=21474834 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962800 p1=5 p2=21474832 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962802 p1=5 p2=21474830 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962761 p1=5 p2=21474828 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962791 p1=5 p2=21474826 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962789 p1=5 p2=21474824 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962792 p1=5 p2=21474822 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962780 p1=5 p2=21474820 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962849 p1=5 p2=21474818 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962674 p1=5 p2=21474816 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962810 p1=5 p2=21474814 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962895 p1=5 p2=21474812 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962645 p1=5 p2=21474810 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962796 p1=5 p2=21474808 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962796 p1=5 p2=21474806 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962796 p1=5 p2=21474804 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962202 p1=5 p2=21474802 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962405 p1=5 p2=21474800 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962732 p1=5 p2=21474798 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962886 p1=5 p2=21474796 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1963079 p1=5 p2=21474794 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962255 p1=5 p2=21474792 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962644 p1=5 p2=21474790 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962753 p1=5 p2=21474788 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 1962773 p1=5 p2=21474786 p3=0

WAIT #1: nam='rdbms ipc reply' ela= 488558 p1=5 p2=21474784 p3=0

WAIT #1: nam='db file sequential read' ela= 21 p1=39 p2=3514547 p3=1

WAIT #1: nam='local write wait' ela= 985775 p1=39 p2=3514547 p3=0

WAIT #1: nam='local write wait' ela= 986405 p1=39 p2=3514547 p3=0

WAIT #1: nam='local write wait' ela= 986162 p1=39 p2=3514547 p3=0

WAIT #1: nam='local write wait' ela= 986304 p1=39 p2=3514547 p3=0

WAIT #1: nam='local write wait' ela= 986261 p1=39 p2=3514547 p3=0

WAIT #1: nam='local write wait' ela= 986195 p1=39 p2=3514547 p3=0

WAIT #1: nam='local write wait' ela= 986308 p1=39 p2=3514547 p3=0

WAIT #1: nam='local write wait' ela= 986326 p1=39 p2=3514547 p3=0

WAIT #1: nam='local write wait' ela= 787645 p1=39 p2=3514547 p3=0

=====================

[本帖最后由 sinbadcn 于 2011-6-3 14:27 编辑]

2011-6-3 14:26 上传

点击文件名下载附件

10.48 KB, 下载次数: 4