Re: 11.2 change in direct insert behavior
Date: Sun, 26 Feb 2012 07:52:48 -0800 (PST)
Message-ID: <7c260c4e-6e0d-483d-8017-128625fe501a_at_b23g2000yqn.googlegroups.com>
On Feb 25, 6:17 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> (snip)
> select sid,type,id1,id2,lmode from v$lock where sid=131;
>
> SQL>
> SID TY ID1 ID2 LMODE
> ---------- -- ---------- ---------- ----------
> 131 TM 84239 0 6
> 131 TX 65548 4981 6
>
> APPEND hint honored and lock mode=6 Exclusive
>
> Essentially, the problem is that /*+ APPEND */ hint in the form that was
> ignored and didn't bother anyone, while providing a placebo to the
> developer, can now suddenly create an exclusive table lock.
Mladen,
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.
On my blog we recently had a discussion (among other things) about how
all descending indexes are in fact function based indexes. The prior
discussion in this thread jogged my memory on a related topic. I
remember someone back in 2000 suggesting that I create an index
similar to the following:
CREATE INDEX EMPLOYEE_CO ON T1 (EMPLOYEE_ID, CLOCK_OUT DESC);
I executed the above command in Oracle Database 8.0.5. OK, so in
8.0.5 I had a normal b*tree index named EMPLOYEE_CO - what happened
when the data in that database was exported and then imported into an
8.1.7.x database? Tom Kyte's book describes what happened:
http://books.google.com/books?id=mV6b43HuEAkC&pg=PA441#v=onepage&q&f=false
So, the change in behavior is due to an expected outcome of a new
Oracle Database feature.
<bs>
Another example - people occasionally joke about a FAST=TRUE parameter
- what if such a parameter (or hint) were ever implemented? Let's
assume that you have a developer that has a tendency to over-use hints
in SQL statements, so you tell the developer about the FAST hint -
that it is important to place the hint in front of the other hints.
The FAST hint instructs Oracle's optimizer that if it has evidence
that a different access path is better, that the optimizer is
permitted to ignore the hints.
Let's test the FAST hint. First, create the table with an index and
collect the statistics.
CREATE TABLE T1 AS
SELECT
ROWNUM C1,
ROUND(SIN(ROWNUM/180*3.1415),4) C2,
RPAD('A',200,'A') C3
FROM
DUAL
CONNECT BY
LEVEL<=1000000;
ALTER TABLE T1 MODIFY(C1 NOT NULL, C2 NOT NULL, C3 NOT NULL);
CREATE INDEX IND_T1_C2 ON T1(C2);
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,NO_INVALIDATE=>FALSE)
First the unhinted version of the query (actually with the
GATHER_PLAN_STATISTICS hint so that we are able to see the runtime
statistics in the execution plan):
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT /*+ GATHER_PLAN_STATISTICS */
C1,
C2
FROM
T1
WHERE
C2 BETWEEN 0.0000 AND 0.0500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST
+COST'));
SQL_ID a019rmc9pcx4q, child number 0
SELECT /*+ GATHER_PLAN_STATISTICS */ C1, C2 FROM T1 WHERE C2 BETWEEN 0.0000 AND 0.0500 Plan hash value: 3617692013
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A- Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | 2408 (100)| 15956 |00:00:00.14 | 31339 | 30305 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 25100 | 2408 (1)| 15956 |00:00:00.14 | 31339 | 30305 | ----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(("C2">=0 AND "C2"<=.05))
The unhinted version of the query completed in 0.14 seconds.
But the developer says that full table scans are bad - the table has
an index on the column, and the developer know that indexes provide
faster access to the data (and it is if we do not flush the buffer
cache):
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1) */
C1,
C2
FROM
T1
WHERE
C2 BETWEEN 0.0000 AND 0.0500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST
+COST'));
SQL_ID 6w9bx87b96btg, child number 0
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1) */ C1, C2 FROM T1 WHERE C2 BETWEEN 0.0000 AND 0.0500 Plan hash value: 236868917
| Id | Operation | Name | Starts | E-Rows |Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | 25082 (100)| 15956 |00:00:23.65 | 17054 | 8191 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 25100 | 25082 (1)| 15956 |00:00:23.65 | 17054 | 8191 |
|* 2 | INDEX RANGE SCAN | IND_T1_C2 | 1 | 25100 | 57 (0)| 15956 |00:00:01.98 | 1098 | 56 | -------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("C2">=0 AND "C2"<=.05)
The hinted version of the query completed in 23.65 seconds - the developer is happy because the optimizer selected to use the index on the table.
You as the DBA recognize the performance problem that the developer
has created, you show him how to measure the elapsed time, and tell
him about the FAST hint:
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SELECT /*+ GATHER_PLAN_STATISTICS FAST(T1 TRUE) INDEX(T1) */
C1,
C2
FROM
T1
WHERE
C2 BETWEEN 0.0000 AND 0.0500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST
+COST'));
SQL_ID 50xjysckq82zb, child number 0
SELECT /*+ GATHER_PLAN_STATISTICS FAST(T1 TRUE) INDEX(T1) */ C1, C2
FROM T1 WHERE C2 BETWEEN 0.0000 AND 0.0500 Plan hash value: 3617692013
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A- Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | 2408 (100)| 15956 |00:00:00.12 | 31339 | 30305 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 25100 | 2408 (1)| 15956 |00:00:00.12 | 31339 | 30305 | ----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(("C2">=0 AND "C2"<=.05))
The query completed in 0.12 seconds, the DBA is happy, the developer is happy, and the developer starts adding the FAST hint to all queries.
Now, what happens in Oracle Database 13WC when Oracle Corp. actually
implements the FAST hint?
</bs>
Charles Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sun Feb 26 2012 - 09:52:48 CST