Re: ** import tuning
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: egorst@xxxxxxxxx
- Date: Wed, 8 Jun 2005 11:04:49 -0700 (PDT)
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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
