Insert-Select much slower than same select on its own

[Database Administrator]

zzzzzz45@hotmail.com wrote:
> Hi,
>
> I'm doing an insert into a table like so:
>
> Insert into table
> (field1, field2...)
> Select value1, value2...
> from view
>
> The select on its own runs in under a second. The insert select takes
> ~50 seconds. The explain plans differ greatly. The stats on the tables
> are up-to-date.
>
> I've tried the all_rows and append hints to no avail.
>
> Can somebody please advise why this drastic deterioration in
> performance may be happening?
>
> The db version is Oracle9i Enterprise Edition Release 9.2.0.6.0 -
> Production
>
> Thanks,
> Mark

how many rows do you select?
does it take under a second second to fetch all the rows?
and are there any indexes on the target table?

[Database Administrator]

zzzzzz45@hotmail.com wrote:
> Hi,
>
> I'm doing an insert into a table like so:
>
> Insert into table
> (field1, field2...)
> Select value1, value2...
> from view
>
> The select on its own runs in under a second. The insert select takes
> ~50 seconds. The explain plans differ greatly. The stats on the tables
> are up-to-date.
>
> I've tried the all_rows and append hints to no avail.
>
> Can somebody please advise why this drastic deterioration in
> performance may be happening?
>
> The db version is Oracle9i Enterprise Edition Release 9.2.0.6.0 -
> Production
>
> Thanks,
> Mark

how many rows do you select?
does it take under a second second to fetch all the rows?
and are there any indexes on the target table?

[Database Administrator]

> how many rows do you select?

V. small number always (under 20)

> does it take under a second second to fetch all the rows?

Yes - in toad I can do the select, copy the rows into excel, print it,
get the print out from my printer, ball it up and throw it at my
monitor faster than oracle can get the rows into the table

> and are there any indexes on the target table?
I tried it with an identical empty table with no indexes and it made no
difference to performance

[Database Administrator]

> how many rows do you select?

V. small number always (under 20)

> does it take under a second second to fetch all the rows?

Yes - in toad I can do the select, copy the rows into excel, print it,
get the print out from my printer, ball it up and throw it at my
monitor faster than oracle can get the rows into the table

> and are there any indexes on the target table?
I tried it with an identical empty table with no indexes and it made no
difference to performance

[Database Administrator]

zzzzzz45@hotmail.com wrote:
> Hi,
>
> I'm doing an insert into a table like so:
>
> Insert into table
> (field1, field2...)
> Select value1, value2...
> from view
>
> The select on its own runs in under a second. The insert select takes
> ~50 seconds. The explain plans differ greatly. The stats on the tables
> are up-to-date.
>
> I've tried the all_rows and append hints to no avail.
>
> Can somebody please advise why this drastic deterioration in
> performance may be happening?
>
> The db version is Oracle9i Enterprise Edition Release 9.2.0.6.0 -
> Production
>
> Thanks,
> Mark

It would have been helpful if you had posted the two explain plans.

It would be helpful to know what indexes exist on the table into
which the insert is taking place.

Potentially this could be a case where HINTS are required. But I
wouldn't want to jump that far yet. As Jonathan Lewis has said, and
I hope I am not misquoting, FULLY HINT if you want to force a
particular execution path.

But first lets see those explain plans generated with DBMS_XPLAN.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Database Administrator]

zzzzzz45@hotmail.com wrote:
> Hi,
>
> I'm doing an insert into a table like so:
>
> Insert into table
> (field1, field2...)
> Select value1, value2...
> from view
>
> The select on its own runs in under a second. The insert select takes
> ~50 seconds. The explain plans differ greatly. The stats on the tables
> are up-to-date.
>
> I've tried the all_rows and append hints to no avail.
>
> Can somebody please advise why this drastic deterioration in
> performance may be happening?
>
> The db version is Oracle9i Enterprise Edition Release 9.2.0.6.0 -
> Production
>
> Thanks,
> Mark

It would have been helpful if you had posted the two explain plans.

It would be helpful to know what indexes exist on the table into
which the insert is taking place.

Potentially this could be a case where HINTS are required. But I
wouldn't want to jump that far yet. As Jonathan Lewis has said, and
I hope I am not misquoting, FULLY HINT if you want to force a
particular execution path.

But first lets see those explain plans generated with DBMS_XPLAN.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Database Administrator]

Hi,

I'm doing an insert into a table like so:

Insert into table
(field1, field2...)
Select value1, value2...
from view

The select on its own runs in under a second. The insert select takes
~50 seconds. The explain plans differ greatly. The stats on the tables
are up-to-date.

I've tried the all_rows and append hints to no avail.

Can somebody please advise why this drastic deterioration in
performance may be happening?

The db version is Oracle9i Enterprise Edition Release 9.2.0.6.0 -
Production

Thanks,
Mark

[Database Administrator]

zzzzzz45@hotmail.com wrote:
> Hi,
>
> I'm doing an insert into a table like so:
>
> Insert into table
> (field1, field2...)
> Select value1, value2...
> from view
>
> The select on its own runs in under a second. The insert select takes
> ~50 seconds. The explain plans differ greatly. The stats on the tables
> are up-to-date.
>
> I've tried the all_rows and append hints to no avail.
>
> Can somebody please advise why this drastic deterioration in
> performance may be happening?
>
> The db version is Oracle9i Enterprise Edition Release 9.2.0.6.0 -
> Production
>
> Thanks,
> Mark

how many rows do you select?
does it take under a second second to fetch all the rows?
and are there any indexes on the target table?

[Database Administrator]

> how many rows do you select?

V. small number always (under 20)

> does it take under a second second to fetch all the rows?

Yes - in toad I can do the select, copy the rows into excel, print it,
get the print out from my printer, ball it up and throw it at my
monitor faster than oracle can get the rows into the table

> and are there any indexes on the target table?
I tried it with an identical empty table with no indexes and it made no
difference to performance

[Database Administrator]

zzzzzz45@hotmail.com wrote:
> Hi,
>
> I'm doing an insert into a table like so:
>
> Insert into table
> (field1, field2...)
> Select value1, value2...
> from view
>
> The select on its own runs in under a second. The insert select takes
> ~50 seconds. The explain plans differ greatly. The stats on the tables
> are up-to-date.
>
> I've tried the all_rows and append hints to no avail.
>
> Can somebody please advise why this drastic deterioration in
> performance may be happening?
>
> The db version is Oracle9i Enterprise Edition Release 9.2.0.6.0 -
> Production
>
> Thanks,
> Mark

It would have been helpful if you had posted the two explain plans.

It would be helpful to know what indexes exist on the table into
which the insert is taking place.

Potentially this could be a case where HINTS are required. But I
wouldn't want to jump that far yet. As Jonathan Lewis has said, and
I hope I am not misquoting, FULLY HINT if you want to force a
particular execution path.

But first lets see those explain plans generated with DBMS_XPLAN.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org