Oracle Database Discussions

Hi there.

We are using Oracle 10g on Solaris.

Every day we refresh a group of schemas in our reporting data warehouse db via Oracle's transportable tablespaces. Most schemas are refreshed quite fast (the entire schema import takes < 15 minutes). One schema in particular is very slow to import ( about 60 minutes).

I have traced the "imp transport_tablespace=y datafiles=...." session, and found that most of the time time appears to be spent importing statistics, and inserting extent information into the dictionary. One table in particular which is highly partitioned (161 partitions, and 3800 subpartitions) takes over 30 minutes to import. We have already reduced the number of histogram statistics to indexed columns only.

Has anyone found a workaround for the slow import of highly partitioned objects ?

Is this just a performance issue of Oracle's and highly partitioned objects that we have to live with ? Dropping the objects takes a very long time too (removing entries from tab$, hist_head$ etc...).

Any input is appreciated.

thanks
Cameron.

+++++++++++++++++++++++++++++++++++
ie. the top 3 SQL statements performed by imp.
+++++++++++++++++++++++++++++++++++

insert into hist_head$(obj#, intcol#, bucket_cnt, row_cnt, cache_cnt,
null_cnt, timestamp#, sample_size, minimum, maximum, distcnt,lowval, hival,
density, spare1, spare2, avgcln, col#)
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1166 0.06 0.10 0 0 0 0
Execute 1166 7.25 10.14 14 1178 5942 1166
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2332 7.31 10.24 14 1178 5942 1166



update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=
decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),
audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,
rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,
analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,
null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,
flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,
spare6=:35
where
obj#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 388 0.04 0.04 0 0 0 0
Execute 388 2.54 4.18 0 2328 392 388
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 776 2.58 4.23 0 2328 392 388




update tabcompart$ set part# = :1, subpartcnt = :2, flags = :3, defts# = :4,
defpctfree = :5, defpctused = :6, definitrans = :7, defmaxtrans = :8,
definiexts = :9, defextsize = :10, defminexts = :11, defmaxexts = :12,
defextpct = :13, deflists = :14, defgroups = :15, defbufpool = :16,
deflogging = :17, analyzetime = :18, samplesize = :19, rowcnt = :20, blkcnt
= :21, empcnt = :22, avgspc = :23, chncnt = :24, avgrln = :25, spare2 = :26
where
obj# = :27


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 389 0.03 0.02 0 0 0 0
Execute 389 1.86 2.72 0 389 786 389
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 778 1.89 2.75 0 389 786 389