What we are talking about here is a serious behavior change how statements like...
insert /*+ append */ into t values (1)...are handled in 11G.
Saving yourself from yourself... not anymore
In previous Oracle versions, the append hint in the cases like above was simply ignored for some good reasons. The hint is being obeyed in 11G.
To better elaborate what that means, let's take the following example:
begin...and compare how behavior will change in 11G.
for i in 1 .. 1000
loop
insert /*+ append */ into t values (i);
commit;
end loop;
end;
First of all, I've seen legacy code like the above in many places where developers were putting append hint everywhere they could because they heard it is like fast=true thing.
After you upgrade, you might expect the following:
If you don't do commit after each row -- the code will break itself with ORA-12838: cannot read/modify an object after modifying it in parallel.
If you do commit (as in my example) -- you have all chances to grow the table beyond the skies as each direct path insert writes beyond the HWM (in 11G, the above code will grow the table by 1000 blocks each time it is executed).
Serious locking issues as each direct path insert has to obtain an exclusive lock on the table first.
The above might produce quite an unpleasant surprises for some of you...
Posts
There is hidden parameter _direct_path_insert_features, which seems controls direct path behaviour. alter session set "_direct_path_insert_features" = 1 turns off direct path for insert .. values().
ReplyDeleteSlava, interesting.
ReplyDeleteLooks like that parameter was introduced in 11G.
Indeed, setting it to 1 seems to be reverting the behavior.
There is a one more pitfall with this feature, try to use
ReplyDeleteforall ... save exceptions
insert /*+append*/ into...
and you will be given "ORA-38910: BATCH ERROR mode is not supported for this operation"
The deeper into the wood you go, the more timber seems to grow :-)
Here is an extract of the 11.1 documentation related to direct-path insert: "Notes: Direct-path INSERT supports only the subquery syntax of the INSERT statement, not the VALUES clause."
ReplyDeleteGregory,
ReplyDeleteit will be (A) bug in the documentation or (B) bug in the RDBMS.
Given that that behavior is not compatible with previous Oracle versions and there were good reasons it wasn't behaving the way it does now -- I tend to think it is B (this is what Tom Kyte thinks as well).
Though there might be people who thinks it is A.
Obviously I would say that's a bug of the documentation or more likely an undocumented new feature:
ReplyDelete* Don't use the hint if you don't want to perform a direct-path write; Bad developers will finally pay for their bad coding practices. They deserve it!
* It works with insert of arrays in the values clause: (1) In Pro*C and (2) In PL/SQL (If you don't use the save exceptions clause of the forall command, as Apex mentioned).
I never load more that one row at a time, so I don't feel concern. Now imagine you play with a big array of data in memory and want to flush it in a table. Why would consider being able to perform a direct path write for that operation an Oracle 11g bug? If you don't have to?
Gregory,
ReplyDeletethere are other ways for insert append'ing array data ...
declare
l_buffer number_table;
begin
l_buffer:=number_table();
select level bulk collect into l_buffer
from dual
connect by level <= 10000;
insert /*+ append */ into t
select *
from table(cast(l_buffer as number_table));
end;
Ppipeline functions works as well.
7006959 is a reference bug number
ReplyDeleteI suspect the purpose of this new feature is to allow PL/SQL FORALL constructions, if you can avoid the apparently still buggy SAVE EXCEPTIONS clause.
ReplyDeleteI agree though that anyone who has coded APPEND hints out of context in the past deserves what they get. I rather wish something similar would happen one day with NOLOGGING.
Hello, Alex,
ReplyDeleteI just found a note 842374.1 describing this behavior.