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

Ed:

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). To give you an idea of the complexity of the view, the plan table goes down to level 217: but like I said, they were both identical on all rows except the first one. I'd dump you a printout but It'd take a bunch o' space.

Do you have any other ideas that I might could try to narrow down the source of the problem?

Ed Prochak wrote in message <7g4ejk$n5l$1_at_nnrp1.dejanews.com>...
>In article <7fqnis$fd1$1_at_fir.prod.itd.earthlink.net>,
> "Dave Cowden" <cowden_at_earthlink.net> wrote:
>> Hi.
>>
>> We have Oracle 8.0.5, Ent. Edition running on Windows NT with some very
>> complex views.
>> When attempting to transfer rows from the complex into a temporary table,
we
>> have
>> experienced some weird behavior:
>>
>> (1) SELECT * FROM <the view> takes about 6 seconds, repeatable after the
>> select is already in theSGA
>> (2) CREATE TABLE AS SELECT * FROM <the view> also returns in about 6
seconds
>> and correctly puts the rows into the destination table
>> (3) INSERT INTO <temp table> SELECT * FROM <the view> takes a whopping 47
>> seconds, no matter how many times it is executed, and sucks the CPU to
100%!
>>
>> *The view is complex but always returns only one row.
>> *Things I've tried:
>> *Adding/removing index from destination table.
>> *Rebuilding the destination table
>> *Restarting the Oracle instance
>> *Executing the INSERT INTO SELECT from a stored procedure instead of
sqlplus
>> *CACHING the destintaion table
>> *SETTING the destintation table to NOLOGGING
>> *Determined that the problem does not occur when selecting from other
tables
>> *Determined that there is some performance degredation in a similar
manner
>> when selecting from other views (this one is
>> just the worst example)
>> * tried writing a stored procedure to select the data from the view into
>> variables, and then insert using bind variables. This works
>> just as quick as a select (6 seconds)
>> * tried inserting into the destination table all literals: also works as
>> quick as a select.
>>
>> Does anybody know what might cause this behavior?
>>
>> TIA
>> Dave Cowden
>> Advanced Automation systems Engineer
>>
>>
>
>You never mentioned doing an autotrace to see where the performance hit
>occurs. That should give you a clue on what it is doing differently between
>the naked SELECT and the INSERT/SELECT.
>
>--
>Ed Prochak
>Magic Interface, Ltd.
>ORACLE services
>440-498-3702
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Tue Apr 27 1999 - 16:58:47 CDT