INSERT /*+ APPEND */ Hint Does Not Seem to Work Consistently

I ran the following SQL in an Oracle 11.2.0.1 database:

SQL> create table tab1
  2  (col1 number)
  3  /
 
Table created.
 
SQL> alter session set sql_trace = true
  2  /
 
Session altered.
 
SQL> insert /*+ append */ into tab1 select 1 from dual
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> insert /*+ append */ into tab1 values(2)
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         1
         2
 
SQL> alter session set sql_trace = false
  2  /
 
Session altered.
 
SQL>

Then I ran the trace file through tkprof. For some reason, the first SQL used a Direct Path load:

SQL ID: f57gyxg0uqcgb
Plan Hash: 1432430773
insert /*+ append */ into tab1 select 1 from dual
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.02       0.02          0          3         30           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.02       0.02          0          3         30           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8354  (ORACLE)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD AS SELECT  (cr=0 pr=0 pw=0 time=0 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD AS SELECT OF 'TAB1'
      1        FAST DUAL
 
... but the second one didn’t. The strange reformatting of the insert statement took place in tkprof, not in Blogger:

SQL ID: 6pb8960hm1hpy
Plan Hash: 0
insert /*+ append */ into tab1
values
(2)
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          7         25           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          7         25           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8354  (ORACLE)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=7 pr=0 pw=0 time=0 us)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD TABLE CONVENTIONAL OF 'TAB1'

P.S. Shortly after publishing this post, a number of people corrected it in the comments below. I won't be changing the post; if I did, the comments would then be out of place. However, I will be testing and understanding the comments before using them as the basis for another post in the near future.

DBA_FEATURE_USAGE_STATISTICS

Oracle introduced this view in version 10. It looks like this in version 11:

 

SQL> desc dba_feature_usage_statistics

Name                       Null?    Type

-------------------------- -------- ------------------

DBID                       NOT NULL NUMBER

NAME                       NOT NULL VARCHAR2(64)

VERSION                    NOT NULL VARCHAR2(17)

DETECTED_USAGES            NOT NULL NUMBER

TOTAL_SAMPLES              NOT NULL NUMBER

CURRENTLY_USED                      VARCHAR2(5)

FIRST_USAGE_DATE                    DATE

LAST_USAGE_DATE                     DATE

AUX_COUNT                           NUMBER

FEATURE_INFO                        CLOB

LAST_SAMPLE_DATE                    DATE

LAST_SAMPLE_PERIOD                  NUMBER

SAMPLE_INTERVAL                     NUMBER

DESCRIPTION                         VARCHAR2(128)

 

SQL>

 

As its name suggests, it allows you to see if a database uses a particular Oracle feature or not. In Oracle 11, it has over 150 entries:

 

SQL> l

  1  select count(*)

  2* from dba_feature_usage_statistics

SQL> /

 

  COUNT(*)

----------

       152

 

SQL>

 

Some of the features reported are shown below:

 

SQL> l

  1* select name from dba_feature_usage_statistics

SQL> /

 

 

NAME

-------------------------------------------------------

Encrypted Tablespaces

MTTR Advisor

Multiple Block Sizes

OLAP - Analytic Workspaces

OLAP - Cubes

Oracle Managed Files

Oracle Secure Backup

Parallel SQL DDL Execution

Parallel SQL DML Execution

Parallel SQL Query Execution

Partitioning (system)

Partitioning (user)

Oracle Text

PL/SQL Native Compilation

Real Application Clusters (RAC)

Recovery Area

Recovery Manager (RMAN)

RMAN - Disk Backup

RMAN - Tape Backup

Etc

 

I checked in a database where I did not think that SQL Loader had been used with the Direct Path option. The value in the DETECTED_USAGES column was zero:

 

SQL> l

  1  select detected_usages

  2  from dba_feature_usage_statistics

  3  where name =

  4* 'Oracle Utility SQL Loader (Direct Path Load)'

SQL> /

 

DETECTED_USAGES

---------------

              0

 

SQL>

 

I checked in a database where I had used SQL Loader with the Direct Path option a few times. The value in the DETECTED_USAGES column seemed far too high:

 

SQL> l

  1  select detected_usages

  2  from dba_feature_usage_statistics

  3  where name =

  4* 'Oracle Utility SQL Loader (Direct Path Load)'

SQL> /

 

DETECTED_USAGES

---------------

            231

 

SQL>

 

I will do some research and once I know how this figure is calculated, I will return to this post and update it.