Author: Mladen Dennis
Import/Export is amongst the most frequent task being accomplished by every database administrator. Performing import/export operations is a common task but somehow performing quick and efficient import/export operations is really critical and we usually keep sitting for hours waiting for import or export to get completed. In this article we will discuss some great tips that will help you in quick and efficient monitoring of import process and this enabling you to speed up the import process.
Setting Import File Parameters:
You can gain tremendous performance boost by setting COMMIT=N in the import parameter file for those database tables that can not commit until data loading gets completed. However larger tables will not benefit from this option because of rollback/undo space overhead.
You can also avoid time consuming ANALYZE statements by setting ANALYZE=N in the import parameter file.
Create Indexes after Import:
You can also boost up the process by creating indexes after your data has finished importing. Setting indexes=n will postpone the creation of your indexes until the import process completes. This option will prevent the overhead of maintaining indexes for the tables that already have indexes.
You can also create an indexfile in order to create indexes after data is imported. You can do this by setting INDEXFILE to a filename and then do importing. This will create a file containing index definitions but no data will be imported. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
No Archivelog Mode:
Stops redo log archiving if it is running. Keeping your database in no archivelog mode for the duration of the import enhances the speed of import process.
Use Large Rollback Segment:
You can enhance import speed by using larger rollback segments. Use a large rollback segment, app 50% the size of the largest table being imported.
Use Large Buffer Parameters:
You can make your import process do more work before performing disk access by setting buffer to larger value. Set the LOG_BUFFER to a big value and restart oracle.
Reduce DB_FILES:
You can keep the db writer batch size down by reducing the DB_FILES to a minimum. In this way db writer will keep I/O moving instead of freezing the system to write large chunks.
Flat Files:
You can dump the non LOB based database tables to flat file by using a custom pro*c program or by using the Copy command. Afterwards you can load data into your target database by using SQL*LOADER.
BIG Tablespace:
You can create a BIG tablespace with a BIG rollback segment as big as your biggest table. All other rollback segments should be set to offline except the SYSTEM rollback segment of course.
Oracle 10g Data Pump:
Oracle 10g Data Pump has tremendously improved the performance of import. This is because Data Pump Import uses the Direct Path method of loading instead of using only conventional mode inserts.
Monitor Import Rate:
Oracle Import Utility usually takes hours for very large tables and we need to track the execution of Oracle Import Process. Below option can help you monitor the rate at which rows are being imported from a running import job.
SELECT SUBSTR(SQL_TEXT,INSTR(SQL_TEXT,'INTO "'),30) TABLE_NAME, ROWS_PROCESSED, ROUND((SYSDATE-TO_DATE(FIRST_LOAD_TIME,'YYYY-MM-DD HH24:MI:SS'))*24*60,1) MINUTES, TRUNC(ROWS_PROCESSED/((SYSDATE-TO_DATE(FIRST_LOAD_TIME,'YYYY-MM-DD HH24:MI:SS'))*24*60)) ROWS_PER_MINUTE
FROM SYS.V_$SQLAREA
WHERE SQL_TEXT LIKE 'INSERT %INTO "%' AND COMMAND_TYPE = 2 AND OPEN_VERSIONS > 0;
Cloning:
Cloning your Oracle database with RMAN is a much quicker option than to import and then export your database data.
Transportable Tablespaces:
Oracle Transportable Tablespaces enables you to copy whole tablespaces between databases in the time it takes to copy the datafiles.
Redo Log Files:
We are well aware of the fact that redo logs are key components in database recovery as they contain both the redo and undo changes needed to make your database consistent. However, the redo logs are heavily used during a database import and therefore recreating the redo logs with a larger size should reduce switching. It is recommended to create several large redo log files at the required size.
Separate physical disk:
It is also advised to place the file to be imported on a separate physical disk from the oracle data files.
Set _disable_logging = true:
In init.ora file setting setting _disable_logging = true makes the call to write redo but actually file-write it not made. _disable_logging=true is an undocumented parameter that can be used to speed import operations. However you should beware of the fact that the resulting import will be unrecoverable. If you do not remove "_disable_logging=true" from init.ora and oracle closes without a shutdown immediate, abort or a crash then you can lose the whole database. Without logging if you have any kind of server crash, then there is no way back and therefore you should make sure that you have a valid backup before using this parameter and use it at your own risk.
Related Tools:
- Magical Oracle Export Import with a GUI Based Oracle Export Import Tool!!
- Improve Oracle Import/Export Speed!!
- An Ideal Configuration Management Solution for Databases, Download Free Now!!
Click here for more Oracle Tools
Related Articles:
- Effectively Prevent Oracle Minimum Downtime Migration Error!!
- Step by Step Guide for Super fast Database Cloning!!
- Go beyond running exp and imp - Follow Database Migration Tips!!
- Beware of Worse Oracle performance after Migration!!
- Speed Up Oracle Data Loading!!
- Enhance performance of Oracle Data Load
Click here for more Oracle Articles More Oracle Articles, Database Articles and DBA Tips
Database Security: Step by step guideline
Great Tips on Oracle Real-Time SQL Monitoring!!
DBA Tips for Changing Column Order in Oracle!!
Oracle-SQL Guide: Look out for Fragmented Indexes
SQL in Action II – Multi-Table Queries
|
