RE: would imp speed affect by setting indexes to NOLOGGING?

From: Kirtikumar Deshpande <kirtikumar_deshpande_at_yahoo.com>
Date: Tue, 27 May 2003 11:15:13 -0800
Message-ID: <F001.005A38B4.20030527111513@fatcity.com>

In respone to someone's question about improving exp/imp speed I had posted the following quite some time ago. Most of it may help you.

• Kirti

This one is a rather long one.. Sorry..

Prepare scripts to build tables, indexes and constraints etc.. Prebuild the
tables in the target database. I am assuming that a database is already
created with all the tablespaces etc..

Improving Export performance:
1) Use direct=y. That will make the export process significantly faster.
2) Along with (1), setting 'recordlength' to multiples of db_block_size or
to its max value (65535) will help 'squeeze out' some more performance gain.
Although, this parameter is to be used when exporting/importing on different
OS where it has different default values, I use it for added performance
gain. You may want to give it a trial run to see if that would help.
3) I do not export indexes.

Improving Import Performance:
1) Keep database in no-archive log mode, if it is not already so.
2) Remember to use ignore=y since tables are already present.
3) Use commit=y to control rollback segment usage. 4) Do not import indexes by setting indexes=n (just to be sure).
5) Set buffer= to a high value, 5-10 MB should work fine (there is no
proportional gain performance in raising this value too high).
6) Set analyze=n to suppress automatic estimation of table statistics.
Analyze tables using your procedures after indexes etc are built.
7) For primary key constraint indexes, I keep the quota on the target
tablespace to 0 to make it fail during import. (something I just find easier
to remember).
8) Set log= to some log file name to capture all (good and bad) messages
from the import process.
9) After the import is completed, set sort_area_size, sort_area_retained_size to a higher value (whatever is adequate and
possible) to speed up index build process. Also, consider TEMPORARY type
temp tablespace with properly configured initial and next (multiples of
sort_area_size) extents. Make sure temp tablespace has ample room, should
index build processes perform disk sorts. Also, make sure quota is okay on
tablespaces for primary key constraint indexes. 10) Run all the index build scripts. Use nologging attribute and consider
building indexes in parallel, if resources are available to do so.
11) Enable all the constraints etc.
12) After all indexes are successfully built, make sure the sort parameters
are adjusted back to what they should be for running the db normally. Spot
check and make sure everything looks fine and okay. 13) Do not forget the SQL*Net thingy.. Make necessary changes to
global_name, TNSNAMES.ora and LISTENER.ora file. Bounce the listener.
14) Run your own procedures to analyze tables and indexes.
15) Take a cold back up.
16) Startup mount and change to archive log (if required). Open the db for
users.
17) Time to hit the door..

Hope this helps...

• gmei <gmei_at_incyte.com> wrote:
  > Everything in the schema on Server B is dropped
  > before import, so I have an
  > empty schema to begin with. I am not sure I
  > understand what you meant by
  > "dropping the indexes before import"? Did you mean
  > export/import indexes
  > separately? I guess I could do that. Right now I
  > just try to keep the
  > program simple because the time is not an issue. I
  > just want to know if
  > there is an "easy and simple" way to increase the
  > imp speed without changing
  > the program too much.
  >
  > Guang
  >
  > > -----Original Message-----
  > > From: root_at_fatcity.com [mailto:root_at_fatcity.com]On
  > Behalf Of
  > > Ron Thomas
  > > Sent: Tuesday, May 27, 2003 1:50 PM
  > > To: Multiple recipients of list ORACLE-L
  > > Subject: Re: would imp speed affect by setting
  > indexes to NOLOGGING?
  > >
  > >
  > >
  > > Have you considered dropping the indexes before
  > import, then
  > > rebuilding parallel nologging?
  > >
  > > Ron Thomas
  > > Hypercom, Inc
  > > rthomas_at_hypercom.com
  > > Each new user of a new system uncovers a new class
  > of bugs.
  > > -- Kernighan
  > >
  > >
  > >
  > >
  > >
  > > gmei_at_incyte.com
  > >
  > >
  > > Sent by: To:
  > > ORACLE-L_at_fatcity.com
  > >
  > > root_at_fatcity.com cc:
  > >
  > >
  > >
  > Subject:
  > > would imp speed affect by setting indexes to
  > NOLOGGING?
  > >
  > >
  > >
  > >
  > > 05/27/2003 11:19
  > >
  > >
  > > AM
  > >
  > >
  > > Please respond to
  > >
  > >
  > > ORACLE-L
  > >
  > >
  > >
  > >
  > >
  > >
  > >
  > >
  > >
  > >
  > >
  > >
  > > Hi,
  > >
  > > Would the imp speed increase by setting all
  > indexes to NOLOGGING?
  > >
  > > I have a schema with 249 tables and 442 indexes
  > and I have a
  > > weekly schema
  > > refresh program running as this:
  > >
  > > exp schema from Server A (Oracle 8173 on Sun
  > Solaris box)
  > > ftp dump file from Server A to Server B, the dump
  > file size
  > > is 1.9GB after
  > > compression
  > > imp schema on Server B (also Oracle 8173 on Sun
  > Solaris box), the imp
  > > usually take about 7 hours.
  > >
  > > With everything elase the same, last week I
  > changed all indexes in the
  > > schema on Server A to "NOLOGGING" (they were all
  > set to
  > > "logging" before),
  > > hoping to increase the speed of imp on Server B.
  > Because I thought the
  > > "create index ... nologging" would reduce redo log
  > writing, therefore
  > > increase the imp speed quite a bit (there are some
  > big
  > > indexes there!). But
  > > this morning I found the imp still took about the
  > same time as before,
  > > although I was told that the system I/O seems to
  > be lower.
  > >
  > > Does anyone have similar experience? TIA.
  > >
  > > Guang
  > >
  

  ---

Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kirtikumar Deshpande
  INET: kirtikumar_deshpande_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue May 27 2003 - 14:15:13 CDT