Re: 11.2 change in direct insert behavior
Date: Sun, 26 Feb 2012 10:46:35 -0800 (PST)
Message-ID: <891fcab2-9e91-43d8-9ed5-7da251f3e7d6_at_s13g2000yqe.googlegroups.com>
On Feb 26, 12:08 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> On Sun, 26 Feb 2012 07:52:48 -0800, Charles Hooper wrote: > > I am sure that you are aware of this fact (so this is for everyone else > > who is not aware), but direct path insert with a VALUES clause is a new > > feature offered with Oracle Database 11.1: > >http://antognini.ch/2009/10/hints-for-direct-path-insert-statements/So, > > the change in behavior is due to an expected outcome of a new Oracle > > Database feature. > > Charles, when the hint the /*+ APPEND_VALUES */ is specified, without > statistics, the statistics is computed. If there are objects which cannot > converted to new version and re-compiled. Oracle is supposed to ignore > and notify Nagios, Bear and the operator Michale Moore are located under > $ORACLE_HOME/install,
Mladen,
You found something interesting - you will see different results from 11.1.0.7 and 11.2.0.2, and I think that Jonathan's comment supports the different results.
Create my test T1 table above. Try the following script on 11.1.0.7
and 11.2.0.2:
INSERT INTO T1 VALUES (1,1,'A');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
ROLLBACK;
INSERT /*+ APPEND_VALUES */ INTO T1 VALUES (1,1,'A');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
ROLLBACK;
INSERT /*+ APPEND */ INTO T1 VALUES (1,1,'A');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
ROLLBACK;
INSERT /*+ APPEND */ INTO T1 VALUES (1,1,'A');
SELECT
SID,
TYPE,
ID1,
ID2,
LMODE
FROM
V$LOCK
WHERE
SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1);
The goal of the above script is to display the execution plan for the
unhinted version of the INSERT VALUES command, an APPEND_VALUES hinted
version, and an APPEND hinted version, and then display the locks for
the session.
This is the result from 11.1.0.7:
SQL_ID bvzpus8q8z51z, child number 0
INSERT INTO T1 VALUES (1,1,'A')
| Id | Operation | Name | Cost |
-------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 1 | LOAD TABLE CONVENTIONAL | | |
-------------------------------------------------
Note
- cpu costing is off (consider enabling it)
SQL_ID aqdbznwxa6j5p, child number 0
INSERT /*+ APPEND_VALUES */ INTO T1 VALUES (1,1,'A')
| Id | Operation | Name | Cost |
-------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 1 | LOAD TABLE CONVENTIONAL | | |
-------------------------------------------------
Note
- cpu costing is off (consider enabling it)
SQL_ID bgb38x2q4cmzk, child number 0
INSERT /*+ APPEND */ INTO T1 VALUES (1,1,'A') Plan hash value: 3581094869
| Id | Operation | Name | Cost |
| 0 | INSERT STATEMENT | | 1 | | 1 | LOAD AS SELECT | | | | 2 | BULK BINDS GET | | |
-----------------------------------------
Note
- cpu costing is off (consider enabling it)
SID TY ID1 ID2 LMODE
--- -- ---------- ---------- ----------
335 AE 100 0 4 335 TM 71063 0 6 335 TX 393232 8051 6
From the above, it should be clear that the APPEND_VALUES hint had no effect on the execution plan, while the APPEND hint caused the execution plan to change. We end up with a mode 6 TM lock on the table.
This is the result from 11.2.0.2:
PLAN_TABLE_OUTPUT
SQL_ID bvzpus8q8z51z, child number 0
INSERT INTO T1 VALUES (1,1,'A')
| Id | Operation | Name | Cost |
-------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 1 | LOAD TABLE CONVENTIONAL | | |
-------------------------------------------------
Note
- cpu costing is off (consider enabling it)
SQL_ID aqdbznwxa6j5p, child number 0
INSERT /*+ APPEND_VALUES */ INTO T1 VALUES (1,1,'A') Plan hash value: 3581094869
| Id | Operation | Name | Cost |
| 0 | INSERT STATEMENT | | 1 | | 1 | LOAD AS SELECT | | | | 2 | BULK BINDS GET | | |
-----------------------------------------
Note
- cpu costing is off (consider enabling it)
SQL_ID bgb38x2q4cmzk, child number 0
INSERT /*+ APPEND */ INTO T1 VALUES (1,1,'A')
| Id | Operation | Name | Cost |
-------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 1 | LOAD TABLE CONVENTIONAL | | |
-------------------------------------------------
Note
- cpu costing is off (consider enabling it)
SID TY ID1 ID2 LMODE
--- -- ---------- ---------- ----------
127 AE 100 0 4 127 TO 65921 1 3 127 TO 5003 1 3 127 TM 75889 0 3 127 TX 393218 1513 6
From the above, it should be clear that the APPEND hint had no effect on the execution plan, while the APPEND_VALUES hint caused the execution plan to change. We end up with a mode 3 TM lock on the table.
The changes are documented in the following Metalink (MOS) articles:
Doc ID 842374.1
Doc ID 1391897.1
I agree that the change in behavior of the APPEND hint from 10.2 to 11.1 and again to 11.2 could cause unexpected behavior, even if that behavior is documented.
Charles Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sun Feb 26 2012 - 12:46:35 CST