Re: Strange Performance Behavior on INSERT INTO SELECT statement...

Thanks everybody for the responses. I am inclined to think that there MUST be some difference in the execution of the view, because very slight changes in the view source will result in very long run times ( the view results in a bunch of joins and group bys that make the difference between 6 seconds and 1 minute-- the 6 seconds is the result of a lot of time spent optimizing the view hierarchy)

I will try to generate an actual trace file. If will be a little while because I'm travelling right now and wont get a chance to get back to the customer site for a few days. If the trace doesnt show a difference, that explain plan didnt turn up, then i'll try to post the trace of the offending insert into select.

Thanks everybody!
Dave

Jonathan Lewis wrote in message
<925330897.18518.1.nnrp-10.9e984b29_at_news.demon.co.uk>...
>Unfortunately EXPLAIN PLAN does not always tell
>the truth. Generate the trace file whilst running the
>INSERT, and running the CREATE.
>
>Newer versions of Oracle include
> STAT #n lines after statements complete.
>The newest versions of Oracle include execution
>path details, including the number of rows examined
>in the row set.
>
>By looking at these lines ('fold' the file if necessary),
>you may find indications that the two access paths
>are different.
>
>Please keep us informed of the results.
>
>
>--
>
>Jonathan Lewis
>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
>Ed Prochak wrote in message <7g6v2h$vh2$1_at_nnrp1.dejanews.com>...
>>>
>>> thanks for the reply: i've been really trying to work this one out.
>>> I forgot to mention but did do a plan trace on both the select and
>>> insert, and they were identical except the first line (which was the
>>> insert of course).
>>>
>>> Do you have any other ideas that I might could try to narrow down
>>> the source of the problem?
>>>
>
>
>
Received on Wed Apr 28 1999 - 16:53:41 CDT