AbstractThis article focuses on system-level changes, init. this article describes how to improve the performance of the import utility of Oracle relational database system in three aspects: the change of ora parameters and the change of import options, it is of great significance for the majority of Oracle system administrators to improve efficiency in database fault recovery. Import and export are two supporting functions of the Oracle database system.Program, Export unload the data in the database to the operating system file, while the import utility restores the data from the export to the database. The import process sometimes takes several hours or even days to complete successfully. Unfortunately, in many cases, the import needs to recover the database from a fault. In this case, the time requirement is high, so this performance is not competent. During the two-year period in Oracle 7 system management, the author summarized several basic parameter changes that can reduce the import time. I. System-level changes
1Increase the db_block_siz parameter whenever possible during database reconstruction. The larger the block size, the less I/O cycles are required. This change is permanent, so the possible effects should be taken into account when modifying it.
2Create and use a large rollback segment, and disable other rollback segments (offline ). A rollback segment requires about 50% of the maximum table size.
3Place the database in noarchivelog mode before the import is complete. This reduces the time required to create and manage archive logs.
4Like the rollback segment, create several large redo log files. The larger the file, the better. Close some small redo log files. The larger the redo log file, the less log switching is required. Check the warning log information such as "thread 1 cannot allocate new log, sequence 17, checkpoint not complete", which means that the log file needs to be expanded or the number of logs needs to be increased.
5If possible, distribute rollback segments, table data, and redo log files on different hard disks. This reduces the possibility of I/O conflicts. Ii. Changes to the Intr. ora Parameter
1Set the value of log_checkpoint_interval to a value greater than the size of the redo log file. This value is usually defined by the block size of the Operating System (UNIX systems are mostly 512 bytes ). In this way, the checkpoint can be minimized (only executed during log switching ).
2Increase the value of sort_area_size. This increase depends on the status of other active processes on the machine and the amount of available idle memory. We can try 5-10 times to find the best value. If the switch (swapping) and page (paging) are started, the value is too large. 3. The import option is changed.
1Set commit = n. This will cause the import to be committed after each object (such as table) ends, rather than the buffer is full. This is why we need a large rollback segment.
2Use a large buffer value. This value depends on the system activity and database size. Usually a few megabytes is enough, but the value can be larger if there is enough memory. Similarly, we can check the page and switch to determine whether the value is too large. This will reduce the number of times the import process reads data into the file (each time it reads data of a buffer size ).
3Set indexes = n. This will speed up the import of non-index tables. Use the indexfile option to create an index after the data is imported. Sort_area
The _ size value must be large enough. Author: Bank of China Fujian Branch (Fuzhou 350005) |
