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

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 - 08:37:57 CDT