Optimising Oracle Imports - page 4
author: Martin "mpot" Pot
date: 25 October 2004
navigation:
<prev
| 1
| 2
| 3
| 4
| next>
Rollback Segments
Definition
A rollback segment is made up of multiple extents that consist of several 'rollback entries' which keeps undo information for transactions. Rollback segments are needed for read-consistent database information, database recovery, and rolling back uncommitted transactions.
Configuration
Use one large dedicated rollback segment for the import. To do this, take all other rollback segments offline. One rollback segment, approximately 50% of the size of the largest table being imported, should be sufficient.
Determine Required Size
Identify the size of the largest table in the current database:
sqlplus system/manager@alias
SQL> SELECT segment_type, segment_name,
to_char ( sum(bytes) / (1024 * 1024) , '999,999.90') as MB
from dba_extents where segment_type='TABLE'
group by segment_type, segment_name order by 3;
Create a new tablespace to hold the temporary rollback segment. In this example, a 500 MB "IMPORT" rollback tablespace is created:
sqlplus system/manager@alias
SQL> create tablespace IMPORT
datafile 'd:\ora8\oradata\SID\import01.dbf'
size 500M reuse autoextend on next 10M
default storage (initial 10M next 10M
minextents 45 maxextents unlimited pctincrease 0);
Create a new rollback segment in this new tablespace, and put the rollback segment online:
SQL> create rollback segment IMPORT
tablespace IMPORT;
SQL> alter rollback segment "IMPORT" online;
Take the default rollback segments offline:
SQL> alter rollback segment RB0 offline;
SQL> alter rollback segment RB1 offline;
SQL> alter rollback segment RB2 offline;
SQL> alter rollback segment RB3 offline;
SQL> alter rollback segment RB4 offline;
SQL> alter rollback segment RB5 offline;
SQL> alter rollback segment RB6 offline;
SQL> alter rollback segment RB7 offline;
SQL> alter rollback segment RB8 offline;
SQL> alter rollback segment RB9 offline;
Once the import is complete, the temporary rollback segment should be removed, and the default rollback segments should be brought back online:
sqlplus system/manager@alias
SQL> alter rollback segment RB0 online;
SQL> alter rollback segment RB1 online;
SQL> alter rollback segment RB2 online;
SQL> alter rollback segment RB3 online;
SQL> alter rollback segment RB4 online;
SQL> alter rollback segment RB5 online;
SQL> alter rollback segment RB6 online;
SQL> alter rollback segment RB7 online;
SQL> alter rollback segment RB8 online;
SQL> alter rollback segment RB9 online;
Once the import is complete, the temporary rollback segment can be removed:
SQL> alter rollback segment IMPORT offline;
SQL> drop rollback segment IMPORT;
And the temporary tablespace can be removed:
SQL> drop tablespace IMPORT;
The temporary datafile, 'd:\ora8\oradata\SID\import01.dbf', can now be deleted.
References
Metalink Note 62005.1 Creating, Optimizing, and Understanding Rollback Segments
Metalink Note 93763.1 Tuning Considerations When Import Is Slow
Metalink Note 1011108.6 General Overview of Rollback Segments
Metalink Note 69464.1 Rollback Segment Configuration & Tips
Import Commit
Definition
The COMMIT option for a database import, if turned on, means the import will commit the data after each array insert, where the size of the array insert is determined by the value of the BUFFER parameter.
Setting the COMMIT option off causes the import to commit after each object (ie, table), rather than after each buffer.
Requirements
Note that using COMMIT=N requires the presence of a large rollback segment.
Configuration
|
Oracle default value:
| |
COMMIT=N
| |
Recommended value:
| |
COMMIT=N
| |
Location:
| |
IMP command-line parameter
|
References
Metalink Note 93763.1 Tuning Considerations When Import Is Slow
Import Buffer
The default IMPort command for the application being migrated has the BUFFER option set to 3000000 bytes (ie, approx 3MB).
Providing the server has sufficient memory, this value can be increased.
Impact
A larger BUFFER value will reduce the number of times the import needs to access the dump file, as it reads BUFFER bytes from the dump file each time it is accessed.
Configuration
|
Recommended value:
| |
BUFFER=30000000 (~30MB)
| |
Location:
| |
IMP command-line parameter
|
References
Metalink Note 93763.1 Tuning Considerations When Import Is Slow
Other Import Considerations
Dump File Location
If possible, locate the Oracle dump file on a different HDD from the Oracle datafiles.
This will reduce HDD contention as the import accesses data from the dump file and writes it to the Oracle datafiles.
Case Study
Test Hardware
A number of database import tests were performed on a development server:
- dual 3.2GHz Xeons
- 4 GB RAM
- 2 x 73GB 10,000RPM Ultra320 SCSI HDDs in RAID1
- Windows 2003 Server
- Oracle 8.1.7 Server
Database Description
An export file from a client's Oracle 8.1.7 database was used for testing the duration of the database import.
This Oracle dump file was approx 3.5 GB, and the dump file was located on a different HDD to the Oracle datafiles, during the import.
Import Using Default Database Configuration
A database instance was created using the default database scripts for the particular application being used, and the database dump was imported.
The duration of this import was 8 hours and 48 minutes (528 minutes).
Import Using Optimised Database
A new database instance was created, using the optimised configuration as described in this document.
After determining that the largest table was approx 1.4GB, a temporary 900MB rollback segment was created for use during the import.
The duration of this import was 1 hour and 9 minutes (69 minutes).
Summary
The configuration described in this document provided a significant reduction in the time taken to perform an import of a large database.
The durations of the timed import were:
|
Default database configuration:
| |
528 minutes
| |
Optimised database configuration:
| |
69 minutes
|
The optimised configuration has resulted in an 87% reduction in the duration of the database import, with additional improvements possible, as detailed below.
Further Improvements
The duration of the import could be further reduced as follows:
Purge unnecessary data from large tables
As detailed in the Pre-Export Activities section, unnecessary data should be purged from large tables prior to exporting the data out of the current database, to reduce the volume of data being exported and imported.
Export and truncate non-critical tables prior to import
As detailed in the Pre-Export Activities section, large non-critical tables should be exported and truncated prior to exporting the full content of the current database. These tables can then be imported into the new database at a later time, once it is in production again. This can significantly reduce the volume of data being exported and imported.
References
Oracle MetaLink (registration and valid Oracle support contract required)
navigation:
<prev
| 1
| 2
| 3
| 4
| next>
| 
