Striving for Optimal Performance

Up to Oracle Database 10g Release 2, direct-path inserts are supported only by INSERT INTO … SELECT … statements (including multitable inserts), MERGE statements (for the part inserting data), and applications using the OCI direct-path interface (for example, the SQL*Loader utility). At the statement level two methods are available to specify that a direct-path insert has to be used:

• Specify the APPEND hint in the SQL statement
• Execute the SQL statement (actually, at least the INSERT part) in parallel

Let’s have a look to an example. Notice that:

• The APPEND hint is used to execute a direct-path insert.
• The APPEND hint does not work with a “regular” INSERT statement that uses the VALUES clause.
• To check whether the direct-path insert is performed, the modified table is queried without committing (or rolling back) the transaction. As a result, after a direct-path insert the database engine raises an ORA-12838.

SQL> SELECT * FROM v$version WHERE rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SQL> CREATE TABLE t (n NUMBER);

SQL> INSERT /*+ append */ INTO t SELECT 1 FROM dual;

SQL> SELECT * FROM t;
SELECT * FROM t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> COMMIT;

SQL> INSERT /*+ append */ INTO t VALUES (2);

SQL> SELECT * FROM t;

         N
----------
         1
         2

Strangely enough, at least for me, in Oracle Database 11g Release 1 the behavior of the APPEND hint has changed. In fact, it is accepted also for a “regular” INSERT statement that uses the VALUES clause. Let’s run the same test as before to illustrate the new behavior.

SQL> SELECT * FROM v$version WHERE rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

SQL> CREATE TABLE t (n NUMBER);

SQL> INSERT /*+ append */ INTO t SELECT 1 FROM dual;

SQL> SELECT * FROM t;
SELECT * FROM t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> COMMIT;

SQL> INSERT /*+ append */ INTO t VALUES (1);

SQL> SELECT * FROM t;
SELECT * FROM t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Even more strange, in Oracle Database 11g Release 2 the behavior of the APPEND hint was reverted to the pre-11g one! But, since the feature is really useful in some situations, a new hint called APPEND_VALUES is available. The following example illustrates the new behavior.

SQL> SELECT * FROM v$version WHERE rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> CREATE TABLE t (n NUMBER);

SQL> INSERT /*+ append */ INTO t SELECT 1 FROM dual;

SQL> SELECT * FROM t;
SELECT * FROM t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> COMMIT;

SQL> INSERT /*+ append */ INTO t VALUES (2);

SQL> SELECT * FROM t;

         N
----------
         1
         2

SQL> COMMIT;

SQL> INSERT /*+ append_values */ INTO t VALUES (3);

SQL> SELECT * FROM t;
SELECT * FROM t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

An execution plan describes the operations carried out by the SQL engine to execute a SQL statement. Every time you have to analyze a performance problem related to a SQL statement, or simply question the decisions taken by the query optimizer, you must know the execution plan. Whenever you deal with an execution plan, you carry out three basic actions: you obtain it, you interpret it, and you judge its efficiency.

I have always found it surprising how little documentation there is about how to interpret execution plans, especially since there seem to be so many people who are unable to correctly read them. I addressed this problem not only in Chapter 6 of my book, Troubleshooting Oracle Performance, but also by presenting this very same topic at several conferences and user-group meeting (e.g. UKOUG, Orcan and SIOUG). The last time I presented it, it was few days ago at Oracle OpenWorld in San Francisco. By the way, big thanks to everyone who attended my presentation! It was really good to see a packed room. That said, I’m truly sorry that Oracle has not been able to provide us with a decent room. I was embarrassed for them…

The aim of this short post is to point out that I just uploaded the slides that I presented at different events here. Since the slides themselves contain few explanations and, therefore, are not very useful without additional information, I also added comments whenever necessary (e.g. where there is an execution plan). I hope you find that document useful.