TRUNCATE TABLE HANG OR SLOW ORACLE

TRUNCATE TABLE HANG OR SLOW ORACLE

Truncating a table is faster compared to delete statement in a database. But if the truncate itself is slow, then what may be the cause for the slowness?

1. If flashback archive is enabled for a table which needs to be truncated. In this case, additional overhead is needed to mimic the data which need to be removed before truncate.

Consider the below example. Flashback archive is enable for the table dbsrc. It take 5.21 seconds

kIsH@Xhydra<>drop table DBSRC;

Table dropped.

kIsH@Xhydra<>create table dbsrc as select * from dba_source;

Table created.

kIsH@Xhydra<>create flashback archive fda_dbsrc_archive tablespace users retention 6 month;

Flashback archive created.

kIsH@Xhydra<>alter table dbsrc flashback archive fda_dbsrc_archive;

Table altered.

kIsH@Xhydra<>set timing on
kIsH@Xhydra<>truncate table dbsrc;

Table truncated.

Elapsed: 00:00:05.21
kIsH@Xhydra<>alter table dbsrc no flashback archive;

Table altered.

Elapsed: 00:00:00.02
kIsH@Xhydra<>drop table dbsrc;

Table dropped.

Oracle has to execute a bunch of queries for FBA archival before execution of truncate statement which leads to additional time.

[oracle@xhydra u01]$ egrep -A1 'lock|select|insert' db9zx_ora_4902.tkprf|grep -v '-'|head -20
select ROWID from "KISH".SYS_FBA_DDL_COLMAP_77786 where ROWNUM = 1

select ROWID from "KISH".SYS_FBA_HIST_77786 where ROWNUM = 1

select ROWID from SYS_MFBA_NHIST_77786 where ROWNUM = 1

select ROWID from "KISH".SYS_FBA_TCRV_77786 where ROWNUM = 1

insert into "KISH".SYS_FBA_HIST_77786  (RID, STARTSCN, ENDSCN, XID, OPERATION, "OWNER", "NAME", "TYPE", "LINE", "TEXT", "ORIGIN_CON_ID") select  v.RID "RID", NVL(r.STARTSCN, NULL) "STARTSCN", 20828692 "ENDSCN", NVL(r.XID, NULL) "XID" ,NVL(r.OP, NULL) "OPERATION",  v."OWNER" "OWNER",  v."NAME" "NAME",  v."TYPE" "TYPE",  v."LINE" "LINE",  v."TEXT" "TEXT",  v."ORIGIN_CON_ID" "ORIGIN_CON_ID" from (select ROWID "RID", "OWNER", "NAME", "TYPE", "LINE", "TEXT", "ORIGIN_CON_ID"  from "KISH"."DBSRC")  v,  (select RID, STARTSCN, ENDSCN, XID, OP from "KISH".SYS_FBA_TCRV_77786 where ENDSCN is NULL) r where v.RID = r.RID(+)

select count(FA#)
from
select OWNERNAME, OBJNAME
from
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1,
  spare2, spare3, signature, spare7, spare8, spare9, nvl(dflcollid, 16382),
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
  sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
select t.name
from

Truncating a table without flashback takes less time of 11 milliseconds

kIsH@Xhydra<>create table dbsrc as select * from dba_source;

Table created.

Elapsed: 00:00:00.72
kIsH@Xhydra<>alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.26
kIsH@Xhydra<>alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.11
kIsH@Xhydra<>truncate table dbsrc;

Table truncated.

Elapsed: 00:00:00.08