Slow Insert

[Ivor1]

Hi,

Got a Oracle proc that just selects 50 rows and inserts them into a table. If I run the SQL manually the select takes 30secs and the insert takes 5 secs. The select uses a DB link.

If I run the same SQL as a procedure I can see it doing the select but then its sits there for 12hrs to do the insert (I left it overnight).

No locks on the table, nothing waiting to commit. In Toad I can see it shows it waiting for a response from the DB link, but its done the select so doesnt need anything else via the DB link. Seems to be db link related,

[Spacecadet]

how big are the tables?

DBlinks can be a pain, typically whats happening in your case is that the server running the query will be importing the entire contents of the destination table to run the query on.

Probably a lot quick to force it to run one insert at a time using the actual values (ie use dynamic SQL) instead of doing the insert into... select from... as i guess you're trying to do.

[Ivor1]

The table being inserted into is empty, for some reason adding the INSERT INTO totally changes the explain plan from something that takes 2mins to run to 12hrs. Ive analysied the indexes, the tables on the source tables etc but its defo the INSERT statement that causes the problem. Its sods law as some of my daily inserts are millions of rows in one batch they run fine, yet when I want to insert 50 rows it has a fecking paddy.

[EqualOpportunities]

The table being inserted into is empty, for some reason adding the INSERT INTO totally changes the explain plan from something that takes 2mins to run to 12hrs. Ive analysied the indexes, the tables on the source tables etc but its defo the INSERT statement that causes the problem. Its sods law as some of my daily inserts are millions of rows in one batch they run fine, yet when I want to insert 50 rows it has a fecking paddy.

If you want to send over the two explain plans, and anything else pertinent, I can tell you what's going on...

[Ivor1]

To big to get on here, the difference is though as soon as I add the INSERT INTO, I see the following on the explain plan, also other changes.

Operation Rows Cost
INSERT STATEMENT optimizer Mode=CHOOSE 12k 27M

What I dont understand is how a INSERT can change the plan totally.

Ahh **** it Im going to lunch

[EqualOpportunities]

To big to get on here, the difference is though as soon as I add the INSERT INTO, I see the following on the explain plan, also other changes.

Operation Rows Cost
INSERT STATEMENT optimizer Mode=CHOOSE 12k 27M

What I dont understand is how a INSERT can change the plan totally.

Ahh **** it Im going to lunch

It is, clearly, to do with the table you are inserting into - I don't know what without looking more closely. Try doing your INSERT with the APPEND hint - that'll most likely speed it up.

[Ivor1]

Tried the APPEND before and no luck, needed quick fix so created table on the fly in the proc and now dont have the problem

[DimPrawn]

Upgrade to Microsoft SQL Server or something else that actually works.

HTH

[TheMonkey]

Upgrade to Microsoft SQL Server or something else that actually works.

HTH

Yeah even MySQL 3 was better than that.

I remember having to patch Oracle on a Solaris machine once. God that was painful.

[SallyAnne]

Hi,

Got a Oracle proc that just selects 50 rows and inserts them into a table. If I run the SQL manually the select takes 30secs and the insert takes 5 secs. The select uses a DB link.

If I run the same SQL as a procedure I can see it doing the select but then its sits there for 12hrs to do the insert (I left it overnight).

No locks on the table, nothing waiting to commit. In Toad I can see it shows it waiting for a response from the DB link, but its done the select so doesnt need anything else via the DB link. Seems to be db link related,

If you send your procedure through I'll have a look at it? It might be something stupid in the procedure that you just cant see anymore.