Optimising Oracle Imports - page 3
author: Martin "mpot" Pot
date: 25 October 2004
navigation:
<prev
| 1
| 2
| 3
| 4
| next>
Redo Log Files
Definition
Redo logs are key components in the recovery of your database. Redo logs contain both the redo and undo changes needed to make your database consistent. When a user commits a transaction, Oracle guarantees that the changes to the blocks are written to the redo logs. Furthermore, undo information needed to roll back uncommitted transactions are also stored in the redo logs/archive files.
Ideal Switching
Ideally, in normal operation, the redo logs should be switching approximately every 20 minutes.
During Import
However, during a database import, the redo logs will be heavily used, and recreating the redo logs with a larger size should reduce switching.
Configuration
|
Default value:
| |
2M
| |
Recommended value:
| |
20M
| |
Location:
| |
database build script
|
Database Build Script
If the database hasn't yet been built, edit your database build script, and change the log file size as follows:
create database SID
controlfile reuse
logfile 'e:\ora8\ORADATA\SID\redo01.log' size 20M reuse,
'e:\ora8\ORADATA\SID\redo02.log' size 20M reuse,
'e:\ora8\ORADATA\SID\redo03.log' size 20M reuse,
'e:\ora8\ORADATA\SID\redo04.log' size 20M reuse
Existing Database
If the database has already been built, you'll need to create new logs at the required size, then remove the existing logs. Note that at any time, the database must have at least two redo log groups. Therefore, if you have only two redo log groups, you'll need to create a third before any of the existing redo logs can be dropped.
Refer to the Oracle documentation (particularly Note 30910.1) for more information on doing this.
Separate HDD
If possible, move the redo logs onto a separate HDD from the database files, to reduce disk access contention during times of high database activity.
After Import
Depending on transaction volumes during normal business hours, 20MB redo logfile size may not required, and thus can be reset to something smaller. Note that 2MB redo logfile size is often found to be too small at many sites.
References
Metalink Note 30910.1 Recreating Database Objects
Metalink Note 93763.1 Tuning Considerations When Import Is Slow
Metalink Note 10698.1 Redo Log Guidelines
Sort Area Size
Definition
The SORT_AREA_SIZE is what Oracle allocates per user process for sorting data. Unlike the two previous parameters, the SORT_AREA_SIZE is outside of the SGA. If the sort_area_size is too small, the process has to then be sorted on disk in the user's temporary tablespace, thus resulting in physical disk IO.
Impact
A larger sort area will reduce swapping and paging.
Configuration
|
Default value:
| |
65536 bytes (ie, 64KB, if not defined)
| |
Recommended value:
| |
655360
| |
Parameter name:
| |
sort_area_size
| |
Location:
| |
init.ora
|
After Import
If this parameter is not defined in init.ora, it will defaults to 64KB.
Note that this should parameter should be reset to the default size after the import is complete.
References
Metalink Note 109907.1 How to Determine an Optimal SORT_AREA_SIZE
Metalink Note 100709.1 Top 8 init.ora Parameters Affecting Performance
Metalink Note 93763.1 Tuning Considerations When Import Is Slow
Redo Log Buffer Size
Definition
LOG_BUFFER specifies the amount of memory, in bytes, that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file.
Impact
In general, larger values for LOG_BUFFER reduce redo log file I/O, particularly with COMMIT=N option which will generates relatively long or numerous transactions. This option is discussed in later section. In a busy system, higher than default value is recommended.
Configuration
|
Default value:
| |
163840 bytes (160KB)
| |
Recommended value:
| |
5242880 (5MB)
| |
Parameter name:
| |
log_buffer
| |
Location:
| |
init.ora
|
References
Metalink Note 30753.1 Init.ora Parameter "LOG_BUFFER" Reference Note
Database Buffer Cache
Caution
Before adjusting any parameters affecting SGA (ie, data buffer cache, shared pool size) you should be aware how much physical memory is available for the database instance on the Oracle server.
Physical Memory
The suggestions in this document assume that the Oracle server has at least 2 GB physical memory installed, and it doesn't have any applications running other than the database instance.
Definition
The buffer cache is used by Oracle to hold copies of database blocks in memory
Impact
The DB_BLOCK_BUFFERS parameter, together with DB_BLOCK_SIZE, determines the total size of the buffer cache. The DB_BLOCK_BUFFERS parameter is measured in Oracle blocks, and specifies the number of database buffers in the buffer cache. Effective use of the buffer cache can greatly reduce the I/O load on the database.
DB_BLOCK_SIZE can be specified only when the database is first created, and the DB_BLOCK_BUFFERS parameter is used to control the size of the buffer cache.
Note that the default value for the application being migrated for DB_BLOCK_SIZE is 2048 bytes (ie, 2KB). With a default DB_BLOCK_BUFFERS of 6400, this means the database has a default buffer cache size of 12.5MB.
Configuration
With regards to the DB_BLOCK_BUFFERS parameter:
|
Default value:
| |
6400 blocks
| |
Recommended value:
| |
262144 for 2KB database block size (cache size of 512MB)
65536 for 8KB database block size (cache size of 512MB)
| |
Parameter name:
| |
db_block_buffers
| |
Location:
| |
init.ora
|
References
Metalink Note 100709.1 Top 8 init.ora Parameters Affecting Performance
Metalink Note 93763.1 Tuning Considerations When Import Is Slow
Metalink Note 30704.1 Init.ora Parameter "DB_BLOCK_BUFFERS" Reference Note
Metalink Note 62143.1 Understanding and Tuning the Shared Pool
Shared Pool Size
Definition
The shared_pool_size is measured in bytes, and is the memory that is allocated to Oracle for areas like the data dictionary, stored procedures, and statements. Comprising a large part of the SGA, the shared_pool_size is comprised of the dictionary cache and library cache, and just like db_block_buffers above, should not be set too low or too high.
Impact
The shared pool contains all SQL parsed information and cursors, and if too small, will cause excessive swapping and paging.
Configuration
|
Default value:
| |
12000000 bytes
| |
Recommended value:
| |
at least 268435456 (256MB)
| |
Parameter name:
| |
shared_pool_size
| |
Location:
| |
init.ora
|
References
Metalink Note 100709.1 Top 8 init.ora Parameters Affecting Performance
Metalink Note 62143.1 Understanding and Tuning the Shared Pool
navigation:
<prev
| 1
| 2
| 3
| 4
| next>
|
