Oracle – Is my APPEND Hint Being Ignored? How To Find Out?

Why is the APPEND hint so great?

In a prior post, we extolled the virtues of the APPEND hint (AKA Direct Path Insert). It is a great option to use

• UNDO usage is negligible. It is only generated for index maintenance and meta data changes. The keyword is index maintenance – indexes generate UNDO.
• No REDO is used if the table involved ultimately has NOLOGGING. The keyword is “ultimately”, as LOGGING can be overridden at the database level with FORCE LOGGING, irrespective of the table level setting.

Oracle documentation outlines the differences between conventional and direct path loads in a very clear fashion. Database programmers should certainly read and understand it very well.

Hints may be ignored

As is the case with any hint, Oracle does not have to respect a hint. It tries to oblige, when it can. APPEND hint is no different. it is documented clearly as to when the hint will be ignored in the above documentation and at AskTom.

Specifically, let us copy the conditions under which it will ignore the APPEND hint and revert to conventional path load:

[BEGIN COPY FROM ORACLE DOCS]

Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:

• You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.
• Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.
• If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.
• The target table cannot be of a cluster.
• The target table cannot contain object type columns.
• Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.
• Direct-path INSERT into a single partition of an index-organized table (IOT), or into a partitioned IOT with only one partition, will be done serially, even if the IOT was created in parallel mode or you specify the APPEND or APPEND_VALUES hint. However, direct-path INSERT operations into a partitioned IOT will honor parallel mode as long as the partition-extended name is not used and the IOT has more than one partition.
• The target table cannot have any triggers or referential integrity constraints defined on it.
• The target table cannot be replicated.
• A transaction containing a direct-path INSERT statement cannot be or become distributed.

 [END COPY FROM ORACLE DOCS]

How to find out if your APPEND hint was honored?

Smart people can remember all of these restrictions and use the hint wisely (but I cannot remember). Fortunately, there is an easy way to check if the append hint was honored as Tom outlined in his AskTom site:

Consider this restriction from above:
• You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.
It says that if you changed an object within a transaction using a DIRECT PATH insert and then tried to access it in any way within the same transaction, you will receive an error:

ORA-12838: cannot read/modify an object after modifying it in parallel

We will exploit this knowledge to make the determination. Let us say you wanted to print out if APPEND hint was used or not used in a DIRECT PATH insert, you can do something like this:

DECLARE
    v_Count INT := 0;

BEGIN

    --
    --Try do to a DIRECT path insert with the APPEND hint
    --
    INSERT /*+ APPEND */ INTO my_table
    SELECT *
       FROM stage_table
       WHERE 0=1;

    --
    ----------Begin: Check and print if APPEND hit was honored --------------
    --
    --If APPEND hit was ignored and a direct path insert was used, we can select from the table
    --  when the transaction is still open...
    --If direct path insert just happened, it cannot be queried until the transaction is
    --  is either committed or rolled back and the following will be the error.
    --  ORA-12838: cannot read/modify an object after modifying it in parallel
    BEGIN
        SELECT COUNT(1)
        INTO v_Count
        FROM my_table
        WHERE 0 = 1;

        dbms_output.put_line('APPEND hint was ignored! Conventional path INSERT happened');

    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE = -12838 THEN --ORA-12838: cannot read/modify an object after modifying it in parallel
                dbms_output.put_line('APPEND hint was honored! Direct path INSERT happened');
            ELSE
                dbms_output.put_line('APPEND hint usage is inconclusive! Ran into an error.' || SQLERRM);
                RAISE;
            END IF;
    END;
    ----------End: Check and print if APPEND hit was honored --------------

    COMMIT;

END;

The comments explain how we perform the check.

Caution:

Just because it was honored when you set up your APPEND based SQL code, it does not mean that it will continue to work forever. You may unwittingly make a change that will result in the APPEND hint being ignored – e.g.: you add a referencial integrity constraint or a trigger on the table.

Even if APPEND was honored, if you went overboard with indexes on your table, the amount of UNDO used will be equivalent to the sum of index sizes put together for all indexes on the table, plus some overhead for meta-data maintenance. This could amount to a lot of UNDO depending on the number of rows you are INSERTing.

Also, even when the APPEND was honored, you could be generating REDO if the table was ultimately in LOGGING mode.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s