Strange Performance Behavior on INSERT INTO SELECT statement...

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 Received on Fri Apr 23 1999 - 16:10:32 CDT