Previous | Table of Contents | Next |
Archiving allows you to recover your database in case of a media failure and should be used as a general good practice. However, when performing a batch data load, you have to realize that the operations involved are a large amount of INSERTs, which you can easily regenerate by re-executing the data load. It will definitely help to disable archiving for the duration of the database load.
Caution:
If there are other transactions going on concurrently with your batch job and a media failure occurs during the load, you may not be able to recover the other transactions.
Tip:
Use the following to put the database in ARCHIVELOG mode:On UNIX, C: > svrmgrl SVRMGR> connect internal SVRMGR> shutdown SVRMGR> startup mount SVRMGR> alter database archivelog SVRMGR> alter database open;
Tip:
Use the following to put the database in NOARCHIVELOG mode:On UNIX, C: > svrmgrl SVRMGR> connect internal SVRMGR> shutdown SVRMGR> startup mount SVRMGR> alter database noarchivelog SVRMGR> alter database open;
Tip:
If a table uses the BLOB and the CLOB datatypes, you can set the LOB portions of the table to be in nologging.
Use the following tips to enhance the performance of data loads:
Tip:
There are two ways you can change the high-water mark: drop and re-create the table, or truncate the table.
Select * From sales (part4) Where month between 10 and 12;
Caution:
The suggestion in the previous example should be used carefully, because if for some reason you change the information stored in a partition or rearrange the partitions such that part4 is no longer the partition, the previous query will not work.
Consider rearranging the partition structure of a partitioned table after periodic use.
Tip:
Use the Alter table command to add, drop, exchange, move, modify, rename, split, and truncate partitions.
The definition of a very large database keeps changing because operating systems and hardware enhancements continue at a rapid pace, and the database size is not a factor anymore for defining the database as being large. It is more realistic to look at the recovery time of the database and define a database to be large if it cannot be recovered in 24 hours. VLDB systems need special considerations as compared to smaller systems, and some of the suggestions in dealing with them might even be opposite of what a DBA would have done for smaller databases.
A VLDB typically consists of:
Tip:
Codes tables with more or less the same size should be placed in a tablespace whose default storage parameters are appropriate for all of them. This will improve the reusability of extents.
Tip:
Codes tables are static in nature and therefore it would be beneficial to use a low pctfree setting.
Tip:
If large transaction tables are truncated and then reloaded with information, set pctfree to a low value; otherwise set pctfree to a high value.
Tip:
If a large transaction table grows at a constant rate, set pctincrease to 0. Otherwise, set it to an appropriate non-zero value.
A VLDB usually has three different types of transactions. Table 33.3 shows the rollback segments that should be used for these types of transactions.
Transaction Type | Rollback Segment |
---|---|
Batch loads | Large, dedicated rollback segments |
Batch aggregates | Medium-sized rollback segments |
Small and short user transactions | A lot of small rollback segments |
Previous | Table of Contents | Next |