Re: ** import tuning

Thanks to Egor for your help. Also to Mladen Marquez, Chris, Michael McMullen, Mark Is "_disable_logging to be set at instance level? In init.ora? Or can I re create tablespace with nologging option expect the same benefit.  

 How do I decide on a appropriate buffer value?  

Another question about import : In export i gave COMPRESS=N to avoid it trying to put all data in one extent. However now it is creating too many extents. I tried to set the next extent for the objects at the source of export hoping that NEXT_EXTENT would be the next extent at the target where it is imported. However that does not work. Is there a way I can do this export import so it sets the next_extent at target to be the same as next_extent at source. This is a periodic activity and I need to recreate the object. I cannot just do truncate and import. Thanks

Egor Starostin <egorst_at_gmail.com> wrote:
> I have a export taken with direct=3Dy. This is done setting a high recor=
dlengh =3D 64K.
Good.
> When importing using this export sould I set recordlength or buffer?
You may set buffer.
> How high is Ok for buffer?

They say, it depends. There is a chance that you will see no time difference between buffer=3D100m and buffer=3D1m.

> I am on 817 on sun 5.8. I need to do this operation frequently and need t=
o tune it.
Just don't forget about direct=3Dy and recordlength=3D64k. And try to parallelize export. If you need to export user's data then export its schema (rows=3Dn) and run several exports in parallel (one for each table). I personally (as a python addicted user :) ) created small python script for such task http://www.oracledba.ru/python/parexp.py).

> Basically is export taken with direct option any different from regular =
export?
Yes. Direct export is faster. Direct export with recordlength=3D64k is even faster.
Direct path export should be always faster (two-three times) than conventional path export (no matter of buffer=3D settings).

As for settings to speedup import, I don't recommend you to regularly use "_disable_logging"/"_wait_for_sync" options. Leave them only for migration tasks. As an option you can increase priority of lgwr process during import (renice -20 ).

When I played with export/import speed issue I enabled system trigger like = this:
***
create or replace trigger traceimpexp after logon on database begin
for tr in (
select * from (
select sid,serial# serial
from v$session
where username =3D user and (substr(program,1,4)=3D'imp@' or substr(pro= gram,1,4)
=3D'exp@')
order by logon_time desc
) where rownum < 2
) loop
dbms_system.set_ev(tr.sid,tr.serial,10046,8,''); end loop;
end;
/
***
and analyzed trace files with my profiler (OraSRP). For example, I played with redo logs/log buffers and when I saw that redolog-related events consumed in sum less than 3-5% of all the time, I stopped to tune them.

Reading of Notes 93763.1 and 155477.1 also might be helpful.

--=20
Egor
http://www.oracledba.ru/orasrp/
Free Oracle Session Resource Profiler

--
http://www.freelists.org/webpage/oracle-l

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

--
http://www.freelists.org/webpage/oracle-l