Improving insert performance Oracle DB

You’re loading a large amount of data into a table and want to insert new records as quickly as possible. Use a combination of the following two features to maximize the speed of insert statements:

• Set the table’s logging attribute to NOLOGGING; this minimizes the generation redo for direct path operations (this feature has no effect on regular DML operations).

• Use a direct path loading feature, such as the following:

• INSERT /*+ APPEND */ on queries that use a subquery for determining which records are inserted

• INSERT /*+ APPEND_VALUES */ on queries that use a VALUES clause

• CREATE TABLE…AS SELECT

Here’s an example to illustrate NOLOGGING and direct path loading. First, run the following query to verify the logging status of a table. In this example, the table name is F_REGS:

Select table_name ,logging from user_tables where table_name = ‘F_REGS’;

Here is some sample output:

TABLE_NAME LOG

—————————— —

F_REGS YES

The prior output verifies that the table was created with LOGGING enabled (the default). To enable

NOLOGGING, use the ALTER TABLE statement as follows:

SQL> alter table f_regs nologging;

Now that NOLOGGING has been enabled, there should be a minimal amount of redo generated for direct path operations. The following example uses a direct path INSERT statement to load data into the table:

insert /*+APPEND */ into f_regs

select * from reg_master;

The prior statement is an efficient method for loading data because direct path operations such as INSERT /*+APPEND */ combined with NOLOGGING generate a minimal amount of redo.

Direct path inserts have two performance advantages over regular insert statements:

• If NOLOGGING is specified, then a minimal amount of redo is generated.

• The buffer cache is bypassed and data is loaded directly into the datafiles. This can significantly improve the loading performance.

The NOLOGGING feature minimizes the generation of redo for direct path operations only. For direct path inserts, the NOLOGGING option can significantly increase the loading speed. One perception is that NOLOGGING eliminates redo generation for the table for all DML operations. That isn’t correct. The NOLOGGING feature never affects redo generation for regular INSERT, UPDATE, MERGE, and DELETE statements.

One downside to reducing redo generation is that you can’t recover the data created via NOLOGGING in the event a failure occurs after the data is loaded (and before you back up the table). If you can tolerate some risk of data loss, then use NOLOGGING but back up the table soon after the data is loaded. If your data is critical, then don’t use NOLOGGING. If your data can be easily re-created, then NOLOGGING is desirable when you’re trying to improve performance of large data loads. What happens if you have a media failure after you’ve populated a table in NOLOGGING mode (and before you’ve made a backup of the table)? After a restore and recovery operation, it will appear that the table has been restored:

SQL> desc f_regs;

Name Null? Type

—————————————– ——– —————————-

REG_ID NUMBER

REG_NAME VARCHAR2(2000)

However, when executing a query that scans every block in the table, an error is thrown.

SQL> select * from f_regs;

This indicates that there is logical corruption in the datafile:

ORA-01578: ORACLE data block corrupted (file # 10, block # 198)

ORA-01110: data file 10: ‘/ora01/dbfile/O11R2/users201.dbf’

ORA-26040: Data block was loaded using the NOLOGGING option

As the prior output indicates, the data in the table is unrecoverable. Use NOLOGGING only in situations where the data isn’t critical or in scenarios where you can back up the data soon after it was created.

There are some quirks of NOLOGGING that need some explanation. You can specify logging characteristics at the database, tablespace, and object levels. If your database has been enabled to force logging, then this overrides any NOLOGGING specified for a table. If you specify a logging clause at the tablespace level, it sets the default logging for any CREATE TABLE statements that don’t explicitly use a logging clause.

You can verify the logging mode of the database as follows:

SQL> select name, log_mode, force_logging from v$database;

The next statement verifies the logging mode of a tablespace:

SQL> select tablespace_name, logging from dba_tablespaces;

And this example verifies the logging mode of a table:

SQL> select owner, table_name, logging from dba_tables where logging = ‘NO’;

How do you tell whether Oracle logged redo for an operation? One way is to measure the amount of redo generated for an operation with logging enabled vs. operating in NOLOGGING mode. If you have a development environment for testing, you can monitor how often the redo logs switch while the transactions are taking place. Another simple test is to measure how long the operation takes with and without logging. The operation performed in NOLOGGING mode should occur faster because a minimal amount of redo is generated during the load.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.