SQL Plus vs Toad IDE - Running insert in SQL Plus takes significantly longer

I'm running a query like this:

INSERT INTO TableA (colA, colB)
Select ColA, ColB 
from TableB

This is huge insert, as it is querying over 2 million rows an then inserting them into the table. My question is in regard to the performance. When I run the query in toad the query takes around 4-5 minutes to run.

When I run the query through sqlplus it is taking way longer. It has already been running 40 minutes+ and it is not finished. I've even done some minor tuning by setting the server output off in case that effected p开发者_JAVA技巧erformance.

Is there any tuning I should be aware of in regard to running the query via sqlplus? Is there any way to find out the difference in how the query is being executed/handled by the different clients?

Note: This is the only way I can transfer my data from table A to table B. I've looked into imp/exp and impdp/expdp and it is not possible in my situation.

Toad - v. 9.6.1.1 SqlPlus - 9.2.0.1.0 Oracle DB - 10g

This sounds like there is something else involved. My wild guess would be that your SQL*Plus session is getting blocked. Can you check v$lock to see if that is the case? There are a lot of scripts / tools to check to see what your session is currently spending its time on. Figure that out and then go from there. I personally like Tanel Poder's Snapper script (http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper).

It could be a thousand things. (@John Gardner: This is one reason why I'm not a huge fan of dba.stackexchange.com - you won't know if it's a programming issue or a DBA issue until you know the answer. I think it's better if we all work together on one site.)

Here are some ideas:

• Different session settings - parallel dml and parallel query may be enabled, forced, or disabled. Look at your login scripts, or look at the session info with select pdml_stats, pq_status, v$session.* from v$session;
• A lock, as @Craig suggested. Although I think it's easier to look at select v$session.blocking_session, v$session.* from v$session; to identify locks.
• Delayed block cleanout will make the second query slower. Run with set autotrace on. The db block gets and redo size are probably larger the second time (the second statement has some extra work to do, although this probably isn't nearly enough to explain the time difference).
• Buffer cache may make the second query faster. Run with set autotrace on, there may be a large difference in physical reads. Although with that much data the chances are probably small that a huge chunk of it is cached.
• Other sessions may be taking up a lot of resources. Look at select * from v$sessmetric order by physical_reads desc,logical_reads desc, cpu desc; Or maybe look at v$sysmetric_history.
• You may want to consider parallel and append hints. You can probably make that query run 10 times faster (although there are some downsides to that approach, such as the data being unrecoverable initially).
• Also, for testing, you may want to use smaller sizes. Run an insert with something like and rownum <= 10000. Performance tuning is very hard, it helps a lot if you can run the statements frequently. There are always some flukes and you want to ignore the outliers, but you can't do that with only two samples.
• You can look at some detailed stats for each run, but you may need to run the query with INSERT /*+ GATHER_PLAN_STATISTICS */.... Then run this to find the sql_id: select * from v$sql where sql_text like '%INSERT%GATHER_PLAN_STATISTICS%'; Then run this to look at the details of each step: select * from v$sql_plan_statistics_all where sql_id = '<sql_id from above>'; (In 11g, you can use v$sql_monitor, or even better, dbms_sqltune.report_sql_monitor.)

A really obvious point, but it's been known to trip people up... are there any indexes on tableA; if so are any of them unique; and if so did you commit or rollback the Toad session before running it again in SQL*Plus? Not doing so is an an easy way of getting a block, as @Craig suggests. In this scenario it won't ever finish - your 40+ minute wait is while it's blocking on the first row insert.

If there are any indexes you're likely to be better off dropping them while you do the insert and recreating them afterwards as that's usually significantly faster.

As other people have already suggested, there are a lot of things that could cause a statement that selects/inserts that much data to perform badly (and inconsistently). While I have seen Toad do things to improve performance sometimes, I've never seen it do anything so much faster, so I'm inclined to think it's more to do with the database rather than the tool.

I would ask the DBA's to check your session and the database while the slow statement is running. They should be able to give you some indication of what's happening - they'll be able to check for any problems such as locking or excessive log file switching. They'll also be able to trace both sessions (Toad and SQL Plus) to see how Oracle's executing those statements and if there are any differences, etc.

Depending what it is you're doing, they might even be able to help you run the insert faster. For example, it can be faster to disable an index, do the insert, then rebuild it; or it might be possible to disable logging temporarily. This would obviously depend on your exact scenario.