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.
