Home » RDBMS Server » Performance Tuning » Insert Oddly Alters Explain Plan (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
Insert Oddly Alters Explain Plan [message #355947] Tue, 28 October 2008 12:13 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I am running into an odd problem in which the optimizer seems seems to make some odd decisions about how best to retrieve a set of data when the selection is part of an insert vs simply being a select statement.

Setup for my example:

CREATE OR REPLACE FUNCTION foo (p_var IN NUMBER)
   RETURN NUMBER DETERMINISTIC
IS
BEGIN
   RETURN p_var - 10;
END;

CREATE TABLE src (grp NOT NULL, val NOT NULL)
AS
SELECT     TRUNC (DBMS_RANDOM.VALUE (1, 100)) grp,
           TRUNC (DBMS_RANDOM.VALUE (1, 1000)) val
      FROM DUAL
CONNECT BY ROWNUM <= 100000;

CREATE INDEX src_idx
ON src(foo(val), grp);

CREATE TABLE dest
(val NUMBER,
val_count NUMBER,
gro_count NUMBER);


Now this is only an example to illustrate my question so you have to take a leap and pretend that the function foo actually has a cost significant enough to merit use of the index rather than the table if that is possible.

Now from this src table I want to get all the foo return values, a count of the number of times each value occurs and a count of the distinct number of grp values associated with each. The optimaizer would not normally choose to use the index for such a request but we can point it in that direction with an explicit index hint like so:

SELECT   --+INDEX(src src_idx)
         foo (val),
         COUNT (*),
         COUNT (DISTINCT grp)
    FROM src
   WHERE foo (val) IS NOT NULL
GROUP BY foo (val);


The explain plans looks like I would expect it does a full scan of the index and then a sort group by for the counts

SELECT STATEMENT  ALL_ROWS  Cost: 99,626  Bytes: 6,979  Cardinality: 997  		
	2 SORT GROUP BY  Cost: 99,626  Bytes: 6,979  Cardinality: 997  	
		1 INDEX FULL SCAN INDEX SRC_IDX Cost: 273  Cardinality: 97,691  


Now if I change this statement from a simple select to an insert based on the select like so:

INSERT INTO dest
   SELECT   --+INDEX(src src_idx)
            foo (val),
            COUNT (*),
            COUNT (DISTINCT grp)
       FROM src
      WHERE foo (val) IS NOT NULL
   GROUP BY foo (val);


My explain plan changes to:

INSERT STATEMENT  ALL_ROWS  Cost: 99,626  Bytes: 6,979  Cardinality: 997  			
	3 SORT GROUP BY  Cost: 99,626  Bytes: 6,979  Cardinality: 997  		
		2 TABLE ACCESS BY INDEX ROWID TABLE SRC Cost: 99,620  Bytes: 683,837  Cardinality: 97,691
			1 INDEX FULL SCAN INDEX SRC_IDX Cost: 273  Cardinality: 97,691  


It is the addition of the access by rowid that is confusing me. What is the point of this access? And why does Oracle only seem to want to do it when I am doing an insert?
Re: Insert Oddly Alters Explain Plan [message #355949 is a reply to message #355947] Tue, 28 October 2008 12:33 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I tried it in oracle apex website and for me both the plan looks the same as your first one. Oracle website apex version is running oracle 11.1.0.6.0.

Having said that never trust the plan you are getting from the explain plan. When optimiser kicks in it can choose a different plan. So try running both the queries but turning on the trace and do a tkprof on the trace file. Let us know with your observation.

Regards

Raj

[Edit:] I didn't notice the oracle version mentioned

[Updated on: Tue, 28 October 2008 12:35]

Report message to a moderator

Re: Insert Oddly Alters Explain Plan [message #355956 is a reply to message #355949] Tue, 28 October 2008 13:32 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Checked it out on APEX...never knew Oracle offered this and yeah this is getting the results I would expect....waiting on some permissions to be able to get the trace.

Based only on execution time it would seem that some extra work is going on during the insert (obviously)...the question is whether a ROWID look-up is contributing to this or not.
Re: Insert Oddly Alters Explain Plan [message #355974 is a reply to message #355956] Tue, 28 October 2008 15:45 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Explain Plan confirmed with Autotrace, must be an issue with 10.2 or at leas my version of 10.2 or some kind of set-up option though I have no idea of any configuration option which would lead to this behavior....
Re: Insert Oddly Alters Explain Plan [message #356377 is a reply to message #355974] Thu, 30 October 2008 09:43 Go to previous message
annagel
Messages: 220
Registered: April 2006
Senior Member
I posted this same question to AskTom the other day when I noticed the submit question option was enabled. Got a response saying this looks like a bug since it does not happen in 11g and a workaround. Posting the workaround here in case any one else happens to run into a similar situation:

INSERT INTO dest
   SELECT   f,
            COUNT (*),
            COUNT (DISTINCT grp)
       FROM (SELECT --+NO_MERGE INDEX(src src_idx)
                    foo (val) f,
                    grp
               FROM src
              WHERE foo (val) IS NOT NULL)
   GROUP BY f;


Which produces the following explain plan:

INSERT STATEMENT  ALL_ROWSCost: 30  Bytes: 16,983  Cardinality: 999  			
	3 SORT GROUP BY  Cost: 30  Bytes: 16,983  Cardinality: 999  		
		2 VIEW NAGEL. Cost: 28  Bytes: 170,000  Cardinality: 10,000  	
			1 INDEX FULL SCAN INDEX NAGEL.SRC_IDX Cost: 28  Bytes: 70,000  Cardinality: 10,000  


The rowid lookup is gone.

[Updated on: Thu, 30 October 2008 09:45]

Report message to a moderator

Previous Topic: Performance issues between Databases
Next Topic: alter system set events
Goto Forum:
  


Current Time: Thu Dec 08 20:22:25 CST 2016

Total time taken to generate the page: 0.15334 seconds