Oracle Very Slow with Inserts

Dear Experts,

We have an Oracle 11gR2 EE server. The server is Windows 2008 R2 with 1.2 TB of free space, and 24 GB of memory. We are running a lots of SQL scripts regularly on the server. Those scripts insert data from a set of tables in one schema to another set of tables in a different schema. Insert statements in scripts are always like: Insert into ... select ... from.  Sometimes we use nested queries, joins and Oracle functions in select part. Recently we have had a performance problem and realized that Insert statements with joins from more than one table are extremely slow. Insert statements with no joins have no problem. Our joins are mostly inner joins from one or two tables. What can be the problem?

Best Regards
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What can be the problem?
The server is Windoze 2008
What else?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Of course you already checked some typical join selects without the INSERT? The Insert does not change the query execution plan, besides adding the INSERT "overhead" of course.
PortletPaulEE Topic AdvisorCommented:
>>"Our joins are mostly inner joins from one or two tables. What can be the problem? "

investigate by using explain plans
slightwv (䄆 Netminder) Commented:
I agree with the execution plan.

Just in case you are not familiar with them, follow these steps and post the results.

In sqlplus:
--make the line size larger
set lines 200

explain plan for
select ... --the test of your entire select statement

--Then display the plan:
select * from table(dbms_xplan.display);
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.